jdbc操作大段文本和二进制数据类型

minzaipiao

使用jdbc操作大段文本和二进制数据类型

1 clob类型 用以存储大容量的文本
mysql中对应的字段类型是mediumText ,text 和longTetxt

向数据库中插入大段的文本

public static void insert(){Connection conn = null;PreparedStatement pstmt = null;ResultSet rs = null;try{String sql = "insert into clob(content) values(?)";conn = JdbcUtils.getInstance().getConnection();pstmt = conn.prepareStatement(sql);File file = new File("src/jdbc/CRUD.java");BufferedReader br = new BufferedReader(new FileReader(file));pstmt.setCharacterStream(1, br, file.length());int result = pstmt.executeUpdate();System.out.println(result);br.close();}catch (SQLException e){e.printStackTrace();}catch (FileNotFoundException e){e.printStackTrace();}catch (IOException e){e.printStackTrace();}finally{JdbcUtils.classAll(conn, pstmt, rs);}}

读取clob类型的数据
public static void read(){Connection conn = null;Statement stmt = null;ResultSet rs = null;try{conn = JdbcUtils.getInstance().getConnection();stmt = conn.createStatement();rs = stmt.executeQuery("select content from clob where id = 2");while (rs.next()){Clob clob = rs.getClob("content");Reader read = clob.getCharacterStream();//Reader read = rs.getCharacterStream("content");File file = new File("CRUD_bak.java");BufferedWriter bw = new BufferedWriter(new FileWriter(file));char[] buffer = new char[1024];/*for(int i; (i=read.read(buffer)) >0;){bw.write(buffer, 0, i);}*/    int c = -1;while((c = read.read(buffer)) != -1){bw.write(buffer, 0, c);}bw.close();read.close();//System.out.println(rs.getString("content"));}}catch (SQLException e){e.printStackTrace();}catch (IOException e){e.printStackTrace();}finally{JdbcUtils.classAll(conn, stmt, rs);}}

2 blob类型用以存储二进制的数据
mysql对应的字段类型: mediumBlob,Bolb,LongBlob

插入数据
public static void insertBlob(){Connection conn = null;PreparedStatement pstmt = null;ResultSet rs = null;try{String sql = "insert into blob_(content) values(?)";conn = JdbcUtils.getInstance().getConnection();pstmt = conn.prepareStatement(sql);File file = new File("Ext2.2英文API.CHM");BufferedInputStream fis = new BufferedInputStream(new FileInputStream(file));pstmt.setBinaryStream(1, fis, fis.available());int result = pstmt.executeUpdate();System.out.println(result);fis.close();}catch (SQLException e){e.printStackTrace();}catch (FileNotFoundException e){e.printStackTrace();}catch (IOException e){e.printStackTrace();}finally{JdbcUtils.classAll(conn, pstmt, rs);}}

读取bolb的数据
public static void readBlob(){Connection conn = null;Statement stmt = null;ResultSet rs = null;try{conn = JdbcUtils.getInstance().getConnection();stmt = conn.createStatement();rs = stmt.executeQuery("select content from blob_ where id = 3");while (rs.next()){//Blob blob = rs.getBlob("content");//InputStream is = blob.getBinaryStream();InputStream is = rs.getBinaryStream("content");File file = new File("Ext2.2英文API_bak.CHM");FileOutputStream os = new FileOutputStream(file);int i =0;while((i = is.read()) != -1){os.write(i);}os.close();is.close();}}catch (SQLException e){e.printStackTrace();}catch (IOException e){e.printStackTrace();}finally{JdbcUtils.classAll(conn, stmt, rs);}}