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 stub
		String 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;
				else
					s += "," + 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));
	}
}