preparedstatement实例

之前写过几个JDBC ,看完Java异常处理的陋习展播觉得自己的异常处理做的都不好 ,今儿找了个异常处理做的很好的 又改了个把小时 ,实践了一下 .

主要是异常要分开处理 , finally一定要释放资源 ,finally里还要try-catch , 释放资源前要有判断条件 ,

然后还有个意外收获是addBatch() 可以批量添加请求 然后一起执行

这玩意考试的意义大于实际操作的意义

以拿到连接为例 小分析一下:

    private static Connection getDBConnection(){
        //Connection conn = null; 后来改成类里边的属性了 ,不必每个方法都自己初始化一个
        try{
            Class.forName(DB_DRIVER);	//try中的语句尽量短
        }catch(ClassNotFoundException e){
            System.err.println(e.getMessage());
        }
        
        try{
            conn = DriverManager.getConnection(DB_CONNECTION,DB_USER, DB_PASSWORD);
        }catch(SQLException e){	//catch尽量多分类处理
            System.err.println(e.getMessage());
        }
        
        return conn;
    }

finally{
        	if (prep != null) //在finally里加了try-catch ,改成先if判断 后进入 try,也能提高一些效率 
            {
        		try
                {
                	prep.close();
                }
        		catch (SQLException ex1)
                {
                }
            }
              
        	if (conn != null)
            {
        		try
                {
                    conn.close();
                }
        		catch (SQLException ex1)
                {
                }
            }
            
        }

上边这种情况比下边更有效率

finally{
            try
            {
                if (prep != null)
                {
                	prep.close();
                }
            }
            catch (SQLException ex1)
            {
            }
            
            try
            {
                if (conn != null)
                {
                    conn.close();
                }
            }
            catch (SQLException ex1)
            {
            }
        }

