JDBC的进阶知识和语法[以mysql为例作Demo] - mysql数据

  一丨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)THEN SET temp = id1; ELSE SET 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测试数据mit(); //执行 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)

查看更多关于JDBC的进阶知识和语法[以mysql为例作Demo] - mysql数据的详细内容...