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
