数据库工具类

  1 package cn.sunny.utils;
  2 
  3 import java.io.IOException;
  4 import java.io.InputStream;
  5 import java.sql.Connection;
  6 import java.sql.DriverManager;
  7 import java.sql.SQLException;
  8 import java.util.List;
  9 import java.util.Map;
 10 import java.util.Properties;
 11 
 12 import org.apache.commons.collections.MapUtils;
 13 import org.apache.commons.dbcp.BasicDataSource;
 14 import org.apache.commons.dbutils.BasicRowProcessor;
 15 import org.apache.commons.dbutils.BeanProcessor;
 16 import org.apache.commons.dbutils.QueryRunner;
 17 import org.apache.commons.dbutils.handlers.ArrayHandler;
 18 import org.apache.commons.dbutils.handlers.ArrayListHandler;
 19 import org.apache.commons.dbutils.handlers.BeanHandler;
 20 import org.apache.commons.dbutils.handlers.BeanListHandler;
 21 import org.apache.commons.dbutils.handlers.ColumnListHandler;
 22 import org.apache.commons.dbutils.handlers.KeyedHandler;
 23 import org.apache.commons.dbutils.handlers.MapHandler;
 24 import org.apache.commons.dbutils.handlers.MapListHandler;
 25 import org.apache.commons.dbutils.handlers.ScalarHandler;
 26 import org.apache.log4j.Logger;
 27 
 28 
 29 /**
 30  * 数据库CRUD工具br>
 31  * 数据库配置db.properties文件
 32  * @author chenyangguang
 33  * @date 2015-5-27上午09:13:48
 34  */
 35 public class DBUtil {
 36     
 37     private static Logger log = Logger.getLogger(DBUtil.class);
 38     
 39     private static String username;
 40     private static String password;
 41     
 42     private static String driver;
 43     private static String url;
 44     
 45     private static BasicDataSource ds = null;
 46     
 47     static{
 48         Properties prop = new Properties();
 49         InputStream in = DBUtil.class.getResourceAsStream("/db.properties");
 50         try {
 51             prop.load(in);
 52             username = prop.getProperty("db.username");
 53             password = prop.getProperty("db.password");
 54             String dbType = prop.getProperty("db.type");
 55             String host = prop.getProperty("db.ip");
 56             String port = prop.getProperty("db.port");
 57             String dbName = prop.getProperty("db.name");
 58             
 59             if ("MySQL".equalsIgnoreCase(dbType)) {
 60                 driver = "com.mysql.jdbc.Driver";
 61                 url = "jdbc:mysql://" + host + ":" + port + "/" + dbName;
 62             } else if ("Oracle".equalsIgnoreCase(dbType)) {
 63                 driver = "oracle.jdbc.driver.OracleDriver";
 64                 url = "jdbc:oracle:thin:@" + host + ":" + port + ":" + dbName;
 65             } else if ("SQLServer".equalsIgnoreCase(dbType)) {
 66                 driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
 67                 url = "jdbc:sqlserver://" + host + ":" + port + ";databaseName=" + dbName;
 68             } else if ("PostgresSQL".equalsIgnoreCase(dbType)) {
 69                 driver = "org.postgresql.Driver";
 70                 url = "jdbc:postgresql://"+host+":"+port+"/"+dbName;
 71             }
 72         } catch (IOException e) {
 73             log.error("获取数据库连接失败",e);
 74         }
 75     }
 76     
 77     private static BasicDataSource getDataSource(){
 78         if (ds == null) {
 79             ds = new BasicDataSource();
 80             ds.setDriverClassName(driver);  
 81             ds.setUsername(username);  
 82             ds.setPassword(password);  
 83             ds.setUrl(url);  
 84         }
 85         return ds;
 86     }
 87     
 88     private static QueryRunner getQueryRunner(){
 89         return new QueryRunner(getDataSource());
 90     }
 91     
 92     
 93     public static Connection openConn(){
 94          Connection conn = null;
 95          try {
 96             Class.forName(driver);
 97             conn = DriverManager.getConnection(url, username, password);
 98         } catch (ClassNotFoundException e) {
 99             log.error("数据库驱动加载失败",e);
100         } catch (SQLException e) {
101             log.error("获取数据库连接失败",e);
102         }
103         return conn;
104     }
105 
106 
107     // 查询（返回Array结果）
108     public static Object[] queryArray(String sql, Object... params) throws SQLException {
109         Object[] result = null;
110         try {
111             result = getQueryRunner().query(sql, new ArrayHandler(), params);
112         } catch (SQLException e) {
113             throw e;
114         }
115         return result;
116     }
117 
118     // 查询（返回ArrayList结果）
119     public static List<Object[]> queryArrayList(String sql, Object... params) throws SQLException {
120         List<Object[]> result = null;
121         try {
122             result = getQueryRunner().query(sql, new ArrayListHandler(), params);
123         } catch (SQLException e) {
124             throw e;
125         }
126         return result;
127     }
128 
129     // 查询（返回Map结果）
130     public static Map<String, Object> queryMap(String sql, Object... params) {
131         Map<String, Object> result = null;
132         try {
133             result = getQueryRunner().query( sql, new MapHandler(), params);
134         } catch (SQLException e) {
135             e.printStackTrace();
136         }
137         return result;
138     }
139     
140     /**
141      * 查询（返回MapList结果）
142      * @param sql
143      * @return
144      */
145     public static List<Map<String, Object>> queryMapList(String sql) {
146         List<Map<String, Object>> result = null;
147         try {
148             result = getQueryRunner().query( sql, new MapListHandler());
149         } catch (SQLException e) {
150             e.printStackTrace();
151         }
152         return result;
153     }
154     
155 
156     /**
157      * 查询（返回MapList结果）
158      * @param sql
159      * @param params
160      * @return
161      */
162     public static List<Map<String, Object>> queryMapList(String sql, Object... params) {
163         List<Map<String, Object>> result = null;
164         try {
165             result = getQueryRunner().query( sql, new MapListHandler(), params);
166         } catch (SQLException e) {
167             e.printStackTrace();
168         }
169         return result;
170     }
171 
172     /**
173      * 查询（返回Bean结果）
174      * @param <T>
175      * @param cls
176      * @param map
177      * @param sql
178      * @param params
179      * @return
180      */
181     public static <T> T queryBean(Class<T> cls, Map<String, String> map, String sql, Object... params) {
182         T result = null;
183         try {
184             if (MapUtils.isNotEmpty(map)) {
185                 result = getQueryRunner().query( sql, new BeanHandler<T>(cls, new BasicRowProcessor(new BeanProcessor(map))), params);
186             } else {
187                 result = getQueryRunner().query( sql, new BeanHandler<T>(cls), params);
188             }
189         } catch (SQLException e) {
190             e.printStackTrace();
191         }
192         return result;
193     }
194 
195     // 查询（返回BeanList结果）
196     public static <T> List<T> queryBeanList(Class<T> cls, Map<String, String> map, String sql, Object... params) throws SQLException {
197         List<T> result = null;
198         try {
199             if (MapUtils.isNotEmpty(map)) {
200                 result = getQueryRunner().query( sql, new BeanListHandler<T>(cls, new BasicRowProcessor(new BeanProcessor(map))), params);
201             } else {
202                 result = getQueryRunner().query( sql, new BeanListHandler<T>(cls), params);
203             }
204         } catch (SQLException e) {
205             throw e;
206         }
207         return result;
208     }
209 
210     // 查询指定列名的）（单条数据）
211     public static <T> T queryColumn(String column, String sql, Object... params) throws SQLException {
212         T result = null;
213         try {
214             result = getQueryRunner().query(sql, new ScalarHandler<T>(column), params);
215         } catch (SQLException e) {
216             throw e;
217         }
218         return result;
219     }
220 
221     // 查询指定列名的）（多条数据）
222     public static <T> List<T> queryColumnList(String column, String sql, Object... params) throws SQLException {
223         List<T> result = null;
224         try {
225             result = getQueryRunner().query(sql, new ColumnListHandler<T>(column), params);
226         } catch (SQLException e) {
227             throw e;
228         }
229         return result;
230     }
231 
232     // 查询指定列名对应的记录映）
233     public static <T> Map<T, Map<String, Object>> queryKeyMap(String column, String sql, Object... params) throws SQLException {
234         Map<T, Map<String, Object>> result = null;
235         try {
236             result = getQueryRunner().query( sql, new KeyedHandler<T>(column), params);
237         } catch (SQLException e) {
238             throw e;
239         }
240         return result;
241     }
242     
243     
244     
245     public static int update(String sql) throws SQLException{
246         int result = 0;
247         try {
248             result = getQueryRunner().update( sql);
249         } catch (SQLException e) {
250             throw e;
251         }
252         return result;
253     }
254     
255     public static int update(String sql,Object param) throws SQLException{
256         int result = 0;
257         try {
258             result = getQueryRunner().update(sql, param);
259         } catch (SQLException e) {
260             throw e;
261         }
262         return result;
263     }
264 
265     /**
266      *  更新（包括UPDATE、INSERT、DELETE，返回受影响的行数）
267      * @param sql
268      * @param params
269      * @return
270      * @throws SQLException
271      */
272     public static int update(String sql, Object... params) throws SQLException {
273         int result = 0;
274         try {
275             result = getQueryRunner().update(sql, params);
276         } catch (SQLException e) {
277             throw e;
278         }
279         return result;
280     }
281     
282     public static void main(String[] args) {
283         QueryRunner q = getQueryRunner();
284         System.out.println(q.getDataSource());
285         List<Map<String, Object>> list = queryMapList("select * from ac_device_basic");
286         System.out.println(list.toString());
287     }
288     
289 }

db.type=PostgresSQL
db.ip=127.0.0.1
db.port=5432
db.name=cms_db
db.username=postgres
db.password=88075998