1.prepareStatement可以替换变量
在SQL语句中可以包含?,可以用
ps=conn.prepareStatement("select * from Cust where ID=?");
int sid=1001;
ps.setInt(1, sid);
rs = ps.executeQuery();
可以把?替换成变量。
2.Statement只能用
int sid=1001;
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select * from Cust where ID="+sid);
来实现
3.PrepareStatement是欲编译方式执行SQL ,多次调用此连接时,速度要比Statement快 ,所以建议用PrepareStatement
例子如下:
下面的程序都有注释,你应该能看懂了! imp ort java.sql.*; public class UsePreparedStatement { public static void main(String[] args) { String url = "jdbc:mysql://localhost/sql_test"; String userName = "root"; String password = "root"; Connection conn = null; try {
//1.加载驱动 Class.forName("com.mysql.jdbc.Driver"); } catch(ClassNotFoundException e) { System.out.println("加载驱动器类时出现异常"); } try {
//2.获取连接 conn = DriverManager.getConnection(url, userName, password); //3.创建PreparedStatement语句 PreparedStatement pstmtDelete = conn.prepareStatement( "DELETE FROM student WHERE stu_id>=?"); PreparedStatement pstmtInsert = conn.prepareStatement( "INSERT INTO student VALUES(?, ?, ?, ?)"); PreparedStatement pstmtSelect = conn.prepareStatement( "SELECT * FROM student WHERE stu_id>=? " + "ORDER BY stu_id"); int id = 15; //使用setXXX方法设置IN参数 pstmtSelect.setString(1, Integer.toString(id)); //多次执行同一语句 for (int i=0; i<3; i++, id++) { //4.使用setXXX方法设置IN参数 pstmtDelete.setString(1, Integer.toString(id)); pstmtInsert.setString(1, Integer.toString(id)); pstmtInsert.setString(2, "name"+id); pstmtInsert.setString(3, "city"+id); pstmtInsert.setDate(4, new Date(78, 2, id)); //5.执行PreparedStatement语句 pstmtDelete.executeUpdate(); pstmtInsert.executeUpdate(); ResultSet rs = pstmtSelect.executeQuery(); System.out.println(""); System.out.println("第 " + (i+1) + " 次循环后的结果集为:"); //显示返回的结果集 while (rs.next()) { String stuID = rs.getString(1); String name = rs.getString(2); String address = rs.getString(3); String birthday= rs.getString(4); System.out.println(stuID + " " + name + " " + address + " " + birthday); } } pstmtDelete.close(); pstmtInsert.close(); pstmtSelect.close(); } catch(SQLException e) { System.out.println("出现SQLException异常"); } finally { //6.关闭语句和数据库连接 try { if (conn != null) conn.close(); } catch(SQLException e) { System.out.println("关闭数据库连接时出现异常"); } } } }