JDBCtemplete 模板

com.augmentum.oes.common; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.Statement; import java.util.ArrayList; import java.util.List; import com.augmentum.oes.exception.DBException; import com.augmentum.oes.util.DBUtil; public class JDBCTemplete<T> { public List<T> query(String sql, JDBCCallback <T> jdbccallbaclk) { Connection conn =null; PreparedStatement stmt = null; ResultSet rs = null; List<T> data = new ArrayList<T>(); boolean needMyClose = false; try { ConnectionHolder connectionHolder = (ConnectionHolder) AppContext.getAppContext().getObject("APP_REQUEST_THREAD_CONNECTION"); if (connectionHolder != null) { conn = connectionHolder.getConn(); } if (conn == null) { conn = DBUtil.getConnection(); needMyClose = true; } stmt = conn.prepareStatement(sql); jdbccallbaclk.setParams(stmt); rs = stmt.executeQuery(); while (rs.next()) { T object = jdbccallbaclk.rsToObject(rs); data.add(object); } } catch (Exception e) { e.printStackTrace(); throw new DBException(); } finally { DBUtil.close(null, stmt, rs); if (needMyClose) { DBUtil.close(conn, null, null); } } return data; } public int insert(String sql, JDBCCallback<T> jdbcCallback) { Connection conn =null; PreparedStatement stmt = null; ResultSet rs = null; int id = 0; boolean needMyClose = false; try { ConnectionHolder connectionHolder = (ConnectionHolder) AppContext.getAppContext().getObject("APP_REQUEST_THREAD_CONNECTION"); if (connectionHolder != null) { conn = connectionHolder.getConn(); } if (conn == null) { conn = DBUtil.getConnection(); needMyClose = true; } stmt = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS); jdbcCallback.setParams(stmt); stmt.executeUpdate(); rs = stmt.getGeneratedKeys(); if (rs.next()) { id = rs.getInt(1); } } catch (Exception e) { e.printStackTrace(); throw new DBException(); } finally { DBUtil.close(null, stmt, null); if (needMyClose) { DBUtil.close(conn, null, null); } } return id; }; public void insertWithoutKey(String sql, JDBCCallback<T> jdbcCallback) { Connection conn =null; PreparedStatement stmt = null; boolean needMyClose = false; try { ConnectionHolder connectionHolder = (ConnectionHolder) AppContext.getAppContext().getObject("APP_REQUEST_THREAD_CONNECTION"); if (connectionHolder != null) { conn = connectionHolder.getConn(); } if (conn == null) { conn = DBUtil.getConnection(); needMyClose = true; } stmt = conn.prepareStatement(sql); jdbcCallback.setParams(stmt); stmt.executeUpdate(); } catch (Exception e) { e.printStackTrace(); throw new DBException(); } finally { DBUtil.close(null, stmt, null); if (needMyClose) { DBUtil.close(conn, null, null); } } }; public T QueryOne(String sql, JDBCCallback<T> jdbcCallback) { List<T> data = query(sql, jdbcCallback); if (data !=null && !data.isEmpty()) { return data.get(0); } else { return null; } } public int update(String sql,JDBCCallback<T> jdbcCallback) { Connection conn =null; PreparedStatement stmt = null; int count = 0; boolean needMyClose = false; try { ConnectionHolder connectionHolder = (ConnectionHolder) AppContext.getAppContext().getObject("APP_REQUEST_THREAD_CONNECTION"); if (connectionHolder != null) { conn = connectionHolder.getConn(); } if (conn == null) { conn = DBUtil.getConnection(); needMyClose = true; } stmt = conn.prepareStatement(sql); jdbcCallback.setParams(stmt); count = stmt.executeUpdate(); } catch (Exception e) { e.printStackTrace(); throw new DBException(); } finally { DBUtil.close(null, stmt, null); if (needMyClose) { DBUtil.close(conn, null, null); } } return count; } public int deleteByid(String sql, JDBCCallback<T> jdbcCallback) { Connection conn =null; PreparedStatement stmt = null; int count = 0; boolean needMyClose = false; try { ConnectionHolder connectionHolder = (ConnectionHolder) AppContext.getAppContext().getObject("APP_REQUEST_THREAD_CONNECTION"); if (connectionHolder != null) { conn = connectionHolder.getConn(); } if (conn == null) { conn = DBUtil.getConnection(); needMyClose = true; } stmt = conn.prepareStatement(sql); jdbcCallback.setParams(stmt); count = stmt.executeUpdate(); } catch (Exception e) { e.printStackTrace(); throw new DBException(); } finally { DBUtil.close(null, stmt, null); if (needMyClose) { DBUtil.close(conn, null, null); } } return count; } public int getCount(String sql,JDBCCallback<T> jdbcCallback) { Connection conn = null; PreparedStatement stmt = null; ResultSet rs = null; int count =0; boolean needMyClose = false; try { ConnectionHolder connectionHolder = (ConnectionHolder) AppContext.getAppContext().getObject("APP_REQUEST_THREAD_CONNECTION"); if (connectionHolder != null) { conn = connectionHolder.getConn(); } if (conn == null) { conn = DBUtil.getConnection(); needMyClose = true; } stmt = conn.prepareStatement(sql); jdbcCallback.setParams(stmt); rs = stmt.executeQuery(); if (rs.next()) { count = rs.getInt(1); } } catch (Exception e) { e.printStackTrace(); throw new DBException(); } finally { DBUtil.close(null, stmt, rs); if (needMyClose) { DBUtil.close(conn, null, null); } } return count; } public int getCountAll(String sql) { return this.getCount(sql, new JDBCAbstractCallback<T>() {}); } } JDBCTemplete

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