数据库连接及操作

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

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 blocke.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 blocke.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 blocke.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 blocke.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 blocke.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 blocke.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 blocke.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);}}