bitsCN.com
一丨Statement
1.1 PerparedStatement （准备Statement,解决参数类型问题）
PHP高级开发技巧与范例
下载
PHP是一种功能强大的网络程序设计语言，而且易学易用，移植性和可扩展性也都非常优秀，本书将为读者详细介绍PHP编程。 全书分为预备篇、开始篇和加速篇三大部分，共9章。预备篇主要介绍一些学习PHP语言的预备知识以及PHP运行平台的架设；开始篇则较为详细地向读者介绍PKP语言的基本语法和常用函数，以及用PHP如何对MySQL数据库进行操作；加速篇则通过对典型实例的介绍来使读者全面掌握PHP。 本书
public static PreparedStatement getPreparedStatement(Connection conn,String sql){ try { pstmt = conn.prepareStatement(sql); } catch (SQLException e) { System.err.println("*Faild In CreateStatement By Connection"); e.printStackTrace(); } return pstmt; }
package com.qsuron.util;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import com.qsuron.util.DB;public class Test2 { public static void main(String[] args) throws SQLException { Connection conn = DB.getConnection("jdbc:mysql://xxx.xxx.xxx.xxx:xxxx/qsuron","qsuron","qsuron"); PreparedStatement pstmt = DB.getPreparedStatement(conn,"insert into student values(?,?,?);"); pstmt.setInt(1,1213400129); pstmt.setString(2,"123456"); pstmt.setString(3,"qsuron"); pstmt.executeUpdate(); DB.close(); }}
1.2 CallableStatement （存储过程）
创建一个存储过程
CREATE DEFINER=`root`@`localhost` PROCEDURE `p`(IN `id1` int,IN `id2` int,IN `password` char(20),IN `name` varchar(15),OUT `temp` int)BEGIN#插入id较大的，返回表中数据数IF(id1>id2)THENSET temp = id1;ELSESET temp = id2;end if;INSERT into student VALUES(temp,password,name);select COUNT(*) INTO temp from student;ENDpublic static CallableStatement getCallableStatement(Connection conn,String sql){ try { pcstmt = conn.prepareCall(sql); } catch (SQLException e) { System.err.println("*Faild In CreateStatement By Connection"); e.printStackTrace(); } return pcstmt; }
package com.qsuron.test;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.SQLException;import java.sql.Types;import com.qsuron.util.DB;public class Test3 { public static void main(String[] args) throws SQLException { Connection conn = DB.getConnection(); CallableStatement pcstmt = DB.getCallableStatement(conn,"{call p(?,?,?,?,?)}"); pcstmt.setInt(1,1213400103); pcstmt.setInt(2,1213400104); pcstmt.setString(3,"123456"); pcstmt.setString(4,"qsuron"); pcstmt.registerOutParameter(5,Types.INTEGER); pcstmt.execute(); System.out.println("Return : " + pcstmt.getInt(5)); DB.close(); }}
1.XX 未完待续
二丨Batch 批处理
package com.qsuron.test;import java.sql.Connection;import java.sql.SQLException;import java.sql.Statement;import com.qsuron.util.DB;public class Test4 { public static void main(String[] args) throws SQLException { Connection conn = DB.getConnection(); Statement stmt = DB.getStatement(conn); stmt.addBatch("insert into student values ('1213400131','1','Q');"); stmt.addBatch("insert into student values ('1213400132','1','Q');"); stmt.addBatch("insert into student values ('1213400133','1','Q');"); stmt.executeBatch(); DB.close(); }}同理，PreparedStatement 也可使用Batch
package com.qsuron.test;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.SQLException;import com.qsuron.util.DB;public class Test5 { public static void main(String[] args) throws SQLException { Connection conn = DB.getConnection(); PreparedStatement pstmt = DB.getPreparedStatement(conn,"insert into student values(?,?,?);"); pstmt.setInt(1,1213400141); pstmt.setString(2,"1"); pstmt.setString(3,"Q"); pstmt.addBatch(); pstmt.setInt(1,1213400142); pstmt.setString(2,"1"); pstmt.setString(3,"Q"); pstmt.addBatch(); pstmt.setInt(1,1213400143); pstmt.setString(2,"1"); pstmt.setString(3,"Q"); pstmt.addBatch(); pstmt.executeBatch(); DB.close(); }}
三丨继Batch之Transaction Google翻译
缘由：如A转账予B，那么JDBC至少要操作2条UPDATE语句(A减B加)，Transaction就是为了保证这两条语句必须同时执行成功或者同时执行失败。
package com.qsuron.test;import java.sql.Connection;import java.sql.SQLException;import java.sql.Statement;import com.qsuron.util.DB;public class Test6 { public static void main(String[] args) throws SQLException { Connection conn = DB.getConnection(); Statement stmt = DB.getStatement(conn); try { conn.setAutoCommit(false); //将自动提交设置为false，将多条语句积累到一起 stmt.addBatch("insert into student values ('1213400135','1','Q');"); stmt.addBatch("insert into student values ('1213400136','1','Q');"); stmt.addBatch("insert into student values ('1213400134','1','Q');"); stmt.executeBatch(); conn.commit(); //执行 conn.setAutoCommit(true); //重置自动提交 } catch (Exception e) { //如果抓到异常就现场恢复 if(conn!=null){ conn.rollback(); //数据回滚 System.out.println("Exception:Rollback!"); conn.setAutoCommit(true); } } DB.close(); }}测试方法：让中间的语句的id发生主键唯一错误。
四丨ResultSet 结果集
1.前后滚动机制
package com.qsuron.test;import java.sql.Connection;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import com.qsuron.util.DB;public class Test7 { public static void main(String[] args) throws SQLException { Connection conn = DB.getConnection(); Statement stmt = DB.getStatement(conn,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY); ResultSet rs = DB.query(stmt,"select * from student order by id;"); rs.last(); System.out.println("当前行数:"+rs.getRow()); System.out.println(rs.getString(1)); rs.previous(); System.out.println(rs.getString(1)); rs.absolute(7); System.out.println(rs.getString(1)); DB.close(); }}
2.JDBC之ResultSet对象-注意事项(点击前往)
转载请注明出处:blog.csdn.net/qsuron 小树博客(qsuron)
bitsCN.com
