JDBC entry (5) --- time type, big data, jdbc entry

JDBC entry (5) --- time type, big data, jdbc entry

I. Time Type

Ing between database types and types in Java:

DATE-> java. SQL. Date: indicates the DATE, which is a Date of the year, month, and day. If there is no hour, minute, and second, the time will be lost.

TIME-> java. SQL. Time: indicates the TIME, which is only Time, minute, and second. If there is no year, month, or day, the date is lost.

TIMESTAMP-> java. SQL. Timestamp: indicates the TIMESTAMP, including year, month, day, hour, minute, second, and millisecond.

• All attributes of domain objects cannot contain objects in the java. SQL package, that is, they cannot use java. SQL. Date.
• ResultSet # getDate () returns java. SQL. Date ()
• PreparedStatement # setDate (int, Date). The second parameter is also java. SQL. Date.

Time type conversion:

• Java. util. Date-> java. SQL. Date, Time, Timestamp
  • Converts the value of util to a millisecond value.
  • Create SQL Date, Time, Timestamp with millisecond Value
• Java. SQL. Date, Time, Timestamp-> java. util. Date
  • This is not necessary: Because java. SQL. Date is a subclass of java. util. Date.

java.util.Date date = new java.util.Date();
long l = date.getTime();
java.sql.Date sqlDate = new java.sql.Date(l);

Ii. Big Data

Big Data is big byte data or big character data. Standard SQL provides the following types to store big data:

Type	Length
Tinyblob	28-1B (256B)
Blob	216-1B (64 K)
Mediumblob	224-1B (16 M)
Longblob	232-1B (4G)
Tinyclob	28-1B (256B)
Clob	216-1B (64 K)
Mediumclob	224-1B (16 M)
Longclob	232-1B (4G)

 

 

 

 

 

 

 

However, mysql does not provide four types of data: tinyclob, clob, mediumclob, and longclob. Instead, the following four types are used to manage text-based big data:

Type	Length
Tinytext	28-1B (256B)
Text	216-1B (64 K)
Mediumtext	224-1B (16 M)
Longtext	232-1B (4G)

 

 

 

 

Instance:

1 package demo4; 2 3 import demo3.JdbcUtils; 4 import org. apache. commons. io. IOUtils; 5 import org. junit. test; 6 import javax. SQL. rowset. serial. serialBlob; 7 import java. io. *; 8 import java. SQL. *; 9 10 public class Demo4 {11/** 12 * save map3 to the database. JdbcUtils is a self-built class 13 */14 @ Test15 public void fun1 () {16 Connection con = null; 17 PreparedStatement pstmt = null; 18 Blob; 19 try {20 con = JdbcUtils. getConne Ction (); 21 String SQL = "INSERT INTO tab_bin VALUES (?,?,?) "; 22 pstmt = con. prepareStatement (SQL); 23 pstmt. setInt (1, 1); 24 pstmt. setString (2, "Xue zhiqian- .mp3 .mp3"); 25/** 26 * Need To Get Blob27 * 1. We have files, the target is Blob28 * 2. First convert the file to byte [] 29*3. Then use byte [] to create Blob30 */31 byte [] bytes = IOUtils. toByteArray (new FileInputStream ("/Users/Shared/Xue zhiqian- .mp3 .mp3"); 32 // use byte [] to create Blob33 blob = new SerialBlob (bytes ); 34 // set the parameter 35 pstmt. setBlob (3, blob); 36 pstmt.exe cuteUpdate (); 37} catch (SQLExcepti On e) {38 e. printStackTrace (); 39} catch (IOException e) {40 e. printStackTrace (); 41} finally {42 try {43 if (pstmt! = Null) pstmt. close (); 44 if (con! = Null) con. close (); 45} catch (SQLException e) {46 e. printStackTrace (); 47} 48} 49} 50/** 51 * read map352 */53 @ Test54 public void fun2 () {55 Connection con = null from the database; 56 PreparedStatement pstmt = null; 57 ResultSet rs = null; 58 Blob; 59 // 1. Obtain the connection 60 try {61 con = JdbcUtils. getConnection (); 62 // 2. a select statement template is provided to create pstmt63 String SQL = "SELECT * FROM tab_bin"; 64 pstmt = con. prepareStatement (SQL); 65 // 3 Pstmt executes the query to obtain ResultSet66 rs = pstmt.exe cuteQuery (); 67 // obtain the DATA 68 if (rs. next () {69 blob = rs. getBlob ("DATA "); 70 // convert Blob into files on hard disk 71/* 72*1. Get input stream object 73*2. Create output stream object 74*3. Write input stream data 75 **/76 InputStream in = blob in the output stream. getBinaryStream (); 77 OutputStream out = new FileOutputStream ("/Users/Mac/Downloads/Xue zhiqian-half 2.mp3"); 78 IOUtils. copy (in, out); 79} 80} catch (SQLException e) {81 e. printStackTra Ce (); 82} catch (IOException e) {83 e. printStackTrace (); 84} finally {85 try {86 if (rs! = Null) rs. close (); 87 if (pstmt! = Null) pstmt. close (); 88 if (con! = Null) con. close (); 89} catch (SQLException e) {90 e. printStackTrace (); 91} 92} 93} 94}

When the stored data exceeds the set value, the following exception is reported:

com.mysql.jdbc.PacketTooBigException: Packet for query is too large (4188642 > 1048576). You can change this value on the server by setting the max_allowed_packet' variable.

You need to add and modify the corresponding location of the mysql configuration file (/etc/my. cnf in Mac:

Save and close. Restart the MySQL server.