jdbc中PreparedStatement替换Statement实现CRUD与Blob类型的操作

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

    }

注意：