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 SQLException100 */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 sql125 * sql语句126 * @param params127 * 执行参数128 * @return129 * @throws SQLException130 */131 public List
> findResult(String sql, List
params)132 throws SQLException {133 List
> list = new ArrayList
>();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
map = new HashMap
();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
> result = jdbcUtil.findResult(191 "select * from BASE_ROLE", null);192 for (Map
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
excuteSql(String sql) {204 DBUtil jdbcUtil = new DBUtil();205 jdbcUtil.getConnection();206 List 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 findResultBySql(String sql) throws SQLException {219 List list = new ArrayList();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 list = DBUtil.excuteSql(sql);5 if(list!=null && list.size()>0){6 resurn = "（"+list.get(0)+"）";7 }