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

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