工作中遇到要查询一堆数据里面最新的那条数据,可以将最后更新时间last_update倒叙后输出。
数据库信息:
CREATE TABLE `xxx_info` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`xxx_id` varchar(10) DEFAULT NULL,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'last_update time',
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'data create time',
PRIMARY KEY (`id`),
KEY `idx_xxx_id` (`xxx_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='xxx_info message'
实体类跟数据库关联:
import com.google.gson.annotations.Expose;
import com.google.gson.annotations.SerializedName;
import org.hibernate.annotations.Type;
import org.joda.time.DateTime;
import javax.persistence.*;
import java.io.Serializable;
/**
* Created by ziqi on 2017/11/29.
*/
@Entity
@Table(name="xxx_info")
public class XxxInfo {
@Expose(serialize = false, deserialize = false)
@Id
@Column(name = "id", updatable = false, unique = true, nullable = false)
@GeneratedValue(strategy = GenerationType.AUTO)
private Integer id;
@SerializedName(value = "xxx_id",alternate = "xxxId")
@Column(name = "xxx_id")
private String xxxId;
@Expose(serialize = false, deserialize = false)
@Column(name = "last_update")
@Type(type = "org.jadira.usertype.dateandtime.joda.PersistentDateTime")
private DateTime lastUpdate;
@Expose(serialize = false, deserialize = false)
@Column(name = "create_time")
@Type(type = "org.jadira.usertype.dateandtime.joda.PersistentDateTime")
private DateTime createTime;
@Override
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getxxxId() {
return xxxId;
}
public void setxxxId(String xxxId) {
this.xxxId = xxxId;
}
public DateTime getLastUpdate() {
return lastUpdate;
}
public void setLastUpdate(DateTime lastUpdate) {
this.lastUpdate = lastUpdate;
}
public DateTime getCreateTime() {
return createTime;
}
public void setCreateTime(DateTime createTime) {
this.createTime = createTime;
}
}
sql让数据库以倒叙输出语句如下:
通过 ”order by“实现,
语法 :order by 字段 asc/desc,
备注:asc是表示升序,desc表示降序。sql:select * from table_name order by column_name1 desc;
解释:上面语句的意思就是根据"column_name1"字段排序,倒叙输出table_name表中的数据。
HibernateDao处理
hibernate查询最新数据:
Session session = getSessionFactory().openSession();
try {
Query query = session.createQuery("select v from VehicleInfo v where v.vin = :vin order by v.lastUpdate desc").setParameter("vin", vin);
List<VehicleInfo> vehicleInfoList = query.list();
return vehicleInfoList.get(0);
} catch (Exception e) {
LOGGER.error(e.getMessage());
throw e;
} finally {
session.close();
}
(from 后跟的是需要查询的实体类的名字,而不是数据库中对应表的名字,这点需要特别注意)