Getting Started with JDBC (5)---Time type, big data

I. Type of TIME

The database type corresponds to a type in Java:

Date->java.sql.date: Represents a date, only a month and a day, no time and seconds, will be lost.

Time->java.sql.time: Indicates the time, only seconds and minutes, no month and day, will lose the date.

Timestamp->java.sql.timestamp: Represents a timestamp, date and time, and milliseconds.

• All properties in the domain object (domain) cannot appear under the java.sql package, that is, you cannot use Java.sql.Date
• Resultset#getdate () returns the Java.sql.Date ()
• Preparedstatement#setdate (Int,date), where the second parameter is also a java.sql.Date

Conversion of the time type:

• Java.util.Date->java.sql.date, Time, Timestamp
  • Converts the date of the util to a millisecond value
  • Create a date, time, Timestamp for SQL using millisecond values
• Java.sql.Date, Time, timestamp->java.util.date
  • This does not need to be handled: 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);

Second, Big Data

The so-called big data, is the large byte data, or large character data. The following types are available in standard SQL to hold large data types:

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

However, there are no four types of Tinyclob, CLOB, Mediumclob, Longclob available in MySQL, but instead use the following four types to handle the large data of the text:

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

Instance:

1 packageDemo4; 2 3 ImportDemo3. Jdbcutils; 4 ImportOrg.apache.commons.io.IOUtils; 5 ImportOrg.junit.Test; 6 ImportJavax.sql.rowset.serial.SerialBlob; 7 Import java.io.*; 8 Import java.sql.*; 9 public classDemo4 {11/**12 * Save Map3 to database, jdbcutils to self-built class 13 */14@Test15 public voidFun1 () {Connection con = null; PreparedStatement pstmt = null; 18Blob blob;19 Try{# con =Jdbcutils.getconnection (); String sql = "INSERT into Tab_bin VALUES (?,?,?)"; pstmt =Con.preparestatement (SQL); Pstmt.setint (); Pstmt.setstring (2, "Joker Xue-half. mp3"); 25/**26 * need to get Blob27 * 1, we have a file, the goal is Blob28 * 2, first turn the file into byte[]29 * 3, and then use byte[] to create Blob30 */31 byte[] bytes = Ioutils.tobytea Rray (New FileInputStream ("/users/shared/joker Xue-half. mp3")); 32//Use byte[] to create Blob33 blob = newSerialblob (bytes); 34//Set parameters Pstmt.setblob (3, blob); 36Pstmt.executeupdate (); PNS} catch(SQLException e) {38E.printstacktrace ();(IOException e) {40E.printstacktrace ();{Try{PSTMT! = null) Pstmt.close (), if (con! = null) Con.close ();(SQLException e) {46E.printstacktrace (); 47}48}49}50/**51 * Reads map352 from the database */53@Test54 public voidFun2 () {Connection con = null; PreparedStatement pstmt = null; ResultSet rs = null; 58Blob blob;59//1, Get connected try{# =Jdbcutils.getconnection (); 62//2, give the SELECT statement template, create pstmt63 String sql = "SELECT * FROM Tab_bin"; pstmt =  con.preparestatement (SQL), 65//3, pstmt Execute query, get ResultSet66 rs =  pstmt.executequery (); 67// Gets the data that is known as column "If " (Rs.next ()) {Rs.getblob blob = ' data ' ), 70//To convert blob into a file on hard disk 71/*72 * 1, get input stream object by BLOB 73 * 2, Create your own output stream object 74 * 3, write the input stream data to the output stream. * */76 InputStream in =  blob.getbinarystream (); OutputStream out = new fileoutputs Tream ("/users/mac/downloads/joker Xue-half 2.mp3" );  ioutils.copy (in, out), + }80} catch  ( SQLException e) {Bayi  e.printstacktrace (); "(IOException e)} catch  (Finall) {e.printstacktrace (); Y  {try  {rs.close (rs! = null ) (), pstmt! = null ) Pstmt.close (), and if (con! = null
                                                               
                                                                ) Con.close (); E.printstacktrace () (SQLException e) {
                                                                }92 () pan>  
                                                                              

The following exception is reported when the stored data is greater than the set value:

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 modifications to the MySQL configuration file (/etc/my.cnf in Mac) to the appropriate location:

Save shutdown, restart MySQL server to resolve.

Getting Started with JDBC (5)---Time type, big data