MySQL中int和date的相互转换
int和date的互转1、业务场景2、Date转int(添加 / 修改时)3、int转Date(查询时)4、小结1、业务场景因为我的实体类中有个Date类型的属性,但是数据表中对应字段的类型却是int类型的,所以才会有这么一个需求,要求添加或修改记录的时候将Date类型转换成Int存进表,查询的时候将int转换为Date类型展示出来,如果不转换的话,进行增删或查询的时候一定会报错。2、Date转i
·
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()
函数,用法比较简单,记住就行。
更多推荐
已为社区贡献2条内容
所有评论(0)