Junky's IT Notebook

Spring in Action 筆記(II) part I

Spring in Action 筆記 (II)

今天來看看使用JDBC來操作數據： 使用的是Derby（JavaDB）數據庫，關于JavaDB的介紹請點擊這里： http://blog.matrix.org.cn/page/icess?catname=%2FJavaDB 。 下面建立一個DatabaseUtils.java的工具類，來操作數據庫 。該類在上面的連接的文章中有講述。

package? test.jdbc;

import? java.io.File;
import? java.io.IOException;
import? java.io.InputStream;
import? java.sql.Connection;
import? java.sql.DriverManager;
import? java.sql.PreparedStatement;
import? java.sql.ResultSet;
import? java.sql.SQLException;
import? java.sql.Statement;
import? java.util.Properties;
import? java.util.logging.Logger;

public?class? DatabaseUtils?{
?? private?static?final? String?DB_PROPERTIES_FILE?=? "jdbc.properties" ;

?? private?static?final? String?DB_OPPOSITE_LOCATION?=? "/.test" ;

?? static? Logger?logger?=?Logger.getLogger(DatabaseUtils. class .getName());

?? private? Connection?dbConnection;

?? private? Properties?dbProperties;

?? private?boolean? isConnected;

?? //?database?name
?? private? String?dbName;

?? private?static?final? String?strCreateTestClobTeble?=? "CREATE?TABLE?APP.test?(id?INT,?name?VARCHAR(30),text?CLOB(64?K))" ;

?? private?static?final? String?strInsertIntoTestTeble?=? "INSERT?INTO?APP.test?(id,?name)??VALUES?(?,??)" ;
?? public?static?final? String?strGetTest?=? "SELECT?*?FROM?APP.test?WHERE?ID?=??" ;
?? private?static?final? String?strCreateCourseTable?=? "create?table?APP.Course?("
?????? +? "????ID??????????INTEGER?NOT?NULL?PRIMARY?KEY?GENERATED?ALWAYS?AS?IDENTITY?(START?WITH?1,?INCREMENT?BY?1),"
?????? +? "????name????VARCHAR(30),?"
?????? +? "????description??VARCHAR(30),?"
?????? +? "????startDate??DATE,?"? +? "????endDate?????DATE?"? +? ")" ;

?? private?static?final? String?strCreateStudentTable?=? "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),?"? +? ")" ;

?? public? DatabaseUtils()?{
???? this ( "test" );
?? }

?? public? DatabaseUtils(String?dbName)?{
???? this .dbName?=?dbName;

???? setDBSystemDir();
???? dbProperties?=?loadDBProperties();
???? String?driverName?=?dbProperties.getProperty( "db.driver" );
???? loadDatabaseDriver(driverName);
???? if? (!dbExists())?{
?????? createDatabase();
???? }
?? }

?? private? Properties?loadDBProperties()?{
???? InputStream?dbPropInputStream?=? null ;

???? dbPropInputStream?=?DatabaseUtils. class
???????? .getResourceAsStream(DB_PROPERTIES_FILE);
???? dbProperties?=? new? Properties();

???? try? {
?????? dbProperties.load(dbPropInputStream);
???? }? catch? (IOException?e)?{
?????? e.printStackTrace();
???? }
???? return? dbProperties;
?? }

?? private?void? setDBSystemDir()?{
???? String?userDir?=?System.getProperty( "user.dir" ,? "." );
???? String?systemDir?=?userDir?+?DB_OPPOSITE_LOCATION;
???? System.setProperty( "derby.system.home" ,?systemDir);

???? //?create?the?db?System?dir
???? File?fileSystemDir?=? new? File(systemDir);
???? fileSystemDir.mkdir();
?? }

?? private?void? loadDatabaseDriver(String?driverName)?{
???? try? {
?????? Class.forName(driverName);
???? }? catch? (ClassNotFoundException?e)?{
?????? e.printStackTrace();
???? }
?? }

?? private?boolean? dbExists()?{
???? boolean? bExists?=? false ;
???? String?dbLocation?=?getDatabaseLocation();
???? File?dbFileDir?=? new? File(dbLocation);
???? if? (dbFileDir.exists())?{
?????? bExists?=? true ;
???? }
???? return? bExists;
?? }

?? 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,?strCreateTestClobTeble);
???? }? catch? (SQLException?e)?{
?????? e.printStackTrace();
???? }

