疯狂JAVA讲义---第十三章：JDBC编程（下）

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

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

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

存储过程(MySql)：

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

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

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

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

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

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

最后讲下JDBC事务

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

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