Lob,large Objects is a type of data that is used to store large objects, and the general lob is divided into BLOBs and clob. BLOBs are typically used to store binary data, such as slices, audio, video, and so on. Clob are often used to store large texts, such as fiction.
There is no dedicated CLOB data type in the MySQL database, and if you want to store large text, MySQL uses the text type. The text type also has Tinytext, text, Mediumtext, and longtext points. The BLOB types in MySQL can also be divided into Tinyblob, blobs, Mediumblob, and Longblob.
Working with JDBC for large text
To store large text in MySQL, you can call the following methods in the JDBC API PreparedStatement:
Sets the specified parameter to the given Reader object
void Setcharacterstream (int parameterindex, reader reader) throws SQLException
Sets the given parameter to the given Reader object, which has a given number of characters (int type)
void Setcharacterstream (int parameterindex, reader reader, int length) throws SQLException
Sets the specified parameter to the given Reader object, which has the length of the given number of characters (long type)
void Setcharacterstream (int parameterindex, reader reader, long length) throws SQLException
If you need to get large text column field values from the MySQL database, you can use the following method of resultset:
Gets the value of the specified column in the current row of this ResultSet object in the form of a Java.io.Reader object
Reader Getcharacterstream (int columnindex) throws SQLException reader Getcharacterstream (String ColumnLabel) throws SQLException
Gets the value of the specified column in the current row of this ResultSet object as a String
String getString (int columnindex) throws SQLException string getString (String ColumnLabel) throws SQLException
Example:
Code:
Import Java.io.File;
Import java.io.FileNotFoundException;
Import Java.io.FileReader;
Import java.io.IOException;
Import Java.io.Reader;
Import java.sql.Connection;
Import java.sql.PreparedStatement;
Import Java.sql.ResultSet;
Import java.sql.SQLException;
/** * Using JDBC to manipulate large text
* * @author
* */
public class Clobtest {
/** * Using JDBC to insert large text data into a database table
* * @throws SQLException
*/
public static void Add () throws SQLException {
Connection conn = null;
PreparedStatement pstmt = null;
Get Database Session Object
Jdbcsession session = Jdbcsessionfactory.getcurrentsession ();
Get database connection
conn = Session.getconnection ();
Create a SQL statement: Add a record of a chapter to a novel table
String sql = "INSERT into novel (?)";
Create a PreparedStatement object
pstmt = conn.preparestatement (sql);
Create a Reader object
File File = new file (Thread.CurrentThread (). GetClass (). GetResource ("/novel/1.txt"). GetPath ());
Reader reader = null;
try {
reader = new FileReader (file);
} catch (FileNotFoundException e) {
E.printstacktrace ();
}
Setting parameters
Pstmt.setcharacterstream (1, Reader, (int) file.length ());
Execute SQL statement
int count = Pstmt.executeupdate ();
Processing results
if (Count > 0)
System.out.println ("add success");
Else
System.out.println ("Add failed");
Freeing resources
Jdbcresourcemanager.close (PSTMT);
Jdbcresourcemanager.close (conn);
Jdbcsessionfactory.closesession ();
}
public static void Read () throws SQLException {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
Get Database Session Object
Jdbcsession session = Jdbcsessionfactory.getcurrentsession ();
Get database connection
conn = Session.getconnection ();
Creating SQL statements
String sql = "SELECT ID, content from novel";
Create a PreparedStatement object
pstmt = conn.preparestatement (sql);
Execute SQL statement
rs = Pstmt.executequery ();
Processing results
while (Rs.next ()) {
Read the contents of a novel
Reader reader = rs.getcharacterstream ("content");
int ch;
try {
while ((ch = reader.read ())! =-1) {
System.out.print ((char) ch);
}
} catch (IOException e) {
E.printstacktrace ();
}
}
Freeing resources
Jdbcresourcemanager.close (RS);
Jdbcresourcemanager.close (PSTMT);
Jdbcresourcemanager.close (conn);
Jdbcsessionfactory.closesession ();
}
public static void Main (string[] args) {
try {
Add ();
Read ();
} catch (SQLException e) {
E.printstacktrace ();
}
}
}
Processing binary data using JDBC
To store binary data in MySQL, you can call the following methods in the JDBC API PreparedStatement:
Sets the specified parameter to the given input stream.
void Setbinarystream (int parameterindex, InputStream x)
Sets the specified parameter to the given input stream, which will have the given number of bytes (int).
void Setbinarystream (int parameterindex, inputstream x, int length)
Sets the specified parameter to the given input stream, which will have the specified number of bytes (long).
void Setbinarystream (int parameterindex, InputStream x, long length)
If you need to get the Binary column field values from the MySQL database, you can use the following method of resultset:
Gets the value of the specified column in the current row of this ResultSet object in the form of a stream that does not interpret bytes.
InputStream getbinarystream (int columnindex)
Gets the value of the specified column in the current row of this ResultSet object in the form of an unexplained byte stream.
InputStream Getbinarystream (String ColumnLabel)
Gets the value of the specified column in the current row of this ResultSet object in the form of a Blob object in the Java programming language.
Blob getBlob (int columnindex)
Gets the value of the specified column in the current row of this ResultSet object in the form of a Blob object in the Java programming language.
Blob GetBlob (String ColumnLabel)
After you get the Blob object, you can continue to call the Getbinarystream () method to get the input stream.
Example:
Code:
Import Java.io.File;
Import Java.io.FileInputStream;
Import Java.io.FileOutputStream;
Import Java.io.InputStream;
Import Java.io.OutputStream;
Import java.sql.Connection;
Import java.sql.PreparedStatement;
Import Java.sql.ResultSet;
/** * Using JDBC to manipulate binary data
* * @author
* */
public class BLOBTest {
/** * Inserting binary data into the database
* * @throws Exception
*/
public static void Add () throws Exception {
Connection conn = null;
PreparedStatement pstmt = null;
Get Database Session Object
Jdbcsession session = Jdbcsessionfactory.getcurrentsession ();
Get database connection
conn = Session.getconnection ();
SQL statement to insert music data
String sql = "INSERT into music (content) VALUES (?)";
pstmt = conn.preparestatement (sql);
Create a PreparedStatement object
pstmt = conn.preparestatement (sql);
Create a Reader object
File File = new file (Thread.CurrentThread (). GetClass (). GetResource ("/music/08.along_in_the_night.mp3"). GetPath ());
InputStream fis = new FileInputStream (file);
The generated stream
Pstmt.setbinarystream (1, FIS, file.length ());
Execute SQL statement
int count = Pstmt.executeupdate ();
Processing results
if (Count > 0)
System.out.println ("add success");
Else
System.out.println ("Add failed");
Freeing resources
Jdbcresourcemanager.close (PSTMT);
Jdbcresourcemanager.close (conn);
Jdbcsessionfactory.closesession ();
}
/** * Read binary data from the database
* * @throws Exception
*/
public static void Read () throws Exception {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
Get Database Session Object
Jdbcsession session = Jdbcsessionfactory.getcurrentsession ();
Get database connection
conn = Session.getconnection ();
Creating SQL statements
String sql = "SELECT ID, content from music WHERE id=?";
Create a PreparedStatement object
pstmt = conn.preparestatement (sql);
Pstmt.setint (1, 1);
Execute SQL statement
rs = Pstmt.executequery ();
Processing results
if (Rs.next ()) {
InputStream in = Rs.getbinarystream ("content");
Get column Field InputStream object
Buffer array
byte buf[] = new byte[1024];
int Len;
Output stream, save the read music data to the D drive
OutputStream out = new FileOutputStream ("D:\\1.mp3");
while (len = In.read (BUF))! =-1) {
Out.write (buf, 0, Len);
}
In.close ();
Out.close ();
}
Freeing resources
Jdbcresourcemanager.close (RS);
Jdbcresourcemanager.close (PSTMT);
Jdbcresourcemanager.close (conn);
Jdbcsessionfactory.closesession ();
}
public static void Main (string[] args) throws Exception {
Add ();
Read ();
}
}
Using JDBC to process large text and big data for MySQL