JDBC handles MySQL Big data

Big Data is also known as LOB (Large Objects), and lobs are divided into: CLOB and Blob,clob for storing large text, blobs for storing binary data , examples, sounds, binary text, and so on.

In actual development, it is sometimes necessary to use a program to save large text or binary data directly into the database for storage.

For MySQL, there are only blobs, and no Clob,mysql store large text using Text,text and blobs are divided into:
Tinytext, TEXT, Mediumtext and Longtext
Tinyblob, BLOBs, Mediumblob, and Longblob

I. Tool class

 Public classJdbcutils {Private StaticString Driver =NULL; Private StaticString URL =NULL; Private StaticString username =NULL; Private StaticString Password =NULL; Static{        Try{URL= "Jdbc:mysql://localhost:3306/test"; Driver= "Com.mysql.jdbc.Driver"; Username= "Root"; Password= "Root"; //Load Database driverClass.forName (driver); }Catch(Exception e) {Throw NewExceptionininitializererror (e); }    }    /**     * @returnConnection Database Connection object *@throwsSQLException*/     Public StaticConnection getconnection ()throwssqlexception{returndrivermanager.getconnection (URL, username,password); }    /*** The resources to be freed include the connection database connection object, the statement object responsible for executing the SQL command, the ResultSet object that stores the query results*/     Public Static voidrelease (Connection conn,statement st,resultset rs) {if(rs!=NULL){            Try{                //close the ResultSet object that stores the query resultsRs.close (); }Catch(Exception e) {e.printstacktrace (); } RS=NULL; }        if(st!=NULL){            Try{                //Close the statement object that is responsible for executing the SQL commandSt.close (); }Catch(Exception e) {e.printstacktrace (); }        }        if(conn!=NULL){            Try{                //Close the Connection database connection objectConn.close (); }Catch(Exception e) {e.printstacktrace (); }        }    }}

Two. Using JDBC to process large text SQL statements for MySQL

CREATE TABLE ' Testclob ' (  int(one) not NULL auto_increment,  ' Resume ' text,  PRIMARY KEY (' id ')) ENGINE=innodb auto_increment=5 DEFAULT Charset=utf8

Build a data.txt under the resource

"Baidu know", is the user's own according to a targeted problem, through the point reward mechanism to launch other users to solve the problem of the search mode. At the same time, the answers to these questions will be further used as search results, to provide other users with similar questions, to achieve the effect of sharing knowledge. Baidu know the biggest feature, is the perfect combination with the search engine, so that users have the tacit knowledge into the explicit knowledge, the user is not only Baidu know the content of users, but also Baidu know the creator, where the accumulated knowledge of the data can be reflected in the search results. Through the interaction of users and search engines, to achieve the community of search engines. Baidu knows also can be seen as a complement to search engine functions, so that the user's mind of tacit knowledge into the explicit knowledge, through the precipitation and organization of the response to form a new information base, wherein the user can be further search and use. This means that users are both user and creator of search engines. Baidu knows can be said to rely on the technology of the search engine of a humane perfect.

The test code is as follows:

 Public classJdbcoperaclob {/*** @Method: Add * @Description: Insert large text data into the database * @Anthor: Aloof Wolf **/@Test Public voidAdd () {Connection conn=NULL; PreparedStatement St=NULL; ResultSet RS=NULL; Reader Reader=NULL; Try{conn=jdbcutils.getconnection (); String SQL= "INSERT INTO Testclob (") "VALUES (?)"; St=conn.preparestatement (SQL); //relative path, when the Web container loads the class file, it loads the class file under this folder.File File =NewFile ("Target/classes/data.txt"); Reader=Newfilereader (file); St.setcharacterstream (1, Reader, (int) file.length ()); intnum =st.executeupdate (); if(num>0) {System.out.println ("Insert success!!" "); }            //Close the streamReader.close (); }Catch(Exception e) {e.printstacktrace (); }finally{           //Jdbcutils.release (Conn, St, RS);        }    }    /*** @Method: Read * @Description: Reads large text data from the database*/@Test Public voidRead () {Connection conn=NULL; PreparedStatement St=NULL; ResultSet RS=NULL; Try{conn=jdbcutils.getconnection (); String SQL= "Select Resume from Testclob where id=3"; St=conn.preparestatement (SQL); RS=St.executequery (); String Contentstr=""; String content= ""; if(Rs.next ()) {//Use resultset.getstring ("field name") to get the contents of large text dataContent = rs.getstring ("Resume"); //Use Resultset.getcharacterstream ("field name") to get the contents of large text dataReader reader = Rs.getcharacterstream ("Resume"); CharBuffer[] =New Char[1024]; intLen = 0; FileWriter out=NewFileWriter ("D:\\1.txt");  while(Len=reader.read (buffer)) >0) {Contentstr+=NewString (buffer); Out.write (Buffer,0, Len);                } out.close ();            Reader.close ();            } System.out.println (content); System.out.println ("-----------------------------------------------");        System.out.println (CONTENTSTR); }Catch(Exception e) {e.printstacktrace (); }finally{jdbcutils.release (conn, St, RS); }    }}

Three. Use JDBC to process MySQL binary data SQL statements

CREATE TABLE ' Testblobim ' (  int(one) not NULL auto_increment,  ' image ' Longblob,  PRIMARY KEY (' id ')) ENGINE=innodb auto_increment=2 DEFAULT Charset=utf8

Put a picture in resource: dilly. jpg

Test code:

 Public classJdbcoperabloblmgs {/*** @Description: Inserting binary data into the database*/@Test Public voidAdd () {Connection conn=NULL; PreparedStatement St=NULL; ResultSet RS=NULL; Try{conn=jdbcutils.getconnection (); String SQL= "INSERT into Testblobim (image) VALUES (?)"; St=conn.preparestatement (SQL); //Relative path , relative path under classFile File =NewFile ("target/classes/dilly. jpg"); FileInputStream FIS=NewFileInputStream (file);//the generated streamSt.setbinarystream (1, FIS, (int) file.length ()); intnum =st.executeupdate (); if(num>0) {System.out.println ("Insert success!!" ");        } fis.close (); }Catch(Exception e) {e.printstacktrace (); }finally{jdbcutils.release (conn, St, RS); }    }    /*** @Description: Reads binary data from the database*/@Test Public voidRead () {Connection conn=NULL; PreparedStatement St=NULL; ResultSet RS=NULL; Try{conn=jdbcutils.getconnection (); String SQL= "Select image from Testblobim where id=?"; St=conn.preparestatement (SQL); St.setint (1, 1); RS=St.executequery (); if(Rs.next ()) {//InputStream in = Rs.getblob ("image"). Getbinarystream ();//This method can alsoInputStream in = Rs.getbinarystream ("image"); intLen = 0; byteBuffer[] =New byte[1024]; FileOutputStream out=NewFileOutputStream ("D:\\1.jpg");  while(len = in.read (buffer)) > 0) {out.write (buffer,0, Len);                } in.close ();            Out.close (); }        } Catch(Exception e) {e.printstacktrace (); } finally{jdbcutils.release (conn, St, RS); }    }}

JDBC handles MySQL Big data