Ryan's Java world!

?

學(xué)習(xí)使用Java DataBase

在前幾天,轉(zhuǎn)貼了java網(wǎng)站上一篇介紹使用Java DB的文章,http://blog.matrix.org.cn/page/icess?entry=using_java_db_in_desktop 里面介紹了如何在桌面程序中使用java DB,今天使用了一下感覺很不錯(cuò).

但上面那篇文章中,是通過一個(gè)桌面程序來介紹java DB的,看的時(shí)候可能會(huì)感到比較混亂,今天我就按照他的例子,從中拿出 介紹java db的代碼, 來看看如何使用它吧!

在看如何使用java db以前,先看看java db 是什么, 有什么特點(diǎn).

Java DB is Sun's supported distribution of the open source Apache Derby 100% Java technology database. It is fully transactional, secure, easy-to-use, standards-based -- SQL, JDBC API, and Java EE -- yet small, only 2MB. The Apache Derby project has a strong and growing community that includes developers from large companies such as Sun Microsystems and IBM as well as individual contributors.

Apache Derby 項(xiàng)目主頁:http://db.apache.org/derby/

Java DB主頁: http://developers.sun.com/prodtech/javadb/

Java DB 的特性以及優(yōu)點(diǎn):

?
Sun Java DB is based on the open source Apache Derby project. It provides a full-featured, robust, small-footprint Java database management system that is cost effective and simple to deploy.

? Java DB is written in Java to take advantage of Java's write once, run anywhere promise. Java DB supports J2SE, J2EE and J2ME standards.

? Java DB technology adheres to database standards such as JDBC and ANSI SQL standards. This means Java DB provides the functionality expected of a sophisticated relational database, including SQL syntax, transaction management, concurrency, triggers, and backups. It also means that it is easy to upgrade an application using Java DB to other standards-based databases, such as Oracle and DB2.

? Java DB is easy to use and requires zero-administration. This eliminates the need for a database administrator.

? The flexibility to support both embedded and client-server mode allows Java DB to adapt to diverse deployment scenarios. In embedded mode, Java DB runs on the same JVM as the application and users may not even be aware that they are accessing a relational database.

? Java DB provides a numbers of security mechanisms including database file encryption, authentication through either external LDAP directory or built-in repository, and authorization. In addition, access to the database can also be controlled at read-only or read-write level.

? Java DB allows users to define trigger to ensure referential integrity. It also supports Java stored procedures.

? With a foot-print of 2MB, Java DB is a perfect fit for desktop applications, mobile solutions and small devices that require a full featured, robust database.

? Java DB is available for free under the Apache license and requires no administration if embedded and little if used in client-server mode.

? Sun offers full, 24 x 7 support for Java DB.

可以看出Java DB的優(yōu)點(diǎn)還是很多的.以后在sun 的軟件中都帶有Java DB了.

下面來看看如何使用Java? DB吧.

嵌入式數(shù)據(jù)庫和一般的數(shù)據(jù)庫不同,在使用以前,需要指定一個(gè)存放數(shù)據(jù)的目錄和指定數(shù)據(jù)庫的配置文件.

配置文件可以很簡單:不用解釋了 如下:

# Sample ResourceBundle properties file

user=

addressuser

password=

addressuser

derby.driver=

org.apache.derby.jdbc.EmbeddedDriver

derby.url=

jdbc:derby:

db.table=

ADDRESS

db.schema=

APP

當(dāng)然了 以上配置信息,也可以用一個(gè)Property 屬性對(duì)象來構(gòu)造,然后在得到數(shù)據(jù)庫連接的時(shí)候把該對(duì)象傳遞進(jìn)去也是可以的. 不過使用配置文件是為了方便修改相關(guān)配置信息而設(shè)定的.

通過如下方法來設(shè)置 數(shù)據(jù)庫數(shù)據(jù)的存放目錄:

private?void?setDBSystemDir()?{
????????//?decide?on?the?db?system?directory
????????//String?userHomeDir?=?System.getProperty("user.home",?".");
????????String?userHomeDir?=?System.getProperty("user.dir",?".");
????????String?systemDir?=?userHomeDir?+?"/.addressbook";
????????System.setProperty("derby.system.home",?systemDir);
????????
????????//?create?the?db?system?directory
????????File?fileSystemDir?=?new?File(systemDir);
????????fileSystemDir.mkdir();
?}

然后就是加載數(shù)據(jù)庫驅(qū)動(dòng)了, Java DB的數(shù)據(jù)庫驅(qū)動(dòng)是集成在一起的,也是通過 Class.forName() 的方式加載的,內(nèi)嵌的驅(qū)動(dòng)名字是org.apache.derby.jdbc.EmbeddedDriver 本例子中使用下面的方法加載驅(qū)動(dòng).
?

