MySQL中int和date的相互转换

1、业务场景

因为我的实体类中有个Date类型的属性，但是数据表中对应字段的类型却是int类型的，所以才会有这么一个需求，要求添加或修改记录的时候将Date类型转换成Int存进表，查询的时候将int转换为Date类型展示出来，如果不转换的话，进行增删或查询的时候一定会报错。

2、Date转int（添加 / 修改时）

需要借助MySQL提供的UNIX_TIMESTAMP()函数来转换。代码如下：

package com.ycz.domain;

import java.util.Date;

/**
 * @Description  实体类
 * @ClassName People
 * @Author yanchengzhi
 * @date 2021.06.08 08:47
 */
public class People {

    private Integer id;

    private String name;

    private Date birth;

    private Integer tag;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Date getBirth() {
        return birth;
    }

    public void setBirth(Date birth) {
        this.birth = birth;
    }

    public Integer getTag() {
        return tag;
    }

    public void setTag(Integer tag) {
        this.tag = tag;
    }
}

dao层：

    /*
    * @description: 添加
    * @param: [people]
    * @return: int
    * @author: yanchengzhi
    * @date: 2021/6/14 11:03
    */
    public int addPeople(People people){
        int res = 0;
        String sql = "insert into people (name,birth,tag) values (?,UNIX_TIMESTAMP(?),?)";
        Connection connection = DataSourceManager.getConnection();
        PreparedStatement ps = null;
        try {
            ps = connection.prepareStatement(sql);
            ps.setString(1,people.getName());
            ps.setDate(2,new Date(people.getBirth().getTime()));
            ps.setInt(3,people.getTag());
            res = ps.executeUpdate();
        } catch (Exception e){
            e.printStackTrace();
        } finally {
            DataSourceManager.closeConnection(connection);
            DataSourceManager.closeStatement(ps);
        }
        return res;
    }

关注的重点是sql语句：

servlet层：

    /*
    * @description: 添加
    * @param: [request, response]
    * @return: void
    * @author: yanchengzhi
    * @date: 2021/6/14 10:51
    */
    private void addPeople(HttpServletRequest request,HttpServletResponse response) throws IOException {
        People people = new People();
        people.setName("云过梦无痕");
        people.setBirth(new Date());
        people.setTag(0);
        response.setContentType("text/html;charset=utf-8");
        //response.setContentType("text/plain; charset=UTF-8");
        PrintWriter writer = response.getWriter();
        int res = peopleDao.addPeople(people);
        if(res > 0){
            writer.write("<h2>添加成功！</h2>");
        } else {
            writer.write("<h2>添加失败！</h2>");
        }
        writer.flush();
        writer.close();
    }

使用UNIX_TIMESTAMP()函数将Date类型参数转换成了int类型，然后添加的时候就不会有问题。

测试：

点击第一个链接：

查看数据表：

最新的一条记录，添加成功。

3、int转Date（查询时）

现在我表中字段类型是int，而实体类属性类型是Date，那么不做处理的话，查询是会出问题的，可以使用MySQL提供的FROM_UNIXTIME()函数来进行转换，代码如下：

    /*
    * @description: 按照id查询
    * @param: [id]
    * @return: com.ycz.domain.People
    * @author: yanchengzhi
    * @date: 2021/6/14 14:20
    */
    public People findById(Integer id) {
        String sql = "select id,name,FROM_UNIXTIME(birth) birthday,tag from people where id = ?";
        Connection connection = DataSourceManager.getConnection();
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        try {
            ps = connection.prepareStatement(sql);
            ps.setInt(1,id);
            resultSet = ps.executeQuery();
            while(resultSet.next()){
                People p = new People();
                p.setId(resultSet.getInt("id"));
                p.setName(resultSet.getString("name"));
                p.setBirth(resultSet.getDate("birthday"));
                p.setTag(resultSet.getInt("tag"));
                return p;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DataSourceManager.closeConnection(connection);
            DataSourceManager.closeStatement(ps);
            DataSourceManager.closeResultSet(resultSet);
        }
        return null;
    }

关注重点是sql语句：

servlet代码：

    /*
    * @description: 按照id查询
    * @param: [request, response]
    * @return: void
    * @author: yanchengzhi
    * @date: 2021/6/14 14:20
    */
    private void findById(HttpServletRequest request,HttpServletResponse response) {
        Integer id = Integer.parseInt(request.getParameter("id"));
        People p = peopleDao.findById(id);
        if(p != null) {
            System.out.println("查找的记录信息如下：");
            System.out.println("-------------------");
            System.out.println("Id号：" + p.getId());
            System.out.println("姓名：" + p.getName());
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy年MM月dd日");
            System.out.println("生日：" + sdf.format(p.getBirth()));
            System.out.println("Tag标记：" + p.getTag());
        } else {
            System.out.println("查询的记录不存在！");
        }
    }

测试：

点击最后一个链接，控制台输出：

没问题。

4、小结

mysql中，int类型转换成date类型需要借助FROM_UNIXTIME()函数，date类型转换为int类型时需要借助UNIX_TIMESTAMP()函数，用法比较简单，记住就行。