Using JDBC to process large text and big data for MySQL

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