Javaweb Learning Summary (34)--using JDBC to process MySQL Big data

First, the basic concept

　　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

Ii. setting up a test environment 2.1, the construction of the test project structure

As follows:

　　

2.2. Write Db.properties configuration file

1 driver=com.mysql.jdbc.driver2 url=jdbc:mysql://localhost:3306/jdbcstudy3 Username=root4 Password=XDP

2.3, write Jdbcutils tool class

 1 package me.gacl.utils; 2 3 Import Java.io.InputStream; 4 Import java.sql.Connection; 5 Import Java.sql.DriverManager; 6 Import Java.sql.ResultSet; 7 Import java.sql.SQLException; 8 Import java.sql.Statement; 9 Import java.util.properties;10 One public class Jdbcutils {A private static String Driver = null;14 private S Tatic string url = null;15 private static string username = null;16 private static string password = null;17 1 8 static{19 try{20//Read the database connection information in the Db.properties file InputStream in = JdbcUtils.class.get ClassLoader (). getResourceAsStream ("Db.properties"); properties prop = new properties (); PROP.L Oad (in); 24 25//Get database connection Driver Driver = prop.getproperty ("Driver"); 27//Get Database Connection URL address-url = prop.getproperty ("url"); 29//Get database connection user name: Username = Prop.getproperty ( "username"); 31//Get database connection password + pasSword = prop.getproperty ("password"); 33 34//LOAD Database driver Class.forName (driver); 36     PNS}catch (Exception e) {}41 throw new Exceptionininitializererror (e); 39}40 /**43 * @Method: GetConnection44 * @Description: Get database Connection Object * @Anthor: Aloof Wolf *47 * @return Con Nection database Connection Object * @throws SQLException49 * */public static Connection getconnection () throws Sqlexception{5     1 return drivermanager.getconnection (URL, username,password),}53/**55 * @Method: release56 * @Description: Free resources, 57 * The resources to be freed include the connection database connection object, the statement object responsible for executing the SQL command, and the ResultSet object that stores the query result * @Anthor: Orphan *60 * @param conn61 * @param st62 * @param rs63 * * * public static void release (Connection                 Conn,statement St,resultset RS) {$ if (rs!=null) {try{67///Close the ResultSet object that stores the query results 68 Rs.close ();}catch (Exception e) {e.printstacktrace ();}72 rs = null;73 }74 if (st!=null) {try{76//Close the statement object responsible for executing the SQL command St.close (         );}catch (Exception e) {e.printstacktrace (); 80}81}82 83             if (conn!=null) {try{85//Close Connection Database Connection object Conn.close (); 87 }catch (Exception e) {e.printstacktrace (); 89}90}91}92}

Third, use JDBC to process the large text of MySQL

For the text type in MySQL, you can call the following method to set

1 Preparedstatement.setcharacterstream (index, reader, length);//Note the length must be set and set to type int

For the text type in MySQL, you can call the following method to get

1 reader = ResultSet. Getcharacterstream (string ColumnLabel); 2 string s = resultset.getstring (string columnlabel);

3.1. Test example

1. Writing SQL test scripts

1 CREATE DATABASE jdbcstudy;2 use jdbcstudy;3 CREATE TABLE TESTCLOB4 (5          ID int primary KEY auto_increment,6          resume TEXT7);

2. Write the test code as follows:

  1 package Me.gacl.demo;  2 3 Import Java.io.File;  4 Import Java.io.FileReader;  5 Import Java.io.FileWriter;  6 Import Java.io.Reader;  7 Import java.sql.Connection;  8 Import java.sql.PreparedStatement; 9 Import Java.sql.ResultSet; Ten import me.gacl.utils.JdbcUtils; Import Org.junit.Test; /** * @ClassName: Jdbcoperaclob * @Description: Use JDBC to manipulate MySQL's large text * @author: Aloof and Pale wolf * @date: 2014-9-19 PM 10:10:04 * * */public class Jdbcoperaclob {/** * @Method: Add * @Description: Insert large text into the database         Data * @Anthor: Aloof and pale wolf * * * * @Test public void Add () {Connection conn = null; 31 PreparedStatement st = null; ResultSet rs = null; Reader reader = null;  try{conn = Jdbcutils.getconnection (); String sql = "INSERT into Testclob (resume) VALUES (?) "; Panax Notoginseng st = conn.preparestatement (SQL);   38//This method gets the path where the spaces are used instead of "%20" 39          String path = JdbcOperaClob.class.getClassLoader (). GetResource ("Data.txt"). GetPath (); 40//Replace "%20" with go home-path = Path.replaceall ("%20", "" "); The file File = new file (path); reader = new FileReader (file); St.setcharacterstream (1, Reader, (int) file.length ()); int num = St.executeupdate (); if (num>0) {System.out.println ("Insert success!! "); 48} 49//Off Stream reader.close (); Wuyi}catch (Exception e) {e.printstacktrace ();}finally{Jdbcutils.releas E (Conn, St, RS); /** * @Method: Read * @Description: Reads large text data from the database. * @Anthor: Aloof and pale wolf * * * * * * @Test N/a public void read () {Connection conn = null; preparedstatemen T st = null; ResultSet rs = null; try{conn = JdbcutilS.getconnection (); String sql = "Select resume from Testclob where id=2"; st = conn.preparestatement (SQL); St.executequery rs = (); Contentstr String = ""; The String content = ""; if (Rs.next ()) {78//Use Resultset.getstring ("field name") to get the contents of the large text data, content = Rs. GetString ("Resume"); 80//Use Resultset.getcharacterstream ("field name") to get the contents of large text data ba reader reader = rs.getcharacterstr EAM ("Resume"); buffer[char] = new char[1024]; The (). int len = 0; FileWriter out = new FileWriter ("D:\\1.txt");                     (Len=reader.read (buffer) >0) {contentstr + = new String (buffer); 87 Out.write (buffer, 0, Len); Out.close (); Reader.close (); SYSTEM.OUT.PRINTLN (CONtent); System.out.println ("-----------------------------------------------"); 94 System.out.println (CONTENTSTR); }catch (Exception e) {e.printstacktrace ();}finally{98 Jdbcutils.releas E (Conn, St, RS); 99}100}101}

Iv. using JDBC to process MySQL binary data

For the blob type in MySQL, you can call the following method settings:

1 PreparedStatement. Setbinarystream (i, inputstream, length);

For the blob type in MySQL, you can call the following method to get:

1 InputStream in  = Resultset.getbinarystream (String columnlabel), 2 inputstream in  

4.1. Test example

1. Writing SQL test scripts

1 CREATE TABLE TESTBLOB2 (3      ID int primary key auto_increment,4      image longblob5);

2. Write the test code as follows:

 1 package Me.gacl.demo; 2 3 Import Java.io.File; 4 Import Java.io.FileInputStream; 5 Import Java.io.FileOutputStream; 6 Import Java.io.InputStream; 7 Import java.sql.Connection; 8 Import java.sql.PreparedStatement; 9 Import java.sql.resultset;10 Import me.gacl.utils.jdbcutils;11 import org.junit.test;12/**14 * @ClassName: Jdbcoper ACLOB15 * @Description: Using JDBC to manipulate MySQL binary data (example, sound, binary) * @author: Aloof and Pale wolf * @date: 2014-9-19 PM 10:10:0418 *19 * * Publ IC class Jdbcoperablob {/**23 * @Method: ADD24 * @Description: Inserting binary data into the database * @Anthor: Aloof Wolf 26         7 */@Test29 public void Add () {Connection conn = null;31 PreparedStatement st = null;32 ResultSet rs = null;33 try{34 conn = Jdbcutils.getconnection (); String sql = "in SERT into Testblob (image) VALUES (?) "; Conn.preparestatement (SQL), 37//This way gets the path, where the space is used instead of the "%20" in the String path = Jdb Coperablob.clasS.getclassloader (). GetResource ("01.jpg"). GetPath (); 39//Replace "%20" with space, Path = Path.replaceall ("%20"             , ""); a. file File = new file (path); FileInputStream fis = new FileInputStream (File);//Generated Stream 43 St.setbinarystream (1, FIS, (int) file.length ()), int num = St.executeupdate (), and if (Nu m>0) {System.out.println ("Insert succeeded!!         ");}48 fis.close ();}catch (Exception e) {e.printstacktrace (); 51     }FINALLY{52 Jdbcutils.release (Conn, St, RS),}54}55,/**57 * @Method: read58 * @Description: Read binary data from the database. * @Anthor: Aloof Wolf *61 * * * @Test63 public void Read () {             Nnection conn = null;65 PreparedStatement st = null;66 ResultSet rs = null;67 try {68 conn = Jdbcutils.getconnection (); String sql = "Select image from Testblob wherE id=? ";             -st = conn.preparestatement (SQL); St.setint (1, 1); rs = St.executequery (); 73                 if (Rs.next ()) {//inputstream in = Rs.getblob ("image"). Getbinarystream ();//This method can also be 75 InputStream in = Rs.getbinarystream ("image"); int len = 0;77 byte buffer[] = new                 byte[1024];78 FileOutputStream out = new FileOutputStream ("d:\\1.jpg"); 80                 while (len = in.read (buffer)) > 0) {bayi out.write (buffer, 0, Len); 82}83 In.close (); Out.close ();}86} catch (Exception e) {E.prin Tstacktrace (); Jdbcutils.release} finally {conn, St, RS); 90}91}92}

So much for the content of using JDBC to process MySQL Big data!

Javaweb Learning Summary (34)--using JDBC to process MySQL Big data