J2EE工具类：DataBaseUtil.java

package com.worthtech.app.sql;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;

public class DataBaseUtil {
	private String driver;

	private String url;

	private String user;

	private String password;

	private Connection conn;

	public DataBaseUtil(String fileName) {
		loadProperties(fileName);
		setConnection();
	}

	// handle the properties file to get the informations for connection
	private void loadProperties(String fileName) {
		Properties props = new Properties();
		try {
			props.load(new FileInputStream(fileName));
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		this.driver = props.getProperty("driver");
		this.url = props.getProperty("url");
		this.user = props.getProperty("user");
		this.password = props.getProperty("password");
	}

	private void setConnection() {
		try {
			Class.forName(driver);
			this.conn = DriverManager.getConnection(url, user, password);
		} catch (ClassNotFoundException classnotfoundexception) {
			System.err.println("db: " + classnotfoundexception.getMessage());
		} catch (SQLException sqlexception) {
			System.err.println("db.getconn(): " + sqlexception.getMessage());
		}
	}

	public Connection getConnection() {
		try {
			if (conn != null && !conn.isClosed()) {
				return this.conn;
			} else {
				setConnection();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return this.conn;
	}

	public void closeConnection() {
		try {
			if (st != null) {
				st.close();
			}
			if (conn != null && !conn.isClosed()) {
				conn.close();
				conn = null;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 增加，修改，删除数据
	 * 
	 * @param sql
	 * @param sqlValue
	 * @return
	 * @throws Exception
	 *             用法： sql="UPDATE user SET password = ? WHERE phone= ?"; String
	 *             sql="INSERT INTO payee(name,card,openBank,addDate,addPhone)
	 *             VALUES(?,?,?,?,?)"; delete ... db.executeUpdate(sql, new
	 *             String[]{name,card,openBank,addDate,addPhone});
	 */
	public int doExecute(String sql, String sqlValue[]) {
		int count = 0;
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			if (sqlValue != null) {
				for (int i = 0; i < sqlValue.length; i++)
					ps.setString(i + 1, sqlValue[i]);
			}
			count = ps.executeUpdate();
		} catch (Exception e) {
		}
		return count;
	}

	/**
	 * 提供更通用点的方法
	 * 
	 * @param sql
	 * @return
	 */
	public int doExecute(String sql) {
		return doExecute(sql, null);
	}

	/**
	 * 获得查询数据，返回一个ArrayList 得到的Arraylist 可以用 Map解析 用法： Arraylist list =
	 * db.getList("select * from user where ***") for(int i=1;i<list.size;i++)
	 * Map map = (Map)list.get(i); map.get("id"); 就是得到了里面的id了
	 * 
	 */
	public ArrayList getList(String sql) {
		Statement st = null;
		ArrayList list = new ArrayList();
		try {
			// PreparedStatement ps = conn.prepareStatement(sql);
			// ResultSet rs=ps.executeQuery();
			st = conn.createStatement();
			ResultSet rs = st.executeQuery(sql);
			ResultSetMetaData meta = rs.getMetaData();
			int count = meta.getColumnCount();
			String cols[] = new String[count];
			for (int i = 0; i < cols.length; i++) {
				if (meta.getColumnName(i + 1) != null)
					cols[i] = meta.getColumnName(i + 1);
				else
					cols[i] = meta.getColumnLabel(i + 1);
			}
			HashMap map = null;
			String fieldValue = null;
			for (; rs.next(); list.add(map)) {
				map = new HashMap();
				for (int i = 0; i < cols.length; i++) {
					int iType = meta.getColumnType(i + 1);
					if (iType == 2 || iType == 3) {
						if (meta.getScale(i + 1) == 0)
							fieldValue = String.valueOf(rs.getLong(i + 1));
						else
							fieldValue = rs.getString(i + 1);
					} else if (iType == 8)
						fieldValue = String.valueOf(rs.getDouble(i + 1));
					else if (iType == 6 || iType == 7)
						fieldValue = String.valueOf(rs.getFloat(i + 1));
					else
						fieldValue = rs.getString(i + 1);
					if (fieldValue == null)
						fieldValue = "";
					else
						fieldValue = fieldValue.trim();
					map.put(cols[i], fieldValue);// .toLowerCase()
				}
			}
		} catch (Exception e) {
		}
		return list;
	}

	/**
	 * 获得记录数
	 * 
	 * @param sql
	 * @return 用法： String sql="select count(*) from bill where ... ;
	 *         count=db.executeQuery(sql);
	 */
	public int getCount(String sql) {
		// String sql="select count(*) from table where ...";
		ResultSet rs = null;
		PreparedStatement ps;
		int count = 0;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			rs.next();
			count = rs.getInt(1);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return count;
	}

	/**
	 * 以下4个方法都可以用doExecute(),getList()替代
	 * 
	 * @param args
	 */
	private Statement st = null;

	public void doInsert(String sql) {
		try {
			st = conn.createStatement();
			int i = st.executeUpdate(sql);
		} catch (SQLException sqlexception) {
			System.err.println("db.executeInset:" + sqlexception.getMessage());
		}
	}

	public void doDelete(String sql) {
		try {
			st = conn.createStatement();
			int i = st.executeUpdate(sql);
		} catch (SQLException sqlexception) {
			System.err.println("db.executeDelete:" + sqlexception.getMessage());
		}
	}

	public void doUpdate(String sql) {
		try {
			st = conn.createStatement();
			int i = st.executeUpdate(sql);
		} catch (SQLException sqlexception) {
			System.err.println("db.executeUpdate:" + sqlexception.getMessage());
		}
	}

	public ResultSet doSelect(String sql) {
		ResultSet rs = null;
		try {
			st = conn.createStatement(
					java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE,
					java.sql.ResultSet.CONCUR_READ_ONLY);
			rs = st.executeQuery(sql);
		} catch (SQLException sqlexception) {
			System.err.println("db.executeQuery: " + sqlexception.getMessage());
		}
		return rs;
	}

	/**
	 * 以下3个为保留方法
	 */
	public void beginTransaction() {
		try {
			conn.setAutoCommit(false);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void commitTransaction() {
		try {
			conn.commit();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void rollbackTransaction() {
		try {
			conn.rollback();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 测试
	 * 
	 * @param args
	 */
	public static void main(String[] args) {
		DataBaseUtil util = new DataBaseUtil("config.properties");
		String sql = "";
		sql = "INSERT INTO user(userId,password,userName,zone,phone) VALUES(?,?,?,?,?)";
		try {
			util.doExecute(sql, new String[] { "name", "pass", "上海", "021",
					"61089712" });
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		sql = "select * from user ";
		ArrayList list = util.getList(sql);
		System.out.println("size=" + list.size());
		for (int i = 0; i < list.size(); i++) {
			Map map = (Map) list.get(i);
			System.out.println("===" + map.get("userId"));
			System.out.println("===" + map.get("password"));
			System.out.println("===" + map.get("userName"));
			System.out.println("===" + map.get("zone"));
			System.out.println("===" + map.get("phone"));
		}
		//		Connection conn = util.getConnection();
		util.closeConnection();
	}
}