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
package
test.jdbc;
import
java.sql.CallableStatement;
import
java.sql.PreparedStatement;
import
java.sql.ResultSet;
import
java.sql.SQLException;
import
java.sql.Types;
import
java.util.List;
import
org.springframework.dao.DataAccessException;
import
org.springframework.jdbc.core.BatchPreparedStatementSetter;
import
org.springframework.jdbc.core.CallableStatementCallback;
import
org.springframework.jdbc.core.JdbcTemplate;
import
org.springframework.jdbc.core.RowCallbackHandler;
import
org.springframework.jdbc.core.RowMapper;
import
org.springframework.jdbc.core.RowMapperResultSetExtractor;
public class
TestJdbcTemplate {
public static final
String strGetTest =
"SELECT * FROM APP.test WHERE ID = ?"
;
private static final
String strInsertIntoTestTeble =
"INSERT INTO APP.test (id, name) VALUES (?, ?)"
;
private
JdbcTemplate jdbcTemplate;
public
TestJdbcTemplate() {}
public
TestJdbcTemplate(JdbcTemplate jdbcTemplate) {
this
.jdbcTemplate = jdbcTemplate;
}
public
JdbcTemplate getJdbcTemplate() {
return
jdbcTemplate;
}
public void
setJdbcTemplate(JdbcTemplate jdbcTemplate) {
this
.jdbcTemplate = jdbcTemplate;
}
/** 测试 插入数据*/
public void
insertTestData(
int
id,String name) {
Object[] params =
new
Object[] {id,name};
jdbcTemplate.update(strInsertIntoTestTeble, params);
System.out.println(
"插入数据成功!"
);
}
/** 测试 插入数据*/
public void
insertTestDataWithTypeChecked(
int
id,String name) {
Object[] params =
new
Object[] {id,name};
int
[] types =
new int
[] {Types.INTEGER,Types.VARCHAR};
jdbcTemplate.update(strInsertIntoTestTeble, params, types);
System.out.println(
"插入数据成功(with Types checked)!"
);
}
/** 测试 批量插入数据*/
public void
insertTestDataByBatchInsert(
final
List<TestData> datas) {
//构造 BatchPreparedStatementSetter
BatchPreparedStatementSetter setter =
new
BatchPreparedStatementSetter() {
public int
getBatchSize() {
return
datas.size();
}
public void
setValues(PreparedStatement ps,
int
index) {
TestData data = datas.get(index);
try
{
ps.setInt(
1
, data.id);
// 从1 开始......
ps.setString(
2
, data.name);
}
catch
(SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
};
jdbcTemplate.batchUpdate(strInsertIntoTestTeble, setter);
System.out.println(
"批量插入数据成功!"
);
}
/**
* 测试 JdbcTemplate
*
@param
id
*/
public void
getTestData(
final int
id) {
final
Object[] params =
new
Object[] {id};
jdbcTemplate.query(strGetTest, params,
new
RowCallbackHandler() {
public void
processRow(ResultSet rs)
throws
SQLException {
// TODO Auto-generated method stub
System.out.println(
"测试JdbcTemplate:: name:"
+ rs.getString(
2
));
}
});
}
/** 测试RowMapper 和 RowMapperResultReader 接口*/
public void
getDataUsingRowMapper(
int
id) {
List datas = jdbcTemplate.query(
"SELECT * FROM APP.test WHERE ID = "
+ id,
new
RowMapper() {
// 实现RowMapper接口, 来映射每一行数据
public
Object mapRow(ResultSet rs,
int
index)
throws
SQLException {
// TODO Auto-generated method stub
System.out.println(
"测试RowMapper 接口: name "
+ rs.getString(
2
));
return null
;
}
});
//datas 中保存查询结果
System.out.println(datas.size());
}
public
TestData getDataUsingRowMapperResultReader(
final int
id) {
final
Object[] params =
new
Object[] {id};
// TODO 有问题.......
TestData data = (TestData) jdbcTemplate.query(strGetTest,params,new RowMapperResultSetExtractor(
new
RowMapper() {
public
Object mapRow(ResultSet rs,int index)
throws
SQLException {
TestData tdata =
new
TestData();
tdata.id = rs.getInt(
1
);
tdata.name = rs.getString(
2
);
return
tdata;
}
}));
return
data;
}
/** 测试调用存储过程..*/
public void
testCallableStatement() {
// 使用 CallableStatementCallback 回调接口 调用存储过程.
CallableStatementCallback cb =
new
CallableStatementCallback() {
public
Object doInCallableStatement(CallableStatement cs)
throws
SQLException, DataAccessException {
cs.execute();
return null
;
}
};
// GET_DATA 为存储过程名
jdbcTemplate.execute(
"{ GET_DATA}"
, cb);
}
// 用对象操作数据, 使用SqlUpdate接口. 见 InsertData 类,,,,,
// 有容器注入 InsertData.
private
InsertData insertData ;
public int
insertTestData(TestData data) {
return
insertData.insert(data);
}
public
InsertData getInsertData() {
return
insertData;
}
public void
setInsertData(InsertData insertData) {
this
.insertData = insertData;
}
//测试插入数据
public void
insertDataUsingSqlUpdate(TestData data) {
insertData.insert(data);
System.out.println(
"使用SqlUpdate接口插入数据 成功....."
);
}
/** 和上面使用SqlUpdate接口一样 把操作创建为对象来操作*/
private
QueryDataById queryDataById;
public void
setQueryDataById(QueryDataById queryDataById) {
this
.queryDataById = queryDataById;
}
//测试
public
TestData getDataUsingMappingSqlQuery(
int
id) {
Object[] params =
new
Object[] {id};
return
(TestData) queryDataById.execute(params).get(
0
);
}
//使用上面两种方法来插入和查询数据,不用和JDBC API交互, 有Spring提供了
//中间代理层
}
|
下面是函有main函数的 主类. TestApp.java
package
test.jdbc;
import
java.util.ArrayList;
import
java.util.List;
import
org.springframework.context.ApplicationContext;
import
org.springframework.context.support.ClassPathXmlApplicationContext;
public class
TestApp {
/**
*
@param
args
*/
public static void
main(String[] args) {
DatabaseUtils dataUtils =
new
DatabaseUtils();
dataUtils.connect();
System.out.println(
"Open database:!"
);
// TODO Auto-generated method stub
//测试连接数据源 ......
ApplicationContext context =
new
ClassPathXmlApplicationContext(
"test/jdbc/spring-traning.xml"
);
/* 测试 DataSource 配置*/
TestDataSource ds = (TestDataSource) context.getBean(
"dataBean"
);
ds.testDataSource();
/* 测试JdbcTemplate 配置*/
TestJdbcTemplate tjt = (TestJdbcTemplate) context.getBean(
"testJdbcTemplate"
);
tjt.insertTestData(
2
,
"test name2"
);
tjt.insertTestDataWithTypeChecked(
3
,
"test name 3"
);
List<TestData> datas =
new
ArrayList<TestData>();
datas.add(
new
TestData(
4
,
"test name 4"
));
datas.add(
new
TestData(
5
,
"test name 5"
));
datas.add(
new
TestData(
6
,
"test name 6"
));
tjt.insertTestDataByBatchInsert(datas);
tjt.getTestData(
1
);
tjt.getTestData(
2
);
tjt.getTestData(
5
);
tjt.getDataUsingRowMapper(
5
);
//TODO 类型转换错误. TestData data = tjt.getDataUsingRowMapperResultReader(1);
// System.out.println("测试使用 RowMapperResultSetExtractor 读取一行数据: " + data.id + "::: name " + data.name);
TestData tdata =
new
TestData(
9
,
"TestSqlUpdate."
);
tjt.insertDataUsingSqlUpdate(tdata);
// 插入数据
tjt.getDataUsingRowMapper(
9
);
// 测试上面插入的数据是否成功
dataUtils.disconnect();
}
}
JDBC配置文件:jdbc.properties
# Sample ResourceBundle properties file
db.username=
addressuser
db.password=
addressuser
db.driver=
org.apache.derby.jdbc.EmbeddedDriver
db.url=
jdbc
:
derby:
db.table=
test
db.schema=
APP
db.urlName=
jdbc
:
derby:test
最后是最重要的配置文件: spring-traning.xml
<?
xml
version
=
"1.0"
encoding
=
"UTF-8"
?><!
DOCTYPE
beans
PUBLIC
"-//SPRING//DTD BEAN//EN"
"spring-beans.dtd"
><
beans
>
<
bean
id
=
"propertyConfigurer"
class
=
"org.springframework.beans.factory.config.PropertyPlaceholderConfigurer"
>
<
property
name
=
"location"
>
<
value
>
test/jdbc/jdbc.properties
</
value
>
</
property
>
</
bean
>
<!-- get dataSource,配置dataSource -->
<!-- 从JNDI得到DataSource -->
<!-- bean id="dataSource" class="org.springframework.jndi.JndiObjectFactoryBean"><property name="jndiName">
<value>java:comp/env/jdbc/myDataSource</value>
</property>
</bean> -->
<!-- 使用Spring中的 DriverManagerDataSource -->
<!-- bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource"><property name="driverClassName">
<value>${db.driver}</value>
</property>
<property name="url">
<value>${db.urlName}</value>
</property>
<property name="username">
<value>${db.username}</value>
</property>
<property name="password">
<value>${db.password}</value>
</property>
</bean -->
<!-- 使用DBCP dataSource -->
<
bean
id
=
"dataSource"
class
=
"org.apache.commons.dbcp.BasicDataSource"
>
<
property
name
=
"driverClassName"
>
<
value
>
${db.driver}
</
value
>
</
property
>
<
property
name
=
"url"
>
<
value
>
${db.urlName}
</
value
>
</
property
>
<
property
name
=
"username"
>
<
value
>
${db.username}
</
value
>
</
property
>
<
property
name
=
"password"
>
<
value
>
${db.password}
</
value
>
</
property
>
</
bean
>
<
bean
id
=
"dataBean"
class
=
"test.jdbc.TestDataSource"
>
<
property
name
=
"dataSource"
>
<
ref
bean
=
"dataSource"
/>
</
property
>
</
bean
>
<!-- ################################################################# -->
<!-- 配合DataSource测试JdbcTemplate -->
<
bean
id
=
"jdbcTemplate"
class
=
"org.springframework.jdbc.core.JdbcTemplate"
>
<
property
name
=
"dataSource"
>
<
ref
bean
=
"dataSource"
/>
</
property
>
</
bean
>
<
bean
id
=
"testJdbcTemplate"
class
=
"test.jdbc.TestJdbcTemplate"
>
<
constructor-arg
>
<
ref
bean
=
"jdbcTemplate"
/>
</
constructor-arg
>
<!-- 测试 SqlUpdate 接口... -->
<
property
name
=
"insertData"
>
<
ref
bean
=
"insertDataUsingSqlUptate"
/>
</
property
>
</
bean
>
<!-- 测试SqlUpdate......接口 -->
<
bean
id
=
"insertDataUsingSqlUptate"
class
=
"test.jdbc.InsertData"
>
<
constructor-arg
>
<
ref
bean
=
"dataSource"
/>
</
constructor-arg
>
</
bean
></
beans
>
ok, Jdbc测试的代码就结束了.
在这里主要学习了,Spring提高的使用Jdbc的一些包装类和接口, 来更方便的使用Jdbc操作数据, 不用些那么一些烦人的 try ... catch...... finally.....
感觉使用Sprig效率是很好,代码看起来也很优美哦. 呵呵:
ok ,今天就终结完了, 下一次看看在Spring中如何高效使用Hibernate吧, 下次见啦: