public int count(String title, String type, Integer number, Integer id, Date date) {
String sql = "select count(*) from wxarticle where 1=1";
if (title != null && !"".equals(title)) {
sql += " and title like %" + title + "%";
}
if (type != null && !"".equals(type)) {
sql += " and type=" + type;
}
if (id != null && !"".equals(id)) {
sql += " and id=" + id;
}
if (date != null) {
String dateStr = DateUtil.dateToString(date, "yyyy-MM-dd");
String begin = dateStr + " 00:00:00";
String end = dateStr + " 23:59:59";
sql += " and createtime>=" + "\'" + begin + "\'";
sql += " and createtime<=" + "\'" + end + "\'";
}
sql += " order by createtime desc";
if (number != null && !"".equals(number)) {
sql += " LIMIT " + number;
}
int count = jdbcTemplate.queryForObject(sql, Integer.class);
return count;
}
public List<WxArticle> queryArticle(
String title,
String type,
Integer number,
Integer id,
Date date,
Integer page,
Integer pageSize) {
String sql = "select * from wxarticle where 1=1";
if (title != null && !"".equals(title)) {
sql += " and title like %" + title + "%";
}
if (type != null && !"".equals(type)) {
sql += " and type=" + type;
}
if (id != null && !"".equals(id)) {
sql += " and id=" + id;
}
if (date != null) {
String dateStr = DateUtil.dateToString(date, "yyyy-MM-dd");
String begin = dateStr + " 00:00:00";
String end = dateStr + " 23:59:59";
sql += " and createtime>=" + "\'" + begin + "\'";
sql += " and createtime<=" + "\'" + end + "\'";
}
sql += " order by createtime desc";
if (number != null && !"".equals(number)) {
sql += " LIMIT " + number;
}
if (page != null && pageSize != null && !"".equals(page) && !"".equals(pageSize)) {
sql += " LIMIT " + page + "," + pageSize;
}
List<Map<String, Object>> temp = jdbcTemplate.queryForList(sql);
return getArticle(temp);
}
public void saveArticle(WxArticle wxArticle) {
String createtime = DateUtil.dateToString(wxArticle.getCreatetime(), null);
String sql =
"INSERT INTO `wxarticle`(title, summary, type, content, status, price, createtime) "
+ "VALUES('TITLE','SUMMARY','TYPE','CONTENT','1', PRICE, 'CREATETIME');";
sql =
sql.replace("TITLE", wxArticle.getTitle())
.replace("SUMMARY", wxArticle.getSummary())
.replace("TYPE", wxArticle.getType())
.replace("CONTENT", wxArticle.getContent())
.replace("PRICE", wxArticle.getPrice() + "")
.replace("CREATETIME", createtime);
jdbcTemplate.execute(sql);
}
public void updateArticle(WxArticle wxArticle) {
String createtime = DateUtil.dateToString(wxArticle.getCreatetime(), null);
String sql =
"UPDATE `wxarticle` SET title='TITLE', summary='SUMMARY', type='TYPE', content='CONTENT', price=PRICE, createtime='CREATETIME' "
+ "where id=ID;";
sql =
sql.replace("TITLE", wxArticle.getTitle())
.replace("SUMMARY", wxArticle.getSummary())
.replace("TYPE", wxArticle.getType())
.replace("CONTENT", wxArticle.getContent())
.replace("PRICE", wxArticle.getPrice() + "")
.replace("CREATETIME", createtime)
.replace("ID", wxArticle.getId() + "");
jdbcTemplate.execute(sql);
}
