1.PreparedStatement的理解：

① PreparedStatement 是Statement的子接口

② An object that represents a precompiled SQL statement.

③ 可以解决Statement的sql注入问题，拼串问题

PreparedStatement和Statement对比？

Statement存在SQL注入问题，PreparedStatement解决了SQL注入问题

Statement是编译一次执行一次，PreparedStatement是编译一次，可执行N次，PreparedStatement效率较高一些

PreparedStatement会在编译阶段做类型的安全检查

2使用PreparedStatement完成insert、update、delete

package loey.java1;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.SQLException;/** * 使用PreparedStatement完成insert、update、delete*/public class JDBCTest09 {
       public static void main(String[] args) {
           Connection conn = null;        PreparedStatement ps = null;        try {
               Class.forName("com.mysql.jdbc.Driver");            conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/bjpowernode"            ,"root","1127");            //String sql = "insert into dept1(deptno,dname,loc) values(?,?,?)";插入            //String sql = "update dept1 set dname = ? where dname = '人事部'";修改            String sql = "delete from dept1 where dname = ?";//删除            ps = conn.prepareStatement(sql);//            ps.setInt(1,50);            ps.setString(2,"人事部");            ps.setString(3,"北京");            //ps.setString(1,"研发部");            ps.setString(1,"研发部");            int count = ps.executeUpdate();            System.out.println((count != 0 )? "删除成功" : "删除失败");        } catch (ClassNotFoundException e) {
               e.printStackTrace();        } catch (SQLException e) {
               e.printStackTrace();        } finally {
               if(ps != null){
                   try {
                       ps.close();                } catch (SQLException e) {
                       e.printStackTrace();                }            }            if(conn != null){
                   try {
                       conn.close();                } catch (SQLException e) {
                       e.printStackTrace();                }            }        }    }}

3.使用PreparedStatement实现查的方法

Connection conn = null;        PreparedStatement ps = null;//这里使用预编译的数据库操作对象        ResultSet rs = null;        try {
               //1.注册驱动            Class.forName("com.mysql.jdbc.Driver");            //2.获取连接            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306" +                    "/bjpowernode", "root", "1127");            //3.获取预编译的数据库操作对象            // sql语句的框架中，一个?，表示一个占位符，一个?将来接收一个"值"。注意：占位符不要用单引号括起来            String sql = "select * from t_user where loginName = ? and loginPwd = ?";            // 程序执行到此处，会发送sql语句框架给DBMS，DBMS对sql语句框架进行预编译。            ps = conn.prepareStatement(sql);            // 给占位符?传值，第一个?的下标是1，第二个?的下标是2（JDBC中下标都从1开始）            ps.setString(1,userLoginInfo.get("loginName"));            ps.setString(2,userLoginInfo.get("loginPwd"));            rs = ps.executeQuery();            while (rs.next()) {
                   isSuccess = true;            }        } catch (ClassNotFoundException e) {
               e.printStackTrace();        } catch (SQLException e) {
               e.printStackTrace();        } finally {
               if (rs != null) {
                   try {
                       rs.close();                } catch (SQLException e) {
                       e.printStackTrace();                }            }            if (ps != null) {
                   try {
                       ps.close();                } catch (SQLException e) {
                       e.printStackTrace();                }            }            if (conn != null) {
                   try {
                       conn.close();                } catch (SQLException e) {
                       e.printStackTrace();                }            }        }

4.测试使用PreparedStatement操作Blob类型的数据

PreparedStatement可以操作Blob类型的变量。

写入操作的方法： setBlob(InputStream is);

读取操作的方法：

Blob blob = getBlob(int index);

InputStream is = blob.getBinaryStream();

DBUtil

