龙空技术网

Java,实战,MySQL日期时间和Java8日期时间类型

古怪今人 162

前言:

此刻大家对“mysql数据类型日期”大概比较注重,兄弟们都需要知道一些“mysql数据类型日期”的相关资讯。那么小编也在网摘上网罗了一些对于“mysql数据类型日期””的相关知识,希望咱们能喜欢,各位老铁们一起来了解一下吧!

MySQL时间日期类型和Java日期类型对应关系:

1、Year,java.lang.Integer;

2、Time,java.sql.Time、java.time.LocalTime;

3、Date, java.sql.Date、java.time.LocalDate;

4、Datetime, java.sql.Timestamp、java.time.LocalDateTime;

5、Timestamp, java.sql.Timestamp、java.time.LocalDateTime;

Java8中java.time包:

Instant:瞬时实例。

LocalDate:本地日期,不包含具体时间 例如:2014-01-14 可以用来记录生日、纪念日、加盟日等。

LocalTime:本地时间,不包含日期。

LocalDateTime:组合了日期和时间,但不包含时差和时区信息。

ZonedDateTime:最完整的日期时间,包含时区和相对UTC或格林威治的时差。

注意:Java8的日期LocalDate、LocalTime、LocalDateTime,则必须要求数据库驱动的版本不能低于4.2。

MySQL时间&日期类型

