Java回顾之JDBC
来源:互联网 发布:面试问到java项目上线 编辑:程序博客网 时间:2024/06/05 14:29
这篇文章里,我们来讨论一些和JDBC相关的话题。
概述
尽管在实际开发过程中,我们一般使用ORM框架来代替传统的JDBC,例如Hibernate或者iBatis,但JDBC是Java用来实现数据访问的基础,掌握它对于我们理解Java的数据操作流程很有帮助。
JDBC的全称是Java Database Connectivity。
JDBC对数据库进行操作的流程:
- 连接数据库
- 发送数据请求,即传统的CRUD指令
- 返回操作结果集
JDBC中常用的对象包括:
- ConnectionManager
- Connection
- Statement
- CallableStatement
- PreparedStatement
- ResultSet
- SavePoint
一个简单示例
我们来看下面一个简单的示例,它使用JDK自带的Derby数据库,创建一张表,插入一些记录,然后将记录返回:
一个简单的JDBC示例private static void test1() throws SQLException{ String driver = "org.apache.derby.jdbc.EmbeddedDriver"; String dbURL = "jdbc:derby:EmbeddedDB;create=true"; Connection con = null; Statement st = null; try { Class.forName(driver); con = DriverManager.getConnection(dbURL); st = con.createStatement(); st.execute("create table foo(ID INT NOT NULL, NAME VARCHAR(30))"); st.executeUpdate("insert into foo(ID,NAME) values(1, 'Zhang San')"); ResultSet rs = st.executeQuery("select ID,NAME from foo"); while(rs.next()) { int id = rs.getInt("ID"); String name = rs.getString("NAME"); System.out.println("ID=" + id + "; NAME=" + name); } } catch(Exception ex) { ex.printStackTrace(); } finally { if (st != null) st.close(); if (con != null) con.close(); }}
如何建立数据库连接
上面的示例代码中,建立数据库连接的部分如下:
String driver = "org.apache.derby.jdbc.EmbeddedDriver";String dbURL = "jdbc:derby:EmbeddedDB;create=true";Class.forName(driver);con = DriverManager.getConnection(dbURL);
建立数据库连接的过程,可以分为两步:
1)加载数据库驱动,即上文中的driver以及Class.forName(dirver)
2)定位数据库连接字符串, 即dbURL以及DriverManager.getConnection(dbURL)
不同的数据库,对应的dirver和dbURL不同,但加载驱动和建立连接的方式是相同的,即只需要修改上面driver和dbURL的值就可以了。
自动加载数据库驱动
如果我们每次建立连接时,都要使用Class.forName(...)来手动加载数据库驱动,这样会很麻烦,我们可以通过配置文件的方式,来保存数据库驱动的信息。
我们可以在classpath中,即编译出来的.class的存放路径,添加如下文件:
META-INF\services\java.sql.Driver对应的内容就是JDBC驱动的全路径,也就是上面driver变量的值:
org.apache.derby.jdbc.EmbeddedDriver接下来,我们在程序中,就不需要再显示的用Class.forName(...)来加载驱动了,它会被自动加载进来,当我们的数据库发生变化时,只需要修改这个文件就可以了,例如当我们的数据库由Derby变为MySQL时,只需要将上述的配置修改为:
com.mysql.jdbc.Driver
但是,需要注意一点,这里只是配置了JDBC驱动的全路径,并没有包含jar文件的信息,因此,我们还是需要将包含该驱动的jar文件手动的放置到程序的classpath中。
JDBC中的基本操作
对于数据库操作来说,CRUD操作应该是最常见的操作了, 即我们常说的增、删、查、改。
JDBC是使用Statement和ResultSet来完成这些操作的。
如何实现CRUD
下面是一个实现CRUD的示例:
JDBC实现基本的CRUD示例private static void insertTest() throws SQLException{ String dbURL = "jdbc:mysql://localhost/test"; Connection con = DriverManager.getConnection(dbURL, "root", "123"); Statement st = con.createStatement(); st.execute("insert into user(ID,NAME) values(1, 'Zhang San')"); st.execute("insert into user(ID,NAME) values(2, 'Li Si')"); st.execute("insert into user(ID,NAME) values(3, 'Wang Wu')"); System.out.println("=====insert test====="); showUser(st); st.close(); con.close();}private static void deleteTest() throws SQLException{ String dbURL = "jdbc:mysql://localhost/test"; Connection con = DriverManager.getConnection(dbURL, "root", "123"); Statement st = con.createStatement(); st.execute("delete from user where ID=3"); System.out.println("=====delete test====="); showUser(st); st.close(); con.close();}private static void updateTest() throws SQLException{ String dbURL = "jdbc:mysql://localhost/test"; Connection con = DriverManager.getConnection(dbURL, "root", "123"); Statement st = con.createStatement(); st.executeUpdate("update user set NAME='TEST' where ID=2"); System.out.println("=====update test====="); showUser(st); st.close(); con.close();}private static void showUser(Statement st) throws SQLException{ ResultSet rs = st.executeQuery("select ID, NAME from user"); while(rs.next()) { int id = rs.getInt("ID"); String name = rs.getString("NAME"); System.out.println("ID:" + id + "; NAME=" + name); } rs.close();}我们顺序调用上面的测试方法:
insertTest();deleteTest();updateTest();执行结果如下:
=====insert test=====ID:1; NAME=Zhang SanID:2; NAME=Li SiID:3; NAME=Wang Wu=====delete test=====ID:1; NAME=Zhang SanID:2; NAME=Li Si=====update test=====ID:1; NAME=Zhang SanID:2; NAME=TEST
上面代码中的showUser方法会把user表中的所有记录打印出来。
如何调用存储过程
存储过程是做数据库开发时经常使用的技术,它可以通过节省编译时间的方式来提升系统性能,我们这里的示例使用MySQL数据库。
如何调用不带参数的存储过程
假设我们现在有一个简单的存储过程,它只是返回user表中的所有记录,存储过程如下:
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetUser`()BEGINselect ID,NAME from user;END我们可以使用CallableStatement来调用存储过程:
调用存储过程示例一private static void execStoredProcedureTest() throws SQLException{ String dbURL = "jdbc:mysql://localhost/test"; Connection con = DriverManager.getConnection(dbURL, "root", "123"); CallableStatement cst = con.prepareCall("call GetUser()"); ResultSet rs = cst.executeQuery(); while(rs.next()) { int id = rs.getInt("ID"); String name = rs.getString("NAME"); System.out.println("ID:" + id + "; NAME=" + name); } rs.close(); cst.close(); con.close();}它的执行结果如下:
ID:1; NAME=Zhang SanID:2; NAME=TEST
如何调用带参数的存储过程
MySQL的存储过程中的参数分为三种:in/out/inout,我们可以把in看做入力参数,out看做出力参数,JDBC对这两种类型的参数设置方式不同:
1)in, JDBC使用类似于cst.set(1, 10)的方式来设置
2)out,JDBC使用类似于cst.registerOutParameter(2, Types.VARCHAR);的方式来设置
我们来看一个in参数的示例,假设我们希望返回ID为特定值的user信息,存储过程如下:
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetUserByID`(in id int)BEGINset @sqlstr=concat('select * from user where ID=', id);prepare psmt from @sqlstr;execute psmt;ENDJava的调用代码如下:
JDBC调用存储过程示例二private static void execStoredProcedureTest2(int id) throws SQLException{ String dbURL = "jdbc:mysql://localhost/test"; Connection con = DriverManager.getConnection(dbURL, "root", "123"); CallableStatement cst = con.prepareCall("call GetUserByID(?)"); cst.setInt(1, id); ResultSet rs = cst.executeQuery(); while(rs.next()) { String name = rs.getString("NAME"); System.out.println("ID:" + id + "; NAME=" + name); } rs.close(); cst.close(); con.close();}我们执行下面的语句:
execStoredProcedureTest2(1);结果如下:
ID:1; NAME=Zhang San
对于out类型的参数,调用方式类似,不再赘述。 获取数据库以及结果集的metadata信息 在JDBC中,我们不仅能够对数据进行操作,我们还能获取数据库以及结果集的元数据信息,例如数据库的名称、驱动信息、表信息;结果集的列信息等。 获取数据库的metadata信息 我们可以通过connection.getMetaData方法来获取数据库的元数据信息,它的类型是DatabaseMetaData。
获取数据库的元数据信息private static void test1() throws SQLException{ String dbURL = "jdbc:mysql://localhost/mysql"; Connection con = DriverManager.getConnection(dbURL, "root", "123"); DatabaseMetaData dbmd = con.getMetaData(); System.out.println("数据库:" + dbmd.getDatabaseProductName() + " " + dbmd.getDatabaseProductVersion()); System.out.println("驱动程序:" + dbmd.getDriverName() + " " + dbmd.getDriverVersion()); ResultSet rs = dbmd.getTables(null, null, null, null); System.out.println(String.format("|%-26s|%-9s|%-9s|%-9s|", "表名称","表类别","表类型","表模式")); while(rs.next()) { System.out.println(String.format("|%-25s|%-10s|%-10s|%-10s|", rs.getString("TABLE_NAME"),rs.getString("TABLE_CAT"), rs.getString("TABLE_TYPE"), rs.getString("TABLE_SCHEM"))); }}这里我们使用的数据库是MySQL中自带的默认数据库:mysql,它会记录整个数据库服务器中的一些信息。上述代码执行结果如下:
数据库:MySQL 5.5.28驱动程序:MySQL-AB JDBC Driver mysql-connector-java-5.0.4 ( $Date: 2006-10-19 17:47:48 +0200 (Thu, 19 Oct 2006) $, $Revision: 5908 $ )|表名称 |表类别 |表类型 |表模式 ||columns_priv |mysql |TABLE |null ||db |mysql |TABLE |null ||event |mysql |TABLE |null ||func |mysql |TABLE |null |。。。
由于mysql中表比较多,上述结果只截取了一部分。
获取结果集的元数据信息
我们可以通过使用resultset.getMetaData方法来获取结果集的元数据信息,它的类型是ResultSetMetaData。
获取结果集的元数据信息private static void test2() throws SQLException{ String dbURL = "jdbc:mysql://localhost/test"; Connection con = DriverManager.getConnection(dbURL, "root", "123"); Statement st = con.createStatement(); ResultSet rs = st.executeQuery("select ID, NAME from user"); ResultSetMetaData rsmd = rs.getMetaData(); for (int i = 1; i <= rsmd.getColumnCount(); i++) { System.out.println("Column Name:" + rsmd.getColumnName(i) + "; Column Type:" + rsmd.getColumnTypeName(i)); }}
它的执行结果如下:
Column Name:ID; Column Type:INTEGER UNSIGNEDColumn Name:NAME; Column Type:VARCHAR
可以看到,它返回类结果集中每一列的名称和类型。
基于ResultSet的操作
当我们需要对数据库进行修改时,除了上述通过Statement完成操作外,我们也可以借助ResultSet来完成。
需要注意的是,在这种情况下,我们定义Statement时,需要添加参数。
Statement构造函数可以包含3个参数:
resultSetType
,它的取值包括:ResultSet.TYPE_FORWARD_ONLY
、ResultSet.TYPE_SCROLL_INSENSITIVE
或ResultSet.TYPE_SCROLL_SENSITIVE,默认情况下,该参数的值是
ResultSet.TYPE_FORWARD_ONLY
。resultSetConcurrency
,它的取值包括:ResultSet.CONCUR_READ_ONLY
或ResultSet.CONCUR_UPDATABLE
,默认情况下,该参数的值是ResultSet.CONCUR_READ_ONLY
。resultSetHoldability
,它的取值包括:ResultSet.HOLD_CURSORS_OVER_COMMIT
或ResultSet.CLOSE_CURSORS_AT_COMMIT
。
为了使得ResultSet能够对数据进行操作我们需要:
- 将resultSetType设置为
ResultSet.TYPE_SCROLL_SENSITIVE
。 - 将resultSetConcurrency设置为
ResultSet.CONCUR_UPDATABLE
。
在通过ResultSet对数据进行调整的过程中,下面方法可能会被调用:
- resultset.last()
- resultset.first()
- resultset.moveToInsertRow()
- resultset.absolute()
- resultset.setxxx()
- resultset.updateRow()
- resultset.insertRow()
下面是一个通过ResultSet对数据进行增、删、改的示例:
通过ResultSet对数据进行增、删、改private static void getResultCount() throws SQLException{ System.out.println("=====Result Count====="); String dbURL = "jdbc:mysql://localhost/test"; Connection con = DriverManager.getConnection(dbURL, "root", "123"); Statement st = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY, ResultSet.CLOSE_CURSORS_AT_COMMIT); ResultSet rs = st.executeQuery("select * from user"); rs.last(); System.out.println("返回结果的条数:"+ rs.getRow()); rs.first(); rs.close(); st.close(); con.close();}private static void insertDataToResultSet() throws SQLException{ System.out.println("=====Insert====="); String dbURL = "jdbc:mysql://localhost/test"; Connection con = DriverManager.getConnection(dbURL, "root", "123"); Statement st = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); ResultSet rs = st.executeQuery("select ID,NAME from user"); rs.moveToInsertRow(); rs.updateInt(1, 4); rs.updateString(2, "Xiao Ming"); rs.insertRow(); showUser(st); rs.close(); st.close(); con.close();}private static void updateDataToResultSet() throws SQLException{ System.out.println("=====Update====="); String dbURL = "jdbc:mysql://localhost/test"; Connection con = DriverManager.getConnection(dbURL, "root", "123"); Statement st = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); ResultSet rs = st.executeQuery("select * from user"); rs.last(); int count = rs.getRow(); rs.first(); rs.absolute(count); rs.updateString(2, "Xiao Qiang"); rs.updateRow(); showUser(st); rs.close(); st.close(); con.close();}private static void delDataFromResultSet() throws SQLException{ System.out.println("=====Delete====="); String dbURL = "jdbc:mysql://localhost/test"; Connection con = DriverManager.getConnection(dbURL, "root", "123"); Statement st = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE, ResultSet.CLOSE_CURSORS_AT_COMMIT); ResultSet rs = st.executeQuery("select * from user"); rs.last(); int count = rs.getRow(); rs.first(); rs.absolute(count); rs.deleteRow(); showUser(st); rs.close(); st.close(); con.close();}分别调用上述方法:
getResultCount();insertDataToResultSet();updateDataToResultSet();delDataFromResultSet();执行结果如下:
=====Result Count=====返回结果的条数:2=====Insert=====ID:1; NAME=Zhang SanID:2; NAME=TESTID:4; NAME=Xiao Ming=====Update=====ID:1; NAME=Zhang SanID:2; NAME=TESTID:4; NAME=Xiao Qiang=====Delete=====ID:1; NAME=Zhang SanID:2; NAME=TEST
可以看到我们对ID为4的记录进行了插入、更新和删除操作。
预处理以及批处理
预处理和批处理都是用来提升系统性能的方式,一种是利用数据库的缓存机制,一种是利用数据库一次执行多条语句的方式。
预处理
数据库服务器接收到Statement后,一般会解析Statement、分析是否有语法错误、定制最优的执行计划,这个过程可能会降低系统的性能。一般的数据库服务器都这对这种情况,设计了缓存机制,当数据库接收到指令时,如果缓存中已经存在,那么就不再解析,而是直接运行。
这里相同的指令是指sql语句完全一样,包括大小写。
JDBC使用PreparedStatement来完成预处理:
预处理示例private static void test1() throws SQLException{ System.out.println("=====Insert a single record by PreparedStatement====="); String dbURL = "jdbc:mysql://localhost/test"; Connection con = DriverManager.getConnection(dbURL, "root", "123"); PreparedStatement pst = con.prepareStatement("insert into user(id,name) values(?,?)"); pst.setInt(1, 5); pst.setString(2, "Lei Feng"); pst.executeUpdate(); showUser(pst); pst.close(); con.close();}执行结果如下:
=====Insert a single record by PreparedStatement=====ID:1; NAME=Zhang SanID:2; NAME=TESTID:5; NAME=Lei Feng
批处理
批处理是利用数据库一次执行多条语句的机制来提升性能,这样可以避免多次建立连接带来的性能损失。
批处理使用Statement的addBatch来添加指令,使用executeBatch方法来一次执行多条指令:
批处理示例private static void test2() throws SQLException{ System.out.println("=====Insert multiple records by Statement & Batch====="); String dbURL = "jdbc:mysql://localhost/test"; Connection con = DriverManager.getConnection(dbURL, "root", "123"); Statement st = con.createStatement(); st.addBatch("insert into user(id,name) values(6,'Xiao Zhang')"); st.addBatch("insert into user(id,name) values(7,'Xiao Liu')"); st.addBatch("insert into user(id,name) values(8,'Xiao Zhao')"); st.executeBatch(); showUser(st); st.close(); con.close();}执行结果如下:
=====Insert multiple records by Statement & Batch=====ID:1; NAME=Zhang SanID:2; NAME=TESTID:5; NAME=Lei FengID:6; NAME=Xiao ZhangID:7; NAME=Xiao LiuID:8; NAME=Xiao Zhao
预处理和批处理相结合
我们可以把预处理和批处理结合起来,利用数据库的缓存机制,一次执行多条语句:
预处理和批处理相结合的示例private static void test3() throws SQLException{ System.out.println("=====Insert multiple records by PreparedStatement & Batch====="); String dbURL = "jdbc:mysql://localhost/test"; Connection con = DriverManager.getConnection(dbURL, "root", "123"); PreparedStatement pst = con.prepareStatement("insert into user(id,name) values(?,?)"); pst.setInt(1, 9); pst.setString(2, "Xiao Zhang"); pst.addBatch(); pst.setInt(1, 10); pst.setString(2, "Xiao Liu"); pst.addBatch(); pst.setInt(1, 11); pst.setString(2, "Xiao Zhao"); pst.addBatch(); pst.executeBatch(); showUser(pst); pst.close(); con.close();}执行结果如下:
=====Insert multiple records by PreparedStatement & Batch=====ID:1; NAME=Zhang SanID:2; NAME=TESTID:5; NAME=Lei FengID:9; NAME=Xiao ZhangID:10; NAME=Xiao LiuID:11; NAME=Xiao Zhao
数据库事务
谈到数据库开发,事务是一个不可回避的话题,JDBC默认情况下,是每一步都自动提交的,我们可以通过设置connection.setAutoCommit(false)的方式来强制关闭自动提交,然后通过connection.commit()和connection.rollback()来实现事务提交和回滚。
简单的数据库事务
下面是一个简单的数据库事务的示例:
简单的数据库事务示例private static void transactionTest1() throws SQLException{ System.out.println("=====Simple Transaction test====="); String dbURL = "jdbc:mysql://localhost/test"; Connection con = DriverManager.getConnection(dbURL, "root", "123"); Statement st = con.createStatement(); try { con.setAutoCommit(false); st.executeUpdate("insert into user(id,name) values(12, 'Xiao Li')"); con.commit(); } catch(Exception ex) { ex.printStackTrace(); con.rollback(); } finally { con.setAutoCommit(true); showUser(st); if (st != null) st.close(); if (con != null) con.close(); }}连续执行上述方法两次,我们可以得出下面的结果:
=====Simple Transaction test=====ID:1; NAME=Zhang SanID:2; NAME=TESTID:5; NAME=Lei FengID:12; NAME=Xiao Li=====Simple Transaction test=====ID:1; NAME=Zhang SanID:2; NAME=TESTID:5; NAME=Lei FengID:12; NAME=Xiao Licom.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException: Duplicate entry '12' for key 'PRIMARY' at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:931) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2870) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1573) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665) at com.mysql.jdbc.Connection.execSQL(Connection.java:3170) at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1316) at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1235) at sample.jdbc.mysql.ResultSetSample.transactionTest1(ResultSetSample.java:154) at sample.jdbc.mysql.ResultSetSample.main(ResultSetSample.java:17)
可以看到,第一次调用时,操作成功,事务提交,向user表中插入了一条记录;第二次调用时,发生主键冲突异常,事务回滚。
带有SavePoint的事务
当我们的事务操作中包含多个处理,但我们有时希望一些操作完成后可以先提交,这样可以避免整个事务的回滚。JDBC使用SavePoint来实现这一点。
带有SavePoint的事务示例private static void transactionTest2() throws SQLException{ System.out.println("=====Simple Transaction test====="); String dbURL = "jdbc:mysql://localhost/test"; Connection con = DriverManager.getConnection(dbURL, "root", "123"); Statement st = con.createStatement(); Savepoint svpt = null; try { con.setAutoCommit(false); st.executeUpdate("insert into user(id,name) values(13, 'Xiao Li')"); st.executeUpdate("insert into user(id,name) values(14, 'Xiao Wang')"); svpt = con.setSavepoint("roll back to here"); st.executeUpdate("insert into user(id,name) values(15, 'Xiao Zhao')"); st.executeUpdate("insert into user(id,name) values(13, 'Xiao Li')"); con.commit(); } catch(Exception ex) { ex.printStackTrace(); con.rollback(svpt); } finally { con.setAutoCommit(true); showUser(st); if (st != null) st.close(); if (con != null) con.close(); }}执行结果如下:
=====Simple Transaction test=====com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException: Duplicate entry '13' for key 'PRIMARY' at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:931) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2870) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1573) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665) at com.mysql.jdbc.Connection.execSQL(Connection.java:3170) at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1316) at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1235) at sample.jdbc.mysql.ResultSetSample.transactionTest2(ResultSetSample.java:185) at sample.jdbc.mysql.ResultSetSample.main(ResultSetSample.java:18)ID:1; NAME=Zhang SanID:2; NAME=TESTID:5; NAME=Lei FengID:13; NAME=Xiao LiID:14; NAME=Xiao Wang
可以看到最终事务报出了主键冲突异常,事务回滚,但是依然向数据库中插入了ID为13和14的记录。
另外,在确定SavePoint后,ID为15的记录并没有被插入,它是通过事务进行了回滚。
- Java回顾之JDBC
- Java回顾之JDBC
- Java技术回顾之JDBC:基本用法
- Java技术回顾之JDBC:连接池
- Java技术回顾之JDBC:连接池
- Java技术回顾之JDBC:JDBC的事务支持
- Java技术回顾之JDBC:JDBC的事务支持
- Java技术回顾之JDBC:ResultSet和RowSet
- Java技术回顾之JDBC:ResultSet和RowSet
- Java回顾之集合
- Java回顾之集合
- Java回顾之反射
- JDBC回顾
- JDBC回顾
- JDBC回顾
- JDBC回顾
- JDBC回顾
- jdbc回顾
- php中操作数据库
- 麻烦11月对我好一点
- Crisis of HDU 还是母函数
- Graphviz
- 51NOD-1674 区间的价值 V2
- Java回顾之JDBC
- Unix下的一些傻瓜问题
- GoogLeNet
- IOS-性能优化/内存优化常用方法小总结
- Mybatis的ResultMap的使用
- 杂花生树(十九)
- 常见对象_Object类的equals()方法
- 远隔天涯
- V4L2(一)框架浅析