Transmission of JDBC big data content and jdbc data

Transmission of JDBC big data content and jdbc data
JDBC-based transmission of big data content

 

What is big data?

There is a record in the database, and many fields in the record are only a few characters. If we want to store a novel in the database, this novel is certainly not composed of several characters, it is composed of tens of thousands of characters. We can say that the data in this novel is big data. Of course, there are all kinds of big data in our life: movies, music, pictures, etc...

 

Big character data content operations

Large Character content: generally refers to a long character type file, such as a novel or a story. The content consists of characters.

The following describes the Big Data Types in MySQL and Oracle.

Data Type	Data size	MySQL	Oracle
Character	Small	Char, varchar	Varchar2
	Large	Text/longtext	Clob
Bytes	Large	Bit, blob, longblob	Blob

 

1. Store big character data in the database (store a text data in a text field in MySQL)

@ Test public void writeInDB () throws Exception {// get connection = sqlUtil. getconnection (); // get the object PreparedStatement preparedStatement = connection. prepareStatement ("insert into book values (?) "); // Prepare a Reader for reading the local File Reader = new FileReader (new File (" e:/test.txt "); // set the big data parameter preparedStatement. setClob (1, reader); // execute the SQL statement preparedStatement.exe cuteUpdate (); // close the resource reader. close (); sqlUtil. close (preparedStatement, connection );}

2. Read large character files from the database to the Local Machine

@ Test public void readFromDB () throws Exception {// get connection = sqlUtil. getconnection (); // create object PreparedStatement preparedStatement = connection. prepareStatement ("SELECT content FROM book"); // set the parameter // preparedStatement. setObject (1, "book"); // obtain the result ResultSet res = preparedStatement.exe cuteQuery (); // obtain the large character content in String format while (res. next () {String content = res. getString ("content"); System. out. println (content);} // closes the resource sqlUtil. close (preparedStatement, connection );}

 

The second method is available after the result is obtained:

@ Test public void readFromDB () throws Exception {// get connection = sqlUtil. getconnection (); // create object PreparedStatement preparedStatement = connection. prepareStatement ("SELECT content FROM book"); // set the parameter // preparedStatement. setObject (1, "book"); // obtain the result ResultSet res = preparedStatement.exe cuteQuery (); FileWriter fileWriter = new FileWriter (new File ("d:/11021.txt ")); // use the Clob object if (res. next () {Clob Clob = res. getClob ("content"); Reader reader = clob. getCharacterStream (); // then write the Reader to the local file char [] cr = new char [1024]; int len = 0; while (len = reader. read (cr ))! =-1) {fileWriter. write (cr, 0, len);} reader. close () ;}// close the resource fileWriter. close (); sqlUtil. close (preparedStatement, connection );}

The above is the reading and writing of large character files ~ Next we will demonstrate how to operate large byte files ~

 

4. Write large byte files to the database

@ Test public void writeInDB () throws Exception {// get connection = sqlUtil. getconnection (); // get the object PreparedStatement preparedStatement = connection. prepareStatement ("insert into book values (?,?) "); // Prepare an InputStream for reading the local File InputStream in = new FileInputStream (new File (" f:/computer.jpg "); // set the big data parameter preparedStatement. setObject (1, 1); preparedStatement. setBlob (2, in); // you can also use this // preparedStatement. setBinaryStream (2, in); // execute the SQL statement preparedStatement.exe cuteUpdate (); // close the resource in. close (); sqlUtil. close (preparedStatement, connection );}

5. Read large byte files from the database to the local device.

@ Test public void readFromDB () throws Exception {// get connection = sqlUtil. getconnection (); // create object PreparedStatement preparedStatement = connection. prepareStatement ("SELECT content FROM book where id =? "); // Set the parameter preparedStatement. setInt (1, 1); // obtain the result ResultSet res = preparedStatement.exe cuteQuery (); FileOutputStream out = new FileOutputStream (new File ("d:/999.jpg ")); // use the Blob Object if (res. next () {// Blob blob = res. getBlob ("content"); // InputStream in = blob. getBinaryStream (); // This can also be an InputStream in = res. getBinaryStream ("content"); // then write the Reader to the local file byte [] buf = new byte [1024]; int len = 0; while ((Len = in. read (buf ))! =-1) {out. write (buf, 0, len);} in. close (); out. close () ;}// close resource sqlUtil. close (preparedStatement, connection );}