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()函数，用法比较简单，记住就行。