Dates in JDBC using JAVA8 localdate, localdatetime__ programming language

As you know, in the entity entity, you can use Java.sql.Date, Java.sql.Timestamp, java.util.Date to map to the database Date, Timestamp, datetime fields

However, Java.sql.Date, Java.sql.Timestamp, java.util.Date These are not easy to use, many methods are outdated.

Java8 inside the new came out some api,localdate, localtime, LocalDateTime very easy to use

If you want to use Java8 's date localdate, LocalDateTime in JDBC, you must require that the database-driven version not be less than 4.2

The following shows how to use the Java8 date localdate, localdatetime to operate in JDBC, respectively Mysql,postgresql

One: MySQL

First create the table:

CREATE TABLE tb_java8date (ID int not NULL PRIMARY key auto_increment,t_date date, T_time time, T_datetime datetime);

Then, join the MySQL driver

<dependency>
	<groupId>mysql</groupId>
	<artifactid>mysql-connector-java</ artifactid>
	<version>5.1.37</version>
</dependency>

It said that the database-driven version can not be less than 4.2, how to judge it.

Directly open the database driver jar, which has a meta-inf/manifest. MF File

Note here, must be at least 4.2

The JDBC code is as follows:

Import java.sql.Connection;
Import Java.sql.DriverManager;
Import java.sql.PreparedStatement;
Import java.time.LocalDate;
Import Java.time.LocalDateTime;
Import Java.time.LocalTime;

public class App. {public
    static void Main (string[] args) throws Exception {
    	Class.forName (" Com.mysql.jdbc.Driver ");
    	Connection conn = drivermanager.getconnection ("Jdbc:mysql://192.168.1.100:3306/db_java8", "root", "root123");
    	PreparedStatement st = conn.preparestatement ("INSERT into tb_java8date (t_date,t_time,t_datetime) VALUES (?,?,?)");
    	St.setobject (1, Localdate.now ());
    	St.setobject (2, Localtime.now ());
    	St.setobject (3, Localdatetime.now ());
    	St.execute ();
    	St.close ();
    	Conn.close ();
    }

Run, querying the database

Mysql> select * from Tb_java8date;
+----+------------+----------+---------------------+
| ID | T_date | T_time | T_datetime |
+----+------------+----------+---------------------+
| 1 | 2016-11-13 | 11:34:31 | 2016-11-13 11:34:31 |
+----+------------+----------+---------------------+
1 row in Set (0.00 sec)

See that it's been successfully plugged into the database.

If you use a Mysql-connector-java version below 5.1.37, the driver version of the database is less than 4.2, and the runtime will report the following error:

Exception in thread "main" Com.mysql.jdbc.MysqlDataTruncation:Data truncation:incorrect Date value: ' \xac\xed\x00\ x05sr\x00\x0djava.time.ser\x95]\x84\xba\x1b "H\XB2\X0C\X00\X00XPW\X07\X03\X00\X00\X07\XE0\X0B\X0DX ' for column ' T_ Date ' at row 1 at
	com.mysql.jdbc.MysqlIO.checkErrorPacket (mysqlio.java:3845)
	at Com.mysql.jdbc.MysqlIO.checkErrorPacket (mysqlio.java:3783) at
	Com.mysql.jdbc.MysqlIO.sendCommand ( mysqlio.java:2447) at
	com.mysql.jdbc.MysqlIO.sqlQueryDirect (mysqlio.java:2594)
	at Com.mysql.jdbc.ConnectionImpl.execSQL (connectionimpl.java:2545) at
	Com.mysql.jdbc.PreparedStatement.executeInternal (preparedstatement.java:1901) at
	Com.mysql.jdbc.PreparedStatement.execute (preparedstatement.java:1193) at
	Com.pp.App.main (app.java:18)

Two: PostgreSQL

First create the table:

CREATE TABLE Tb_java8date (ID serial NOT NULL primary key,t_date date, t_time time, t_datetime timestamp);

Then, join the PostgreSQL database driver

<dependency>
	<groupId>org.postgresql</groupId>
	<artifactid>postgresql</ artifactid>
	<version>9.4.1212</version>
</dependency>

Note The minimum database driver version added here is 4.2, and the test method is similar to the above

The JDBC code is as follows:

Import java.sql.Connection;
Import Java.sql.DriverManager;
Import java.sql.PreparedStatement;
Import java.time.LocalDate;
Import Java.time.LocalDateTime;
Import Java.time.LocalTime;

public class App. {public
    static void Main (string[] args) throws Exception {
    	class.forname ("org.postgresql.Drive R ");
    	Connection conn = drivermanager.getconnection ("Jdbc:postgresql://127.0.0.1:5432/pg_java8", "admin", "123456");
    	PreparedStatement st = conn.preparestatement ("INSERT into tb_java8date (t_date,t_time,t_datetime) VALUES (?,?,?)");
    	System.out.println (St.getclass ());
    	St.setobject (1, Localdate.now ());
    	St.setobject (2, Localtime.now ());
    	St.setobject (3, Localdatetime.now ());
    	St.execute ();
    	St.close ();
    	Conn.close ();
    }

Run, and then query the database table

Found, has been successfully executed

If you join the dependency, the database driver version below 4.2, run will report the following error:

Exception in thread "main" Org.postgresql.util.PSQLException:Can ' t infer the SQL type to a for a instance of Java.time . Localdate. Use SetObject () with a explicit Types value to specify the type.
	At Org.postgresql.jdbc.PgPreparedStatement.setObject (pgpreparedstatement.java:1051) at
	Com.pp.App.main ( APP2.JAVA:16)

The above only demonstrates the Mysql,postgresql two databases, other databases, please test yourself. I'm not going to show you here, and the methods are similar.