• PrepareStatement可以操作Blob类型

4.1MySQL BLOB类型

image.png

4.2像数据表中插入大数据类型

public void test1(){
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = JDBCUtils.getConnection();
            String sql = "insert into customers(name,email,birth,photo)" +
                    "values(?,?,?,?)";
            ps = conn.prepareStatement(sql);
            ps.setObject(1, "张宇浩");
            ps.setObject(2, "zhangyuhao@qq.com");
            ps.setObject(3, "1992-09-08");
            FileInputStream fis = new FileInputStream(new File(
                    "src", "demo.JPG"
            ));
//index，InputStream
            ps.setBlob(4, fis);
            ps.execute();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, ps);
        }

4.3修改数据表中的Blob类型字段

• 使用get（别名）方式比索引更好

@Test
    public void queryBlob(){
        Connection conn = null;
        PreparedStatement ps = null;
        InputStream is = null;
        FileOutputStream fos = null;
        try {
            conn = JDBCUtils.getConnection();
            String sql = "select id,name,email,birth,photo from customers " +
                    "where id = ?";
            ps = conn.prepareStatement(sql);
            ps.setObject(1, 20);
            ResultSet rs = ps.executeQuery();
            if(rs.next())
            {
    //            int id = rs.getInt(1);
    //            String name = rs.getString(2);
    //            String email = rs.getString(3);
    //            java.sql.Date photo = rs.getDate(4);
                //方式2：
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String email = rs.getString("email");
                Date birth = rs.getDate("birth");
            }
            FileInputStream fis = new FileInputStream(new File(
                    "src", "demo.JPG"
            ));
            Blob photo = rs.getBlob("photo");
            //下载
            is = photo.getBinaryStream();
            fos = new FileOutputStream("copyPhoto.jpg");
            byte[] buffer = new byte[1024];
            int len = 0;
            while((len = is.read(buffer)) != -1)
            {
                fos.write(buffer, 0, len);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                is.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
            try {
                fos.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
            JDBCUtils.closeResource(conn, ps);
        }

4.4从数据表中读入(query)大数据类型

MediumBlob最大16M，改mysql的配置文件my.ini