???? dbProperties.remove( "create" );
???? return? bCreated;
?? }

?? private?boolean? createTables(Connection?dbConnection,?String?creatTableSql)?{
???? boolean? bCreatedTables?=? false ;
???? Statement?statement?=? null ;

???? try? {
?????? statement?=?dbConnection.createStatement();
?????? statement.execute(creatTableSql);
?????? bCreatedTables?=? true ;
???? }? catch? (SQLException?e)?{
?????? e.printStackTrace();
???? }
???? return? bCreatedTables;
?? }

?? public? String?getDatabaseUrl()?{

???? return? dbProperties.getProperty( "db.url" )?+?dbName;
?? }

?? public? String?getDatabaseLocation()?{
???? String?dbLocation?=?System.getProperty( "derby.system.home" )?+? "/"
???????? +?dbName;
???? return? dbLocation;
?? }

?? public?boolean? connect()?{
???? String?dbUrl?=?getDatabaseUrl();
???? try? {
?????? logger.info( "DBUrl:?"? +?dbUrl);
?????? dbConnection?=?DriverManager.getConnection(dbUrl,?dbProperties);
?????? isConnected?=?dbConnection?!=? null ;
???? }? catch? (SQLException?e)?{
?????? //?TODO?Auto-generated?catch?block
?????? e.printStackTrace();
?????? isConnected?=? false ;
?????? logger.info( "create?connection?if?failed!" );
???? }
???? return? isConnected;
?? }

?? public? Connection?getConnection()?{
???? return? dbConnection;
?? }

?? public?void? disconnect()?{
???? if? (isConnected)?{
?????? String?dbUrl?=?getDatabaseUrl();
?????? dbProperties.put( "shutdown" ,? "true" );
?????? try? {
???????? System.out.println( "斷開數據庫連接????????????????" );
???????? DriverManager.getConnection(dbUrl,?dbProperties);
???????? System.out.println( "????????????????" );
?????? }? catch? (SQLException?e)?{
???????? //?e.printStackTrace();
???????? logger.info( "disconnect?the?connection?Successful!" );
?????? }
?????? isConnected?=? false ;
???? }
?? }

?? /**
??? *? @param? args
??? */
?? public?static?void? main(String[]?args)?{
???? //?TODO?Auto-generated?method?stub
???? DatabaseUtils?testdb?=? new? DatabaseUtils();
???? logger.info(testdb.getDatabaseLocation());
???? logger.info(testdb.getDatabaseUrl());
???? testdb.connect();
???? Connection?c?=?testdb.getConnection();
???? PreparedStatement?ps?=? null ;
???? try? {
?????? ps?=?c.prepareStatement(DatabaseUtils.strInsertIntoTestTeble,?Statement.RETURN_GENERATED_KEYS);
?????? ps.setInt( 1 ,? 1 );
?????? ps.setString( 2 ,? "test?Icerain" );
?????? int? i?=ps.executeUpdate();
?????? System.out.println(i);
?????? ps.close();
??????
?????? ps?=?c.prepareStatement(DatabaseUtils.strGetTest);
?????? ps.setInt( 1 ,? 1 );
?????? ResultSet?rs?=?ps.executeQuery();
?????? if (rs.next())?{
???????? String?name?=?rs.getString( 2 );
???????? System.out.println(name);
?????? }
?????? ps.close();
???? }? catch? (SQLException?e)?{
?????? //?TODO?Auto-generated?catch?block
?????? e.printStackTrace();
???? }
???? testdb.disconnect();
?? }

}

下面是一個插入數據的類 InsertData.java

package? test.jdbc;

import? java.sql.Types;

import? javax.sql.DataSource;

