bitscn.com
 一丨statement 1.1 perparedstatement （准备statement,解决参数类型问题） 
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;end
public 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