PrepareStatement的用法

PrepareStatement是Statement的一个子接口，可以预编译SQL语句，预编译后的语句被存储在PrepareStatement的对象中，从而可以使用该对象多次高效的执行该SQL语句。并且PrepareStatement可以使用占位符编写SQL语句。下面是一个结合了Statement和PrepareStatement的程序：

package perpared;

import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;
import java.sql.PreparedStatement;
public class PerparedStatementTest {
	
	private String driver;
	private String url;
	private String user;
	private String pass;
	private Connection conn;
	private Statement stmt;
	private ResultSet rs;
	private PreparedStatement pstmt;

	public void initParam(String paramFile) throws Exception{
		Properties props=new Properties();
		props.load(new FileInputStream(paramFile));
		driver=props.getProperty("driver");
		url=props.getProperty("url");
		user=props.getProperty("user");
		pass=props.getProperty("pass");
	}
	public void initJdbc() {
		
		try{
			Class.forName(driver);
			conn=DriverManager.getConnection(url,user,pass);

			
		}
		catch(Exception e){
			
			e.printStackTrace();
		}
	}
	public void insertUseStatement() throws Exception{
		
		long start=System.currentTimeMillis();
		try{
			
			stmt=conn.createStatement();
			for(int i=0;i<100;i++){
				stmt.executeUpdate("insert into student_table(student_id,student_name)"+
					"values("+i+",'name');");
			}
			System.out.println(System.currentTimeMillis()-start);

		}
		finally{
			if(stmt!=null){stmt.close();}
			
		}
	}
	//PerparedStatement相对于Statement可以更为的高效
	public void insertUsePerpare() throws Exception{
		long start=System.currentTimeMillis();
		try{
			//使用占位符?,?
			pstmt=conn.prepareStatement("insert into student_table values(?,?);");
			//插入100条记录
			for(int i=0;i<100;i++){
				pstmt.setString(2,"name"+i);
				pstmt.setInt(1, i);
				pstmt.executeUpdate();
			}
			System.out.println(System.currentTimeMillis()-start);
		}
		finally{
			if(pstmt!=null){pstmt.close();}
		}
	}
	
	public void deleteUsePerpare() throws Exception{
		
		try{
			pstmt=conn.prepareStatement("delete from student_table where student_id=? "
					+ "or student_name=?");
			for(int i=0;i<100;i++){
				pstmt.setInt(1, i);
				pstmt.setString(2, "name"+i);
				pstmt.executeUpdate();
			}
			
		}
		finally{
			if(conn!=null){conn.close();}
			if(pstmt!=null){pstmt.close();}
			
		}
	}
	public static void main(String[] args) throws Exception{
		
		PerparedStatementTest pst=new PerparedStatementTest();
		pst.initParam("mysql.ini");
		pst.initJdbc();
		pst.insertUsePerpare();
		pst.deleteUsePerpare();
	}
	
	

}

其中的配置文件mysql.ini在上一篇中已经写过了，所以就不再写了。

感觉这种占位符对于数据库的记录增加，删除，修改确实挺有好处的。