–PreparedStatement 
–为占位符“?”赋值 
–使用PreparedStatement动态执行SQL语句
####################Michael分割线######################
• PreparedStatement 
–Statement只能静态操作SQL语句,如果要想动态操作SQL语句又该如何实现呢?例如:注册会员 
–这里可以使用PreparedStatement来动态操作SQL语句 
–PreparedStatement通过使用占位符“?”,来预生成SQL语句,从而达到动态操作的功能
• 为占位符“?”赋值 
–根据当前SQL的数据类型
调用相应的如下方法
image 
• 使用PreparedStatement动态执行SQL语句 
–insert
image
image
ConnectionUtil.java
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;    
        }    


Customer.java
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;    
        }    

TestPrepareStatement.java
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();    
                }    
        }    

Main.java
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);    
        }    


image
可以删除所选的用户,现在我们删除ID为2的用户
TestPrepareStatement.java
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();    
                }    
        }    

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);    
        }    


image
也可以更新数据,现在我们更新所有用户名为Michael
TestPrepareStatement.java
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");    
        }    


image
##############Michael分割线##################
更新name为51cto的用户名为alibaba
image
TestPrepareStatement.java
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");    
        }    


image
####################Michael分割线######################
• 使用PreparedStatement动态执行SQL语句 
–query
image
TestPrepareStatement.java
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;    
        }    

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");    
                TestPrepareStatement.query("Michael");    
        }    


显示有两个客户名为Michael
image
####################Michael分割线######################