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.driver
2 url=jdbc:mysql://localhost:3306/jdbcstudy
3 username=root
4 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; public class Jdbcutils {Ten private static string driver = NULL; + private static string url = null; 1
5 private static String username = null;
private static String password = NULL; static{try{20//Read the database connection information in the Db.properties file. InputStream in = Jdbcuti
Ls.class.getClassLoader (). getResourceAsStream ("db.properties");
Properties prop = new properties ();
Prop.load (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);      }catch (Exception e) {$ throw new Exceptionininitializererror (e); 39} 40
/** * @Method: getconnection * @Description: Get database Connection Object * @Anthor: Aloof Wolf 46 * * @return Connection Database Connection Object * @throws SQLException * */public static Connection getconnection (
) throws sqlexception{return drivermanager.getconnection (URL, username,password); 52} 53 54/** * @Method: Release * @Description: free resources, 57 * The resources to be freed include the connection database connection object, which is responsible for executing the statement object of the SQL command, storing the query results ResultSet objects * @Anthor: Lonely pale Wolf * * @param conn * @param st @param RS/P   ublic static void release (Connection conn,statement St,resultset rs) {$ if (rs!=null) {try{67              Close the ResultSet object that stores the results of the query. Rs.close ();
}catch (Exception e) {e.printstacktrace (); n} + rs = null;                 (st!=null) {try{76//Close the statement object responsible for executing the SQL command 77
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 Testclob
4 (
5          ID int primary KEY auto_increment,
6          resume text
7);

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 * @Descriptio N: inserting large text data into the database * @Anthor: Aloof Wolf * * *, @Test public void Add () {Connectio
 n conn = null;
 PreparedStatement st = null;
 ResultSet rs = null;
 Reader reader = null;
 try{conn = Jdbcutils.getconnection ();
 The String sql = "INSERT into Testclob (?)";
 Panax Notoginseng st = conn.preparestatement (SQL);          38   The path obtained in this way, where the spaces are used instead of the "%20" in place of the JdbcOperaClob.class.getClassLoader String path = *. 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.release (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;
 PreparedStatement st = null;         68ResultSet 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 large text data content = R
 S.getstring ("Resume"); 80//Use Resultset.getcharacterstream ("field name") to get the contents of large text data ba reader reader = Rs.getcharacterst
 Ream ("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);
 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.release (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 Testblob
2 (
3      ID int primary key auto_increment,
4      image Longblob
5);

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;
Ten import me.gacl.utils.JdbcUtils;
Import Org.junit.Test; /** * @ClassName: Jdbcoperaclob * @Description: Using JDBC to manipulate MySQL binary data (example, sound, binary) * @author: Aloof and pale wolf * @date : 2014-9-19 10:10:04 * * */public class Jdbcoperablob {/** * @Method: Add * @Descript Ion: Inserting binary data into the database * @Anthor: Aloof Wolf * * *, @Test public void Add () {Connection Co
nn = null;
PreparedStatement st = null;
ResultSet rs = null; try{conn = Jdbcutils.getconnection (); String sql = "INSERT into Testblob (image)
VALUES (?) ";
The ST = conn.preparestatement (SQL);        37//This method gets the path where the spaces are used instead of "%20" 38     String path = JdbcOperaBlob.class.getClassLoader (). GetResource ("01.jpg"). GetPath ();
39//Replace "%20" with a space of $ path = Path.replaceall ("%20", "");
A. file File = new file (path); FileInputStream fis = new FileInputStream (file),//generated stream St.setbinarystream (1, FIS, (int) file.
Length ());
A. int num = St.executeupdate (); if (num>0) {System.out.println ("Insert succeeded!!
");
Fis.close (); }catch (Exception e) {e.printstacktrace ();}finally{Jdbcutils.releas
E (Conn, St, RS);
/** * @Method: Read * @Description: Reads binary data from the database. * @Anthor: Aloof and pale wolf * * * * * * @Test Connection, public void Read () {conn = null; preparedstateme
NT St = NULL;
ResultSet rs = null; try {conn = Jdbcutils.getconnection ();
The String sql = "Select image from Testblob where id=?";
+ st = conn.preparestatement (SQL);
St.setint (1, 1);
A. rs = St.executequery ();                 if (Rs.next ()) {//inputstream in = Rs.getblob ("image"). Getbinarystream ();//This method can also be 75
InputStream in = Rs.getbinarystream ("image");
int len = 0;
[buffer[] = new byte[1024];
FileOutputStream out = new FileOutputStream ("d:\\1.jpg");                 (len = in.read (buffer)) > 0) {bayi out.write (buffer, 0, Len) 82
} in.close ();
Out.close ();             Exception} catch (e) {e.printstacktrace (); 89
Jdbcutils.release (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