Java 自定义JDBC类库

链接：具体讲解请参见Wikipedia

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Map;

public class MySQLHelper implements AutoCloseable {
	// tool variables
	private static String sql;
	private static StringBuilder sbuilder;
	static {
		sbuilder = new StringBuilder();
	}
	private String info;

	private String url;
	private String host;
	private int port;
	private String dbName;

	private String usr;
	private String pwd;

	private Connection conn;
	private Statement stmt;

	public String getUrl() {
		return url;
	}

	public void setUrl(String url) {
		this.url = url;
	}

	public String getHost() {
		return host;
	}

	public void setHost(String host) {
		this.host = host;
	}

	public int getPort() {
		return port;
	}

	public void setPort(int port) {
		this.port = port;
	}

	public String getDbName() {
		return dbName;
	}

	public void setDbName(String dbName) {
		this.dbName = dbName;
	}

	public String getUsr() {
		return usr;
	}

	public void setUsr(String usr) {
		this.usr = usr;
	}

	public String getPwd() {
		return pwd;
	}

	public void setPwd(String pwd) {
		this.pwd = pwd;
	}

	public String getInfo() {
		return info;
	}

	public MySQLHelper() throws ClassNotFoundException, SQLException {
		this.host = "localhost";
		this.port = 3306;
		this.dbName = "test";
		this.usr = "root";
		this.pwd = "123456";
		this.url = "jdbc:mysql://localhost:3306";
		ConnectDB();
	}

	public MySQLHelper(String url, String usr, String pwd)
			throws ClassNotFoundException, SQLException {
		this.url = url;
		this.usr = usr;
		this.pwd = pwd;
		ConnectDB();
	}

	public MySQLHelper(String host, int port, String usr, String pwd)
			throws ClassNotFoundException, SQLException {
		this.host = host;
		this.port = port;
		this.usr = usr;
		this.pwd = pwd;
		this.url = "jdbc:mysql://" + this.host + ":" + this.port;
		ConnectDB();
	}

	public MySQLHelper(String host, int port, String dbName, String usr,
			String pwd) throws ClassNotFoundException, SQLException {
		this.host = host;
		this.port = port;
		this.dbName = dbName;
		this.usr = usr;
		this.pwd = pwd;
		this.url = "jdbc:mysql://" + this.host + ":" + this.port + "/"
				+ this.dbName;
		ConnectDB();
	}

	/**
	 * 注册JDBC驱动 + 创建数据库连接
	 * 
	 * @return
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	private Connection ConnectDB() throws ClassNotFoundException, SQLException {
		try {
			// 注册JDBC类
			Class.forName("com.mysql.jdbc.Driver");
			// 打开数据库连接
			this.conn = DriverManager.getConnection(this.url+"?useUnicode=true&characterEncoding=UTF8", this.usr,
					this.pwd);
			this.stmt = conn.createStatement();
			this.info = "数据库连接成功！";
		} catch (ClassNotFoundException e) {
			this.info = "com.mysql.jdbc.Driver导入失败！";
			throw new ClassNotFoundException(this.info);
		} catch (SQLException e) {
			this.info = "MySQL数据库连接失败！";
			throw new SQLException(this.info);
		}
		return this.conn;
	}

	/**
	 * 创建数据库
	 * 
	 * @param newDBName
	 * @throws SQLException
	 */
	public void CreateDB(String newDBName) throws SQLException {
		sql = "CREATE DATABASE IF NOT EXISTS " + newDBName
				+ " DEFAULT CHARSET = utf8";
		try {
			stmt.executeUpdate(sql);
			this.info = "数据库创建成功";
		} catch (SQLException e) {
			this.info = "创建数据库失败";
			throw e;
		}
	}

	/**
	 * 创建数据表
	 * 
	 * @param newTBName
	 *            新的数据表名
	 * @param dbName
	 *            数据库名
	 * @param properties
	 *            字段属性
	 * @throws SQLException
	 */
	public void CreateTable(String dbName, String newTBName,
			Map<String, String> properties) throws SQLException {
		setDbName(dbName);
		if (properties.size() == 0)
			throw new SQLException("字段以及字段信息[properties]不能为空！");
		sbuilder.delete(0, sbuilder.length());
		sbuilder.append(String.format("CREATE TABLE IF NOT EXISTS `%s`.`%s` (",
				dbName, newTBName));

		for (Map.Entry<String, String> entry : properties.entrySet()) {
			sbuilder.append(entry.getKey() + " " + entry.getValue() + ",");
		}
		sbuilder.deleteCharAt(sbuilder.length() - 1);
		sbuilder.append(")");
		sql = sbuilder.toString();
		try {
			this.info = "创建数据表" + newTBName + "成功！";
			stmt.executeUpdate(sql);
		} catch (SQLException e) {
			this.info = "创建数据表" + newTBName + "失败！";
			System.out.println(sql);
			throw e;
		}
	}

	/**
	 * 创建数据表
	 * 
	 * @param newTBName
	 *            新的数据表名
	 * @param properties
	 *            字段属性
	 * @throws SQLException
	 */
	public void CreateTable(String newTBName, Map<String, String> properties)
			throws SQLException {
		if (this.dbName == null || this.dbName == "")
			throw new SQLException("请先选择一个数据库！");
		else
			CreateTable(this.dbName, newTBName, properties);
	}

	/**
	 * 删除数据库
	 * 
	 * @param dbName
	 *            数据库名
	 * @throws SQLException
	 */
	public void DropDB(String dbName) throws SQLException {
		sql = "DROP DATABASE IF EXISTS " + dbName;
		try {
			stmt.executeUpdate(sql);
			this.info = "数据库删除成功";
		} catch (SQLException e) {
			this.info = "数据库删除失败！" + sql;
			throw e;
		}
	}

	/**
	 * 删除数据库
	 * 
	 * @throws SQLException
	 */
	public void DropDB() throws SQLException {
		if (this.dbName == null || this.dbName == "")
			throw new SQLException("请先选择一个数据库！");
		else
			DropDB(this.dbName);
	}

	/**
	 * 删除数据表
	 * 
	 * @param dbName
	 *            数据库名
	 * @param tbName
	 *            数据表名
	 * @throws SQLException
	 */
	public void DropTable(String dbName, String tbName) throws SQLException {
		sql = String.format("DROP TABLE IF EXISTS `%s`.`%s`", dbName, tbName);
		try {
			stmt.executeUpdate(sql);
			this.info = "数据表删除成功";
		} catch (SQLException e) {
			this.info = "数据表删除失败" + sql;
			throw e;
		}
	}

	/**
	 * 删除数据表
	 * 
	 * @param tbName
	 *            数据表名
	 * @throws SQLException
	 */
	public void DropTable(String tbName) throws SQLException {
		if (this.dbName == null || this.dbName == "")
			throw new SQLException("请先选择一个数据库！");
		else
			DropTable(this.dbName, tbName);
	}

	/**
	 * 查询数据表中全部信息
	 * 
	 * @param tbName
	 * @return
	 * @throws SQLException
	 */
	public ResultSet selectAll(String tbName) throws SQLException {
		sql = String.format("SELECT * FROM `%s`.`%s`", this.dbName, tbName);
		try {
			this.info = "数据库SELECT-ALL查询成功";
			return stmt.executeQuery(sql);
		} catch (SQLException e) {
			this.info = "数据库SELECT-ALL查询失败";
			throw e;
		}
	}

	/**
	 * 查询数据表中的部分信息
	 * 
	 * @param field
	 * @param tbName
	 * @param condition
	 * @return
	 * @throws SQLException
	 */
	public ResultSet select(String field, String tbName, String condition)
			throws SQLException {
		if ("" == condition)
			condition = "1";
		sql = String.format("SELECT %s FROM `%s`.`%s` where %s", field,
				this.dbName, tbName, condition);
		try {
			this.info = "数据库SELECT查询成功";
			return stmt.executeQuery(sql);
		} catch (SQLException e) {
			this.info = "数据库SELECT查询失败";
			throw e;
		}
	}

	/**
	 * 插入数据到数据表
	 * 
	 * @param dbName
	 *            数据库名称
	 * @param tbName
	 *            数据表名称
	 * @param item
	 *            记录映射表
	 * @throws SQLException
	 */
	public void insert(String dbName, String tbName, Map<String, Object> item)
			throws SQLException {
		sbuilder.delete(0, sbuilder.length());
		sbuilder.append(String.format("INSERT INTO `%s`.`%s` ( ", dbName,
				tbName));
		for (Map.Entry<String, Object> e : item.entrySet()) {
			sbuilder.append(e.getKey() + ",");
		}
		sbuilder.setCharAt(sbuilder.length() - 1, ')');
		sbuilder.append(" VALUES ( ");
		for (Map.Entry<String, Object> e : item.entrySet()) {
			sbuilder.append("'" + e.getValue() + "'" + ",");
		}
		sbuilder.setCharAt(sbuilder.length() - 1, ')');
		sql = sbuilder.toString();
		try {
			int ret = stmt.executeUpdate(sql);
			this.info = "数据库INSERT更新成功， " + ret + "行受影响";
		} catch (SQLException e) {
			System.out.println(sql);
			this.info = "数据库INSERT更新失败";
			throw e;
		}
	}

