DBUtil.java
1 package org.guangsoft.util;
2
3 import java.io.InputStream;
4 import java.sql.Connection;
5 import java.sql.DriverManager;
6 import java.sql.PreparedStatement;
7 import java.sql.ResultSet;
8 import java.sql.ResultSetMetaData;
9 import java.util.ArrayList;
10 import java.util.List;
11 import java.util.Properties;
12
13 import org.apache.commons.beanutils.BeanUtils;
14
15
16 /**
17 *
18 * @author guanghe
19 */
20 public class DBUtil
21 {
22 //定义连接资源
23 private static Connection ct = null;
24 private static PreparedStatement ps = null;
25 private static ResultSet rs = null;
26
27 //定义配置参数
28 private static String driver = null;
29 private static String url = null;
30 private static String username = null;
31 private static String password = null;
32
33 //定义配置文件引入
34 private static Properties pp = null;
35 private static InputStream is = null;
36
37 //读取配置参数，加载驱动
38 static
39 {
40 try
41 {
42 pp = new Properties();
43 is = DBUtil.class.getClassLoader().getResourceAsStream("org/guangsoft/util/db.properties");
44 pp.load(is);
45 driver = pp.getProperty("driver");
46 url = pp.getProperty("url");
47 username = pp.getProperty("username");
48 password = pp.getProperty("password");
49 Class.forName(driver);
50 }
51 catch (Exception e)
52 {
53 e.printStackTrace();
54 System.exit(0);
55 }
56 finally
57 {
58 try
59 {
60 is.close();
61 }
62 catch (Exception e)
63 {
64 e.printStackTrace();
65 }
66 is = null;
67 }
68 }
69
70 //获取连接
71 public static Connection getConnection()
72 {
73 try
74 {
75 ct = DriverManager.getConnection(url, username, password);
76 }
77 catch (Exception e)
78 {
79 e.printStackTrace();
80 }
81 return ct;
82 }
83
84 //执行DQL查询
85 public static<T> List<T> executeQuery(String sql, Object[] parameters,Class<T> clazz)
86 {
87 List<T> list = new ArrayList<T>();
88 try
89 {
90 //得到数据结果集
91 ct = getConnection();
92 ps = ct.prepareStatement(sql);
93 if (parameters != null)
94 {
95 for (int i = 0; i < parameters.length; i++)
96 {
97 ps.setObject(i + 1, parameters[i]);
98 }
99 }
100 rs = ps.executeQuery();
101
102 //封装数据
103 ResultSetMetaData metaData = rs.getMetaData();
104 int columnCount = metaData.getColumnCount();
105 while(rs.next())
106 {
107 T t = clazz.newInstance();
108 for(int i = 0; i < columnCount; i++)
109 {
110 String columnName = metaData.getColumnName(i+1);
111 Object value = rs.getObject(columnName);
112 BeanUtils.copyProperty(t, columnName, value);
113 }
114 list.add(t);
115 }
116 }
117 catch(Exception e)
118 {
119 e.printStackTrace();
120 }
121 finally
122 {
123 close();
124 }
125 return list;
126 }
127
128 //执行DML更新
129 public static int executeUpdate(String sql, Object[] parameters)
130 {
131 try
132 {
133 ct = getConnection();
134 ps = ct.prepareStatement(sql);
135 if (parameters != null)
136 {
137 for (int i = 0; i < parameters.length; i++)
138 {
139 ps.setObject(i + 1, parameters[i]);
140 }
141 }
142 return ps.executeUpdate();
143 }
144 catch (Exception e)
145 {
146 e.printStackTrace();
147 }
148 finally
149 {
150 close();
151 }
152 return 0;
153 }
154
155 //关闭所有资源连接
156 public static void close()
157 {
158 if (rs != null)
159 {
160 try
161 {
162 rs.close();
163 }
164 catch (Exception e)
165 {
166 e.printStackTrace();
167 }
168 rs = null;
169 }
170 if (ps != null)
171 {
172 try
173 {
174 ps.close();
175 }
176 catch (Exception e)
177 {
178 e.printStackTrace();
179 }
180 ps = null;
181 }
182 if (null != ct)
183 {
184 try
185 {
186 ct.close();
187 }
188 catch (Exception e)
189 {
190 e.printStackTrace();
191 }
192 ct = null;
193 }
194 }
195
196 }
db.properties
1 driver = com.mysql.jdbc.Driver
2 url = jdbc:mysql://localhost:3306/test
3 username = root
4 password =root