mysqldbsource.java

此文档针对开发人员和测试人员。第二章对软件进行了全面的描述。第三章对接口进行了分析。第四章对软件实现的功能进行概述。第五章对软件后续开发实现提出的要求。第六章提出其他一些在软件开发过程中需要注意的问题

package com.ciash.common.db.bean;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Iterator;
import java.util.LinkedList;

import javax.swing.JOptionPane;

/**
 * 该类将提供一个数据库连接池，其中封装了select、insert、delete等SQL语句的操作方式
 * <p>
 * 请注意！该类提供了参数的设置，请使用Parameter对象进行操作
 * <p>
 * 实例： <br>
 * <code>
 * ConnectionStringBean connBean = new ConnectionStringBean();
 * <br>
 * connBean.setDriver("com.mysql.jdbc.Driver"); &nbsp;&nbsp;&nbsp;&nbsp;
 * <br>
 * //测试数据，请在此输入你真正的驱动名
 * <br>
 * connBean.setURL("jdbc:mysql://localhost:3306/bms_db"); &nbsp;&nbsp;&nbsp;&nbsp;
 * //测试数据，请在此输入你真正的URL
 * <br>
 * connBean.setPsw("admin"); &nbsp;&nbsp;&nbsp;
 * //测试数据，请在此输入你真正的用户名
 * <br>
 * connBean.setId("root"); &nbsp;&nbsp;&nbsp;
 * //测试数据，请在此输入你真正的密码
 * <br>
 * DBSource s = DBSource.getInstance(connBean)
 * &nbsp;&nbsp;&nbsp;&nbsp;
 * //此方法将返回唯一实例，除非你调用了close()方法
 * <br>
 * s.setSelectCommand("SELECT * FROM <i>table_name</i> WHERE <i>column_name</i> = @<i>parameter_name</i>");
 * <br>
 * s.addSelectParameter(new Parameter("<i>parameter_name</i>", "<i>variable_name</i>"));
 * <br>
 * s.select()
 * &nbsp;&nbsp;&nbsp;&nbsp;
 * //此返回值将是JDBC的ResultSet对象
 * </code>
 * <p>
 *
 * @see Parameter
 */
public class MySqlDBSource {

	private ConnectionStringBean connectionStringBean;

	private String selectCommand;

	private String insertCommand;

	private String updateCommand;

	private String deleteCommand;

	private static Connection connection;

	private LinkedList selectParameterList;

	private LinkedList updateParameterList;

	private LinkedList insertParameterList;

	private LinkedList deleteParameterList;

	private static MySqlDBSource instance = new MySqlDBSource(); // 连接池实例

	/**
	 * 使用保护的连接池构造器，确保只有一个连接池被创建
	 *
	 * @param connectionStringBean
	 */
	protected MySqlDBSource() {
		selectParameterList = new LinkedList();
		updateParameterList = new LinkedList();
		insertParameterList = new LinkedList();
		deleteParameterList = new LinkedList();
	}

	public void setSelectCommand(String selectCommand) {
		this.selectCommand = selectCommand;
		selectParameterList.clear();
	}

	public String getSelectCommand() {
		return selectCommand;
	}

	public void setDeleteCommand(String deleteCommand) {
		this.deleteCommand = deleteCommand;
		deleteParameterList.clear();
	}

	public String getDeleteCommand() {
		return deleteCommand;
	}

	public void setInsertCommand(String insertCommand) {
		this.insertCommand = insertCommand;
		insertParameterList.clear();
	}

	public String getInsertCommand() {
		return insertCommand;
	}

	public void setUpdateCommand(String updateCommand) {
		this.updateCommand = updateCommand;
		updateParameterList.clear();
	}

	public String getUpdateCommand() {
		return updateCommand;
	}

	public void setConnectionString(ConnectionStringBean connectionStringBean) {
		this.connectionStringBean = connectionStringBean;
	}

	public ConnectionStringBean getConnectionString() {
		return connectionStringBean;
	}

	/**
	 * 执行删除操作
	 *
	 * @throws SQLException
	 *             操作失败
	 */
	public void delete() {
		Statement state = null;
		try {
			state = connection.createStatement(
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_UPDATABLE);
			Iterator iterator = deleteParameterList.iterator();
			while (iterator.hasNext()) {
				Parameter p = (Parameter) iterator.next();
				deleteCommand = deleteCommand.replace(p.getKey(), p.getValue());
			}
			String command = connection.nativeSQL(deleteCommand);
			state.execute(command);
		} catch (SQLException ex) {
			ex.printStackTrace();
		}

	}

