一、PreparedStatement
对于需要反复执行相同的sql语句时,Sun公司就为我们提供了另外一种对象:PreparedStatement。用它的好处就是:当数据库见到PreparedStatement的sql语句时,数据库端直接先到数据库缓冲区当中找它,如找不到,则会编译它一次。如能找到,就直接用。而对于Statement对象,每执行一次,都需要把SQL语句传输到数据库端,数据库要对每一次来的SQL语句进行编译处理。
二、示例
PreparedStatement和Statement同时插入1万条数据比较所用时间
public class BaseDao {
String driver="com.mysql.jdbc.Driver";
String url="jdbc:mysql://localhost:3306/test2?characterEncoding=utf-8";
String user="root";
String password="root";
//使用PreparedStatement insert update delete
public void executeNoQuery(String sql, Object[] params) {
Connection conn=null;
PreparedStatement pre=null;
try {
Class.forName(driver); //加载驱动
conn =DriverManager.getConnection(url, user, password);//创建连接
pre =conn.prepareStatement(sql);
for (int i = 0; i < params.length; i++) {
pre.setObject(i + 1, params[i]);
}
pre.execute();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
try {
conn.close();
pre.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
//使用Statement 执行insert update delete
public void executeNoQuery(String sql) {
Connection conn=null;
Statement stmt=null;
try {
//加载驱动
Class.forName(driver);
//创建连接
conn =DriverManager.getConnection(url, user, password);
stmt =conn.createStatement();
stmt.execute(sql);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
try {
conn.close();
stmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
//编写测试类
public class start {
public static void main(String[] args) {
BaseDao baseDao =new BaseDao();
long stmt= System.currentTimeMillis();
for(int i=1;i<=1000;i++) {
String sql="insert into master(id,name,price) values('"+i+"','"+"忘忧"+i+"','"+i+"') ";
baseDao.executeNoQuery(sql);
}
long stmt2=System.currentTimeMillis();
System.out.println("Statement执行插入1000条数据所用时间:"+(stmt2-stmt));
long pre= System.currentTimeMillis();
String sql2="insert into master(id,name,price) VALUES(?,?,?)";
for(int c=1001;c<=2001;c++) {
Object[] params= {c,"lotus"+c,1000+c};
baseDao.executeNoQuery(sql2, params);
}
long pre2= System.currentTimeMillis();
System.out.println("PreparedStatement执行插入1000条数据所用时间:"+(pre2-pre));