Spring in Action 笔记(II) 博客分类： BlogJava SpringJavaSQLJDBCDerby
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;
|
相关推荐
Spring in Action 中文 清晰版 (带阅读笔记)
Spring In Action中的笔记总结哦 Spring In Action中的笔记总结哦
Spring in Action中文清晰版(带阅读笔记). Spring in Action中文清晰版(带阅读笔记).
Spring in Action中文清晰版(带阅读笔记)
Spring in Action 中文 清晰版 (带阅读笔记)(共压缩4分卷) 此第3分卷 是扫描版的 感谢分享的兄弟
Spring in action的学习笔记，里面有许多代码示例，部分内容是自己根据多方面资料综合而成
Spring In Action（第二版）中文版_笔记 by uuwoxin 1.1 Spring是什么 如果说BeanFactory使Spring成为容器的话，那么上下文环境就是使Spring成为框架的原因。 上下文环境扩展了BeanFactory，添加了对I18N（国际化）...
Spring in Action 中间带有读书笔记， 第2部分共4部分
《Spring+in+action+中文版（第4版）》读书笔记带图片
Spring in Action 中间带有读书笔记 第4部分共4部分
Spring in Action 中间带有读书笔记，第3部分共4部分
spring_in_action_学习笔记 希望对各位学习spring的同学们 有帮助
Spring in Action 中文 清晰版 (带阅读笔记).part2
Spring in Action 中文 清晰版 (带阅读笔记).part1
本人阅读 《Spring in Action》一书笔记,点点滴滴,从博文中连缀成册,不多不少,共列下了 100 条。
spring in action 中文版,可能大家都知道了这本书吧,spring的入门书籍,介绍全面。我找了好久才好不容易找到一个是[完全]的中文版,不过是带有人为笔记的,版式上有点花,但总聊胜于无吧.当时是很高兴的.（一共34个包，...