完整代码:

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 prepJDBC {

    private static final String DB_DRIVER = "oracle.jdbc.driver.OracleDriver";
    private static final String DB_CONNECTION = "jdbc:oracle:thin:@localhost:1521:ORCL";
    private static final String DB_USER = "scott";
    private static final String DB_PASSWORD = "890307";
    
    private Connection conn = null;//这就不能是static的了 多个实例查询会出问题的
    private PreparedStatement prep = null;
    
    public static void main(String[] args) {
        try{
            //createTable();
            insertRecord();
            batchInsertRecords();
            deleteRecords();
        
            selectRecords();
        }catch(SQLException e){
            System.err.println(e.getMessage());
        }
    }
    
    /*
     * BUG:http://bugs.mysql.com/bug.php?id=4633d
     * INSERT INTO h_user(username) VALUES(?,?)
     * You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?,?)' at line 1
     *You're calling Statement.executeUpdate(String) with your prepared statement placeholder
     *query, which is incorrect. You should be calling PreparedStatement.executeUpdate().
     *Remove the reference to the query from your call to executeUpdate() and things should
     *work.
     */
    private static void insertRecord() throws SQLException{
    
        String sql = "INSERT INTO h_user"
            +"(id,username,password) VALUES"
            +"(?,?,?)";
        
        try{
            conn = getDBConnection();
            prep = conn.prepareStatement(sql);
            prep.setInt(1,250);
            prep.setString(2, "Kity");
            prep.setString(3, "woshikity");
            
            System.out.println(sql);
            //Executes the SQL statement in this PreparedStatement object
            prep.executeUpdate();//preparedStatement.executeUpdate(sql); error
    
            System.out.println("A Record has been inserted!");
        }catch(SQLException e){
            System.out.println(e.getMessage());
        }finally{
        	if (prep != null)
            {
        		try
                {
                	prep.close();
                }
        		catch (SQLException ex1)
                {
                }
            }
              
        	if (conn != null)
            {
        		try
                {
                    conn.close();
                }
        		catch (SQLException ex1)
                {
                }
            }
            
        }
    }
    private static void createTable() throws SQLException{
     
        String sql = "CREATE TABLE h_user("
            +"id NUMBER(9),"
            +"username VARCHAR2(50) NOT NULL,"
            +"password VARCHAR2(50) NOT NULL,"
            +"CONSTRAINT h_user_id_pk PRIMARY KEY(id)"
            +");";
        
        try{
            conn = getDBConnection();
            prep = conn.prepareStatement(sql);
            System.out.println(sql);
            prep.execute();
            System.out.println("Table \"h_user\" is created!");
        }catch(SQLException e){
            System.out.println(e.getMessage());
        }finally{
        	if (prep != null)
            {
        		try
                {
                	prep.close();
                }
        		catch (SQLException ex1)
                {
                }
            }
              
        	if (conn != null)
            {
        		try
                {
                    conn.close();
                }
        		catch (SQLException ex1)
                {
                }
            }
        }
        
    }
    
    private static void selectRecords() throws SQLException{
      
        String sql = "SELECT id,username,password FROM h_user where id > ?";
        try{
            conn = getDBConnection();
            prep = conn.prepareStatement(sql);
            prep.setInt(1, 3);
            System.out.println(sql);
            
            //execute select SQL statement
            
            ResultSet rs = prep.executeQuery();
            while(rs.next()){
                int id = rs.getInt("id");
                String username = rs.getString("username");
             System.out.println("id: "+ id);
             System.out.println("username: "+ username);
            }
        }catch(SQLException e){
            System.out.println(e.getMessage());
        }finally{
        	if (prep != null)
            {
        		try
                {
                	prep.close();
                }
        		catch (SQLException ex1)
                {
                }
            }
              
        	if (conn != null)
            {
        		try
                {
                    conn.close();
                }
        		catch (SQLException ex1)
                {
                }
            }
        }
    }
    private static void deleteRecords() throws SQLException{

        String sql = "DELETE FROM h_user where id=?";
        try{
            conn = getDBConnection();
            prep = conn.prepareStatement(sql);
            prep.setInt(1, 3);
            System.out.println(sql);
            prep.executeUpdate();
            
        }catch(SQLException e){
            System.out.println(e.getMessage());
        }finally{
        	if (prep != null)
            {
        		try
                {
                	prep.close();
                }
        		catch (SQLException ex1)
                {
                }
            }
              
        	if (conn != null)
            {
        		try
                {
                    conn.close();
                }
        		catch (SQLException ex1)
                {
                }
            }
        }
    }
    private static void batchInsertRecords() throws SQLException{
  
        String sql = "INSERT INTO h_user(id,username,password) values"
            +"(?,?,?)";
        
        try{
            conn = getDBConnection();
            prep = conn.prepareStatement(sql);
         
            conn.setAutoCommit(false);
            prep.setInt(1, 251);
            prep.setString(2, "Ella");
            prep.setString(3, "elladepassword");
            prep.addBatch();
            
            prep.setInt(1, 252);
            prep.setString(2, "Jerry");
            prep.setString(3, "oooooooooo");
            prep.addBatch();
            prep.executeBatch();
         conn.commit();
         
            System.out.println("Batch Records have been inserted!");
        }catch(SQLException e){
            System.err.println(e.getMessage());
        }finally{
        	if (prep != null)
            {
        		try
                {
                	prep.close();
                }
        		catch (SQLException ex1)
                {
                }
            }
              
        	if (conn != null)
            {
        		try
                {
                    conn.close();
                }
        		catch (SQLException ex1)
                {
                }
            }
          
        }
    }
    private static Connection getDBConnection(){
       
        try{
            Class.forName(DB_DRIVER);	//try中的语句尽量短
        }catch(ClassNotFoundException e){
            System.err.println(e.getMessage());
        }
        
        try{
            conn = DriverManager.getConnection(DB_CONNECTION,DB_USER, DB_PASSWORD);
        }catch(SQLException e){	//catch尽量多分类处理
            System.err.println(e.getMessage());
        }
        
        return conn;
    }

}