Java EE WEB工程师培训-JDBC+Servlet+JSP整合开发之06.JDBC PreparedStatement

package com.michael.jdbc;    

import java.sql.Connection;    
import java.sql.DriverManager;    
import java.util.Properties;    

public  class ConnectionUtil {    
         //第一种方法    
         public Connection getConnection(){    
                Connection conn =  null;    
                 try {    
                         //Class.forName加载驱动    
                        Class.forName( "com.mysql.jdbc.Driver");    
                         //DriverManager获得连接    
                        conn = DriverManager.getConnection( "jdbc:mysql://localhost:3306/jdbc_db","root","mysqladmin");    
                        return conn;    
                } catch (Exception e) {    
                        e.printStackTrace();    
                }    
                return null;    
        }    
        //第二种方法    
        public Connection getConnection(String driver,String url,String user,String password){    
                Connection conn = null;    
                try {    
                        //Class.forName加载驱动    
                        Class.forName(driver);    
                        //DriverManager获得连接    
                        conn = DriverManager.getConnection(url,user,password);    
                        return conn;    
                } catch (Exception e) {    
                        e.printStackTrace();    
                }    
                return null;    
        }    
        //第三种方法    
        public Connection openConnection(){    
                String driver = "";    
                String url = "";    
                String user = "";    
                String password = "";    
                Properties prop = new Properties();    
                Connection conn = null;    
                try {    
                        //加载属性文件    
                        prop.load(this.getClass().getClassLoader().getResourceAsStream("DBConfig.properties"));    
                        driver = prop.getProperty("driver");    
                        url = prop.getProperty("url");    
                        user = prop.getProperty("user");    
                        password = prop.getProperty("password");    
                        //Class.forName加载驱动    
                        Class.forName(driver);    
                        //DriverManager获得连接    
                        conn = DriverManager.getConnection(url,user,password);    
                        return conn;    
                } catch (Exception e) {    
                        e.printStackTrace();    
                }    
                return null;    
        }    

} 

package com.michael.jdbc;    

public  class Customer {    
         private  int id;    
         private String name;    
         private String email;    
         public String getEmail() {    
                 return email;    
        }    
         public  void setEmail(String email) {    
                 this.email = email;    
        }    
         public  int getId() {    
                 return id;    
        }    
         public  void setId( int id) {    
                 this.id = id;    
        }    
         public String getName() {    
                 return name;    
        }    
         public  void setName(String name) {    
                 this.name = name;    
        }    
} 

package com.michael.jdbc;    

import java.sql.Connection;    
import java.sql.PreparedStatement;    
import java.sql.SQLException;    

public  class TestPrepareStatement {    
         public  static  void add(Customer c){    
                Connection conn =  new ConnectionUtil().openConnection();    
                String sql =  "insert into CustomerTbl(name,email) values(?,?)";    
                 try {    
                        PreparedStatement pstmt = conn.prepareStatement(sql);    
                        pstmt.setString(1, c.getName());    
                        pstmt.setString(2, c.getEmail());    
                        pstmt.executeUpdate();    
                }  catch (SQLException e) {    

                        e.printStackTrace();    
                }    
        }    
} 

package com.michael.main;    

import com.michael.jdbc.Customer;    
import com.michael.jdbc.TestPrepareStatement;    

public  class Main {    

         /**    
         * @param args    
         */    
         public  static  void main(String[] args) {    
                Customer c =  new Customer();    
                c.setName( "51blog");    
                c.setEmail( "51blog@51blog.com");    
                TestPrepareStatement.add(c);    
        }    

} 

package com.michael.jdbc;    

import java.sql.Connection;    
import java.sql.PreparedStatement;    
import java.sql.SQLException;    

public  class TestPrepareStatement {    
         public  static  void delete( int id){    
                Connection conn =  new ConnectionUtil().openConnection();    
                String sql =  "delete from CustomerTbl where id = ?";    
                 try {    
                        PreparedStatement pstmt = conn.prepareStatement(sql);    
                        pstmt.setInt(1,id);    
                        pstmt.executeUpdate();    
                }  catch (SQLException e) {    

                        e.printStackTrace();    
                }    
        }    
} 

package com.michael.main;    

import com.michael.jdbc.TestPrepareStatement;    

public  class Main {    

