DBUtil 代码细节

本文转载自：http://my.oschina.net/huangyong/blog/162447

在项目中，可用该 DBUtil 类封装 Apache Commons DbUtils 类库，代码如下：

001public classDBUtil {

002 

003    privatestatic final Logger logger = Logger.getLogger(DBUtil.class);

004 

005    // 打开数据库连接（type: MySQL，Oracle，SQLServer）

006    publicstatic Connection openConnection(String type, String host, String port, String name, String username, String password) {

007        Connection conn =null;

008        try{

009            String driver;

010            String url;

011            if(type.equalsIgnoreCase("MySQL")) {

012                driver ="com.mysql.jdbc.Driver";

013                url ="jdbc:mysql://" + host + ":" + port + "/"+ name;

014            }else if (type.equalsIgnoreCase("Oracle")) {

015                driver ="oracle.jdbc.driver.OracleDriver";

016                url ="jdbc:oracle:thin:@" + host +":" + port + ":"+ name;

017            }else if (type.equalsIgnoreCase("SQLServer")) {

018                driver ="com.microsoft.jdbc.sqlserver.SQLServerDriver";

019                url ="jdbc:sqlserver://" + host +":" + port + ";databaseName="+ name;

020            }else {

021                thrownew RuntimeException();

022            }

023            Class.forName(driver);

024            conn = DriverManager.getConnection(url, username, password);

025        } catch (Exception e) {

026            logger.error(e.getMessage(), e);

027            thrownew RuntimeException(e.getMessage(), e);

028        }

029        returnconn;

030    }

031 

032    // 关闭数据库连接

033    publicstatic void closeConnection(Connection conn) {

034        try{

035            if(conn != null) {

036                conn.close();

037            }

038        } catch (Exception e) {

039            logger.error(e.getMessage(), e);

040            thrownew RuntimeException(e.getMessage(), e);

041        }

042    }

043 

044    // 查询（返回 Array）

045    publicstatic Object[] queryArray(QueryRunner runner, String sql, Object... params) {

046        Object[] result =null;

047        try{

048            result = runner.query(sql,new ArrayHandler(), params);

049        } catch (SQLException e) {

050            logger.error(e.getMessage(), e);

051            thrownew RuntimeException(e.getMessage(), e);

052        }

053        printSQL(sql);

054        returnresult;

055    }

056 

057    // 查询（返回 ArrayList）

058    publicstatic List<Object[]> queryArrayList(QueryRunner runner, String sql, Object... params) {

059        List<Object[]> result =null;

060        try{

061            result = runner.query(sql,new ArrayListHandler(), params);

062        } catch (SQLException e) {

063            logger.error(e.getMessage(), e);

064            thrownew RuntimeException(e.getMessage(), e);

065        }

066        printSQL(sql);

067        returnresult;

068    }

069 

070    // 查询（返回 Map）

071    publicstatic Map<String, Object> queryMap(QueryRunner runner, String sql, Object... params) {

072        Map<String, Object> result =null;

073        try{

074            result = runner.query(sql,new MapHandler(), params);

075        } catch (SQLException e) {

076            logger.error(e.getMessage(), e);

077            thrownew RuntimeException(e.getMessage(), e);

078        }

079        printSQL(sql);

080        returnresult;

081    }

082 

083    // 查询（返回 MapList）

084    publicstatic List<Map<String, Object>> queryMapList(QueryRunner runner, String sql, Object... params) {

085        List<Map<String, Object>> result =null;

086        try{

087            result = runner.query(sql,new MapListHandler(), params);

088        } catch (SQLException e) {

089            logger.error(e.getMessage(), e);

090            thrownew RuntimeException(e.getMessage(), e);

091        }

092        printSQL(sql);

093        returnresult;

094    }

095 

096    // 查询（返回 Bean）

097    publicstatic <T> T queryBean(QueryRunner runner, Class<T> cls, Map<String, String> map, String sql, Object... params) {

098        T result =null;

099        try{

100            if(MapUtil.isNotEmpty(map)) {

101                result = runner.query(sql,new BeanHandler<T>(cls, new BasicRowProcessor(newBeanProcessor(map))), params);

102            }else {

103                result = runner.query(sql,new BeanHandler<T>(cls), params);

104            }

105            printSQL(sql);

106        } catch (SQLException e) {

107            logger.error(e.getMessage(), e);

108            thrownew RuntimeException(e.getMessage(), e);

109        }

110        returnresult;

111    }

112 

113    // 查询（返回 BeanList）

114    publicstatic <T> List<T> queryBeanList(QueryRunner runner, Class<T> cls, Map<String, String> map, String sql, Object... params) {

115        List<T> result =null;

116        try{

117            if(MapUtil.isNotEmpty(map)) {

118                result = runner.query(sql,new BeanListHandler<T>(cls,new BasicRowProcessor(newBeanProcessor(map))), params);

119            }else {

120                result = runner.query(sql,new BeanListHandler<T>(cls), params);

121            }

122            printSQL(sql);

123        } catch (SQLException e) {

124            logger.error(e.getMessage(), e);

125            thrownew RuntimeException(e.getMessage(), e);

126        }

127        returnresult;

128    }

129 

130    // 查询指定列名的值（单条数据）

131    publicstatic Object queryColumn(QueryRunner runner, String column, String sql, Object... params) {

132        Object result =null;

133        try{

134            result = runner.query(sql,new ScalarHandler<Object>(column), params);

135        } catch (SQLException e) {

136            logger.error(e.getMessage(), e);

137            thrownew RuntimeException(e.getMessage(), e);

138        }

139        printSQL(sql);

140        returnresult;

141    }

142 

143    // 查询指定列名的值（多条数据）

144    publicstatic <T> List<T> queryColumnList(QueryRunner runner, String column, String sql, Object... params) {

145        List<T> result =null;

146        try{

147            result = runner.query(sql,new ColumnListHandler<T>(column), params);

148        } catch (SQLException e) {

149            logger.error(e.getMessage(), e);

150            thrownew RuntimeException(e.getMessage(), e);

151        }

152        printSQL(sql);

153        returnresult;

154    }

155 

156    // 查询指定列名对应的记录映射

157    publicstatic <T> Map<T, Map<String, Object>> queryKeyMap(QueryRunner runner, String column, String sql, Object... params) {

158        Map<T, Map<String, Object>> result =null;

159        try{

160            result = runner.query(sql,new KeyedHandler<T>(column), params);

161        } catch (SQLException e) {

162            logger.error(e.getMessage(), e);

163            thrownew RuntimeException(e.getMessage(), e);

164        }

165        printSQL(sql);

166        returnresult;

167    }

168 

169    // 更新（包括 UPDATE、INSERT、DELETE，返回受影响的行数）

170    publicstatic int update(QueryRunner runner, Connection conn, String sql, Object... params) {

171        intresult = 0;

172        try{

173            if(conn != null) {

174                result = runner.update(conn, sql, params);

175            }else {

176                result = runner.update(sql, params);

177            }

178            printSQL(sql);

179        } catch (SQLException e) {

180            logger.error(e.getMessage(), e);

181            thrownew RuntimeException(e.getMessage(), e);

182        }

183        returnresult;

184    }

185 

186    privatestatic void printSQL(String sql) {

187        if(logger.isDebugEnabled()) {

188            logger.debug("SQL: "+ sql);

189        }

190    }

191}