Jdbc封装的增删查改

package com.huiyun.common.dao;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Date;
import java.util.Enumeration;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.servlet.ServletRequest;

import org.junit.Test;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.ibm.icu.text.SimpleDateFormat;
import com.tis.helper.SqlM;

public class CommonDao {
	static SimpleDateFormat ymdhms = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	public static JSONObject getById(String tableName, String pkName,Serializable id) {
		String sql="select * from ["+tableName+"] where "+pkName+"=?";
		List<Object> param=new ArrayList<Object>();
		JSONArray ja= excuteQuery(sql,param);
		if(ja.size()>0)		
			return ja.getJSONObject(0);
		else
			return new JSONObject();
	}
	public static JSONArray excuteQuery(String sql, List<Object> param) {
		SqlM sqlM = new SqlM();
		Connection con = sqlM.conn;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = con.prepareStatement(sql);
			for (int i = 0; param != null && i < param.size(); i++) {
				Object value = param.get(i);
				if (value == null) {
					pstmt.setNull(i + 1, Types.OTHER);
				} else if(value instanceof Date) {
					String datestr = ymdhms.format((java.util.Date) value);
					pstmt.setString(i + 1, datestr);
				} else  {
					pstmt.setObject(i + 1, value);
				}
			}
			rs = pstmt.executeQuery();
			return rs2JSONObject(rs);
		} catch (SQLException e) {
			e.printStackTrace();
			return new JSONArray();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (con != null) {

				sqlM.closeConnection();

			}
		}

	}
	
	public static boolean exists(String sql, List<Object> param) {
		SqlM sqlM = new SqlM();
		Connection con = sqlM.conn;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = con.prepareStatement(sql);
			for (int i = 0; param != null && i < param.size(); i++) {
				Object value = param.get(i);
				if (value == null) {
					pstmt.setNull(i + 1, Types.OTHER);
				} else if(value instanceof Date) {
					String datestr = ymdhms.format((java.util.Date) value);
					pstmt.setString(i + 1, datestr);
				}else {
					pstmt.setObject(i + 1, value);
				}
			}
			rs = pstmt.executeQuery();
			return rs.next();
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (con != null) {

				sqlM.closeConnection();

			}
		}

	}

	public static JSONArray rs2JSONObject(ResultSet rs) {
		try {
			ResultSetMetaData rsmd = rs.getMetaData();
			int cols = rsmd.getColumnCount();
			List<String> names = new ArrayList<String>();
			for (int i = 0; i < cols; i++) {
				names.add(rsmd.getColumnLabel(i + 1));
			}
			JSONArray ja = new JSONArray();
			while (rs.next()) {
				JSONObject jo = new JSONObject();
				for (String name : names) {
					jo.put(name, rs.getObject(name));
				}
				ja.add(jo);
			}
			return ja;
		} catch (SQLException e) {

			e.printStackTrace();
			return new JSONArray();
		}
	}

	public static int update(String tableName, JSONObject jo, String pkName) {
		SqlM sqlM = new SqlM();
		Connection con = sqlM.conn;
		PreparedStatement pstmt = null;
		StringBuffer sql = new StringBuffer("update ").append(tableName)
				.append(" set ");
		Set<String> keys = jo.keySet();
		boolean isFirst = true;
		List<Object> param = new ArrayList<Object>();
		for (String key : keys) {
			if (jo.get(key) != null && !key.equalsIgnoreCase(pkName)) {
				if (isFirst) {
					sql.append(" [").append(key).append("]=?");
					isFirst = false;
				} else {
					sql.append(" ,[").append(key).append("]=? ");

				}
				param.add(jo.get(key));
			}
		}
		sql.append(" where [").append(pkName).append("]=?");
		param.add(jo.get(pkName));
		System.out.println(sql);
		System.out.println(JSON.toJSONStringWithDateFormat(param,
				"yyyy-MM-dd HH:mm:ss"));
		try {
			pstmt = con.prepareStatement(sql.toString());
			int index = 1;
			for (Object o : param) {
				if (o instanceof java.util.Date) {
					// long time=((java.util.Date)o).getTime();
					// javax.sql.Date d=new javax.sql.Date(time);
					// pstmt.setDate(index, d);
					String datestr = ymdhms.format((java.util.Date) o);
					pstmt.setString(index, datestr);
					index++;
				} else {
					pstmt.setObject(index, o);
					index++;
				}

			}
			return pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println(sql);
			System.out.println(jo.toJSONString());
		} finally {
			
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (con != null) {

				sqlM.closeConnection();

			}
		}
		return -1;

	}
	public static boolean isExist(String name,String...pkNames){
		for(String pkName:pkNames){
			if(name.equalsIgnoreCase(pkName))
				return true;
		}
		return false;
	}
	public static int update(String tableName, JSONObject jo, String... pkNames) {
		SqlM sqlM = new SqlM();
		Connection con = sqlM.conn;
		PreparedStatement pstmt = null;
		StringBuffer sql = new StringBuffer("update ").append(tableName)
				.append(" set ");
		Set<String> keys = jo.keySet();
		boolean isFirst = true;
		List<Object> param = new ArrayList<Object>();
		
		
		for (String key : keys) {
			if (jo.get(key) != null && !isExist(key,pkNames)) {
				if (isFirst) {
					sql.append(" [").append(key).append("]=?");
					isFirst = false;
				} else {
					sql.append(" ,[").append(key).append("]=? ");

				}
				param.add(jo.get(key));
			}
		}
		sql.append(" where ");
		isFirst = true;
		for(String pkName:pkNames){
			if(isFirst){
				sql.append("  [").append(pkName).append("]=?");
				param.add(jo.get(pkName));
				isFirst=false;
			}else{
				sql.append(" and [").append(pkName).append("]=?");
				param.add(jo.get(pkName));
			}
		}
		System.out.println(sql);
		System.out.println(JSON.toJSONStringWithDateFormat(param,
				"yyyy-MM-dd HH:mm:ss"));
		try {
			pstmt = con.prepareStatement(sql.toString());
			int index = 1;
			for (Object o : param) {
				if (o instanceof java.util.Date) {
					// long time=((java.util.Date)o).getTime();
					// javax.sql.Date d=new javax.sql.Date(time);
					// pstmt.setDate(index, d);
					String datestr = ymdhms.format((java.util.Date) o);
					pstmt.setString(index, datestr);
					index++;
				} else {
					pstmt.setObject(index, o);
					index++;
				}

			}
			return pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println(sql);
			System.out.println(jo.toJSONString());
		} finally {
			
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (con != null) {

				sqlM.closeConnection();

			}
		}
		return -1;

	}

	public static int insert(String tableName, JSONObject jo) {
		SqlM sqlM = new SqlM();
		Connection con = sqlM.conn;
		PreparedStatement pstmt = null;
		StringBuffer sql = new StringBuffer("insert into  [").append(
				tableName.trim()).append("] (");
		Set<String> keys = jo.keySet();
		boolean isFirst = true;
		List<Object> param = new ArrayList<Object>();
		StringBuffer vsql = new StringBuffer(" values(");
		for (String key : keys) {
			if (jo.get(key) != null) {
				if (isFirst) {
					sql.append("[").append(key).append("]");
					vsql.append("?");
					isFirst = false;
				} else {
					sql.append(" ,[").append(key).append("]");
					vsql.append(" ,?");

				}
				param.add(jo.get(key));
			}
		}
		sql.append(" ) ");
		vsql.append(" ) ");
		System.out.println(sql.append(vsql));
		try {
			pstmt = con.prepareStatement(sql.toString());
			int index = 1;
			for (String key : keys) {

				Object o = jo.get(key);
				if (o != null) {

					if (o instanceof java.util.Date) {
						// long time=((java.util.Date)o).getTime();
						// javax.sql.Date d=new javax.sql.Date(time);
						// pstmt.setDate(index, d);
						String datestr = ymdhms.format((java.util.Date) o);
						pstmt.setString(index, datestr);
						index++;
					} else {
						pstmt.setObject(index, o);
						index++;
					}
				}
			}
			return pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println(sql);
			System.out.println(jo.toJSONString());
		}finally {
			
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (con != null) {

				sqlM.closeConnection();

			}
		}
		return -1;

	}

	public static int update(String tableName, ServletRequest request,
			String pkName) {
		SqlM sqlM = new SqlM();
		Connection con = sqlM.conn;
		PreparedStatement pstmt = null;
		StringBuffer sql = new StringBuffer("update ").append(tableName)
				.append(" set ");
		Enumeration<String> ekeys = request.getParameterNames();
		Set<String> keys = new HashSet<String>();
		while (ekeys.hasMoreElements()) {
			keys.add(ekeys.nextElement());
		}
		boolean isFirst = true;
		List<Object> param = new ArrayList<Object>();
		for (String key : keys) {
			if (request.getParameter(key) != null
					&& !key.equalsIgnoreCase(pkName)) {
				if (isFirst) {
					sql.append(" [").append(key).append("]=?");
					isFirst = false;
				} else {
					sql.append(" ,[").append(key).append("]=? ");
				}
				param.add(request.getParameter(key));
			}
		}
		sql.append(" where [").append(pkName).append("]=?");
		param.add(request.getParameter(pkName));
		System.out.println(sql);
		System.out.println(JSON.toJSONStringWithDateFormat(param,
				"yyyy-MM-dd HH:mm:ss"));
		try {
			pstmt = con.prepareStatement(sql.toString());
			int index = 1;
			for (Object o : param) {
				pstmt.setObject(index, o);
				index++;
			}
			return pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println(sql);

		}finally {
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (con != null) {
				sqlM.closeConnection();
			}
		}
		return -1;

	}

	public static int insert(String tableName, ServletRequest request) {
		SqlM sqlM = new SqlM();
		Connection con = sqlM.conn;
		PreparedStatement pstmt = null;
		StringBuffer sql = new StringBuffer("insert into  [").append(
				tableName.trim()).append("] (");
		Enumeration<String> ekeys = request.getParameterNames();
		Set<String> keys = new HashSet<String>();
		while (ekeys.hasMoreElements()) {
			keys.add(ekeys.nextElement());
		}
		boolean isFirst = true;
		List<Object> param = new ArrayList<Object>();
		StringBuffer vsql = new StringBuffer(" values(");
		for (String key : keys) {
			if (request.getParameter(key) != null) {
				if (isFirst) {
					sql.append("[").append(key).append("]");
					vsql.append("?");
					isFirst = false;
				} else {
					sql.append(" ,[").append(key).append("]");
					vsql.append(" ,?");

				}
				param.add(request.getParameter(key));
			}
		}
		sql.append(" ) ");
		vsql.append(" ) ");
		System.out.println(sql.append(vsql));
		try {
			pstmt = con.prepareStatement(sql.toString());
			int index = 1;
			for (String key : keys) {
				if (request.getParameter(key) != null) {
					pstmt.setObject(index, request.getParameter(key));
					index++;
				}
			}
			return pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println(sql);

		}finally {
			
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (con != null) {

				sqlM.closeConnection();

			}
		}
		return -1;

	}

	@Test
	public void test() {
		List<Object> list=new ArrayList<Object>();
		list.add(1);
		JSONArray ja = excuteQuery("select *  from dw_sale_bill where memberId=?", list);
		System.out.println(JSON.toJSONStringWithDateFormat(ja,
				"yyyy-MM-dd HH:mm:ss"));
	}

	@Test
	public void qgc_test() {
		JSONObject jo = new JSONObject();
		jo.put("name", "name");
		jo.put("desc", "desc");
		jo.put("bfloatb", 4.0f);
		jo.put("bintb", 4);
		jo.put("cdate", new Date());
	
		insert("qgc_test", jo);
		jo.put("name", "name2");
		jo.put("desc", "desc2");
		jo.put("bfloatb", 5.0f);
		jo.put("bintb", 5);
		jo.put("id", 2);
		//jo.put("ts", new Date());
		update("qgc_test", jo, "id");
	}
	
	public static JSONObject request2JSONObject(ServletRequest request,String... names){
		JSONObject jo=new JSONObject();
		for(String name:names){
			String value=request.getParameter(name);
			jo.put(name, value);
		}
		return jo;
		
	}
	
	public static List<JSONObject> request2JSONObjects(ServletRequest request,String... names){
		List<JSONObject> list=new ArrayList<JSONObject>();
		
		Map<String, String[]> map = request.getParameterMap();
		int max=0;
		for(String name:names){
			String[] values=map.get(name);
			if(values!=null&&max<values.length){
				max=values.length;
			}
		}
		for(int i=0;i<max;i++){
			JSONObject jo=new JSONObject();
			for(String name:names){
				String[] values=map.get(name);
				if(i<values.length){
				jo.put(name, values[i]);
				}
			}
		}
		return list;
		
	}
	static int max(int... v){
		int max=0;
		for(int i=0;i<v.length;i++){
			if(v[i]>max)
				max=v[i];
		}
		return max;
	}

}