数据库连接及操作

     设置静态连接参数，然后设置路径，加载驱动，返回一个连接对象。

package com.weixin.javabean;

import java.sql.Connection;  
import java.sql.DriverManager;  
import java.sql.PreparedStatement;  
import java.sql.SQLException;
import java.sql.Statement;

  
public class DBConf {  
	private static final String url = "jdbc:mysql://33333/HAHAH?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true";  
    private static final String name = "com.mysql.jdbc.Driver";  
    private static final String user = "root";  
    private static final String password = "123456";  
    private static Connection conn=null;
    
    public static Connection getConn() {
        try {  
            Class.forName(name); 
            return DriverManager.getConnection(url, user, password);  //调用方法返回一个连接对象
        } catch (Exception e) {  
            e.printStackTrace();  
        }
		return null;  
    }  
  
}  

数据库操作：主要注意操作对象为harshtable列表，利用迭代进行sql的拼接，i="1'，为标志性，使循环执行一次，数据url拼接好后，进行数据库操作，注意try ,catch抓错。注意：只进行了一次数据库连接，只产生了一个数据库连接对象，所有的数据库操作都只利用这一个，

package com.weixin.util;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Hashtable;
import java.util.Iterator;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.stringtree.json.JSONValidatingWriter;

import com.google.gson.Gson;
import com.google.gson.reflect.TypeToken;
import com.weixin.javabean.DBConf;
import com.weixin.javabean.UserInfo;

public class DBUtil {
	private static Connection conn = DBConf.getConn();
	private static Statement stat = null;
	private static ResultSet rs = null;
    private static String sql=null;
    private static Hashtable <String,String>list=new Hashtable<String,String>();
    private static Gson gson=new Gson();
    /*public static Connection getCon()//需要多个连接对象时
    {   
    	cout++;
    	if(count%3==0)
			return conn;
    	else if(count%3==1)
			return conn1;
    	else if(count%3==2)
			return conn2;
    }*/
	/**
	 * 功能：数据库插入操作
	 * @param ht 存储字段信息的hashtable 存储格式(Hashtable)ht.put(字段名，字段值);
	 * @param tablename 操作的数据库表名
	 * @return 返回json格式的执行信息
	 */
	public static  String insert(Hashtable ht, String tablename) {
		String before_sql, after_sql;
		before_sql = "insert into " + tablename + "(";
		after_sql = " values('";
		int i = 1;
		for (Iterator iter = ht.keySet().iterator(); iter.hasNext(); i = 0) {
			String key = (String) iter.next();
			Object value =  ht.get(key);
			if (i != 1) {
				before_sql += ",";
				after_sql += "','";
			}
			before_sql += key;
			after_sql += value;
		}
		sql = before_sql + ")" + after_sql + "')";
		
		logout.ps.println("insert sql:" + sql);
		
		try {
			
			
			stat = conn.createStatement();
			list.put("errorcode", stat.executeUpdate(sql)+"");
			list.put("errmsg", "ok");
			return gson.toJson(list);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			
			e.printStackTrace(logout.ps);
			
			list.put("errorcode", "-1");
			list.put("errmsg", e.toString());
			
			e.printStackTrace();
			return gson.toJson(list);
			
		}
		
	}

	/**
	 * 
	 * 功能：查询数据库 （注意：返回记录哪怕只有一条也是用json数组存放的，即用[]括起来的）
	 * @param ht Hashtable对象，其中存放sql查询条件
	 * @param tablename  带查询表名
	 * @return 返回json格式的字符串
	 */
	public static String select(Hashtable ht, String tablename) {
		String sql;
		sql = "select * from " + tablename;
		int i = 1;
		for (Iterator iter = ht.keySet().iterator(); iter.hasNext(); i = 0) {
			String key = (String) iter.next();
			Object value = ht.get(key);
			if (i == 1) {
				sql += " where ";
			} else {
				sql += " and ";
			}
			sql += key + "='" + value+"'";
		}
		
		logout.ps.println("select sql:" + sql);
		
		try {
			String json = new JSONValidatingWriter().write(new QueryRunner().query(conn, sql, new MapListHandler()));
			return json;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			
			e.printStackTrace(logout.ps);
			
			e.printStackTrace();
		}
		return null;
	}
	
