Mysql 数据库操作类

package cn.com.vanos.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.Map.Entry;

import javax.xml.registry.DeleteException;
public class DBManager {
	public static String dbName = "vanos";
	static Connection conn = null;
	public synchronized static Connection openConnection() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://127.0.0.1:3306/" + dbName
					+ "?useUnicode=true&characterEncoding=UTF-8";
			conn = DriverManager.getConnection(url, "root", "root");
		} catch (Exception e) {
			System.out.println("Exception:" + e.getMessage());
		}
		return conn;
	}

	public static void main(String[] args) throws SQLException {
		openConnection();
//		ResultSet rs = execSql("describe t_user");
//		rs.next();
//		System.out.println(rs.getString("field"));
//		select("t_user",0,5 );
		//选择第2页 ，每页存放的最大值 3
//		selectPage("t_user", 2 , 3 );
//		
//		getLine("t_user" ,"id = '1'");
//		getLine("t_user" ,"username = 'Wangzai'");
		
//		HashMap map = getLine("t_user" ,"id = '8'");
//		map.put("password", "12");
//		update("t_user" , map , "id = '8'");
		
		delete("t_user" , "username='RenWang'");
		
		closeConn();
	}
	
	private static boolean execute(String sql){
		try {
			Statement stat = conn.createStatement();
			System.out.print("SQL: " + sql);
			boolean ret = stat.execute(sql);
			System.out.println(" [" + stat.getUpdateCount() +  " row(s) affected]");
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}
	
	public static void delete(String table , String whereClause){
		execute("delete from " + table +" where " + whereClause);
	}
	
	//UPDATE `vanos`.`t_user` 
	//SET `Email`='wac2056@163.com',`Phonenum`='1231231231232' 
	//WHERE `Id`='8'; 
	public static void update(String table, HashMap tableValues , String whereClause){
		String sqlValues = "";
		Set <Entry<String, Object>> entrySet = tableValues.entrySet();
		for ( Iterator <Entry<String, Object>> itr = entrySet.iterator();itr.hasNext();){
			Entry entry = itr.next();
			sqlValues += entry.getKey() + " = '"+entry.getValue()+"',";
		}
		sqlValues = sqlValues.substring(0, sqlValues.length()-1);
		execute("update " + table + " set "+ sqlValues +" where " + whereClause );

	}
	//insert  into `t_user`(`Id`,`Username`,`Password`,`Email`,`Phonenum`)
	//values (1,'RenWang','123','wac056@163.com','12312312312')
	public static void add(String table,  HashMap tableValues){
		String sqlValues = "";
		String sqlFields = "";
		Set <Entry<String, Object>> entrySet = tableValues.entrySet();
		for ( Iterator <Entry<String, Object>> itr = entrySet.iterator();itr.hasNext();){
			Entry entry = itr.next();
			sqlFields += " `"+entry.getKey()+"`,";
			sqlValues += " '"+entry.getValue()+"',";
		}
		sqlFields = sqlFields.substring(0, sqlFields.length()-1);
		sqlValues = sqlValues.substring(0, sqlValues.length()-1);
		execute("insert into " + table + " ("+ sqlFields +") values ( " + sqlValues + ")" );
	}
	
	/**
	 * 
	 * @param table	 		Table Name
	 * @param curPage       Current Page Number
	 * @param maxItemSize	the size that a page  contain
	 * @param fields		what fields of table you want to get.
	 */
	public static List selectPage(String table , int curPage , int maxItemSize ,String ... fields ) throws SQLException{
		return select(table , (curPage-1) * maxItemSize ,  (curPage) *maxItemSize , fields);
	}
	
	public static List select(String table , int min , int max, String ... fields  ) throws SQLException{
		String filed2Sql = "";
		if ( fields.length == 0 ){
			filed2Sql = "*" ;
		}else{
			for (String field : fields){
				filed2Sql += " " + field + " ,";
			}
			if (filed2Sql !=null)
				filed2Sql = filed2Sql.substring(0, filed2Sql.length()-1);
		}
		
		ResultSet rs = execSql("select " + filed2Sql +" from " + table + " LIMIT " + min + " , " + max);
		List<HashMap<String , Object>> list = new ArrayList<HashMap<String , Object>>();
		while( rs.next() ) {
			HashMap<String , Object> map =  new HashMap<String, Object>();
			if ( fields.length == 0 ){
				fields = allFileds(table);
			}
			for ( String field :fields){
				map.put(field, rs.getString(field));
			}
			System.out.println(map);
			list.add(map);
		}
		return list;
	}
	
	public static void init(){
		//Get tables // fields 
		
	}
	
	public static HashMap<String, Object> getLine(String table , String whereClause ) throws SQLException{
		String[] fieldsOfTable = allFileds(table);
		HashMap<String , Object> map =  new HashMap<String, Object>();
		ResultSet rs = execSql("select * from " + table + " where " + whereClause);
		if( rs.next() ) {
			for ( String field :fieldsOfTable){
				map.put(field, rs.getString(field));
			}
			System.out.println(map);
			return map;
		}
		return null;
	}
	
	/**'
	 * Get ALl Table in this Database
	 * @throws SQLException 
	 */
	public static String[]  allTableName() throws SQLException{
		ResultSet rs = execSql("show tables");
		rs.last();
		// get Result count .
		String[] result_table = new String[rs.getRow()];
		int rowIndex = 0; 
		rs.beforeFirst();
		while( rs.next() ) {
			result_table[rowIndex++] = rs.getString(1);
		}
		return result_table;
	}
	
	/*
	 * Get All the field Names  by Table NAME.
	 */
	public static String [] allFileds( String tableName) throws SQLException {
		ResultSet rs = execSql("DESCRIBE " + tableName);
		rs.last();
		// get Result count .
		String[] result_table = new String[rs.getRow()];
		int rowIndex = 0; 
		rs.beforeFirst();
		while( rs.next() ) {
			result_table[rowIndex++] = rs.getString(1);
		}
		return result_table;
	}
	
	public static ResultSet execSql(String sql) {
		Statement ps = null;
		try {
			ps = conn.createStatement();
			System.out.println("SQL : " + sql);
			ResultSet rs = ps.executeQuery(sql);
			return rs;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
		}
		return null;
	}

	public static void closeConn() {
		try {
			conn.close();
		} catch (SQLException e) {
			System.out.println(e.getMessage());
		}
	}

}