一丨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.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)