CREATE TABLE `apply_time_table` (	`id` INT(11) NOT NULL AUTO_INCREMENT,	`time_millis` BIGINT(20) NOT NULL,	`string_date` VARCHAR(50) NOT NULL,	`date` DATE NOT NULL,	`time` TIME NOT NULL,	`year` YEAR NOT NULL,	`datetime` DATETIME NOT NULL,	`timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,	PRIMARY KEY (`id`) USING BTREE)COMMENT='mysql时间测试',COLLATE='utf8_general_ci',ENGINE=InnoDB

时间范围查询案例(复制):

SELECT * FROM `apply_time_table` att WHERE att.datetime >= '2021-03-03 00:00:00' AND att.datetime <= '2021-03-03 23:59:59';SELECT * FROM `apply_time_table` att WHERE att.datetime >= str_to_date('2021-03-03 00:00:00','%Y-%m-%d %H:%i:%S') AND att.datetime <= str_to_date('2021-03-03 23:59:59','%Y-%m-%d %H:%i:%S');SELECT * FROM `apply_time_table` att WHERE att.datetime BETWEEN '2021-03-03 00:00:00' AND '2021-03-03 23:59:59';SELECT * FROM `apply_time_table` att WHERE att.datetime BETWEEN str_to_date('2021-03-03 00:00:00','%Y-%m-%d %H:%i:%S') AND str_to_date('2021-03-03 23:59:59','%Y-%m-%d %H:%i:%S');
代码案例:

重点内容:

import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.SQLException;import java.time.LocalDate;import java.time.LocalDateTime;import java.time.LocalTime;public class Java8AndMySQLDateAndTime {    /**     * @param connection     */    public static void insert(Connection connection) {        PreparedStatement pstat = null;        try {            StringBuilder sqlBuilder = new StringBuilder();            sqlBuilder.append("insert into `apply_time_table`(`time_millis`,`string_date`,`date`,`time`,`year`,`datetime`,`timestamp`)");            sqlBuilder.append(" ");            sqlBuilder.append("value(?,?,?,?,?,?,?)");            // 创建PreparedStatement            pstat = connection.prepareStatement(sqlBuilder.toString());            //==============================================================//            //==构建参数            //==============================================================//            long timeMillis = System.currentTimeMillis();            String stringDate = DateStrUtils.toString(new java.util.Date());            java.sql.Date date = new java.sql.Date(timeMillis);            java.sql.Time time = new java.sql.Time(timeMillis);            java.sql.Timestamp timestamp = new java.sql.Timestamp(timeMillis);            // 预处理            pstat.setLong(1, System.currentTimeMillis());//mysql类型:bigint            pstat.setString(2, stringDate);//mysql类型:varchar            pstat.setDate(3, date);//mysql类型:date            pstat.setTime(4, time);//mysql类型:time            pstat.setInt(5, 2021);//mysql类型:year            pstat.setTimestamp(6, timestamp);//mysql类型:datetime            pstat.setTimestamp(7, timestamp);//mysql类型:timestamp            // 执行            pstat.execute();            // 处理返回结果(这里无结果)        } catch (SQLException e) {            e.printStackTrace();        } finally {            JDBCHelper.close(pstat);        }    }    /**     * @param connection     */    public static void insert2(Connection connection) {        PreparedStatement pstat = null;        try {            StringBuilder sqlBuilder = new StringBuilder();            sqlBuilder.append("insert into `apply_time_table`(`time_millis`,`string_date`,`date`,`time`,`year`,`datetime`,`timestamp`)");            sqlBuilder.append(" ");            sqlBuilder.append("value(?,?,?,?,?,?,?)");            // 创建PreparedStatement            pstat = connection.prepareStatement(sqlBuilder.toString());            //==============================================================//            //==构建参数            //==============================================================//            long timeMillis = System.currentTimeMillis();            String stringDate = DateStrUtils.toString(new java.util.Date());            // 预处理            pstat.setObject(1, System.currentTimeMillis());//mysql类型:bigint            pstat.setObject(2, stringDate);//mysql类型:varchar            pstat.setObject(3, LocalDate.now());//mysql类型:date            pstat.setObject(4, LocalTime.now());//mysql类型:time            pstat.setObject(5, 2088);//mysql类型:year            pstat.setObject(6, LocalDateTime.now());//mysql类型:datetime            pstat.setObject(7, LocalDateTime.now());//mysql类型:timestamp            // 执行            pstat.execute();            // 处理返回结果(这里无结果)        } catch (SQLException e) {            e.printStackTrace();        } finally {            JDBCHelper.close(pstat);        }    }    public static void main(String[] args) {        Connection connection = JDBCHelper.createConnection();        insert(connection);        insert2(connection);        JDBCHelper.close(connection);    }}
字符串日期转换工具类:
import java.text.ParseException;import java.text.SimpleDateFormat;import java.time.LocalDateTime;import java.time.format.DateTimeFormatter;import java.util.Date;public class DateStrUtils {    /**     * @param pattern     * @param dateStr     * @return     */    public static LocalDateTime toLocalDateTime(String pattern, String dateStr) {        try {            DateTimeFormatter frommater = DateTimeFormatter.ofPattern(pattern);            return LocalDateTime.parse(dateStr, frommater);        } catch (Exception e) {            //        }        return null;    }    /**     * @param dateStr     * @return     */    public static LocalDateTime toLocalDateTime(String dateStr) {        return toLocalDateTime("yyyy-MM-dd HH:mm:ss", dateStr);    }    /**     * @param partten     * @param localDateTime     * @return     */    public static String toString(String partten, LocalDateTime localDateTime) {        try {            DateTimeFormatter frommater = DateTimeFormatter.ofPattern(partten);            return frommater.format(localDateTime);        } catch (Exception e) {            //        }        return "";    }    /**     * @param localDateTime     * @return     */    public static String toString(LocalDateTime localDateTime) {        return toString("yyyy-MM-dd HH:mm:ss", localDateTime);    }    /**     * @param pattern     * @param dateStr     * @return     */    public static Date toDate(String pattern, String dateStr) {        try {            SimpleDateFormat simpleDateFormat = new SimpleDateFormat(pattern);            return simpleDateFormat.parse(dateStr);        } catch (ParseException e) {            e.printStackTrace();        }        return null;    }    /**     * @param dateStr     * @return     */    public static Date toDate(String dateStr) {        return toDate("yyyy-MM-dd HH:mm:ss",dateStr);    }    /**     * @param pattern     * @param date     * @return     */    public static String toString(String pattern, Date date) {        try {            SimpleDateFormat simpleDateFormat = new SimpleDateFormat(pattern);            return simpleDateFormat.format(date);        } catch (Exception e) {            e.printStackTrace();        }        return "";    }    /**     * @param date     * @return     */    public static String toString(Date date) {        return toString("yyyy-MM-dd HH:mm:ss", date);    }    public static void main(String[] args) {        LocalDateTime localDateTime = DateStrUtils.toLocalDateTime("2021-03-02 12:12:32");        System.out.println(localDateTime);        String strlocalDateTime = DateStrUtils.toString(localDateTime);        System.out.println(strlocalDateTime);        Date date = DateStrUtils.toDate("2021-03-02 12:12:32");        System.out.println(date);        String strDate = DateStrUtils.toString(date);        System.out.println(strDate);    }}

标签: #mysql数据类型日期 #timestampjava #javadate类型 #数据库中的datetimejava用什么接