欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

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

程序员文章站 2022-05-19 20:50:58
...
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
相关标签: mysql 知识