1. DAO（抽象类）

• DAO：Data Access Object访问数据信息的类和接口，包括了对数据的CRUD（Create、Retrival、 Update、Delete），而不包含任何业务相关的信息。
• 作用：为了实现功能的模块化，更有利于代码的维护和升级。

1.1. 测试使用的表

以sutdent表为例：

1.2. 具体实现代码

public abstract class DAO {
	public static void main(String[] args) throws Exception {
		DAO dao = new DAO ();
		Connection conn = JDBCUtils.getConnection ("jdbc.properties");
		String sql = "select `id`,`name`,`email`,`age` from student where id <> ?";
		int id = 1;
		List<Student> list = dao.queryForList (conn, Student.class,sql,id);
		list.forEach (System.out::println);
		String sql1 = "select count(*) from student ";
		Long count = dao.getValue (conn,sql1);
		System.out.println ("============================");
		System.out.println (count);

	}
	public int update(Connection conn,String sql,Object... args){
//		Connection conn = JDBCUtils.getConnection ("jdbc.properties");
		PreparedStatement statement = null;
		try {
			statement = conn.prepareStatement (sql);
			for (int i = 0;i < args.length;i++){
				statement.setObject (i+1,args[i]);
			}
			return statement.executeUpdate ();
		} catch (SQLException e) {
			e.printStackTrace ();
		}finally {
			JDBCUtils.closeResource (null,statement,null);
		}
		return 0;
	}

	public <T> List<T> queryForList(Connection conn, Class<T> clazz, String sql, Object... args) {
//		Connection conn = JDBCUtils.getConnection ("jdbc.properties");
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		try {
			statement = conn.prepareStatement (sql);
			for (int  i = 0;i < args.length;i++){
				statement.setObject (i + 1,args[i]);
			}
			resultSet = statement.executeQuery ();

			ResultSetMetaData metaData = resultSet.getMetaData ();
			int columnCount = metaData.getColumnCount ();
			List<T> list = new ArrayList<> ();
			while(resultSet.next ()){
				T result = clazz.getDeclaredConstructor ().newInstance ();
				for (int i = 0;i < columnCount;i++){
					Object value = resultSet.getObject (i + 1);
					//获取字段名字
					String columnName = metaData.getColumnLabel (i + 1);
					Field field = clazz.getDeclaredField (columnName);
					field.setAccessible (true);
					field.set (result,value);
				}
				list.add (result);
			}

			return list;
		} catch (SQLException e) {
			e.printStackTrace ();
		} catch (InstantiationException e) {
			e.printStackTrace ();
		} catch (IllegalAccessException e) {
			e.printStackTrace ();
		} catch (InvocationTargetException e) {
			e.printStackTrace ();
		} catch (NoSuchMethodException e) {
			e.printStackTrace ();
		} catch (NoSuchFieldException e) {
			e.printStackTrace ();
		} finally {
			JDBCUtils.closeResource (null,statement, resultSet);
		}
		return null;
	}

	public <T> T queryForInstance(Connection conn,Class<T> clazz,String sql,Object... args){
		PreparedStatement statement = null;
		ResultSet resultSet = null;

		try {
			statement = conn.prepareStatement (sql);

			for (int i = 0;i < args.length;i++){
				statement.setObject (i + 1,args[i]);
			}

			resultSet = statement.executeQuery ();

			ResultSetMetaData metaData = resultSet.getMetaData ();
			int columnCount = metaData.getColumnCount ();
			if (resultSet.next ()){
				T tmp = clazz.getDeclaredConstructor ().newInstance ();
				for (int i = 0;i < columnCount;i++){
					//读取值
					Object value = resultSet.getObject (i + 1);
					//获取列名
					String columnName = metaData.getColumnLabel (i + 1);
					Field field = clazz.getDeclaredField (columnName);
					field.setAccessible (true);
					field.set (tmp,value);
				}
				return tmp;
			}
			return null;
		} catch (SQLException e) {
			e.printStackTrace ();
		} catch (InstantiationException e) {
			e.printStackTrace ();
		} catch (InvocationTargetException e) {
			e.printStackTrace ();
		} catch (NoSuchMethodException e) {
			e.printStackTrace ();
		} catch (IllegalAccessException e) {
			e.printStackTrace ();
		} catch (NoSuchFieldException e) {
			e.printStackTrace ();
		} finally {
			JDBCUtils.closeResource (null,statement, resultSet);
		}

		return null;
	}

