mysql数据库的链接工具_mysql数据库连接工具类C3P0

1 packagecom.dl.network_flow.db;2

3 importjava.sql.Connection;4 importjava.sql.PreparedStatement;5 importjava.sql.ResultSet;6 importjava.sql.ResultSetMetaData;7 importjava.sql.SQLException;8 importjava.sql.Statement;9 importjava.sql.Timestamp;10 importjava.util.ArrayList;11 importjava.util.Date;12 importjava.util.HashMap;13 importjava.util.List;14 importjava.util.Map;15

16 importorg.apache.log4j.Logger;17

18 /*oracle和mysql的分页区别：19 * 1、语句的记录起始部分不一样，oralce是“记录查询结果的结束行”和“查询结果的开始行”，mysql是从“记录起始行”和“该页显示的条数”20 * 2、数据基数：oracle是从1开始，mysql是从0开始21 **/

22 public classBaseDao {23

24 protected Logger log = Logger.getLogger(this.getClass());25

26 /**

27 * 执行新增和修改的数据库操作,不用处理返回的ResultSet结果集28 *29 *@paramsql30 * sql语句31 *@paramparams32 * 参数，若为日期，需要特别处理33 *@return

34 */

35 public intexecuteSql(String sql, Object[] params) {36 Connection connection = null;37 PreparedStatement preparedStatement = null;38 ResultSet resultSet = null;39

40 try{41 connection =ConnectionFactory.getInstance().getConnection();42 preparedStatement =connection.prepareStatement(sql);43 //log.debug("executeSql sql = " + sql);44 //log.debug("params = " + params);

45 if (params != null) {46 //设置sql语句参数

47 for (int i = 0; i < params.length; i++) {48 //log.debug("params[i] = " + params[i]);

49 if (params[i] != null) {50 if (params[i] instanceofjava.util.Date) {51 preparedStatement52 .setTimestamp(i + 1, newTimestamp(53 ((Date) params[i]).getTime()));54 } else{55 preparedStatement.setObject(i + 1, params[i]);56 }57 } else{58 preparedStatement.setString(i + 1, "");59 }60 }61 }62 returnpreparedStatement.executeUpdate();63 } catch(SQLException e) {64 log.error(e.getMessage() + "code = " + e.getErrorCode()+",sql:"+sql);65 ///throw new RuntimeException(e.getMessage() + "code = " +66 //e.getErrorCode());

67 return -1;68 } finally{69 ConnectionFactory.getInstance().closeConnection(connection,70 preparedStatement, resultSet);71 }72

73 }74

75 /*

76 * 批量执行sql语句 paramsArr是个2维数组，第一维度表示各条记录，第二维度表示各条记录里的各个parameter值77 */

78 public int[] executeBatchSql(String sql, Object[][] paramsArr) {79 Connection connection = null;80 PreparedStatement preparedStatement = null;81 ResultSet resultSet = null;82 try{83 connection =ConnectionFactory.getInstance().getConnection();84 preparedStatement =connection.prepareStatement(sql);85

86 if (paramsArr != null) {87 for (int s = 0; s < paramsArr.length; s++) {88 Object[] params =paramsArr[s];89 if (params != null) {90 //设置sql语句参数

91 for (int i = 0; i < params.length; i++) {92 if (params[i] != null) {93 if (params[i] instanceofjava.util.Date) {94 preparedStatement.setTimestamp(95 i + 1,96 newTimestamp(((Date) params[i])97 .getTime()));98 } else{99 preparedStatement.setObject(i + 1,100 params[i]);101 }102 } else{103 preparedStatement.setString(i + 1, "");104 }105 }106 preparedStatement.addBatch();///批量增加1条

107 }108 }109 }110 return preparedStatement.executeBatch();///批量执行

111 } catch(SQLException e) {112 e.printStackTrace();113 //log.error(e.getMessage() + "code = " + e.getErrorCode());

114 log.error(e.getMessage() + "code = " + e.getErrorCode()+",sql:"+sql);115 } finally{116 ConnectionFactory.getInstance().closeConnection(connection,preparedStatement, resultSet);117 }118 return null;119 }120

121 /**

122 * 批量执行不同的sql语句 不包含查询123 * executeBatchSql124 * @time 2015年9月23日下午4:23:16125 * @packageName com.dl.ios6126 *@paramsql 多个sql语句的数组127 *@return

128 */

129 public int[] executeBatchSql(String[] sql){130 Connection connection = null;131 PreparedStatement preparedStatement = null;132 ResultSet resultSet = null;133

134 connection =ConnectionFactory.getInstance().getConnection();135 Statement state = null;136 try{137 if(sql!=null&&sql.length>0){138 boolean autoCommit =connection.getAutoCommit();139 connection.setAutoCommit(false);140 state =connection.createStatement();141 for (int i = 0; i < sql.length; i++) {142 state.addBatch(sql[i]);143 }144 int j[] =state.executeBatch();145 connection.commit();146 connection.setAutoCommit(autoCommit);147 state.close();148 ConnectionFactory.getInstance().closeConnection(connection, preparedStatement, resultSet);149 returnj;150 }151 } catch(SQLException e) {152 state = null;153 ConnectionFactory.getInstance().closeConnection(connection, preparedStatement, resultSet);154 }155 return null;156 }157

158

159 /*

160 * 批量执行sql语句 paramsArr是个2维数组，第一维度表示各条记录，第二维度表示各条记录里的各个parameter值161 */

162 public int[] executeBatchSql(String sql, ListparamsList) {163 Connection connection = null;164 PreparedStatement preparedStatement = null;165 ResultSet resultSet = null;166 try{167

168 connection =ConnectionFactory.getInstance().getConnection();169 preparedStatement =connection.prepareStatement(sql);170

171 if (paramsList == null){172 return null;173 }174 ///遍历所有记录

175 for (int i = 0; i < paramsList.size(); i++) {176 Object[] tObj =paramsList.get(i);177 if (tObj == null) {178 continue;179 }180 ///遍历记录中的每个字段

181 for (int j = 0; j < tObj.length; j++) {182 Object curObj =tObj[j];183 if (curObj != null) {184 if (curObj instanceofjava.util.Date) {185 preparedStatement.setTimestamp(j + 1,186 newTimestamp(((java.util.Date) curObj).getTime()));187 } else{188 preparedStatement.setObject(j + 1, curObj);189 }190 } else{191 preparedStatement.setString(j + 1, "");192 }193 }///遍历记录中的每个字段

194 preparedStatement.addBatch();///添加一条记录

195 }///遍历所有记录

196

197 return preparedStatement.executeBatch();///批量执行

198 } catch(SQLException e) {199 e.printStackTrace();200 log.error(e.getMessage() + "code = " + e.getErrorCode()+",sql:"+sql);201 } finally{202 ConnectionFactory.getInstance().closeConnection(connection,203 preparedStatement, resultSet);204 }205 return null;206 }207

208 /*

209 * 执行sql操作，把sql和params结合成一个sql语句210 * 执行sql查询的结果集交给sqlExecute这个接口函数处理，处理后封装的对象放到List里211 */

212 public List>queryForList(String sql, Object[] params) {213 Connection connection = null;214 PreparedStatement preparedStatement = null;215 ResultSet resultSet = null;216

217 try{218 connection =ConnectionFactory.getInstance().getConnection();219 preparedStatement =connection.prepareStatement(sql);220 //设置sql语句参数

221 if (params != null) {222 for (int i = 0; i < params.length; i++) {223 //log.debug("params[i] = " + params[i]);

224 preparedStatement.setObject(i + 1, params[i]);225 }226 }227 resultSet =preparedStatement.executeQuery();228 ResultSetMetaData md = resultSet.getMetaData(); //得到结果集(rs)的结构信息，比如字段数、字段名等

229 int columnCount =md.getColumnCount();230 List> list = new ArrayList>();231 Map rowData = new HashMap();232 while(resultSet.next()) {233 rowData = new HashMap(columnCount);234 for (int i = 1; i <= columnCount; i++) {235 rowData.put(md.getColumnLabel(i), resultSet.getObject(i));236 }237 list.add(rowData);238 }239

240 returnlist;241 } catch(SQLException e) {242 //log.error(e.getMessage());

243 log.error(e.getMessage() + "code = " + e.getErrorCode()+",sql:"+sql);244 } finally{245 ConnectionFactory.getInstance().closeConnection(connection,246 preparedStatement, resultSet);247 }248 return null;249 }250

251

252 public MapqueryForMap(String sql, Object[] params) {253 Connection connection = null;254 PreparedStatement preparedStatement = null;255 ResultSet resultSet = null;256

257 try{258 connection =ConnectionFactory.getInstance().getConnection();259 preparedStatement =connection.prepareStatement(sql);260 //设置sql语句参数

261 if (params != null) {262 for (int i = 0; i < params.length; i++) {263 //log.debug("params[i] = " + params[i]);

264 preparedStatement.setObject(i + 1, params[i]);265 }266 }267 resultSet =preparedStatement.executeQuery();268 ResultSetMetaData md = resultSet.getMetaData(); //得到结果集(rs)的结构信息，比如字段数、字段名等

269 int columnCount =md.getColumnCount();270 Map rowData = new HashMap();271 while(resultSet.next()) {272 rowData = new HashMap(columnCount);273 for (int i = 1; i <= columnCount; i++) {274 rowData.put(md.getColumnLabel(i), resultSet.getObject(i));275 }276 break;277 }278

279 returnrowData;280 } catch(SQLException e) {281 log.error(e.getMessage() + "code = " + e.getErrorCode()+",sql:"+sql);282 } finally{283 ConnectionFactory.getInstance().closeConnection(connection,preparedStatement, resultSet);284 }285 return null;286 }287

288 }