PreparedStatement从Statement继承而来。
同构sql:sql语句基本一样,只是具体参数数值不同。
异构sql:sql语句完全不一样。
Statement不足:
1. 效率比较低 2. 对字段类型的支持比较差 3. 语法含义不清晰(结构不清楚)。
由于编译不需要参数,PreparedStatement可以使用“?”来替代sql语句中的某些参数,它先将不带参数的sql语句发送到数据库,进行预编译,然后PreparedStatement会再将设置好的参数发送给数据库。
在使用PreparedStatement设置相应参数时,要指明参数的位置和类型,以及给出参数的具体值,根据不同的参数类型使用不同的setXXX(参数的位置,参数值)来设置参数。
如:String sql=”update student set name=? where id=4;”
//其中的?代表占位符,在这里并没有设置具体值。
PreparedStatement pstm=con.prepareStatement(sql);
// sql语句已经发送到数据库去编译了,即预编译。
pstm.setXXX(参数的位置,参数的值)
//把参数值存放在PreparedStatement对象中。
Pstm.executeUpdate();
// 由于已经预编译过,因此不需要再传入sql语句,就可以直接执行。
Statement代码如下:
package com.ambow.day19.jdbc;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import com.ambow.day19.jdbc.util.JDBCConAndClo;
//注:当执行多插入和多修改时可以使用批量处理addBatch,executeBatch;
public class JDBCStatementTest {
public static void main(String args[]){
Connection con = null;
Statement stm = null;
ResultSet rs = null;
try {
//1.加载JDBC驱动和连接数据库
con=JDBCConAndClo.getConnectionBao();
System.out.println("con="+con);
// //*用Statement向数据库插入数据:
// String sql1="insert into student values(12,'wang','java',55)";
// String sql2="insert into student values(13,'wang','java',95)";
// String sql3="insert into student values(14,'wadedng','java',45)";
// stm = con.createStatement();
// stm.executeUpdate(sql1);
// stm.executeUpdate(sql2);
// stm.executeUpdate(sql3);
// System.out.println("插入成功!");
//*用Statement从数据库中删除数据:
String sql11="delete from student where id=1";
String sql12="delete from student where id=2";
String sql13="delete from student where id=3";
stm = con.createStatement();
stm.executeUpdate(sql11);
stm.executeUpdate(sql12);
stm.executeUpdate(sql13);
System.out.println("删除成功!");
//*用Statement从数据库查询数据:
//2. 执行sql语句:
String sql = "select * from student";
// 创建一个statement(发送sql)
stm = con.createStatement();
// 执行查询sql语句
rs = stm.executeQuery(sql);
// 3.获取sql结果集:
while(rs.next()){
System.out.print(rs.getString("id")+" ");
System.out.print(rs.getString("name")+" ");
System.out.print(rs.getString("course")+" ");
System.out.println(rs.getString("score"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
//4.关闭数据库,并释放资源:
JDBCConAndClo.closeResultSet(rs);
JDBCConAndClo.closeStatement(stm);
JDBCConAndClo.closeConnection(con);
}
}
}
PreparedStatement代码如下:
package com.ambow.day19.jdbc;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.ambow.day19.jdbc.util.JDBCConAndClo;
//注:当执行多插入和多修改时可以使用批量处理addBatch,executeBatch;
public class JDBCPreparedStatementTest {
public static void main(String args[]){
Connection con=null;
PreparedStatement pstm=null;
ResultSet rs=null;
try {
con=JDBCConAndClo.getConnectionBao();
//*用PreparedStatement向数据库中插入数据;
//String sql="insert into student values(10,'李四','高数',90)";
String sql="insert into student values(?,?,?,?)";
//1.先创建PreparedStatement语句(发送slq请求):
pstm=con.prepareStatement(sql);
//2.在设置sql语句:
pstm.setInt(1,11);
pstm.setString(2,"wangqinqin");
pstm.setString(3, "hibernate");
pstm.setInt(4, 85);
//3.再执行sql语句:
pstm.executeUpdate();
System.out.println("插入成功!");
//*用PreparedStatement从数据库中删除数据;
String sql2="delete from student where id=?";
pstm=con.prepareStatement(sql2);
pstm.setInt(1,5);
pstm.executeUpdate();
System.out.println("删除成功!");
//*用PreparedStatement从数据库中查询出数据;
String sql1="select * from student where id=?";
pstm=con.prepareStatement(sql1);
pstm.setInt(1,8);
rs=pstm.executeQuery();
System.out.println("查询结果为:");
//循环取得结果;
while(rs.next()){
System.out.print(rs.getString("id")+" ");
System.out.print(rs.getString("name")+" ");
System.out.print(rs.getString("course")+" ");
System.out.println(rs.getString("score"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
JDBCConAndClo.closeResultSet(rs);
JDBCConAndClo.closePreparedStatement(pstm);
JDBCConAndClo.closeConnection(con);
}
}
}
其中连接和关闭数据库已经封装到另一个包JDBCConAndClo类中:
package com.ambow.day19.jdbc.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCConAndClo {
public static void main(String args[]) {
JDBCConAndClo jc = new JDBCConAndClo();
jc.getConnectionBao();
}
//加载JDBC驱动程序和连接数据库;
public static Connection getConnectionBao() {
Connection con = null;
String URL = "jdbc:oracle:thin:@localhost:1521:ambow";
String user = "system";
String password = "wqq123";
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
con = DriverManager.getConnection(URL, user, password);
if (!con.isClosed()) {
System.out.println("连接数据库成功!");
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
System.out.println("con=" + con);
return con;
}
//关闭ResultSet
public static void closeResultSet(ResultSet rs) {
if (rs != null) {
try {
rs.close();
rs = null;
} catch (SQLException e) {
e.printStackTrace();
}
}
}
//关闭Statement
public static void closeStatement(Statement stm) {
if (stm != null) {
try {
stm.close();
stm = null;
} catch (SQLException e) {
e.printStackTrace();
}
}
}
//关闭PreparedStatement
public static void closePreparedStatement(PreparedStatement pstm) {
if (pstm != null) {
try {
pstm.close();
pstm = null;
} catch (SQLException e) {
e.printStackTrace();
}
}
}
//关闭Connection
public static void closeConnection(Connection con) {
if (con != null) {
try {
con.close();
con = null;
} catch (SQLException e) {
e.printStackTrace();
}
con = null;
}
}
}
挺好的,转自http://wangqinqin.iteye.com/blog/547275