JDBC当中PreparedStatement和Statement的效率比较

一、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));