         /**    
         * @param args    
         */    
         public  static  void main(String[] args) {    
                 /*    
                Customer c = new Customer();    
                c.setName("51blog");    
                c.setEmail("51blog@51blog.com");    
                TestPrepareStatement.add(c);    
                */    
                 //删除ID为2的用户    
                TestPrepareStatement.delete(2);    
        }    

} 

package com.michael.jdbc;    

import java.sql.Connection;    
import java.sql.PreparedStatement;    
import java.sql.SQLException;    

public  class TestPrepareStatement {    
         public  static  void update(String name){    
                Connection conn =  new ConnectionUtil().openConnection();    
                String sql =  "update CustomerTbl set name = ?";    
                 try {    
                        PreparedStatement pstmt = conn.prepareStatement(sql);    
                        pstmt.setString(1, name);    
                        pstmt.executeUpdate();    
                }  catch (SQLException e) {    

                        e.printStackTrace();    
                }    
        }    
} 

Main.java 

package com.michael.main;    

import com.michael.jdbc.TestPrepareStatement;    

public  class Main {    

         /**    
         * @param args    
         */    
         public  static  void main(String[] args) {    
                 /*    
                Customer c = new Customer();    
                c.setName("51blog");    
                c.setEmail("51blog@51blog.com");    
                TestPrepareStatement.add(c);    
                */    
                 //删除ID为2的用户    
                 //TestPrepareStatement.delete(2);    
                TestPrepareStatement.update( "Michael");    
        }    

} 

package com.michael.jdbc;    

import java.sql.Connection;    
import java.sql.PreparedStatement;    
import java.sql.SQLException;    

public  class TestPrepareStatement {    
         public  static  void update(String name1,String name2){    
                Connection conn =  new ConnectionUtil().openConnection();    
                String sql =  "update CustomerTbl set name = ? where name = ?";    
                 try {    
                        PreparedStatement pstmt = conn.prepareStatement(sql);    
                        pstmt.setString(1, name1);    
                        pstmt.setString(2, name2);    
                        pstmt.executeUpdate();    
                }  catch (SQLException e) {    

                        e.printStackTrace();    
                }    
        }    
}    

Main.java 

package com.michael.main;    

import com.michael.jdbc.TestPrepareStatement;    

public  class Main {    

         /**    
         * @param args    
         */    
         public  static  void main(String[] args) {    
                 /*    
                Customer c = new Customer();    
                c.setName("51blog");    
                c.setEmail("51blog@51blog.com");    
                TestPrepareStatement.add(c);    
                */    
                 //删除ID为2的用户    
                 //TestPrepareStatement.delete(2);    
                 //TestPrepareStatement.update("Michael");    
                TestPrepareStatement.update( "Michael", "51cto");    
        }    

} 

package com.michael.jdbc;    

import java.sql.Connection;    
import java.sql.PreparedStatement;    
import java.sql.ResultSet;    
import java.sql.SQLException;    
import java.util.ArrayList;    
import java.util.List;    

public  class TestPrepareStatement {    
         public  static List query(String name){    
                Connection conn =  new ConnectionUtil().openConnection();    
                String sql =  "select * from CustomerTbl where name = ?";    
                 try {    
                        PreparedStatement pstmt = conn.prepareStatement(sql);    
                        pstmt.setString(1, name);    
                        ResultSet rs = pstmt.executeQuery();    
                        List list =  new ArrayList();    
                         while(rs.next()){    
                                 int id = rs.getInt(1);    
                                String email = rs.getString(3);    
                                Customer c =  new Customer();    
                                c.setId(id);    
                                c.setName(name);    
                                c.setEmail(email);    
                                list.add(c);    
                        }    
                        System.out.println(list.size());    
                         return list;    
                }  catch (SQLException e) {    
                        e.printStackTrace();    
                }    
                 return  null;    
        }    
} 

package com.michael.main;    

import com.michael.jdbc.TestPrepareStatement;    

public  class Main {    

         /**    
         * @param args    
         */    
         public  static  void main(String[] args) {    
                 /*    
                Customer c = new Customer();    
                c.setName("51blog");    
                c.setEmail("51blog@51blog.com");    
                TestPrepareStatement.add(c);    
                */    
                 //删除ID为2的用户    
                 //TestPrepareStatement.delete(2);    
                 //TestPrepareStatement.update("Michael");    
                 //TestPrepareStatement.update("Michael","51cto");    
                TestPrepareStatement.query( "Michael");    
        }    

}