	public void addDeleteParameter(Parameter parameter) {
		deleteParameterList.add(parameter);
	}

	public void removeDeleteParameter(Parameter parameter) {
		deleteParameterList.remove(parameter);
	}

	/**
	 * 执行插入操作
	 *
	 * @return true 插入操作成功
	 * @throws SQLException
	 *             close the connection is failed
	 */
	public void insert() {
		try {
			Statement state = null;
			state = connection.createStatement();
			Iterator iterator = insertParameterList.iterator();
			while (iterator.hasNext()) {
				Parameter p = (Parameter) iterator.next();
				insertCommand = insertCommand.replace(p.getKey(), p.getValue());
			}
			String command = connection.nativeSQL(insertCommand);
			state.execute(command);
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
	}

	public void addInsertParameter(Parameter parameter) {
		insertParameterList.add(parameter);
	}

	public void removeInsertParameter(Parameter parameter) {
		insertParameterList.remove(parameter);
	}

	/**
	 * 执行选择操作
	 *
	 * @return 返回执行结果
	 * @throws SQLException
	 *             throw exception when close connection ouccor an error
	 */
	public ResultSet select() {
		ResultSet result = null;
		try {
			Statement state = connection.createStatement(
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_UPDATABLE);
			Iterator iterator = selectParameterList.iterator();
			while (iterator.hasNext()) {
				Parameter p = (Parameter) iterator.next();
				selectCommand = selectCommand.replace(p.getKey(), p.getValue());
			}
			String command = connection.nativeSQL(selectCommand);
			result = state.executeQuery(command);
		} catch (SQLException ex) {
			JOptionPane.showMessageDialog(null, "数据库连接出错", "出错了！",
					JOptionPane.ERROR_MESSAGE);
			ex.printStackTrace();
		}
		return result;
	}

	public void addSelectParameter(Parameter parameter) {
		selectParameterList.add(parameter);
	}

	public void removeSelectParameter(Parameter parameter) {
		selectParameterList.remove(parameter);
	}

	/**
	 * 执行更新操作
	 *
	 * @throws SQLException
	 *             throw this exception when close connnection fail
	 */
	public void update() {
		Statement state = null;
		try {
			state = connection.createStatement();

			Iterator iterator = updateParameterList.iterator();
			while (iterator.hasNext()) {
				Parameter p = (Parameter) iterator.next();
				updateCommand = updateCommand.replace(p.getKey(), p.getValue());
			}
			String command = connection.nativeSQL(updateCommand);
			state.execute(command);
			connection.close();
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
	}

	public void addUpdateParameter(Parameter parameter) {
		updateParameterList.add(parameter);
	}

	public void removeUpdateParameter(Parameter parameter) {
		updateParameterList.remove(parameter);
	}

	/**
	 * 关闭连接池，不再使用
	 */
	public void close() {
        System.out.println("关闭数据库连接");
		try {
			if (!connection.isClosed()) {
				connection.close();
			}

		} catch (SQLException ex) {
			ex.printStackTrace();
		}
	}

	/**
	 * 静态工厂方法，确保只有一个连接池被创建
	 *
	 * @return 返回创建的连接池
	 */
	public static MySqlDBSource getInstance(
			ConnectionStringBean connectionStringBean) {
		try {
            System.out.println("检查数据库连接池通信状况");
			if (connection == null || connection.isClosed()) {
				// 只有当新建或者关闭后才能获得新的实例
                System.out.println("数据库连接没有连接上数据库");
				instance.initSource(connectionStringBean);
				// 初始化新实例的连接
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
		return instance;
	}

	protected void initSource(ConnectionStringBean connectionStringBean) {
		setConnectionString(connectionStringBean);
        System.out.println("连接数据库");
		connection = getNewConn();
        System.out.println("数据库连接成功");
	}

	public Connection getConnection() {
		return connection;
	}

	private Connection getNewConn() {
		try {
            System.out.println("装载数据库驱动");
			Class.forName(connectionStringBean.getDriver());
			// load driver
			String id = connectionStringBean.getId();
			String psw = connectionStringBean.getPsw();
			String conn = connectionStringBean.getURL();
			return DriverManager.getConnection(conn, id, psw);
		} catch (SQLException ex) {
			ex.printStackTrace();
			return null;
		} catch (ClassNotFoundException ex) {
			ex.printStackTrace();
			return null;
		}
	}

}