JDBC: Database operations: Handling large Object CLOB data

Goal:

Understand the fundamentals of big object processing,

Master the reading and writing operation of CLOB data.

You can use the Clob class to work with large text data.

Large object handling mainly refers to CLOB and blob two types of fields. You can store a lot of text.

To handle such big data operations in your program, you must use PreparedStatement to complete. All file contents are saved and read from the large text field by IO stream.

Write large data objects;

Use the methods in the PreparedStatement interface.

void setasciistream (intint  length)           void setbinarystream (int  int  length)           writes the binary input stream data into a binary field. 

Reading Big Data Objects

Read using the ResultSet interface method:

CLOB represents large text data, and MySQL provides longtext for large text data, which is the maximum amount of data stored in this field is 4G.

For example, the following database steps:

CREATE TABLE Userclob (    int,    name varchar (),    longtext)

Write the above text to a field in the datasheet.

 Packageclass set;Importjava.sql.Connection;ImportJava.sql.DriverManager;Importjava.sql.SQLException;Importjava.sql.PreparedStatement;ImportJava.io.File;ImportJava.io.FileInputStream;ImportJava.io.InputStream; Public classclobdemo01{//define a MySQL database driver     Public Static FinalString dbdriver = "Org.gjt.mm.mysql.Driver" ; //Define the connection address of the MySQL database     Public Static FinalString Dburl = "Jdbc:mysql://localhost:3306/sys" ; //connection user name for MySQL database     Public Static FinalString DBUSER = "root" ; //connection password for MySQL database     Public Static FinalString Dbpass = "AAAAAA" ;  Public Static voidMain (String args[])throwsexception{//all exceptions ThrownConnection conn =NULL;//Database Connection        PreparedStatement pstmt = null ;//Declaring Database preprocessing object String name= "Xiao Hua";//Show NameString sql = "INSERT into Userclob (name,note) VALUES (?,?)" ;    Class.forName (Dbdriver); //Load driverconn =drivermanager.getconnection (Dburl,dbuser,dbpass);//Establish connection pstmt = conn.preparestatement (sql); Create a Preapredstatement objectFile f =NewFile ("D:" + File.separator + "Mldn.txt");//Create a File object instance inputstream input = null; input = new FileInputStream (f); Reading a file from an input streamPstmt.setstring (1, name);        //Set a value for the first field . Pstmt.setasciistream ( 2,input, (int f.length ());//sets the input stream specified by the input instance to the second field,pstmt.executeupdate ();//performs the update operation.            Conn.close ();         //Database Shutdown    }};

View results after execution:

Read operations are performed using the IO stream below:

 Packageclass set;Importjava.sql.Connection;ImportJava.sql.DriverManager;Importjava.sql.SQLException;Importjava.sql.PreparedStatement;ImportJava.sql.ResultSet;ImportJava.io.InputStream;ImportJava.util.Scanner; Public classclobdemo01{//define a MySQL database driver     Public Static FinalString dbdriver = "Org.gjt.mm.mysql.Driver" ; //Define the connection address of the MySQL database     Public Static FinalString Dburl = "Jdbc:mysql://localhost:3306/sys" ; //connection user name for MySQL database     Public Static FinalString DBUSER = "root" ; //connection password for MySQL database     Public Static FinalString Dbpass = "AAAAAA" ;  Public Static voidMain (String args[])throwsexception{//all exceptions ThrownConnection conn =NULL;//Database ConnectionPreparedStatement pstmt =NULL ; ResultSet rs =NULL ; intid = 3;//number to readStringSQL= "Select Name,note from Userclob WHERE id=?" ;    Class.forName (Dbdriver); //Load Driverconn =drivermanager.getconnection (Dburl,dbuser,dbpass); pstmt = conn.preparestatement (sql); Create a Preapredstatement objectPstmt.setint (1, id); RS= Pstmt.executequery ();//Perform query operations        if(Rs.next ()){//If the result of the query has a value, read the first piece of dataString name = rs.getstring (1);//Read the first field value of this data. StringBufferNote=NewStringBuffer (); System.out.println ("Name:" +name); InputStream input=Rs.getasciistream (2);//Read byte stream by ResultSet interface, read the second field value of this data,           Scanner scan = new Scanner (input); reading content using the Scanner class Scan.Usedelimiter("\ r \ n");//to wrap a file as a separator             while(Scan.hasnext ()) { Note. Append (scan.next ()). Append ("\ n") ; } System.out.println ("Content:" +note);        Input.close ();        } rs.close ();        Pstmt.close ();            Conn.close (); //Database Shutdown    }};

Output Result:

Clob class

The above approach is to read large text data objects directly through the resultset, of course, you can also use the Getclob () method provided in ResultSet, the whole content into Clob object,

Using CLOB directly makes it easy to get large text data. You can also perform simple operations on these texts, such as intercepting specified string literals.

ResultSet class Gets the Clob object method:

Clob getclob (int  columnindex)           

The Clob class has a method for text interception.

String getsubstring (longint  length)           gets a copy of the specified substring in the CLOB value specified by this Clob object 

The Clob class also has a truncated text method:

 void truncate (long   len)           intercepts the Clob value specified by this Clob, making it a length of Len characters. 

 Packageclass set;Importjava.sql.Connection;ImportJava.sql.DriverManager;Importjava.sql.SQLException;Importjava.sql.PreparedStatement;ImportJava.sql.Clob;ImportJava.sql.ResultSet; Public classclobdemo01{//define a MySQL database driver     Public Static FinalString dbdriver = "Org.gjt.mm.mysql.Driver" ; //Define the connection address of the MySQL database     Public Static FinalString Dburl = "Jdbc:mysql://localhost:3306/sys" ; //connection user name for MySQL database     Public Static FinalString DBUSER = "root" ; //connection password for MySQL database     Public Static FinalString Dbpass = "AAAAAA" ;  Public Static voidMain (String args[])throwsexception{//all exceptions ThrownConnection conn =NULL;//Database ConnectionPreparedStatement pstmt =NULL ; ResultSet RS=NULL ; intid = 3;//number to readString SQL= "Select Name,note from Userclob WHERE id=?" ;    Class.forName (Dbdriver); //Load Driverconn =drivermanager.getconnection (Dburl,dbuser,dbpass); Pstmt= conn.preparestatement (SQL);//Create a Preapredstatement object        Pstmt.setint (1 , id);//Set the value of the first parameter to the value of the variable ID RS=Pstmt.executequery (); if(Rs.next ()) {String name= rs.getstring (1) ; System.out.println ("Name:" +name); Clob C = Rs.getclob (2) ; String Note= c.getsubstring (1,200);//intercept the first 200 stringsSystem.out.println ("Content:" +note); c.truncate ( 100); Read Only 100 itemsSystem.out.println ("Partially read content:" +c.getsubstring (1, (int ) c.length ()))        ;        } rs.close ();        Pstmt.close ();            Conn.close (); //Database Shutdown    }};

Output Result:

 Tue Apr 23:18:51 CST warn:establishing SSL connection without server ' s identity verification is not recommen Ded. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must is established by default if explicit Opti On isn ' t set. For compliance with existing applications not using SSL, the Verifyservercertificate property was set to ' false '.  You need either to explicitly disable SSL by setting Usessl=false , or set Usessl=true  and provide truststore for   server certificate Verification. Name: Xiao Hua content: 345436453646FASDFDSAGDFSADGWRTRFRGDSAVDAFSDFSADFDSAF345436453646FASDFDSAGDFSADGWRTRFRGDSAVDAFSDFSADF DSAFSDAFDSAFASD345436453646FASDFDSAGDFSADGWRTRFRGDSAVDAFSDFSADFDSAFSDAFDSAFASD345436453646FASDFDSA Partial Read content: 345436453646FASD FDSAGDFSADGWRTRFRGDSAVDAFSDFSADFDSAF345436453646FASDFDSAGDFSADGWRTRFRGDSAVDAFSDFSA  

with the Clob class, it is very convenient to handle the reading function of big data.。

JDBC: Database operations: Handling large Object CLOB data