使用JDBC处理大数据与大文本

使用JDBC处理大数据

基本概念：大数据也称为LOB(Large Objects),LOB又分为：clob和blob

Colb用于存储大文本

Blob 用于存储二进制数据，例如图像、声音、二进制文

在MySQL中只有blob,没有clob，mysql存储大文本用text

Text分为：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT

Blob分为：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB

使用JDBC处理大文本

对于MySQL中的Text类型，可调用如下方法设置：

PreparedStatement.setCharacterStream(index, reader, length);

//注意length长度须设置，并且设置为int型

 

//大文本数据的存储

    public void insert(){

       Connection con=null;

       PreparedStatement st=null;

       ResultSet rs=null;

       //获取连接

       try {

           con=DBManager.getConnection();

           String sql="insert into testclob (resume) value(?)";

           st=con.prepareStatement(sql);

           //从文件中读出数据，用流

           //获取指定的流对象--通过反射

           //得到该资源文件对象---->获取路径

           File f=new File("1.txt");

           //自动读取1.txt文件中的内容，存入数据库的表中

           st.setCharacterStream(1,new FileReader(f), f.length());//给第一个对象设定输入流对象

           int result=st.executeUpdate();

           if(result>0){

              System.out.println("插入成功");

           }else{

              System.out.println("插入失败");

           }

       } catch (SQLException e) {

           // TODO Auto-generated catch block

           e.printStackTrace();

       }//资源释放

 catch (FileNotFoundException e) {

           // TODO Auto-generated catch block

           e.printStackTrace();

       }

       finally{

           DBManager.release(con, st, rs);

       }

    }

 

public void find(){

       //读取记录

       Connection con=null;

       PreparedStatement st=null;

       ResultSet rs=null;

      

       //获取连接

       try {

           con=DBManager.getConnection();

           String sql="select resume from  testclob where id=1";

           st=con.prepareStatement(sql);

           rs=st.executeQuery();

           if(rs.next()){

              //如果有返回结果，读取结果集

           /*  String str=rs.getString("resume");

              System.out.println(str);*/

              Reader reader=rs.getCharacterStream("resume");//按照列名读取，得到的是字符流对象

           //每次读取1024个字节

              char buff[]=new char[1024];

              int len=0;

             

              //直接输出从控制台窗口中显示读取出的内容

reader.read(buff);//返回值是一个实际读到的字节数

              while((len=reader.read(buff))>0){

                  System.out.println(new String(buff,0,len));

              }

              //新建一个文件，把读取到的写入到新文件中

              FileWriter writer=new FileWriter("3.txt");

              while((len=reader.read(buff))>0){

                  writer.write(buff,0,len);

              }

              writer.close();

              reader.close();

           }

       } catch (SQLException e) {

           // TODO Auto-generated catch block

           e.printStackTrace();

       } catch (IOException e) {

           // TODO Auto-generated catch block

           e.printStackTrace();

       }

       //资源释放

       finally{

           DBManager.release(con, st, rs);

       }

附加：DBManager.java对数据库的链接

public class DBManager {

   

    private static String username;

    private static String password;

    private static String url;

    private static String driver;

 

    static{

       try{

           InputStream in = DBManager.class.getClassLoader().getResourceAsStream("config/dbcp.properties");

           Properties prop = new Properties();

           prop.load(in);

           driver = prop.getProperty("driverClassName");

           url = prop.getProperty("url");

           username = prop.getProperty("username");

           password = prop.getProperty("password");

           Class.forName(driver);

       }catch (Exception e) {

           throw new ExceptionInInitializerError(e);

       }

    }

    public static Connection getConnection() throws SQLException{

       return DriverManager.getConnection(url, username, password);

    }

    public static void release(Connection conn,Statement st,ResultSet rs){

       if(rs!=null){

           try{

              rs.close();

           }catch (Exception e) {e.printStackTrace();}

            rs = null;

       }

       if(st!=null){

           try{

              st.close();

           }catch (Exception e) {e.printStackTrace();}

           st = null;

       }

       if(conn!=null){

           try{

              conn.close();

           }catch (Exception e) {e.printStackTrace();}

           conn = null;

       }

    }

}