	/**
	 * 插入数据到数据表,请确保MySQLHelper对象dbName已经设置
	 * 
	 * @param tbName
	 *            数据表名称
	 * @param item
	 *            记录映射表
	 * @throws SQLException
	 */
	public void insert(String tbName, Map<String, Object> item)
			throws SQLException {
		if (this.dbName == null || this.dbName == "")
			throw new SQLException("请先选择一个数据库！");
		else
			insert(this.dbName, tbName, item);
	}

	/**
	 * 更新数据表
	 * 
	 * @param fieldName
	 *            字段名
	 * @param fieldValue
	 *            字段值
	 * @param condition
	 *            更新条件
	 * @param tbName
	 *            数据表名
	 * @throws SQLException
	 */
	public void update(String fieldName, String fieldValue, String condition,
			String tbName) throws SQLException {
		if ("" == condition)
			condition = "1";
		sql = String.format("UPDATE `%s`.`%s` SET %s='%s' WHERE %s",
				this.dbName, tbName, fieldName, fieldValue, condition);
		try {
			int ret = stmt.executeUpdate(sql);
			this.info = "数据库UPDATE更新成功， " + ret + "行受影响";
		} catch (SQLException e) {
			System.out.println(sql);
			this.info = "数据库UPDATE更新失败";
			throw e;
		}
	}

	/**
	 * 删除数据表中指定的元素，请确保MySQLHelper对象dbName已经设置
	 * 
	 * @param tbName
	 * @param condition
	 * @return
	 * @throws SQLException
	 */
	public void delete(String tbName, String condition) throws SQLException {
		if (this.dbName == null || this.dbName == "")
			throw new SQLException("请先选择一个数据库！");
		else
			delete(this.dbName, tbName, condition);
	}

	public void delete(String dbName, String tbName, String condition)
			throws SQLException {
		sql = String.format("DELETE FROM `%s`.`%s` WHERE %s", dbName, tbName,
				condition);
		try {
			int ret = stmt.executeUpdate(sql);
			this.info = "数据表DELETE操作删除成功, " + ret + "行受影响！";
		} catch (SQLException e) {
			this.info = "数据表DELETE操作删除失败";
			throw e;
		}
	}

	/**
	 * 释放数据库连接资源
	 * 
	 * @throws SQLException
	 */
	public void release() throws SQLException {
		try {
			if (null != stmt)
				stmt.close();
			if (null != conn)
				conn.close();
			this.info = "释放连接成功";
		} catch (SQLException e) {
			this.info = "释放连接失败";
			throw e;
		}

	}

	/**
	 * 打印出所有的数据库名称
	 * 
	 * @throws SQLException
	 */
	public void showDBs() throws SQLException {
		ResultSet rs = stmt.executeQuery("show databases");
		printResult(rs);
	}

	/**
	 * 打印数据库的查询结果集ResultSet
	 * 
	 * @param result
	 * @throws SQLException
	 */
	public static void printResult(ResultSet result) throws SQLException {
		// 获取字段名
		ResultSetMetaData rsmd = result.getMetaData();
		int L = rsmd.getColumnCount();
		ArrayList<String> fieldNames = new ArrayList<>();
		for (int i = 1; i <= L; i++) {
			fieldNames.add(rsmd.getColumnName(i));
			System.out.print(rsmd.getColumnName(i) + (i == L ? "\n" : "\t"));
		}
		while (result.next()) {
			for (int i = 1; i <= L; i++) {
				System.out.print(result.getString(i) + (i == L ? "\n" : "\t"));
			}
		}
	}

	/**
	 * try 自动关闭, 自动释放连接资源
	 */
	@Override
	public void close() throws Exception {
		// TODO Auto-generated method stub
		this.release();
		System.out.println(this.info);
	}
}