package test.jdbc;
import java.sql.*;
public class JDBCTest {
private static Connection conn = null;
static{
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.0.1:1521:SXT", "scott", "tiger");
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//最基本的操作读取
public void testStmt(){
ResultSet rs = null;
Statement stmt = null;
try {
stmt = conn.createStatement();
rs = stmt.executeQuery("select * from dept");
while(rs.next()) {
System.out.println(rs.getString("deptno"));
System.out.println(rs.getInt("deptno"));
}
rs.close();
stmt.close();
conn.close();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//PreparedStatement读取
public void testPrepStmt(){
PreparedStatement pstmt = null;
try {
pstmt = conn.prepareStatement("insert into dept2 values (?, ?, ?)");
pstmt.setInt(1, 33);
pstmt.setString(2, "name");
pstmt.setString(3, "loc");
pstmt.executeUpdate();
pstmt.close();
conn.close();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//调用存储过程
public void testProc(){
CallableStatement cstmt;
try {
cstmt = conn.prepareCall("{call p(?, ?, ?, ?)}");
cstmt.registerOutParameter(3, Types.INTEGER);
cstmt.registerOutParameter(4, Types.INTEGER);
cstmt.setInt(1, 3);
cstmt.setInt(2, 4);
cstmt.setInt(4, 5);
cstmt.execute();
System.out.println(cstmt.getInt(3));
System.out.println(cstmt.getInt(4));
cstmt.close();
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//执行批处理
public void testBatch(){
/*
9. Statement stmt = conn.createStatement();
10. stmt.addBatch("insert into dept2 values (51, '500', 'haha')");
11. stmt.addBatch("insert into dept2 values (52, '500', 'haha')");
12. stmt.addBatch("insert into dept2 values (53, '500', 'haha')");
13. stmt.executeBatch();
14. stmt.close();
15. */
try {
PreparedStatement ps = conn.prepareStatement("insert into dept2 values (?, ?, ?)");
ps.setInt(1, 61);
ps.setString(2, "haha");
ps.setString(3, "bj");
ps.addBatch();
ps.setInt(1, 62);
ps.setString(2, "haha");
ps.setString(3, "bj");
ps.addBatch();
ps.setInt(1, 63);
ps.setString(2, "haha");
ps.setString(3, "bj");
ps.addBatch();
ps.executeBatch();
ps.close();
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//将整批分批执行批处理
//这才是理想的解决方案,它避免了SQL注入和内存不足的问题。看看我们如何递增计数器计数,一旦BATCHSIZE 达到 1000,我们调用executeBatch
public void testBatchs(){
String sql = "insert into employee (name, city, phone) values (?, ?, ?)";
PreparedStatement ps = conn.prepareStatement(sql);
final int batchSize = 1000;
int count = 0;
for (Employee employee: employees) {
ps.setString(1, employee.getName());
ps.setString(2, employee.getCity());
ps.setString(3, employee.getPhone());
ps.addBatch();
if(++count % batchSize == 0) {
ps.executeBatch();
}
}
ps.executeBatch(); // insert remaining records
ps.close();
conn.close();
}
//执行事务
public void testTransaction(){
Statement stmt = null;
try {
conn.setAutoCommit(false);
stmt = conn.createStatement();
stmt.addBatch("insert into dept2 values (51, '500', 'haha')");
stmt.addBatch("insert into dept2 values (52, '500', 'haha')");
stmt.addBatch("insert into dept2 values (53, '500', 'haha')");
stmt.executeBatch();
conn.commit();
} catch (SQLException e) {
try {
conn.rollback();
conn.setAutoCommit(true);
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
e.printStackTrace();
}finally{
try {
if(stmt != null)
stmt.close();
if(conn != null)
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}