	//泛型方法，调用的时候obj.<Integer>getValue(conn,sql,1."arg2")
	//查询特殊值
	public <E> E getValue(Connection conn,String sql,Object... args){
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		try {
			statement = conn.prepareStatement (sql);
			for (int i = 0;i < args.length;i++){
				statement.setObject (i +1,args[i]);
			}

			resultSet = statement.executeQuery ();

			if (resultSet.next ()){
				return (E) resultSet.getObject (1);
			}
		} catch (SQLException e) {
			e.printStackTrace ();
		} finally {
			JDBCUtils.closeResource (null,statement, resultSet);
		}
		return null;
	}

}

2. StudentDAO（接口）

该接口具体定义在某张表上的操作所需的方法。

具体实现代码：

public interface StudentDAO  {

	/**
	 *
	 * @param conn 数据库连接
	 * @param student 插入的学生对象
	 * @return 返回成功条数
	 */
	int insert(Connection conn, Student student);

	/**
	 *
	 * @param conn  数据库连接
	 * @param id  //删除的id
	 * @return  //返回记录数
	 */
	int deleteByID(Connection conn,int id);

	/**
	 *
	 * @param conn 数据库连接
	 * @param student  要更新的学生信息
	 * @return  成功条数
	 */
	int update(Connection conn,Student student);

	/**
	 * 根据指定ID查询
	 * @param conn  数据库连接
	 * @param id  特定的ID
	 * @return 返回查询到的对象
	 */
	Student getStudentByID(Connection conn,int id) throws Exception;

	/**
	 *
	 * @param conn  数据库连接
	 * @return 返回查询到的对象列表
	 */
	List<Student> getAll(Connection conn);

	/**
	 *  查询记录数目
	 * @param conn 数据库连接
	 * @return  返回记录数
	 */
	Long getCount(Connection conn);

	/**
	 * 查询最大的年龄
	 * @param conn  数据库连接
	 * @return 返回查询到的对象
	 */
	int getMaxAge(Connection conn);
}
3. StudentDAOImpl(具体实现类)
该类继承DAO，利用DAO的通用基础操作。
实现了StudentDAO接口，具体实现在Student表上的操作
实现代码：

public class StudentDAOImpl extends DAO implements StudentDAO {
	/**
	 * @param conn    数据库连接
	 * @param student 插入的学生对象
	 * @return 返回成功条数
	 */
	@Override
	public int insert(Connection conn, Student student) {
		String sql = "insert into student(`name`,`email`,`age`) values(?,?,?)";
		return update (conn,sql,student.getName (),student.getEmail (),student.getAge ());
	}

	/**
	 * @param conn 数据库连接
	 * @param id   //删除的id
	 * @return //返回记录数
	 */
	@Override
	public int deleteByID(Connection conn, int id) {
		String sql = "delete from student where id = ?";
		return update (conn,sql,id);
	}

	/**
	 * @param conn    数据库连接
	 * @param student 要更新的学生信息
	 * @return 成功条数
	 */
	@Override
	public int update(Connection conn, Student student) {
		String sql = "update student set `name` = ?,`email` = ?,`age` = ? where `id` = ?";
		return update (conn,sql,student.getName (),student.getEmail (),student.getAge (),student.getId ());
	}

	/**
	 * 根据指定ID查询
	 *
	 * @param conn 数据库连接
	 * @param id   特定的ID
	 * @return 返回查询到的对象
	 */
	@Override
	public Student getStudentByID(Connection conn, int id)  {
		String sql = "select id,`name`,email,age from student where id = ?";
		return queryForInstance (conn,Student.class,sql,id);
	}

	/**
	 * @param conn 数据库连接
	 * @return 返回查询到的对象列表
	 */
	@Override
	public List<Student> getAll(Connection conn) {
		String sql = "select id,`name`,email,age from student";
		return queryForList (conn,Student.class,sql);
	}

	/**
	 * 查询记录数目
	 *
	 * @param conn 数据库连接
	 * @return 返回记录数
	 */
	@Override
	public Long getCount(Connection conn) {
		String sql = "select count(*) from student";
		return this.<Long>getValue (conn,sql);
	}

	/**
	 * 查询最大的年龄
	 *
	 * @param conn 数据库连接
	 * @return 返回查询到的对象
	 */
	@Override
	public int getMaxAge(Connection conn) {
		String sql = "select max(age) from student";
		return this.<Integer>getValue (conn,sql);
	}
}