| Blog信息 |
|
blog名称:
日志总数:1304
评论数量:2242
留言数量:5
访问次数:7594903
建立时间:2006年5月29日 |
| |
|
 [Spring]Spring in Action笔记(II)--Derby（JavaDB）数据库
软件技术
lhwork 发表于 2006/7/14 10:21:08 |
| 今天来看看使用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.javapackage 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.javapackage 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.javapackage 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.javapackage 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.javapackage 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.javapackage 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 filedb.username=addressuserdb.password=addressuserdb.driver=org.apache.derby.jdbc.EmbeddedDriverdb.url=jdbc : derby:db.table=testdb.schema=APPdb.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吧, 下次见啦: |
|
|