数据库工具类实现

来源：互联网发布：php程序用什么软件编辑：程序博客网时间：2024/05/01 06:54
数据库工具类实现

对数据库资源进行统一申请和释放，易于管理与使用，本例中对数据库连接的获取使用的c3p0的数据库连接池，详情可观本博客另一篇介绍
package com.Android.util;import java.sql.Connection;  import java.sql.PreparedStatement;  import java.sql.ResultSet;  import java.sql.ResultSetMetaData;  import java.sql.SQLException;  import java.util.ArrayList;  import java.util.HashMap;  import java.util.List;  import java.util.Map;  public class JdbcUtil {      // 定义数据库的链接      private Connection connection;      // 定义sql语句的执行对象      private PreparedStatement pstmt;      // 定义查询返回的结果集合      private ResultSet resultSet;      public JdbcUtil() {          //从数据库获取数据库连接        connection = ConnectionManager.getInstance().getConnection();    }      /**      * 执行更新操作      *       * @param sql      *            sql语句      * @param params      *            执行参数      * @return 执行结果      * @throws SQLException      */      public boolean updateByPreparedStatement(String sql, List<?> params)              throws SQLException {          boolean flag = false;          int result = -1;// 表示当用户执行添加删除和修改的时候所影响数据库的行数          pstmt = connection.prepareStatement(sql);          int index = 1;          // 填充sql语句中的占位符          if (params != null && !params.isEmpty()) {              for (int i = 0; i < params.size(); i++) {                  pstmt.setObject(index++, params.get(i));              }          }          result = pstmt.executeUpdate();          flag = result > 0 ? true : false;          return flag;      }      /**      * 执行查询操作      *       * @param sql      *            sql语句      * @param params      *            执行参数      * @return      * @throws SQLException      */      public List<Map<String, Object>> findResult(String sql, List<?> params)              throws SQLException {          List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();          int index = 1;          pstmt = connection.prepareStatement(sql);          if (params != null && !params.isEmpty()) {              for (int i = 0; i < params.size(); i++) {                  pstmt.setObject(index++, params.get(i));              }          }          resultSet = pstmt.executeQuery();          ResultSetMetaData metaData = resultSet.getMetaData();          int cols_len = metaData.getColumnCount();          while (resultSet.next()) {              Map<String, Object> map = new HashMap<String, Object>();              for (int i = 0; i < cols_len; i++) {                  String cols_name = metaData.getColumnName(i + 1);                  Object cols_value = resultSet.getObject(cols_name);                  if (cols_value == null) {                      cols_value = "";                  }                  map.put(cols_name, cols_value);              }              list.add(map);          }          return list;      }      /**      * 执行查询操作      *       * @param sql      *            sql语句      * @param params      *            执行参数      * @return      * @throws SQLException      */      public List findResultToBeanList(String sql, List<?> params, Class<?> cls )              throws SQLException {          List<?> list = null;        int index = 1;          pstmt = connection.prepareStatement(sql);          if (params != null && !params.isEmpty()) {              for (int i = 0; i < params.size(); i++) {                  pstmt.setObject(index++, params.get(i));              }          }          resultSet = pstmt.executeQuery();          try {            list =  GetData.resultSetToList(resultSet, cls);        } catch (Exception e) {            // TODO Auto-generated catch block            e.printStackTrace();        }         return list;    }      /**      * 执行查询操作      *       * @param sql      *            sql语句      * @param params      *            执行参数      * @return      * @throws SQLException      */      public int findResultTotalNumber(String sql, List<?> params )              throws SQLException {          int totalNumber = 0;        int index = 1;          pstmt = connection.prepareStatement(sql);          if (params != null && !params.isEmpty()) {              for (int i = 0; i < params.size(); i++) {                  pstmt.setObject(index++, params.get(i));              }          }          resultSet = pstmt.executeQuery();          try {            if(resultSet.next()){                totalNumber = resultSet.getInt(1);              }        } catch (Exception e) {            // TODO Auto-generated catch block            e.printStackTrace();        }         return totalNumber;    }    /**      * 释放资源      */      public void releaseConn() {          if (resultSet != null) {              try {                  resultSet.close();              } catch (SQLException e) {                  e.printStackTrace();              }          }          if (pstmt != null) {              try {                  pstmt.close();              } catch (SQLException e) {                  e.printStackTrace();              }          }          if (connection != null) {              try {                  connection.close();              } catch (SQLException e) {                  e.printStackTrace();              }          }      }      public static void main(String[] args) {          JdbcUtil jdbcUtil = new JdbcUtil();          try {              List<Map<String, Object>> result = jdbcUtil.findResult(                      "select * from newsTable", null);              for (Map<String, Object> m : result) {                  System.out.println(m);              }          } catch (SQLException e) {              e.printStackTrace();          } finally {              jdbcUtil.releaseConn();          }      }  }
0 0