mysql.java

图书馆管理系统 能满足 查询

/*
 * Created on 2005-4-14
 * 
 * @author [e-mail]
 * －－－－－－－－－－－－－
 *  2006-5-13 修改
 *	1. 加入了数据库连接池 ，节约系统资源。
 */
package com.jxyd.sql;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.Date;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * 访问数据库操作的通用类
 */
public class Mysql {

	// 数据库连接
	private Connection _conn;

	// 执行语句
	private Statement _stmt;

	// 预存储执行语句
	private PreparedStatement _pstmt;

	Linkdb linkdb;

	/**
	 * 默认的构造方法 建立数据库连接
	 * 
	 */
	public Mysql() {

		initConnection();
	}

	/**
	 * 用现有的数据连接初始化当前连接
	 * 
	 * @param conn
	 */
	public Mysql(Connection conn) {
		_conn = conn;
	}

	/**
	 * 返回当前连接
	 * 
	 * @return
	 */
	public Connection getConnection() {
		return _conn;
	}

	/**
	 * 执行预存储语句
	 * 
	 * @param sql
	 * @throws SQLException
	 */
	public void prepareStatement(String sql) throws SQLException {
		_pstmt = _conn.prepareStatement(sql);
	}

	/**
	 * 执行预存储语句
	 * 
	 * @param sql
	 * @param isScrollSensitive
	 *            是否可以自由滚动
	 * @throws SQLException
	 */
	public void prepareStatement(String sql, boolean isScrollSensitive)
			throws SQLException {
		if (!isScrollSensitive) {
			_pstmt = _conn.prepareStatement(sql,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
		} else {
			_pstmt = _conn
					.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE,
							ResultSet.CONCUR_READ_ONLY);
		}
	}

	/**
	 * 执行预存储语句
	 * 
	 * @param sql
	 * @param resultSetType
	 *            记录集类型
	 * @param resultSetConcurrency
	 * @throws SQLException
	 */
	public void prepareStatement(String sql, int resultSetType,
			int resultSetConcurrency) throws SQLException {
		_pstmt = _conn.prepareStatement(sql, resultSetType,
				resultSetConcurrency);
	}

	/**
	 * 
	 * @param index
	 * @param value
	 * @throws SQLException
	 */
	public void setString(int index, String value) throws SQLException {
		_pstmt.setString(index, value);
	}

	/**
	 * 
	 * @param index
	 * @param value
	 * @throws SQLException
	 */
	public void setInt(int index, int value) throws SQLException {
		_pstmt.setInt(index, value);
	}

	/**
	 * 
	 * @param index
	 * @param value
	 * @throws SQLException
	 */
	public void setBoolean(int index, boolean value) throws SQLException {
		_pstmt.setBoolean(index, value);
	}

	/**
	 * 
	 * @param index
	 * @param value
	 * @throws SQLException
	 */
	public void setDate(int index, Date value) throws SQLException {
		_pstmt.setDate(index, value);
		
	}

	/**
	 * 
	 * @param index
	 * @param value
	 * @throws SQLException
	 */
	public void setLong(int index, long value) throws SQLException {
		_pstmt.setLong(index, value);
	}

	/**
	 * 
	 * @param index
	 * @param value
	 * @throws SQLException
	 */
	public void setFloat(int index, float value) throws SQLException {
		_pstmt.setFloat(index, value);
	}

	/**
	 * 
	 * @param index
	 * @param value
	 * @throws SQLException
	 */
	public void setDouble(int index, double value) throws SQLException {
		_pstmt.setDouble(index, value);
	}

	/**
	 * 
	 * @param index
	 * @param in
	 * @param length
	 * @throws SQLException
	 */
	public void setBinaryStream(int index, InputStream in, int length)
			throws SQLException {
		_pstmt.setBinaryStream(index, in, length);
	}

	/**
	 * 返回预存储语句参数
	 * 
	 * @return
	 */
	public PreparedStatement getPreparedStatement() {
		return _pstmt;
	}

	/**
	 * 清除预存储语句参数
	 * 
	 */
	public void clearParameters() {
		try {
			_pstmt.clearParameters();
		} catch (SQLException sqle) {
			sqle.printStackTrace();
		}
	}

	/**
	 * 执行插入或更新操作
	 * 
	 * @param sql
	 * @throws SQLException
	 */
	public void execute(String sql) throws SQLException {
		if (_stmt == null) {
			_stmt = _conn.createStatement();
		}
		_stmt.execute(sql);
	}

	/**
	 * 执行插入或更新操作
	 * 
	 * @throws SQLException
	 */
	public boolean execute() throws SQLException {
		if (_pstmt != null) {
			return _pstmt.execute();
		}
		  return false;
	}

	/**
	 * 执行查询
	 * 
	 * @param sql
	 * @return
	 * @throws SQLException
	 */
	public ResultSet executeQuery(String sql) throws SQLException {
		if (_stmt == null) {
			_stmt = _conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
		}
		return _stmt.executeQuery(sql);

	}

	/**
	 * 执行查询
	 * 
	 * @return
	 * @throws SQLException
	 */
	public ResultSet executeQuery() throws SQLException {
		if (_pstmt != null) {
			return _pstmt.executeQuery();
		} else {
			return null;
		}
	}

	/**
	 * 执行更新操作
	 * 
	 * @param sql
	 * @throws SQLException
	 */
	public void executeUpdate(String sql) throws SQLException {
		if (_stmt == null) {
			_stmt = _conn.createStatement();
		}
		_stmt.executeUpdate(sql);
	}

	/**
	 * 执行更新操作
	 * 
	 * @throws SQLException
	 */
	public int executeUpdate() throws SQLException {
		if (_pstmt != null) {
			return _pstmt.executeUpdate();
		}
		  return 0;
	}

	/**
	 * 关闭连接，释放资源
	 * 
	 */
	public void freeResource() {
		try {
			if (_stmt != null) {
				_stmt.close();
			}
			if (_pstmt != null) {
				_pstmt.close();
			}
			if (_conn != null) {
				_conn.close();
			}
			_stmt = null;
			_pstmt = null;
			_conn = null;
			linkdb.releaseConnection();
		} catch (SQLException sqle) {
			sqle.printStackTrace();
		}
	}

	/**
	 * 设置事务的提交方式
	 * 
	 * @param autoCommit
	 */
	public void setAutoCommit(boolean autoCommit) {

		try {
			_conn.setAutoCommit(autoCommit);
		} catch (SQLException se) {
			se.printStackTrace();
		}
	}

	/**
	 * 事务提交
	 * 
	 */
	public void commit() {

		try {
			_conn.commit();
		} catch (SQLException se) {
			se.printStackTrace();
		}
	}

	/**
	 * 事务回滚
	 * 
	 */
	public void rollback() {

		try {
			_conn.rollback();
		} catch (SQLException se) {
			se.printStackTrace();
		}
	}

	/**
	 * 初始化数据库连接
	 */
	private void initConnection() {

		try {
			linkdb = new Linkdb();
			linkdb.openConnection("dbConn");
			this._conn = linkdb.getConnection();
			this._conn.setAutoCommit(false);
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

}