	/**
	 * 功能：数据库更新操作
	 * @param ht 待更新的数据信息，包括查询条件
	 * @param tablename 操作的数据库表
	 * @param id 查询条件的字段名
	 * @return 返回json格式的执行情况
	 */
	public static String update(Hashtable ht, String tablename,String id){
		String before_sql, after_sql, sql;
		before_sql = "update " + tablename + " set ";
		after_sql = " where ";
		int i = 1;
		for (Iterator iter = ht.keySet().iterator(); iter.hasNext(); ) {
			String key = (String) iter.next();
			Object value =  ht.get(key);
			
			if(key.equals(id))
			{
				after_sql+=key+ "='" + value+"'";
			}else
			{
				if (i != 1) {
					before_sql += " , ";
				}
			before_sql += key+ "='" + value+"'";
			i=0;
			}
		}
		sql = before_sql +  after_sql;
		
		logout.ps.println("update sql:" + sql);
		

		try {
			stat = conn.createStatement();
			list.put("errorcode", stat.executeUpdate(sql)+"");
			list.put("errmsg", "ok");
			return gson.toJson(list);
		} catch (SQLException e) {
			
			e.printStackTrace(logout.ps);
			
			// TODO Auto-generated catch block
			e.printStackTrace();
			list.put("errorcode", "-1");
			list.put("errmsg", e.toString());
			return gson.toJson(list);
		}
		
	}
	
	

	/**
	 * @param ht 删除条件
	 * @param tablename 数据库表名
	 * @return 返回操作情况  0失败
	 */
	public static int delete(Hashtable ht, String tablename){
		String before_sql, after_sql, sql;
		sql = "delete from " + tablename + " where ";
		int i = 1;
		for (Iterator iter = ht.keySet().iterator(); iter.hasNext();i=0 ) {
			String key = (String) iter.next();
			Object value =  ht.get(key);
			
				if (i != 1) {
					sql += " and ";
				}
			sql += key+ "='" + value+"'";
		}
		
		logout.ps.println("delete sql:" + sql);
		

		try {
			stat = conn.createStatement();
			
			return stat.executeUpdate(sql);
		} catch (SQLException e) {
			
			e.printStackTrace(logout.ps);
			
			// TODO Auto-generated catch block
			e.printStackTrace();
			return -1;
		}
	}
	
	
	/**
	 * 获取表中最大的主键值
	 * @param tablename 数据库表名
	 * @param id 主键字段名
	 * @return 最大的主键值
	 */
	public static String getNextID(String tablename,String id) {
		sql="select MAX("+id+") as "+id+" from "+tablename;
		
		logout.ps.println("sql:"+sql);
		try {
			String json = new JSONValidatingWriter().write(new QueryRunner().query(conn, sql, new MapListHandler()));
			
			logout.ps.println("json:"+json);
			json=json.replace("[", "");
			json=json.replace("]", "");
			Hashtable<String,Double> ht=gson.fromJson(json, Hashtable.class);
			double d=ht.get(id);
			
			logout.ps.println("ht:"+(int)d+"");
			
			return  (int)d+"";
		} catch (SQLException e) {
			
			e.printStackTrace(logout.ps);
			
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	
	
	/**
	 * @param sql 查询sql语句
	 * @return json格式的结果集
	 */
	public static String select(String sql) {
		
		logout.ps.println("DBUtil seleclt(sql):"+sql);
		
		try {
			String json = new JSONValidatingWriter().write(new QueryRunner().query(conn, sql, new MapListHandler()));
			return json;
		} catch (SQLException e) {
			
			e.printStackTrace(logout.ps);
			
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	
	/**
	 * @param sql 更新，删除sql语句
	 * @return 返回操作情况
	 */
	public static String update(String sql) {
		
		logout.ps.println("user defined update sql:"+sql);
		
		try {
				stat = conn.createStatement();
				list.put("errorcode", stat.executeUpdate(sql)+"");
				list.put("errmsg", "ok");
				return gson.toJson(list);
		} catch (SQLException e) {
			
			e.printStackTrace(logout.ps);
			
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	
	public static void main(String[] args) {
		Hashtable ht = new Hashtable();
		String str=select(ht,"BillInfo");
		str="\"rows\":"+str;
		System.out.println(str);

	}
}