dbutils

package g.Factory;import java.sql.Connection;import java.sql.SQLException;import java.util.List;import java.util.Map;import java.util.Map.Entry;import javax.servlet.http.HttpServletRequest;import org.apache.commons.dbutils.QueryRunner;import org.apache.commons.dbutils.handlers.MapHandler;import org.apache.commons.dbutils.handlers.MapListHandler;import com.alibaba.druid.pool.DruidDataSource;import com.alibaba.fastjson.JSON;import com.alibaba.fastjson.JSONArray;import com.alibaba.fastjson.JSONObject;import com.alibaba.fastjson.serializer.SerializerFeature;public class DbUtils{public static void main(String[] args) throws Exception{// TODO Auto-generated method stubString sql = "select * from usrs";System.out.println(exeJSONObject(sql, null));// TestSql();}private static DruidDataSource dataSource = null;static{try{dataSource = new DruidDataSource();dataSource.setDriverClassName("com.mysql.jdbc.Driver");dataSource.setUrl("jdbc:mysql://localhost:3306/wxbase?autoReconnect=true&useUnicode=true&characterEncoding=utf-8");dataSource.setUsername("root");dataSource.setPassword("qq1111");dataSource.setInitialSize(3);dataSource.setMinIdle(1);dataSource.setMaxActive(10);// dataSource.setFilters("stat");dataSource.setPoolPreparedStatements(false);} catch (Exception e){e.printStackTrace();}}private static synchronized Connection getConnection(){Connection conn = null;try{conn = dataSource.getConnection();} catch (Exception e){e.printStackTrace();}return conn;}public static JSONObject getParamJsonObject(HttpServletRequest request){if (request.getParameter("ir") == null || !request.getParameter("ir").equalsIgnoreCase("1"))return null;Map<String, String[]> m = request.getParameterMap();JSONObject json = new JSONObject(true);for (String key : m.keySet()){String s = "";for (String v : m.get(key)){if (s == "")s += v;elses += "," + v;}json.put(key, s);}return json;}public static int exeInsert(JSONObject json, String tableName) throws Exception{String sql = "insert into " + tableName + " (";for (Entry<String, Object> entry : json.entrySet())sql += entry.getKey() + ",";sql += ") values ('";for (Entry<String, Object> entry : json.entrySet())sql += entry.getValue() + "','";sql += ");";sql = sql.replace(",')", ")").replace(",)", ")");int i = exeCommand(sql, null);return i;}public static int exeUpdate(JSONObject json, String tableName, String where) throws SQLException{String sql = "update " + tableName + " set ";for (Entry<String, Object> entry : json.entrySet())sql += entry.getKey() + "='" + entry.getValue() + "',";sql += "where (" + where + ");";sql = sql.replaceAll(",where", " where");int i = exeCommand(sql, null);return i;}public static int exeDelete(String sql) throws SQLException{int i = exeCommand(sql, null);return i;}public static int exeCommand(String sql, Object[] parm) throws SQLException{Connection con = getConnection();QueryRunner runner = new QueryRunner();int i = runner.update(con, sql, parm);if (!con.isClosed())con.close();return i;}public static List<Map<String, Object>> exeFinds(String sql, Object[] parm) throws SQLException{Connection con = getConnection();QueryRunner runner = new QueryRunner();List<Map<String, Object>> listmap = runner.query(con, sql, new MapListHandler(), parm);if (!con.isClosed())con.close();return listmap;}public static JSONArray exeJSONArray(String sql, Object[] parm) throws SQLException{Connection con = getConnection();QueryRunner runner = new QueryRunner();List<Map<String, Object>> listmap = runner.query(con, sql, new MapListHandler(), parm);if (!con.isClosed())con.close();return JSONArray.parseArray(JSON.toJSONString(listmap, SerializerFeature.WriteDateUseDateFormat));}public static Map<String, Object> exeFind(String sql, Object[] parm) throws SQLException{Connection con = getConnection();QueryRunner runner = new QueryRunner();Map<String, Object> map = runner.query(con, sql, new MapHandler(), parm);if (!con.isClosed())con.close();return map;}public static JSONObject exeJSONObject(String sql, Object[] parm) throws SQLException{Connection con = getConnection();QueryRunner runner = new QueryRunner();Map<String, Object> map = runner.query(con, sql, new MapHandler(), parm);if (!con.isClosed())con.close();return JSONObject.parseObject(JSON.toJSONString(map, SerializerFeature.WriteDateUseDateFormat));}}