DBhelp.java

database

public class DBhelp<T>{

  
	final String DRIVER="com.mysql.jdbc.Driver";
	final String URL="jdbc:mysql://127.0.0.1:3306/bookSystem";
	final String USERNAME="root";
	final String PASSWORD="123";
	
	/**
	 * 获取数据库连接对象
	 * @return
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public Connection getConnection() throws ClassNotFoundException, SQLException{
		Class.forName(DRIVER);
		return DriverManager.getConnection(URL, USERNAME, PASSWORD);
	}
	/**
	 * 释放数据库资源
	 * @param rs
	 * @param st
	 * @param conn
	 */
	public void close(ResultSet rs,Statement st,Connection conn){
		
		try {
			if(rs!=null){
				rs.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				if(st!=null){
					st.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}finally{
				try {
					if(conn!=null){
						conn.close();
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
	
	public void close(Statement st,Connection conn){
		close(null, st, conn);
	}
	
//	数据在数据库和内存之间的转换
//	查找指定对象的操作
	public T executeQueryTObject(String sql,RowMapper<T> rowMapper,Object...objects ){
		Connection conn=null;
		PreparedStatement pst=null;
		ResultSet rs=null;
		T result = null;
		try {
//			连接数据库
			conn=getConnection();
//			声明一个对象，将sql语句发送到数据库
			pst=conn.prepareStatement(sql);
			for (int i = 0; i < objects.length; i++) {
				pst.setObject(i+1, objects[i]);
			}
//			执行sql语句
			rs=pst.executeQuery();
//			rs.next:将光标移动到插入行
			if(rs.next()){
				result=rowMapper.rowMapper(rs);
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
//		利用返回的值进行下面的操作
		return result;
	}
	/**
	 * 添加、修改、删除的操作
	 * @param sql
	 * @param objects
	 * @return
	 */
	public int executeSQL(String sql,Object...objects ){
		int row = 0;
		Connection conn=null;
		PreparedStatement pst=null;
		
		try {
//			连接数据库
			conn=getConnection();
//			创建声明发送sql语句的对象
			pst=conn.prepareStatement(sql);
			for (int i = 0; i < objects.length; i++) {
//				存进数据库
				pst.setObject(i+1, objects[i]);
			}
//			执行sql语句
			row=pst.executeUpdate();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return row;
	}
	
	/**
	 * 查找全部
	 * @param sql
	 * @param rowMapper
	 * @param objects
	 * @return
	 */
	public List<T> executeQueryToList(String sql,RowMapper<T> rowMapper,Object...objects ){
		Connection conn=null;
		PreparedStatement pst=null;
		ResultSet rs=null;
		ArrayList<T> list=new ArrayList<T>();

		try {
			// 连接数据库
			conn = getConnection();
			// 创建声明发送sql语句的对象
			pst = conn.prepareStatement(sql);
			for (int i = 0; i < objects.length; i++) {
				pst.setObject(i + 1, objects[i]);
			}
//			提取数据:数据库→内存
			rs=pst.executeQuery();
			while(rs.next()){
				T result=rowMapper.rowMapper(rs);
				list.add(result);
			}
			
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
//		返回一个list对象
		return list;
	}
	
	public T executeQueryByName(String sql,RowMapper<T> rowMapper,Object...objects ){
		Connection conn=null;
		PreparedStatement pst=null;
		ResultSet rs=null;
		T result=null;
		try {
			// 连接数据库
			conn = getConnection();
			// 创建声明发送sql语句的对象
			pst = conn.prepareStatement(sql);
			for (int i = 0; i < objects.length; i++) {
				pst.setObject(i + 1, objects[i]);
			}
//			提取数据:数据库→内存
			rs=pst.executeQuery();
			if(rs.next()){
				result=rowMapper.rowMapper(rs);
			}
			
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
//		返回一个list对象
		return result;
	}
	
}