当前位置：Gxlcms > 数据库问题 > JDBCtemplete 模板

JDBCtemplete 模板

时间：2021-07-01 10:21:17 帮助过：11人阅读

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 
  1. package com.augmentum.oes.common;
  2. import java.sql.PreparedStatement;
  3. import java.sql.ResultSet;
  4. import java.sql.SQLException;
  5. public interface JDBCCallback<T> {
  6.     T rsToObject(ResultSet rs) throws SQLException;
  7.     void setParams(PreparedStatement stmt) throws SQLException;
  8. }

　　加入数据

技术分享 
  1. <span style="color: #0000ff">package</span><span style="color: #000000"> com.augmentum.oes.dao.impl;
  2. </span><span style="color: #0000ff">import</span><span style="color: #000000"> java.sql.PreparedStatement;
  3. </span><span style="color: #0000ff">import</span><span style="color: #000000"> java.sql.ResultSet;
  4. </span><span style="color: #0000ff">import</span><span style="color: #000000"> java.sql.SQLException;
  5. </span><span style="color: #0000ff">import</span><span style="color: #000000"> java.util.List;
  6. </span><span style="color: #0000ff">import</span><span style="color: #000000"> org.springframework.jdbc.core.JdbcTemplate;
  7. </span><span style="color: #0000ff">import</span><span style="color: #000000"> com.augmentum.oes.common.JDBCAbstractCallback;
  8. </span><span style="color: #0000ff">import</span><span style="color: #000000"> com.augmentum.oes.common.JDBCCallback;
  9. </span><span style="color: #0000ff">import</span><span style="color: #000000"> com.augmentum.oes.dao.QuestionDao;
  10. </span><span style="color: #0000ff">import</span><span style="color: #000000"> com.augmentum.oes.model.Question;
  11. </span><span style="color: #0000ff">import</span><span style="color: #000000"> com.augmentum.oes.util.Pagination;
  12. </span><span style="color: #0000ff">import</span><span style="color: #000000"> com.augmentum.oes.util.StringUtil;
  13. </span><span style="color: #0000ff">public</span> <span style="color: #0000ff">class</span> QuestionDaoImpl <span style="color: #0000ff">implements</span><span style="color: #000000"> QuestionDao{
  14.     </span><span style="color: #0000ff">private</span> Question rsToQuestion(ResultSet rs) <span style="color: #0000ff">throws</span><span style="color: #000000"> SQLException {
  15.         Question question </span>= <span style="color: #0000ff">new</span><span style="color: #000000"> Question();
  16.         question.setId(rs.getInt(</span>"id"<span style="color: #000000">));
  17.         question.setQuestion_desc(rs.getString(</span>"question_desc"<span style="color: #000000">));
  18.         question.setRight_choice_name(rs.getString(</span>"right_choice_name"<span style="color: #000000">));
  19.         question.setChoice_a(rs.getString(</span>"choice_a"<span style="color: #000000">));
  20.         question.setChoice_b(rs.getString(</span>"choice_b"<span style="color: #000000">));
  21.         question.setChoice_c(rs.getString(</span>"choice_c"<span style="color: #000000">));
  22.         question.setChoice_d(rs.getString(</span>"choice_d"<span style="color: #000000">));
  23.         question.setQuestion_status(rs.getInt(</span>"question_status"<span style="color: #000000">));
  24.         </span><span style="color: #0000ff">return</span><span style="color: #000000"> question;
  25.     }
  26.     </span><span style="color: #0000ff">private</span><span style="color: #000000"> JdbcTemplate jdbcTemplate;
  27.     @Override
  28.     </span><span style="color: #0000ff">public</span> Question queryById(<span style="color: #0000ff">final</span> <span style="color: #0000ff">int</span><span style="color: #000000"> question_id){
  29.         String sql </span>= "SELECT * FROM question where id = ?"<span style="color: #000000"> ;
  30.         JDBCCallback</span><Question> j = <span style="color: #0000ff">new</span> JDBCAbstractCallback<Question><span style="color: #000000">() {
  31.             @Override
  32.             </span><span style="color: #0000ff">public</span> <span style="color: #0000ff">void</span> setParams(PreparedStatement stmt) <span style="color: #0000ff">throws</span><span style="color: #000000"> SQLException {
  33.                 stmt.setInt(</span>1<span style="color: #000000">, question_id);
  34.                 </span><span style="color: #0000ff">super</span><span style="color: #000000">.setParams(stmt);
  35.             }
  36.             @Override
  37.              </span><span style="color: #0000ff">public</span> Question rsToObject(ResultSet rs) <span style="color: #0000ff">throws</span><span style="color: #000000"> SQLException {
  38.                  </span><span style="color: #0000ff">return</span><span style="color: #000000"> rsToQuestion(rs);
  39.             }
  40.         };
  41.         List</span><Question> list =<span style="color: #000000"> jdbcTemplete.query(sql,j);
  42.         </span><span style="color: #0000ff">return</span> list.get(0<span style="color: #000000">);
  43.     }
  44.     @Override
  45.     </span><span style="color: #0000ff">public</span> <span style="color: #0000ff">int</span> update(<span style="color: #0000ff">final</span><span style="color: #000000"> Question question) {
  46.         String sql </span>= "UPDATE question SET question_desc=?,right_choice_name=?,"
  47.                 + "choice_a=?, choice_b=?,choice_c=?,choice_d=?,question_status=? WHERE id = ?  "<span style="color: #000000">;
  48.         </span><span style="color: #0000ff">int</span> count =jdbcTemplete.update(sql, <span style="color: #0000ff">new</span> JDBCAbstractCallback<Question><span style="color: #000000">() {
  49.             @Override
  50.             </span><span style="color: #0000ff">public</span> <span style="color: #0000ff">void</span> setParams(PreparedStatement stmt) <span style="color: #0000ff">throws</span><span style="color: #000000"> SQLException {
  51.                 stmt.setString(</span>1<span style="color: #000000">, question.getQuestion_desc());
  52.                 stmt.setString(</span>2<span style="color: #000000">, question.getRight_choice_name());
  53.                 stmt.setString(</span>3<span style="color: #000000">, question.getChoice_a());
  54.                 stmt.setString(</span>4<span style="color: #000000">, question.getChoice_b());
  55.                 stmt.setString(</span>5<span style="color: #000000">, question.getChoice_c());
  56.                 stmt.setString(</span>6<span style="color: #000000">, question.getChoice_d());
  57.                 stmt.setInt(</span>7<span style="color: #000000">, question.getQuestion_status());
  58.                 stmt.setInt(</span>8<span style="color: #000000">, question.getId());
  59.             }
  60.         });
  61.         </span><span style="color: #0000ff">return</span><span style="color: #000000"> count;
  62.     }
  63.     @Override
  64.     </span><span style="color: #0000ff">public</span> List<Question> getListByKeyWord(<span style="color: #0000ff">final</span><span style="color: #000000"> String keyword, Pagination pagination,String orderTags) {
  65.         pagination.setTotalCount(</span><span style="color: #0000ff">this</span><span style="color: #000000">.getCount(keyword));
  66.         </span><span style="color: #0000ff">if</span> (pagination.getCurrentPage() ><span style="color: #000000"> pagination.getTotalCount()) {
  67.             pagination.setCurrentPage(pagination.getTotalCount());
  68.         }
  69.         String sql </span>="SELECT * FROM question WHERE question_status = 0 AND question_desc LIKE ? ORDER BY id "+(StringUtil.isEmpty(orderTags)?"ASC":"DESC")+" LIMIT "+pagination.getOffset()+","+<span style="color: #000000">pagination.getPageSize() ;
  70.         List</span><Question> list = jdbcTemplete.query(sql, <span style="color: #0000ff">new</span> JDBCAbstractCallback<Question><span style="color: #000000">() {
  71.             @Override
  72.             </span><span style="color: #0000ff">public</span> <span style="color: #0000ff">void</span> setParams(PreparedStatement stmt) <span style="color: #0000ff">throws</span><span style="color: #000000"> SQLException {
  73.                 stmt.setString(</span>1,"%"+keyword+"%"<span style="color: #000000">);
  74.             }
  75.             @Override
  76.             </span><span style="color: #0000ff">public</span> Question rsToObject(ResultSet rs) <span style="color: #0000ff">throws</span><span style="color: #000000"> SQLException {
  77.                 </span><span style="color: #0000ff">return</span><span style="color: #000000"> rsToQuestion(rs);
  78.             }
  79.         });
  80.         </span><span style="color: #0000ff">return</span><span style="color: #000000"> list;
  81.     }
  82.     @Override
  83.     </span><span style="color: #0000ff">public</span> List<Question><span style="color: #000000"> getList(Pagination pagination,String orderTags) {
  84.         pagination.setTotalCount(</span><span style="color: #0000ff">this</span>.getCount(<span style="color: #0000ff">null</span><span style="color: #000000">));
  85.         </span><span style="color: #0000ff">if</span> (pagination.getCurrentPage() ><span style="color: #000000"> pagination.getTotalCount()) {
  86.             pagination.setCurrentPage(pagination.getTotalCount());
  87.         }
  88.         String sql </span>="SELECT * FROM question WHERE question_status = 0 ORDER BY id "+(StringUtil.isEmpty(orderTags)?"ASC":"DESC")+" LIMIT "+pagination.getOffset()+","+<span style="color: #000000">pagination.getPageSize() ;
  89.         List</span><Question> list = jdbcTemplete.query(sql, <span style="color: #0000ff">new</span> JDBCAbstractCallback<Question><span style="color: #000000">() {
  90.             @Override
  91.             </span><span style="color: #0000ff">public</span> Question rsToObject(ResultSet rs) <span style="color: #0000ff">throws</span><span style="color: #000000"> SQLException {
  92.                 </span><span style="color: #0000ff">return</span><span style="color: #000000"> rsToQuestion(rs);
  93.             }
  94.         });
  95.         </span><span style="color: #0000ff">return</span><span style="color: #000000"> list;
  96.     }
  97.     @Override
  98.     </span><span style="color: #0000ff">public</span> <span style="color: #0000ff">int</span> deleteById(<span style="color: #0000ff">final</span> <span style="color: #0000ff">int</span><span style="color: #000000"> id) {
  99.        </span><span style="color: #008000">//</span><span style="color: #008000"> String sql = "DELETE FROM question where id = ?";</span>
  100.         String sql = "UPDATE question SET question_status=1 WHERE id= ?"<span style="color: #000000">;
  101.         </span><span style="color: #0000ff">int</span> count = jdbcTemplete.deleteByid(sql, <span style="color: #0000ff">new</span> JDBCAbstractCallback<Question><span style="color: #000000">() {
  102.             @Override
  103.             </span><span style="color: #0000ff">public</span> <span style="color: #0000ff">void</span> setParams(PreparedStatement stmt) <span style="color: #0000ff">throws</span><span style="color: #000000"> SQLException {
  104.                 stmt.setInt(</span>1<span style="color: #000000">, id);
  105.             }
  106.         });
  107.         </span><span style="color: #0000ff">return</span><span style="color: #000000"> count;
  108.     }
  109.     @Override
  110.     </span><span style="color: #0000ff">public</span> <span style="color: #0000ff">int</span> getCount(<span style="color: #0000ff">final</span><span style="color: #000000"> String question_desc) {
  111.         </span><span style="color: #0000ff">int</span> count =0<span style="color: #000000">;
  112.         String sql </span>= "SELECT count(*) FROM question WHERE question_status = 0"<span style="color: #000000">;
  113.         </span><span style="color: #0000ff">if</span><span style="color: #000000"> (StringUtil.isEmpty(question_desc)) {
  114.             count </span>=<span style="color: #000000"> jdbcTemplete.getCountAll(sql);
  115.         } </span><span style="color: #0000ff">else</span><span style="color: #000000"> {
  116.             sql </span>= sql +" where question_desc LIKE ?"<span style="color: #000000">;
  117.             count </span>= jdbcTemplete.getCount(sql, <span style="color: #0000ff">new</span> JDBCAbstractCallback<Question><span style="color: #000000">() {
  118.             @Override
  119.             </span><span style="color: #0000ff">public</span> <span style="color: #0000ff">void</span> setParams(PreparedStatement stmt) <span style="color: #0000ff">throws</span><span style="color: #000000"> SQLException {
  120.                 stmt.setString(</span>1,"%"+question_desc+"%"<span style="color: #000000">);
  121.                 </span><span style="color: #0000ff">super</span><span style="color: #000000">.setParams(stmt);
  122.             }
  123.             });
  124.         }
  125.         </span><span style="color: #0000ff">return</span><span style="color: #000000"> count;
  126.     }
  127.     @Override
  128.     </span><span style="color: #0000ff">public</span> <span style="color: #0000ff">int</span><span style="color: #000000"> getNextId() {
  129.         </span><span style="color: #0000ff">int</span> nextId = 0<span style="color: #000000">;
  130.         String sql </span>= "SELECT max(id) FROM question"<span style="color: #000000">;
  131.         nextId </span>=<span style="color: #000000"> jdbcTemplete.getCountAll(sql);
  132.         </span><span style="color: #0000ff">return</span><span style="color: #000000"> nextId;
  133.     }
  134.     @Override
  135.     </span><span style="color: #0000ff">public</span> <span style="color: #0000ff">void</span> addUpdate(<span style="color: #0000ff">final</span><span style="color: #000000"> Question question) {
  136.         String sql </span>= "INSERT INTO question(question_status) VALUES (?)"<span style="color: #000000">;
  137.          jdbcTemplete.insert(sql, </span><span style="color: #0000ff">new</span> JDBCAbstractCallback<Question><span style="color: #000000">() {
  138.              @Override
  139.              </span><span style="color: #0000ff">public</span> <span style="color: #0000ff">void</span> setParams(PreparedStatement stmt) <span style="color: #0000ff">throws</span><span style="color: #000000"> SQLException {
  140.                  stmt.setInt(</span>1<span style="color: #000000">, question.getQuestion_status());
  141.              }
  142.          });
  143.     }
  144. }</span>
View Code

 

JDBCtemplete 模板

标签：value   else   ret   tco   one   edm   acl   cal   where   

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！