Java JDBC读取大文本类型数据（CLOB）

Java JDBC读取大文本类型数据（CLOB）

开发中我们常常要把一些大文本类型数据存入数据库，例如一片文章这是后我们就需要数据库的CLOB类型来存储了。

 

一、建立数据库表

此不省略，因个人业务需求来建立。

 

二、程序实现

 

1、建立Java JDBC 工具类

 

package com.haerbin.JDBCProject.Connection;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;public final class JdbcUtil_Oracle{private static String url = "jdbc:oracle:thin:@localhost:1521:orcl";private static String user = "scott";private static String password = "tigger";private JdbcUtil_Oracle(){}static{try{Class.forName("oracle.jdbc.driver.OracleDriver");}catch (ClassNotFoundException e){throw new ExceptionInInitializerError(e);}}public static Connection getConnection() throws SQLException{return DriverManager.getConnection(url, user, password);}public static void free(ResultSet rs, Statement st, Connection conn){try{if (rs != null)rs.close();}catch (SQLException e){e.printStackTrace();}finally{try{if (st != null)st.close();}catch (SQLException e){e.printStackTrace();}finally{if (conn != null)try{conn.close();}catch (SQLException e){e.printStackTrace();}}}}}

 

2、建立大文本类模型（DAOMAIN）

 

package com.haerbin.JDBCProject.Daomain;public class Clob{private int id;private Clob clob_text;public int getId(){return id;}public void setId(int id){this.id = id;}public Clob getClob_text(){return clob_text;}public void setClob_text(Clob clob_text){this.clob_text = clob_text;}}

 

3、建立业务层接口

 

package com.haerbin.JDBCProject.Dao;import java.io.File;import java.io.IOException;import java.io.Reader;public interface ClobDao{public void AddClob(Reader reader, File file) throws IOException;public void getClob(Reader reader, File file) throws IOException;}

 

4、建立业务实现代码

 

package com.haerbin.JDBCProject.DaoImpl;import java.io.BufferedWriter;import java.io.File;import java.io.FileWriter;import java.io.IOException;import java.io.Reader;import java.io.Writer;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import com.haerbin.JDBCProject.Connection.JdbcUtil_Oracle;import com.haerbin.JDBCProject.Dao.ClobDao;public class ClobDaoImpl implements ClobDao{/** * 将clob类型数据存入数据库。 */public void AddClob(Reader reader, File file) throws IOException{Connection conn = null;PreparedStatement ps = null;ResultSet rs = null;try{conn = JdbcUtil_Oracle.getConnection();String sql = "insert into clobtable(id, clob_text) values(my_sequences.nextval, ?)";ps = conn.prepareStatement(sql);ps.setCharacterStream(1, reader, (int) file.length());ps.executeUpdate();}catch (Exception e){e.printStackTrace();}finally{JdbcUtil_Oracle.free(rs, ps, conn);reader.close();}}/** * 从数据库中取得clob类型数据 */public void getClob(Reader reader, File file) throws IOException{Connection conn = null;PreparedStatement ps = null;ResultSet rs = null;int i;// 用来装Reader读取的字符char[] buffer = new char[1024]; // Reader时候的缓冲区Writer writer = new BufferedWriter(new FileWriter(file));try{conn = JdbcUtil_Oracle.getConnection();String sql = "select clob_text from clobtable";ps = conn.prepareStatement(sql);rs = ps.executeQuery();while (rs.next()){reader = rs.getCharacterStream("clob_text");while ((i = reader.read(buffer)) != -1){writer.write(buffer, 0, i);System.out.println(new String(buffer, 0, i));}}}catch (Exception e){e.printStackTrace();}finally{JdbcUtil_Oracle.free(rs, ps, conn);reader.close();writer.close();}}}

 

5、调用接口实现大文本对象的存入

 

package com.haerbin.JDBCProject.Beans;import java.io.BufferedReader;import java.io.File;import java.io.FileNotFoundException;import java.io.FileReader;import java.io.IOException;import java.io.Reader;import com.haerbin.JDBCProject.Dao.ClobDao;import com.haerbin.JDBCProject.DaoImpl.ClobDaoImpl;public class ClobTest{/** * 数据库中存入大文本类型 jdbc 存入大文本需要一个Reader , 通过 Reader reader = new * BufferedReader(new FileReader(file)); 来实现， 传过去File的原因是 ， * jdbc那个方法需要知道文本的长度， 用file.length()可以求出来。 jdbc存入大文本只能是纯文本的. *  *  * @throws IOException */public static void main(String[] args) throws IOException{/*存入clob类型数据*/ClobDao cd = new ClobDaoImpl();/*MYSQL 用汉字的时候不可以插入，  ORACLE可以  ，  这是因为数据库字符问题*/File file = new File("D:/clob.txt");Reader reader = new BufferedReader(new FileReader(file));cd.AddClob(reader, file);/*从数据库读取clob类型数据*///ClobDao cd = new ClobDaoImpl();////File file = new File("D:/clob.txt");////Reader reader = new BufferedReader(new FileReader(file));////cd.getClob(reader, file);}}