1.操作DML
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class TestDML {
public static void main(String[] args) {
// Statement stmt = null;
PreparedStatement pstmt = null;
Connection conn = null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager
.getConnection("jdbc:mysql://localhost:3306/mydata", "root", "root");
// stmt = conn.createStatement();
// String sql = "insert into dept values (60, 'F', 'F')";
// stmt.executeUpdate(sql);
pstmt = conn.prepareStatement("insert into dept values (?, ?, ?)");
pstmt.setInt(1, 70);
pstmt.setString(2, "G");
pstmt.setString(3, "G");
pstmt.executeUpdate();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
// if (stmt != null) {
// stmt.close();
// stmt = null;
// }
if (pstmt != null) {
pstmt.close();
pstmt = null;
}
if (conn != null) {
conn.close();
conn = null;
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
2.调用存储过程
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Types;
public class TestProc {
public static void main(String[] args) {
CallableStatement cstmt = null;
Connection conn = null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager
.getConnection("jdbc:mysql://localhost:3306/mydata", "root", "root");
// p (v_a in number, v_b number, v_c out number, v_d in out number)
cstmt = conn.prepareCall("(call p(?, ?, ?, ?))");
// 指定第三个和第四个是输出参数
cstmt.registerOutParameter(3, Types.INTEGER);
cstmt.registerOutParameter(4, Types.INTEGER);
// 给输入参数赋值
cstmt.setInt(1, 30);
cstmt.setInt(2, 40);
cstmt.setInt(4, 50);
cstmt.execute();
System.out.println(cstmt.getInt(3) + " " + cstmt.getInt(4));
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (cstmt != null) {
cstmt.close();
cstmt = null;
}
if (conn != null) {
conn.close();
conn = null;
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
3.batch批处理
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
/**
* 这里面做了事务的管理,共五步骤[不要求代码严谨的话,可以不要这五步骤]
*
*/
public class TestBatch {
public static void main(String[] args) {
// Statement stmt = null;
PreparedStatement pstmt = null;
Connection conn = null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager
.getConnection("jdbc:mysql://localhost:3306/mydata", "root", "root");
conn.setAutoCommit(false);// 1.设置不自动提交
// stmt = conn.createStatement();
// stmt.addBatch("insert into dept values (80, 'H', 'H')");
// stmt.addBatch("insert into dept values (90, 'I', 'I')");
// stmt.addBatch("insert into dept values (100, 'J', 'J')");
// stmt.executeBatch();
pstmt = conn.prepareStatement("insert into dept values (?, ?, ?)");
pstmt.setInt(1, 110);
pstmt.setString(2, "K");
pstmt.setString(3, "K");
pstmt.addBatch();
pstmt.setInt(1, 120);
pstmt.setString(2, "L");
pstmt.setString(3, "L");
pstmt.addBatch();
pstmt.setInt(1, 130);
pstmt.setString(2, "M");
pstmt.setString(3, "M");
pstmt.addBatch();
pstmt.executeBatch();
conn.commit();// 2.手动提交
conn.setAutoCommit(true);// 3.回复自动提交
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
try {
if (conn != null) {
conn.rollback();// 4.回滚事务
conn.setAutoCommit(true);// 5.回复自动提交
}
} catch (SQLException e1) {
e1.printStackTrace();
}
} finally {
try {
// if (stmt != null) {
// stmt.close();
// stmt = null;
// }
if (pstmt != null) {
pstmt.close();
pstmt = null;
}
if (conn != null) {
conn.close();
conn = null;
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
4.可滚动的结果集
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestScroll {
public static void main(String[] args) {
ResultSet rs = null;
Statement stmt = null;
Connection conn = null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager
.getConnection("jdbc:mysql://localhost:3306/mydata", "root", "root");
// 可滚动的结果集[对于滚动不敏感,并且只读]
stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
rs = stmt.executeQuery("select * from dept");
rs.next();// 结果集向后移一位
System.out.println(rs.getString("dname") + " " + rs.getInt("deptno"));
rs.last();// 结果集定位到最后一位
System.out.println(rs.getString("dname") + " " + rs.getInt("deptno"));
System.out.println(rs.isLast());// 是否最后一条
System.out.println(rs.isAfterLast());// 是否最后的下一条
System.out.println(rs.getRow());// 当前在哪个位置
rs.previous();// 结果集向前移一位
System.out.println(rs.getString("dname") + " " + rs.getInt("deptno"));
rs.absolute(6);// 定位结果集到第六位
System.out.println(rs.getString("dname") + " " + rs.getInt("deptno"));
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (rs != null) {
rs.close();
rs = null;
}
if (stmt != null) {
stmt.close();
stmt = null;
}
if (conn != null) {
conn.close();
conn = null;
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
5.可更新的结果集
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
* mysql支持可更新的结果集,而oracle不支持
*
*/
public class TestUpdateResultSet {
public static void main(String[] args) {
ResultSet rs = null;
Statement stmt = null;
Connection conn = null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager
.getConnection("jdbc:mysql://localhost:3306/mydata", "root", "root");
stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
rs = stmt.executeQuery("select * from dept");
rs.next();
// 更新一条数据
rs.updateString("dname", "AAAAA");
rs.updateRow();
// 插入一条数
rs.moveToInsertRow();
rs.updateInt("deptno", 140);
rs.updateString("dname", "N");
rs.updateString("loc", "N");
rs.insertRow();
// 将光标移到新建的行
rs.moveToCurrentRow();
// 删除第六行
rs.absolute(6);
rs.deleteRow();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (rs != null) {
rs.close();
rs = null;
}
if (stmt != null) {
stmt.close();
stmt = null;
}
if (conn != null) {
conn.close();
conn = null;
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}