package loey.DBUtil;import org.junit.Test;import java.io.IOException;import java.io.InputStream;import java.sql.*;import java.util.Properties;/** * JDBC工具类，简化JDBC编程 */public class DBUtil {
       /**     * 工具类中的构造方法是私有的     * 因为工具类中的方法都是静态的，直接通过类名去调即可。     */    private DBUtil() {
       }    private static Properties getProperties(){
           //ResourceBundle bundle = ResourceBundle.getBundle("jdbc");        //String driver = bundle.getString("driver");        //String url = bundle.getString("url");        //String user = bundle.getString("user");        //String password = bundle.getString("password");        //String sql = bundle.getString("sql");        // 1.读取配置文件中的4个基本信息        Properties pros = new Properties();        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");        try {
               pros.load(is);        } catch (IOException e) {
               e.printStackTrace();        }        return pros;    }    /**     * 静态代码块，类加载的时候执行     * 把注册驱动程序的代码放在静态代码块中，避免多次获取连接对象时重复调用     */    static{
           Properties pros = getProperties();        String driver = pros.getProperty("driver");        try {
               Class.forName(driver);        } catch (ClassNotFoundException e) {
               e.printStackTrace();        }    }    /**     * @return 获取连接     * @throws SQLException     */    public static Connection getConnection(String database) throws Exception {
           Properties pros = getProperties();        String url = pros.getProperty("url");        String user = pros.getProperty("user");        String password = pros.getProperty("password");        Connection conn = DriverManager.getConnection(url + database, user, password);        return conn;    }    public static void close(Connection conn, Statement ps, ResultSet rs) {
           if (rs != null) {
               try {
                   rs.close();            } catch (SQLException e) {
                   e.printStackTrace();            }        }        if (ps != null) {
               try {
                   ps.close();            } catch (SQLException e) {
                   e.printStackTrace();            }        }        if (conn != null) {
               try {
                   conn.close();            } catch (SQLException e) {
                   e.printStackTrace();            }        }    }}

向数据表customers中插入Blob类型的字段

/**     * 向数据表customers中插入Blob类型的字段     */    @Test    public void testInsert(){
           Connection conn = null;        PreparedStatement ps = null;        FileInputStream fis = null;        try {
               conn = DBUtil.getConnection("test");            String sql = "insert into customers(name,email,birth,photo) values(?,?,?,?)";            ps = conn.prepareStatement(sql);//            ps.setString(1,"朴灿烈");            ps.setString(1,"pcy");            ps.setString(2,"pcy@163.com");            ps.setString(3, "1992-11-27");//            fis = new FileInputStream(new File("朴灿烈.jpg"));            fis = new FileInputStream(new File("pcy.jpg"));            ps.setBlob(4,fis);            int count = ps.executeUpdate();            System.out.println(count == 1 ? "插入成功" : "插入失败");        } catch (Exception e) {
               e.printStackTrace();        } finally {
               if(fis != null){
                   try {
                       fis.close();                } catch (IOException e) {
                       e.printStackTrace();                }            }            DBUtil.close(conn,ps,null);        }    }

查询数据表customers中Blob类型的字段

/**     * 查询数据表customers中Blob类型的字段     */    @Test    public void testQuery(){
           Connection conn = null;        PreparedStatement ps = null;        ResultSet rs = null;        InputStream is = null;        FileOutputStream fos = null;        try {
               conn = DBUtil.getConnection("test");            String sql = "select id,name,email,birth,photo from customers where id = ?";            ps = conn.prepareStatement(sql);//            ps.setInt(1,22);            ps.setInt(1,23);            rs = ps.executeQuery();            if(rs.next()){
                   int id = rs.getInt("id");                String name = rs.getString("name");                String email = rs.getString("email");                Date birth = rs.getDate("birth");                Customer cust = new Customer(id,name,email,birth);                System.out.println(cust);                //将Blob类型的字段下载下来，以文件的方式保存在本地                Blob photo = rs.getBlob("photo");                is = photo.getBinaryStream();//                fos = new FileOutputStream(new File("灿烈1.jpg"));                fos = new FileOutputStream(new File("pcy1.jpg"));                byte[] buffer = new byte[1024];                int len;                while((len = is.read(buffer)) != -1){
                       fos.write(buffer,0,len);                }            }        } catch (Exception e) {
               e.printStackTrace();        } finally {
               if(is != null){
                   try {
                       is.close();                } catch (IOException e) {
                       e.printStackTrace();                }            }            if(fos != null){
                   try {
                       fos.close();                } catch (IOException e) {
                       e.printStackTrace();                }            }            DBUtil.close(conn,ps,rs);        }    }

注意：