1 package cn.com.ecinc.ecoa.todo;
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.sql.SQLException;
10 import java.util.ArrayList;
11 import java.util.HashMap;
12 import java.util.List;
13 import java.util.Map;
14 import java.util.Properties;
15
16
17
18 public class DBUtil {
19
20
21
22 // 表示定义数据库的用户名
23 private static String USERNAME ;
24
25 // 定义数据库的密码
26 private static String PASSWORD;
27
28 // 定义数据库的驱动信息
29 private static String DRIVER;
30
31 // 定义访问数据库的地址
32 private static String URL;
33
34 // 定义数据库的链接
35 private Connection connection;
36
37 // 定义sql语句的执行对象
38 private PreparedStatement pstmt;
39
40 // 定义查询返回的结果集合
41 private ResultSet resultSet;
42
43 static{
44 //加载数据库配置信息，并给相关的属性赋值
45 loadConfig();
46 }
47
48 /**
49 * 加载数据库配置信息，并给相关的属性赋值
50 */
51 public static void loadConfig() {
52 try {
53 InputStream inStream = DBUtil.class
54 .getResourceAsStream("/application.properties");
55 Properties prop = new Properties();
56 prop.load(inStream);
57 USERNAME = prop.getProperty("jdbc.username_nw");
58 PASSWORD = prop.getProperty("jdbc.password_nw");
59 DRIVER= prop.getProperty("jdbc.driverClassName");
60 URL = prop.getProperty("jdbc.url_nw");
61 } catch (Exception e) {
62 throw new RuntimeException("读取数据库配置文件异常！", e);
63 }
64 }
65
66 /**
67 * 获取数据库连接
68 *
69 * @return 数据库连接
70 */
71 public Connection getConnection() {
72 try {
73 Class.forName(DRIVER); // 注册驱动
74 connection = DriverManager.getConnection(URL, USERNAME, PASSWORD); // 获取连接
75 } catch (Exception e) {
76 throw new RuntimeException("get connection error!", e);
77 }
78 return connection;
79 }
80
81 public Connection getConnection(String URL, String USERNAME, String PASSWORD) {
82 try {
83 Class.forName(DRIVER); // 注册驱动
84 connection = DriverManager.getConnection(URL, USERNAME, PASSWORD); // 获取连接
85 } catch (Exception e) {
86 throw new RuntimeException("get connection error!", e);
87 }
88 return connection;
89 }
90
91 /**
92 * 执行更新操作
93 *
94 * @param sql
95 * sql语句
96 * @param params
97 * 执行参数
98 * @return 执行结果
99 * @throws SQLException
100 */
101 private boolean updateByPreparedStatement(String sql, List<?> params)
102 throws SQLException {
103 boolean flag = false;
104 int result = -1;// 表示当用户执行添加删除和修改的时候所影响数据库的行数
105 pstmt = connection.prepareStatement(sql);
106 int index = 1;
107 // 填充sql语句中的占位符
108 if (params != null && !params.isEmpty()) {
109 for (int i = 0; i < params.size(); i++) {
110 pstmt.setObject(index++, params.get(i));
111 }
112 }
113 result = pstmt.executeUpdate();
114 flag = result > 0 ? true : false;
115 return flag;
116 }
117
118
119
120
121 /**
122 * 执行查询操作
123 *
124 * @param sql
125 * sql语句
126 * @param params
127 * 执行参数
128 * @return
129 * @throws SQLException
130 */
131 public List<Map<String, Object>> findResult(String sql, List<?> params)
132 throws SQLException {
133 List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
134 int index = 1;
135 pstmt = connection.prepareStatement(sql);
136 if (params != null && !params.isEmpty()) {
137 for (int i = 0; i < params.size(); i++) {
138 pstmt.setObject(index++, params.get(i));
139 }
140 }
141 resultSet = pstmt.executeQuery();
142 ResultSetMetaData metaData = resultSet.getMetaData();
143 int cols_len = metaData.getColumnCount();
144 while (resultSet.next()) {
145 Map<String, Object> map = new HashMap<String, Object>();
146 for (int i = 0; i < cols_len; i++) {
147 String cols_name = metaData.getColumnName(i + 1);
148 Object cols_value = resultSet.getObject(cols_name);
149 if (cols_value == null) {
150 cols_value = "";
151 }
152 map.put(cols_name, cols_value);
153 }
154 list.add(map);
155 }
156 return list;
157 }
158
159 /**
160 * 释放资源
161 */
162 public void releaseConn() {
163 if (resultSet != null) {
164 try {
165 resultSet.close();
166 } catch (SQLException e) {
167 e.printStackTrace();
168 }
169 }
170 if (pstmt != null) {
171 try {
172 pstmt.close();
173 } catch (SQLException e) {
174 e.printStackTrace();
175 }
176 }
177 if (connection != null) {
178 try {
179 connection.close();
180 } catch (SQLException e) {
181 e.printStackTrace();
182 }
183 }
184 }
185
186 public static void main(String[] args) {
187 DBUtil jdbcUtil = new DBUtil();
188 jdbcUtil.getConnection();
189 try {
190 List<Map<String, Object>> result = jdbcUtil.findResult(
191 "select * from BASE_ROLE", null);
192 for (Map<String, Object> m : result) {
193 System.out.println(m);
194 }
195 } catch (SQLException e) {
196 e.printStackTrace();
197 } finally {
198 jdbcUtil.releaseConn();
199 }
200 }
201
202
203 public static List<Object> excuteSql(String sql) {
204 DBUtil jdbcUtil = new DBUtil();
205 jdbcUtil.getConnection();
206 List<Object> result = null;
207 try {
208 result = jdbcUtil.findResultBySql(sql);
209 } catch (SQLException e) {
210 e.printStackTrace();
211 } finally {
212 jdbcUtil.releaseConn();
213 }
214
215 return result;
216 }
217
218 public List<Object> findResultBySql(String sql) throws SQLException {
219 List<Object> list = new ArrayList<Object>();
220 int index = 1;
221 pstmt = connection.prepareStatement(sql);
222 resultSet = pstmt.executeQuery();
223 ResultSetMetaData metaData = resultSet.getMetaData();
224 int cols_len = metaData.getColumnCount();
225 while (resultSet.next()) {
226 for (int i = 0; i < cols_len; i++) {
227 String cols_name = metaData.getColumnName(i + 1);
228 Object cols_value = resultSet.getObject(cols_name);
229 if (cols_value == null) {
230 cols_value = "";
231 }
232 list.add(cols_value);
233 }
234 }
235 return list;
236 }
237
238 }
1 String sql =" SELECT COUNT(WF_TASK_ITEM.ID) FROM WF_TASK_ITEM left join WF_INSTANCE on WF_TASK_ITEM.WF_INSTANCE_ID=WF_INSTANCE.ID " +
2 " WHERE OWNER_ACCOUNT='"+SystemUtil.getLoginObject().getPersonAccount()+"' AND WF_TASK_ITEM.STATUS='0' AND ITEM_TYPE='TODO' and (WF_INSTANCE.MODULE='shouwen' or WF_INSTANCE.MODULE='fawen' or WF_INSTANCE.MODULE='hytz' or WF_INSTANCE.MODULE='dzgg' or WF_INSTANCE.MODULE='qsbg')";
3 String resurn = "";
4 List<Object> list = DBUtil.excuteSql(sql);
5 if(list!=null && list.size()>0){
6 resurn = "（"+list.get(0)+"）";
7 }
