时间:2021-07-01 10:21:17 帮助过:11人阅读
package com.augmentum.oes.common; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; public interface JDBCCallback<T> { T rsToObject(ResultSet rs) throws SQLException; void setParams(PreparedStatement stmt) throws SQLException; }
加入数据
package com.augmentum.oes.dao.impl; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.List; import org.springframework.jdbc.core.JdbcTemplate; import com.augmentum.oes.common.JDBCAbstractCallback; import com.augmentum.oes.common.JDBCCallback; import com.augmentum.oes.dao.QuestionDao; import com.augmentum.oes.model.Question; import com.augmentum.oes.util.Pagination; import com.augmentum.oes.util.StringUtil; public class QuestionDaoImpl implements QuestionDao{ private Question rsToQuestion(ResultSet rs) throws SQLException { Question question = new Question(); question.setId(rs.getInt("id")); question.setQuestion_desc(rs.getString("question_desc")); question.setRight_choice_name(rs.getString("right_choice_name")); question.setChoice_a(rs.getString("choice_a")); question.setChoice_b(rs.getString("choice_b")); question.setChoice_c(rs.getString("choice_c")); question.setChoice_d(rs.getString("choice_d")); question.setQuestion_status(rs.getInt("question_status")); return question; } private JdbcTemplate jdbcTemplate; @Override public Question queryById(final int question_id){ String sql = "SELECT * FROM question where id = ?" ; JDBCCallback<Question> j = new JDBCAbstractCallback<Question>() { @Override public void setParams(PreparedStatement stmt) throws SQLException { stmt.setInt(1, question_id); super.setParams(stmt); } @Override public Question rsToObject(ResultSet rs) throws SQLException { return rsToQuestion(rs); } }; List<Question> list = jdbcTemplete.query(sql,j); return list.get(0); } @Override public int update(final Question question) { String sql = "UPDATE question SET question_desc=?,right_choice_name=?," + "choice_a=?, choice_b=?,choice_c=?,choice_d=?,question_status=? WHERE id = ? "; int count =jdbcTemplete.update(sql, new JDBCAbstractCallback<Question>() { @Override public void setParams(PreparedStatement stmt) throws SQLException { stmt.setString(1, question.getQuestion_desc()); stmt.setString(2, question.getRight_choice_name()); stmt.setString(3, question.getChoice_a()); stmt.setString(4, question.getChoice_b()); stmt.setString(5, question.getChoice_c()); stmt.setString(6, question.getChoice_d()); stmt.setInt(7, question.getQuestion_status()); stmt.setInt(8, question.getId()); } }); return count; } @Override public List<Question> getListByKeyWord(final String keyword, Pagination pagination,String orderTags) { pagination.setTotalCount(this.getCount(keyword)); if (pagination.getCurrentPage() > pagination.getTotalCount()) { pagination.setCurrentPage(pagination.getTotalCount()); } String sql ="SELECT * FROM question WHERE question_status = 0 AND question_desc LIKE ? ORDER BY id "+(StringUtil.isEmpty(orderTags)?"ASC":"DESC")+" LIMIT "+pagination.getOffset()+","+pagination.getPageSize() ; List<Question> list = jdbcTemplete.query(sql, new JDBCAbstractCallback<Question>() { @Override public void setParams(PreparedStatement stmt) throws SQLException { stmt.setString(1,"%"+keyword+"%"); } @Override public Question rsToObject(ResultSet rs) throws SQLException { return rsToQuestion(rs); } }); return list; } @Override public List<Question> getList(Pagination pagination,String orderTags) { pagination.setTotalCount(this.getCount(null)); if (pagination.getCurrentPage() > pagination.getTotalCount()) { pagination.setCurrentPage(pagination.getTotalCount()); } String sql ="SELECT * FROM question WHERE question_status = 0 ORDER BY id "+(StringUtil.isEmpty(orderTags)?"ASC":"DESC")+" LIMIT "+pagination.getOffset()+","+pagination.getPageSize() ; List<Question> list = jdbcTemplete.query(sql, new JDBCAbstractCallback<Question>() { @Override public Question rsToObject(ResultSet rs) throws SQLException { return rsToQuestion(rs); } }); return list; } @Override public int deleteById(final int id) { // String sql = "DELETE FROM question where id = ?"; String sql = "UPDATE question SET question_status=1 WHERE id= ?"; int count = jdbcTemplete.deleteByid(sql, new JDBCAbstractCallback<Question>() { @Override public void setParams(PreparedStatement stmt) throws SQLException { stmt.setInt(1, id); } }); return count; } @Override public int getCount(final String question_desc) { int count =0; String sql = "SELECT count(*) FROM question WHERE question_status = 0"; if (StringUtil.isEmpty(question_desc)) { count = jdbcTemplete.getCountAll(sql); } else { sql = sql +" where question_desc LIKE ?"; count = jdbcTemplete.getCount(sql, new JDBCAbstractCallback<Question>() { @Override public void setParams(PreparedStatement stmt) throws SQLException { stmt.setString(1,"%"+question_desc+"%"); super.setParams(stmt); } }); } return count; } @Override public int getNextId() { int nextId = 0; String sql = "SELECT max(id) FROM question"; nextId = jdbcTemplete.getCountAll(sql); return nextId; } @Override public void addUpdate(final Question question) { String sql = "INSERT INTO question(question_status) VALUES (?)"; jdbcTemplete.insert(sql, new JDBCAbstractCallback<Question>() { @Override public void setParams(PreparedStatement stmt) throws SQLException { stmt.setInt(1, question.getQuestion_status()); } }); } }View Code
JDBCtemplete 模板
标签:value else ret tco one edm acl cal where