import? org.springframework.jdbc.core.SqlParameter;
import? org.springframework.jdbc.object.SqlUpdate;

public?class? InsertData? extends? SqlUpdate?{
?? //?需要注入一個DataSource...
?? public? InsertData(DataSource?ds)?{
???? setDataSource(ds);?? //?TODO?注意?設置數據源
???? setSql( "INSERT?INTO?APP.test?(id,?name)??VALUES?(?,??)" );
???? declareParameter( new? SqlParameter(Types.INTEGER));
???? declareParameter( new? SqlParameter(Types.VARCHAR));
????
???? compile(); //?TODO?注意?,?要編譯以后才可以使用
?? }
??
?? //?覆蓋insert方法
?? public?int? insert(TestData?data)?{
???? Object[]?params?=? new? Object[]?{data.id,data.name};
???? return? update(params);?? //?執行插入操作....
?? }

}

很簡單, 并帶有詳細注釋.

下面是一個查詢的類 QueryDataById.java

package? test.jdbc;

import? java.sql.ResultSet;
import? java.sql.SQLException;
import? java.sql.Types;

import? javax.sql.DataSource;

import? org.springframework.jdbc.core.SqlParameter;
import? org.springframework.jdbc.object.MappingSqlQuery;

public?class? QueryDataById? extends? MappingSqlQuery{
?? private?static?final? String?sql?=? "SELECT?*?FROM?APP.test?WHERE?ID?=??" ;
?? public? QueryDataById(DataSource?ds)?{
???? super (ds,sql);
???? declareParameter( new? SqlParameter( "id" ,Types.INTEGER));
???? compile();
?? }
　

? //?覆蓋mapRow方法
?? @Override
?? protected? Object?mapRow(ResultSet?rs,? int? index)? throws? SQLException?{
???? //?TODO?Auto-generated?method?stub
???? TestData?tdata?=? new? TestData();
???? tdata.id?=?rs.getInt( 1 );
???? tdata.name?=?rs.getString( 2 );
????
???? return? tdata;
?? }
??
}

也很簡單.

注意:

?以上兩個類都實現了Spring簡化Jdbc操作的一些接口, 關于接口的信息請查考文檔, 這里不在詳細講述.

下面是一個很簡單的測試(數據)實體類.TestData.java

package? test.jdbc;

public?class? TestData?{
?? public?int? id;
?? public? String?name;
?? public? TestData( int? id,?String?name)?{
???? this .id?=?id;
???? this .name?=?name;
?? }
?? public? TestData()?{}
}

下面是一個測試數據源是否注入正確的類:TestDataSource.java

package? test.jdbc;

import? java.sql.Connection;
import? java.sql.PreparedStatement;
import? java.sql.ResultSet;

import? javax.sql.DataSource;

public?class? TestDataSource?{
?? private? DataSource?dataSource;
　

// 注入數據源
?? public?void? setDataSource(DataSource?dataSource)?{
???? this .dataSource?=?dataSource;
?? }
　

//測試數據源
?? public?void? testDataSource()?{
???? try? {
?????? System.out.println( "Test?DataSource!!!" );
?????? Connection?connection?=?dataSource.getConnection();
?????? if? (connection?!=? null )
???????? System.out.println( "test?ok!" );
??????
?????? PreparedStatement?ps?=? null ;
?????? ps?=?connection.prepareStatement(DatabaseUtils.strGetTest);
?????? ps.setInt( 1 ,? 1 );
?????? ResultSet?rs?=?ps.executeQuery();
?????? if (rs.next())?{
???????? String?name?=?rs.getString( 2 );
???????? System.out.println( "測試數據源配置:"? +?name);
?????? }
?????? ps.close();
???? }? catch? (Exception?e)?{
?????? e.printStackTrace();
???? }
?? }

}

下面是測試Spring提高的Jdbc功能的主要測試類, 測試了一些使用JDBC操作數據的常用功能, 其他沒有測試的請查看其Doc,TestJdbcTemplate.java

posted on 2006-05-25 20:21 junky 閱讀(351) 評論(0)  編輯  收藏 所屬分類: spring