JDBC——BLOB 数据的插入与读取

最新推荐文章于 2023-07-05 16:25:06 发布

weixin_33800463

最新推荐文章于 2023-07-05 16:25:06 发布

阅读量188

点赞数

文章标签： java 数据库移动开发

原文链接：https://my.oschina.net/u/2260265/blog/348577

版权

2019独角兽企业重金招聘Python工程师标准>>>

/**
* 插入 Blob 类型的数据必须使用 PreparedStatement，因为 BLOB 类型
* 的数据是无法使用字符串拼写的。
*
* 调用 setBlob(int index, InputStream inputStream)
*/
@Test
public void testInsertBlob() {
  Connection connection = null;
  PreparedStatement preparedStatement = null;
  try {
   connection = JDBCTools.GetConnection();
   String sql = "INSERT INTO mytable(name, email, hiredate, picture)"
     + "VALUES(?, ?, ?, ?)";
   preparedStatement = connection.prepareStatement(sql);
   preparedStatement.setString(1, "MyDream");
   preparedStatement.setString(2, "MyDream@jdbc.com");
   preparedStatement.setDate(3,
     new Date(new java.util.Date().getTime()));
   InputStream inputStream = new FileInputStream("picture.jpg");
   preparedStatement.setBlob(4, inputStream);

   preparedStatement.executeUpdate();

} catch (Exception e) {

   e.printStackTrace();
  } finally {
   JDBCTools.release(null, preparedStatement, connection);
  }
}

/**
* 读取 BLOB数据：
* 1.使用 getBlob 方法读取到 Blob 对象
* 2.调用 Blob 的 getBinaryStream（）方法得到输入流，再使用 IO 操作即可。
*/
@Test
public void readBlog(){
  Connection connection = null;
  PreparedStatement preparedStatement = null;
  ResultSet resultSet = null;
  try {
   connection = JDBCTools.GetConnection();
   String sql = "SELECT id, name, email, hiredate, picture"
   + " FROM mytable WHERE id = 6";
   preparedStatement = connection.prepareStatement(sql);
   resultSet = preparedStatement.executeQuery();

   if(resultSet.next()){
    int id = resultSet.getInt(1);
    String name = resultSet.getString(2);
    String email = resultSet.getString(3);

    System.out.println(id + ", " + name + ", " + email);
   }
   Blob picture = resultSet.getBlob(5);

   InputStream in = picture.getBinaryStream();
   OutputStream out = new FileOutputStream("table.jpg");
   byte [] buffer = new byte[1024];
   int len = 0;
   while((len = in.read(buffer)) != -1 ){
    out.write(buffer, 0, len);
   }

   out.close();
   in.close();
  } catch (Exception e) {
   e.printStackTrace();
  } finally{
   JDBCTools.release(resultSet, preparedStatement, connection);
  }
}