基于Java回顾之JDBC的使用详解
程序员文章站
2023-12-12 15:35:10
尽管在实际开发过程中，我们一般使用orm框架来代替传统的jdbc，例如hibernate或者ibatis，但jdbc是java用来实现数据访问的基础，掌握它对于我们理解ja...
尽管在实际开发过程中，我们一般使用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 san
id:2; name=li si
id:3; name=wang wu
=====delete test=====
id:1; name=zhang san
id:2; name=li si
=====update test=====
id:1; name=zhang san
id:2; name=test
上面代码中的showuser方法会把user表中的所有记录打印出来。
如何调用存储过程
存储过程是做数据库开发时经常使用的技术，它可以通过节省编译时间的方式来提升系统性能，我们这里的示例使用mysql数据库。
如何调用不带参数的存储过程
假设我们现在有一个简单的存储过程，它只是返回user表中的所有记录，存储过程如下：
复制代码 代码如下:
create definer=`root`@`localhost` procedure `getuser`()
begin
select 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 san
id: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)
begin
set @sqlstr=concat('select * from user where id=', id);
prepare psmt from @sqlstr;
execute psmt;
end
java的调用代码如下：
复制代码 代码如下:
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 unsigned
column 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 san
id:2; name=test
id:4; name=xiao ming
=====update=====
id:1; name=zhang san
id:2; name=test
id:4; name=xiao qiang
=====delete=====
id:1; name=zhang san
id: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 san
id:2; name=test
id: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 san
id:2; name=test
id:5; name=lei feng
id:6; name=xiao zhang
id:7; name=xiao liu
id: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 san
id:2; name=test
id:5; name=lei feng
id:9; name=xiao zhang
id:10; name=xiao liu
id: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 san
id:2; name=test
id:5; name=lei feng
id:12; name=xiao li
=====simple transaction test=====
id:1; name=zhang san
id:2; name=test
id:5; name=lei feng
id:12; name=xiao li
com.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 san
id:2; name=test
id:5; name=lei feng
id:13; name=xiao li
id:14; name=xiao wang
可以看到最终事务报出了主键冲突异常，事务回滚，但是依然向数据库中插入了id为13和14的记录。
另外，在确定savepoint后，id为15的记录并没有被插入，它是通过事务进行了回滚。