????private?void?loadDatabaseDriver(String?driverName)?{
????????//?load?Derby?driver
????????try?{
????????????Class.forName(driverName);
????????}?catch?(ClassNotFoundException?ex)?{
????????????ex.printStackTrace();
????????}
????????
????}

這樣,加載了驅(qū)動(dòng)以后,只有得到Connection就可以操作數(shù)據(jù)了.

在操作數(shù)據(jù)以前,我們先建立一個(gè) 數(shù)據(jù)表, 使用的sql 語句如下:

????private?static?final?String?strCreateAddressTable?=
????????????"create?table?APP.ADDRESS?("?+
????????????"????ID??????????INTEGER?NOT?NULL?PRIMARY?KEY?GENERATED?

???????????? ALWAYS?AS?IDENTITY?(START?WITH?1,?INCREMENT?BY?1),"?+
????????????"????LASTNAME????VARCHAR(30),?"?+
????????????"????FIRSTNAME???VARCHAR(30),?"?+
????????????"????MIDDLENAME??VARCHAR(30),?"?+
????????????"????PHONE???????VARCHAR(20),?"?+
????????????"????EMAIL???????VARCHAR(30),?"?+
????????????"????ADDRESS1????VARCHAR(30),?"?+
????????????"????ADDRESS2????VARCHAR(30),?"?+
????????????"????CITY????????VARCHAR(30),?"?+
????????????"????STATE???????VARCHAR(30),?"?+
????????????"????POSTALCODE??VARCHAR(20),?"?+
????????????"????COUNTRY?????VARCHAR(30)?"?+
????????????")";

然后使用如下方法來建立數(shù)據(jù)庫和數(shù)據(jù)表:

????private?boolean?createTables(Connection?dbConnection)?{
????????boolean?bCreatedTables?=?false;
????????Statement?statement?=?null;
????????try?{
????????????statement?=?dbConnection.createStatement();
????????????//?創(chuàng)建表格...
????????????statement.execute(strCreateAddressTable);
????????????bCreatedTables?=?true;
????????}?catch?(SQLException?ex)?{
????????????ex.printStackTrace();
????????}
????????
????????return?bCreatedTables;
????}
????private?boolean?createDatabase()?{
????????boolean?bCreated?=?false;
????????Connection?dbConnection?=?null;
????????
????????String?dbUrl?=?getDatabaseUrl();
????????dbProperties.put("create",?"true");
????????
????????try?{
????????????dbConnection?=?DriverManager.getConnection(dbUrl,?dbProperties);
????????????bCreated?=?createTables(dbConnection);
????????}?catch?(SQLException?ex)?{
????????}
????????dbProperties.remove("create");
????????return?bCreated;
????}

由于是嵌入式數(shù)據(jù)庫,創(chuàng)建表格也要編程來實(shí)現(xiàn),當(dāng)然也有和IDE配合使用的插件可以使用.

在創(chuàng)建數(shù)據(jù)庫和表時(shí), 需要指定 一個(gè)create 屬性為true, 就象上面的那樣, 同樣,在斷開連接 關(guān)閉數(shù)據(jù)庫時(shí),我們也要指定屬性 用編程的方法來關(guān)閉數(shù)據(jù)庫, 這是和普通數(shù)據(jù)庫不同的地方. 下面是關(guān)閉數(shù)據(jù)庫的代碼:

????public?void?disconnect()?{
????????if(isConnected)?{
????????????String?dbUrl?=?getDatabaseUrl();
????????????dbProperties.put("shutdown",?"true");
????????????try?{
????????????????DriverManager.getConnection(dbUrl,?dbProperties);
????????????}?catch?(SQLException?ex)?{
????????????}
????????????isConnected?=?false;
????????}
????}

然后我們就可以通過得到Connection 象操作普通數(shù)據(jù)庫一樣來操作Java DB了. 如下:

dbConnection = DriverManager.getConnection(dbUrl, dbProperties);
stmtSaveNewRecord = dbConnection.prepareStatement(strSaveAddress, Statement.RETURN_GENERATED_KEYS);

?

性能如何呢: 經(jīng)過我的測試 插入 100 行(每行有11個(gè)字符串) 數(shù)據(jù) 要2100 多毫秒. 取出100 行需要 1500多毫秒 不知道大家認(rèn)為如何.

本文用到的全部代碼: 可以在此出下載:? http://icess.my.china.com/downloads/index.htm?