JDBC处理文本和二进制文件
JDBC支持文本(CLOB)和二进制(BLOB)文件的处理,比如要往数据库里存取文章或者图片。这都是用流的思想来解决的。
来两个Demo看看JDBC是怎么操作文本和二进制文件的。
CLOB:
package com.wxisme.jdbcclob; import java.io.File; import java.io.FileNotFoundException; import java.io.FileReader; import java.io.IOException; import java.io.Reader; import java.sql.Clob; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Timestamp; import java.text.DateFormat; import java.text.ParseException; import java.text.SimpleDateFormat; /** * 数据库中对文本对象的操作。 * @author wxisme * */ public class CLOBTest { /** * 标准时间对象转换成long * @param date * @return long */ public static long dateTolong(String date) { DateFormat format = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss"); long time = 0; try { time = format.parse(date).getTime(); } catch (ParseException e) { e.printStackTrace(); } return time; } /** * 向数据库中插入一条带有Clob文本对象字段的记录 */ public void test1() { Connection conn = null; PreparedStatement ps = null; try { Class.forName("com.mysql.jdbc.Driver"); } catch (ClassNotFoundException e) { e.printStackTrace(); } String c = "jdbc:mysql://localhost:3306/student?useUnicode=true&characterEncoding=utf8"; try { conn = DriverManager.getConnection(c, "root", "1234"); String sql = "insert into student (name,pswd,time,stime,myImg) values(?,?,?,?,?)"; ps = conn.prepareStatement(sql); java.sql.Date date = new java.sql.Date(dateTolong("2015-5-14 9:25:32")); Timestamp stime = new Timestamp(dateTolong("2015-5-14 9:25:32")); ps.setString(1, "张三"); ps.setString(2, "123456"); ps.setDate(3, date); ps.setTimestamp(4, stime); ps.setClob(5, new FileReader("e:" +File.separator + "a.txt")); ps.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } catch (FileNotFoundException e) { e.printStackTrace(); } } public static void main(String[] args) { //从数据库中读取Clob文本对象并输出 Connection conn = null; PreparedStatement ps = null; ResultSet rs = null; Reader r = null; try { Class.forName("com.mysql.jdbc.Driver"); } catch (ClassNotFoundException e) { e.printStackTrace(); } String c = "jdbc:mysql://localhost:3306/student?useUnicode=true&characterEncoding=utf8"; try { conn = DriverManager.getConnection(c, "root", "1234"); String sql = "select myImg from student where id=?"; ps = conn.prepareStatement(sql); ps.setInt(1, 1); rs = ps.executeQuery(); while(rs.next()) { Clob clob = rs.getClob("myImg"); r = clob.getCharacterStream(); int t = 0; while((t=r.read()) != -1) { System.out.println((char)t); } } } catch (SQLException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); }finally { try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } try { ps.close(); } catch (SQLException e) { e.printStackTrace(); } try { r.close(); } catch (IOException e) { e.printStackTrace(); } try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } }
BLOB:
package com.wxisme.jdbcblob; import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.io.InputStream; import java.io.OutputStream; import java.sql.Blob; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; /** * 数据库操作二进制文件 Blob类的使用 * @author wxisme * */ public class BLOBTest { /** * 向数据库中插入二进制 */ public void test1() { Connection conn = null; PreparedStatement ps = null; try { Class.forName("com.mysql.jdbc.Driver"); } catch (ClassNotFoundException e) { e.printStackTrace(); } String c = "jdbc:mysql://localhost:3306/student?useUnicode=true&characterEncoding=utf8"; try { conn = DriverManager.getConnection(c, "root", "1234"); String sql = "insert into student (img) values(?)"; ps = conn.prepareStatement(sql); ps.setBlob(1, new FileInputStream("e:" + File.separator + "b.jpg")); ps.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } catch (FileNotFoundException e) { e.printStackTrace(); }finally { try { ps.close(); } catch (SQLException e) { e.printStackTrace(); } try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } public static void main(String[] args) { //从数据库中读取二进制文件并恢复 Connection conn = null; PreparedStatement ps = null; ResultSet rs = null; InputStream r = null; OutputStream os = null; try { Class.forName("com.mysql.jdbc.Driver"); } catch (ClassNotFoundException e) { e.printStackTrace(); } String c = "jdbc:mysql://localhost:3306/student?useUnicode=true&characterEncoding=utf8"; try { conn = DriverManager.getConnection(c, "root", "1234"); String sql = "select img from student where id=?"; ps = conn.prepareStatement(sql); ps.setInt(1, 3); rs = ps.executeQuery(); os = new FileOutputStream(new File("e:/e.jpg")); while(rs.next()) { Blob blob = rs.getBlob("img"); r = blob.getBinaryStream(); int t = 0; while((t=r.read()) != -1) { os.write(t); } } } catch (SQLException e) { e.printStackTrace(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); }finally { try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } try { ps.close(); } catch (SQLException e) { e.printStackTrace(); } try { r.close(); } catch (IOException e) { e.printStackTrace(); } try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } try { os.close(); } catch (IOException e) { e.printStackTrace(); } } } }
作者:Pickle
声明:对于转载分享我是没有意见的,出于对博客园社区和作者的尊重一定要保留原文地址哈。
致读者:坚持写博客不容易,写高质量博客更难,我也在不断的学习和进步,希望和所有同路人一道用技术来改变生活。觉得有点用就点个赞哈。
![](https://images2015.cnblogs.com/blog/735119/201701/735119-20170111112835275-168981902.gif)
![](https://images2015.cnblogs.com/blog/735119/201701/735119-20170111112841431-2047172832.jpg)
![](https://images2015.cnblogs.com/blog/735119/201701/735119-20170111112847494-1544911856.jpg)
![](https://images2015.cnblogs.com/blog/735119/201701/735119-20170111112904385-918783221.jpg)
![](https://images2015.cnblogs.com/blog/735119/201701/735119-20170111120143119-1302805212.jpg)
![](https://images2015.cnblogs.com/blog/735119/201701/735119-20170111112856369-1466718103.jpg)
![](https://images2015.cnblogs.com/blog/735119/201701/735119-20170111112830416-794416355.jpg)
![](https://images2015.cnblogs.com/blog/735119/201701/735119-20170111113919510-1155220901.gif)