学步园

继续昨天的话题，昨天讲了JDBC的基本概念和操作，但当sql语句要传入参数的时候为了防止sql入侵，一般不用拼接sql的方法，而使用PreparedStatement。PreparedStatement不但安全，而且效率高。下面举个例子对比他们的效率，eg:（连接写在了配置文件里了，具体看我昨天的博客）

public class PreparedStatementTest<br /> {<br /> private String driver;<br /> private String url;<br /> private String user;<br /> private String pass;<br /> Connection conn;<br /> Statement stmt;<br /> PreparedStatement pstmt;</p> <p> public void initParam(String paramFile)throws Exception<br /> {<br /> //使用Properties类来加载属性文件<br /> Properties props = new Properties();<br /> props.load(new FileInputStream(paramFile));<br /> driver = props.getProperty("driver");<br /> url = props.getProperty("url");<br /> user = props.getProperty("user");<br /> pass = props.getProperty("pass");<br /> }</p> <p> public void insertUseStatement()throws Exception<br /> {<br /> long start = System.currentTimeMillis();<br /> try<br /> {<br /> //使用Connection来创建一个Statment对象<br /> stmt = conn.createStatement();<br /> //需要使用100条SQL语句来插入100条记录<br /> for (int i = 0; i < 100 ; i++ )<br /> {<br /> stmt.executeUpdate("insert into student_table values(null,'姓名"<br /> + i + "',1)");<br /> }<br /> System.out.println("使用Statement费时:" + (System.currentTimeMillis() - start));<br /> }<br /> //使用finally块来关闭数据库资源<br /> finally<br /> {<br /> if (stmt != null)<br /> {<br /> stmt.close();<br /> }<br /> }<br /> }</p> <p> public void insertUsePrepare()throws Exception<br /> {<br /> long start = System.currentTimeMillis();<br /> try<br /> {<br /> //使用Connection来创建一个PreparedStatment对象<br /> pstmt = conn.prepareStatement("insert into student_table values(null,?,1)");<br /> //100次为PreparedStatement的参数设置，就可以插入100条记录<br /> for (int i = 0; i < 100 ; i++ )<br /> {<br /> pstmt.setString(1 , "姓名" + i);<br /> pstmt.executeUpdate();<br /> }<br /> System.out.println("使用PreparedStatement费时:" + (System.currentTimeMillis() - start));<br /> }<br /> //使用finally块来关闭数据库资源<br /> finally<br /> {<br /> if (pstmt != null)<br /> {<br /> pstmt.close();<br /> }<br /> }</p> <p> }<br /> //定义打开连接的方法<br /> public void getConn()throws Exception<br /> {<br /> if (conn == null)<br /> {<br /> //加载驱动<br /> Class.forName(driver);<br /> //获取数据库连接<br /> conn = DriverManager.getConnection(url , user , pass);<br /> }<br /> }<br /> //定义关闭连接的方法<br /> public void closeConn()throws Exception<br /> {<br /> if (conn != null)<br /> {<br /> conn.close();<br /> }<br /> }</p> <p> public static void main(String[] args) throws Exception<br /> {<br /> PreparedStatementTest pt = null;<br /> try<br /> {<br /> pt = new PreparedStatementTest();<br /> pt.initParam("mysql.ini");<br /> pt.getConn();<br /> pt.insertUseStatement();<br /> pt.insertUsePrepare();<br /> }<br /> finally<br /> {<br /> pt.closeConn();<br /> }<br /> }<br /> }

下面再讲下如何使用JDBC调数据库的存储过程，eg

存储过程(MySql)：

create procedure add_pro(a int, b int, out sum int)<br /> begin<br /> set sum=a+b;<br /> end;

调用：（注意传出参数需要注册）

public class CallableStatementTest<br /> {<br /> private String driver;<br /> private String url;<br /> private String user;<br /> private String pass;<br /> Connection conn;<br /> CallableStatement cstmt;</p> <p> public void initParam(String paramFile)throws Exception<br /> {<br /> //使用Properties类来加载属性文件<br /> Properties props = new Properties();<br /> props.load(new FileInputStream(paramFile));<br /> driver = props.getProperty("driver");<br /> url = props.getProperty("url");<br /> user = props.getProperty("user");<br /> pass = props.getProperty("pass");<br /> }</p> <p> public void callProcedure()throws Exception<br /> {<br /> try<br /> {<br /> //加载驱动<br /> Class.forName(driver);<br /> //获取数据库连接<br /> conn = DriverManager.getConnection(url , user , pass);<br /> //使用Connection来创建一个CallableStatment对象<br /> cstmt = conn.prepareCall("{call add_pro(?,?,?)}");<br /> cstmt.setInt(1, 4);<br /> cstmt.setInt(2, 5);<br /> //注册CallableStatement的第三个参数是int类型<br /> cstmt.registerOutParameter(3, Types.INTEGER);<br /> //执行存储过程<br /> cstmt.execute();<br /> //获取，并输出存储过程传出参数的值。<br /> System.out.println("执行结果是: " + cstmt.getInt(3));<br /> }<br /> //使用finally块来关闭数据库资源<br /> finally<br /> {<br /> if (cstmt != null)<br /> {<br /> cstmt.close();<br /> }<br /> if (conn != null)<br /> {<br /> conn.close();<br /> }<br /> }<br /> }</p> <p> public static void main(String[] args) throws Exception<br /> {<br /> CallableStatementTest ct = new CallableStatementTest();<br /> ct.initParam("mysql.ini");<br /> ct.callProcedure();<br /> }<br /> }

然后再讲下可滚动，可更新的结果集，eg（其实就是在Statement创建时多加2个参数）

public class ResultSetTest<br /> {<br /> private String driver;<br /> private String url;<br /> private String user;<br /> private String pass;<br /> Connection conn;<br /> PreparedStatement pstmt;<br /> ResultSet rs;<br /> public void initParam(String paramFile)throws Exception<br /> {<br /> //使用Properties类来加载属性文件<br /> Properties props = new Properties();<br /> props.load(new FileInputStream(paramFile));<br /> driver = props.getProperty("driver");<br /> url = props.getProperty("url");<br /> user = props.getProperty("user");<br /> pass = props.getProperty("pass");<br /> }</p> <p> public void query(String sql)throws Exception<br /> {<br /> try<br /> {<br /> //加载驱动<br /> Class.forName(driver);<br /> //获取数据库连接<br /> conn = DriverManager.getConnection(url , user , pass);<br /> //使用Connection来创建一个PreparedStatement对象<br /> //传入控制结果集可滚动，可更新的参数。<br /> pstmt = conn.prepareStatement(sql , ResultSet.TYPE_SCROLL_INSENSITIVE<br /> , ResultSet.CONCUR_UPDATABLE);<br /> rs = pstmt.executeQuery();<br /> rs.last();<br /> int rowCount = rs.getRow();<br /> for (int i = rowCount; i > 0 ; i-- )<br /> {<br /> rs.absolute(i);<br /> System.out.println(rs.getString(1) + "/t"<br /> + rs.getString(2) + "/t" + rs.getString(3));<br /> //修改单元格多对应的值<br /> rs.updateString(2 , "学生名" + i);<br /> //提交修改<br /> rs.updateRow();<br /> }<br /> }<br /> //使用finally块来关闭数据库资源<br /> finally<br /> {<br /> if (rs != null)<br /> {<br /> rs.close();<br /> }<br /> if (pstmt != null)<br /> {<br /> pstmt.close();<br /> }<br /> if (conn != null)<br /> {<br /> conn.close();<br /> }<br /> }<br /> }<br /> public static void main(String[] args) throws Exception<br /> {<br /> ResultSetTest rt = new ResultSetTest();<br /> rt.initParam("mysql.ini");<br /> rt.query("select * from student_table");<br /> }<br /> }<br />

注意可更新结果集，一般只能来自一张表，而其要包括主键，否则会引起更新失败。

最后讲下JDBC事务

其实使用很简单，只要关闭自动提交的功能conn。setAuntoCommit（false）然后要提交时用conn.commit,没有commit前后悔了就用rollback,eg

public class TransactionTest<br /> {<br /> private String driver;<br /> private String url;<br /> private String user;<br /> private String pass;<br /> Connection conn;<br /> Statement stmt;<br /> public void initParam(String paramFile)throws Exception<br /> {<br /> //使用Properties类来加载属性文件<br /> Properties props = new Properties();<br /> props.load(new FileInputStream(paramFile));<br /> driver = props.getProperty("driver");<br /> url = props.getProperty("url");<br /> user = props.getProperty("user");<br /> pass = props.getProperty("pass");<br /> }<br /> public void insertInTransaction(String[] sqls)throws Exception<br /> {<br /> try<br /> {<br /> //加载驱动<br /> Class.forName(driver);<br /> //获取数据库连接<br /> conn = DriverManager.getConnection(url , user , pass);<br /> //关闭自动提交，开启事务<br /> conn.setAutoCommit(false);<br /> //使用Connection来创建一个Statment对象<br /> stmt = conn.createStatement();<br /> for (String sql : sqls)<br /> {<br /> stmt.executeUpdate(sql);<br /> }<br /> //提交事务<br /> conn.commit();<br /> }<br /> //使用finally块来关闭数据库资源<br /> finally<br /> {<br /> if (stmt != null)<br /> {<br /> stmt.close();<br /> }<br /> if (conn != null)<br /> {<br /> conn.close();<br /> }<br /> }<br /> }<br /> public static void main(String[] args) throws Exception<br /> {<br /> TransactionTest tt = new TransactionTest();<br /> tt.initParam("mysql.ini");<br /> String[] sqls = new String[]{<br /> "insert into student_table values(null , 'aaa' ,1)",<br /> "insert into student_table values(null , 'bbb' ,1)",<br /> "insert into student_table values(null , 'ccc' ,1)",<br /> //下面这条SQL语句将会违反外键约束，<br /> //因为teacher_table中没有ID为5的记录。<br /> "insert into student_table values(null , 'ccc' ,5)"<br /> };<br /> tt.insertInTransaction(sqls);<br /> }<br /> }<br />