JDBC学习笔记-07-DAO及相关实现类

  • DAO:Data Access Object访问数据信息的类和接口,包括了对数据的CRUD(Create、Retrival、Update、Delete),而不包含任何业务相关的信息。有时也称作:BaseDAO

  • 作用:为了实现功能的模块化,更有利于代码的维护和升级。

  • 层次结构
    在这里插入图片描述
    BeanDAO

public abstract class BeanDAO {
	// 通用增删改操做-------version2.0(考虑事务)
	public int updata(Connection conn, String sql, Object... args) {// sql语句中占位符个数与可变形参个数必须相同
		PreparedStatement ps = null;
		try {
			// 1.加载并注册驱动获取数据库的连接
//					conn = JdbcUtils.getConnection();

			// 2.预编译SQL语句返回PrepareStatement的实例
			ps = conn.prepareStatement(sql);
			// 3.填充占位符
			for (int i = 0; i < args.length; i++) {
				ps.setObject(i + 1, args[i]);
			}

			// 4.执行
			return ps.executeUpdate();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			// 5.关闭资源
			JdbcUtils.closeResource(null, ps);
		}
		return 0;
	}

	// 通用的查询一条记录的操做---(考虑了事务的)
	public <T> T getInstance(Connection conn, Class<T> clazz, String sql, Object... args) {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			// 获取连接
			conn = JdbcUtils.getConnection();
			// 获取PreparedStatement
			ps = conn.prepareStatement(sql);
			// 填充占位符
			for (int i = 0; i < args.length; i++) {// 填充占位符从1开始
				ps.setObject(i + 1, args[i]);
			}

			// 执行SQL语句
			rs = ps.executeQuery();
			ResultSetMetaData metaData = rs.getMetaData();
			int columnCount = metaData.getColumnCount();

			// 类似于迭代器指针
			if (rs.next()) {
//						Order order = new Order();
				T t = clazz.newInstance();
				// 遍历一条记录里的各个字段
				for (int i = 0; i < columnCount; i++) {
					// 遍历每一个字段的名字为反射提供属性名和
//							String columnName = metaData.getColumnName(i+1);
					String columnLabel = metaData.getColumnLabel(i + 1);
					Object columnValue = rs.getObject(i + 1);

					// 为order指定的columnName赋值columnValue,通过反射
					Field field = clazz.getDeclaredField(columnLabel);
					field.setAccessible(true);
					field.set(t, columnValue);
				}
				return t;
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			// 关闭资源
			JdbcUtils.closeResource(null, ps, rs);
		}
		return null;
	}

	// 通用的查询多条记录集合的操做---(考虑事务的)
	public <T> List<T> getListInstance(Connection conn, Class<T> clazz, String sql, Object... args) {
		PreparedStatement ps = null;
		ResultSet rs = null;
		ArrayList<T> alist = null;
		try {
			conn = JdbcUtils.getConnection();
			ps = conn.prepareStatement(sql);
			for (int i = 0; i < args.length; i++) {
				ps.setObject(i + 1, args[i]);
			}

			rs = ps.executeQuery();
			// 获取结果集元数据
			ResultSetMetaData rsmd = rs.getMetaData();
			int columnCount = rsmd.getColumnCount();
			alist = new ArrayList<T>();

			while (rs.next()) {
				T t = clazz.newInstance();
				for (int i = 0; i < columnCount; i++) {
					// 获取列别名
					String columnLabel = rsmd.getColumnLabel(i + 1);
					// 获取列值
					Object columnValue = rs.getObject(i + 1);

					// 为列名为columnLabel的赋值,通过反射
					Field field = clazz.getDeclaredField(columnLabel);
					// 防止field为私有权限
					field.setAccessible(true);
					field.set(t, columnValue);
				}
				alist.add(t);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JdbcUtils.closeResource(null, ps, rs);
		}
		return alist;
	}

	@Test
	public void test() throws Exception {
		Connection conn = JdbcUtils.getConnection();

		String sql = "select count(*) from customers";
		Object count = getValue(conn, sql);
		System.out.println(count);
	}

	public <E> E getValue(Connection conn, String sql, Object... args) {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);

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

			rs = ps.executeQuery();
			if (rs.next()) {
				Object object = rs.getObject(1);
				System.out.println(object.getClass());

				return (E) rs.getObject(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JdbcUtils.closeResource(null, ps, rs);
		}
		return null;

	}
}

CustomersDao

//此接口用于规范针对于Customer表的操做
public interface CustomersDao {
	/**
	 * 
	 * @Description:将cust对象添加到数据库中
	 * @author:about1134
	 * @param conn
	 * @param cust
	 */
	void insert(Connection conn,Customer cust);
	
	/**
	 * 
	 * @Description:针对指定的id,删除表中的一条记录
	 * @author:about1134
	 * @param conn
	 * @param id
	 */
	void deleteById(Connection conn,int id);
	
	/**
	 * 
	 * @Description:针对内存中的cust对象,去修改数据表中的指定记录
	 * @author:about1134
	 * @param conn
	 * @param cust
	 */
	void update(Connection conn,Customer cust);
	/**
	 * 
	 * @Description:针对指定的id查询得到对应得到Customer对象
	 * @author:about1134
	 * @param conn
	 * @param id
	 * @return
	 */
	Customer getCustomerById(Connection conn,int id);
	
	/**
	 * 
	 * @Description:查询表中的所用记录构成的集合
	 * @author:about1134
	 * @param conn
	 * @return
	 */
	List<Customer> getAll(Connection conn);
	
	/**
	 * 
	 * @Description:返回数据表中的数据的条目数
	 * @author:about1134
	 * @param conn
	 * @return
	 */
	Long getCount(Connection conn);
	
	/**
	 * 
	 * @Description:返回数据表中最大得到生日
	 * @author:about1134
	 * @param conn
	 * @return
	 */
	Date getMaxBirth(Connection conn);
}

CustomerDaoImpl实现类

public class CustomerDaoImpl extends BeanDAO implements CustomersDao {
	
	@Override
	public void insert(Connection conn, Customer cust) {
		String sql = "insert into Customers(name,email,birth)values(?,?,?)";
		updata(conn, sql, cust.getName(),cust.getEmail(),cust.getBirth());
	}

	@Override
	public void deleteById(Connection conn, int id) {
		String sql = "delete from customers where id = ?";
		updata(conn, sql , id);
	}

	@Override
	public void update(Connection conn, Customer cust) {
		String sql = "update customers set name = ?,email = ?,birth = ? where id = ?";
		// TODO Auto-generated method stub
		updata(conn, sql , cust.getName(),cust.getEmail(),cust.getBirth(),cust.getId());
	}

	@Override
	public Customer getCustomerById(Connection conn, int id) {
		
		String sql = "select id,name,birth,email from customers where id = ?";
		Customer instance = getInstance(conn, Customer.class, sql , id);
		return instance;
	}

	@Override
	public List<Customer> getAll(Connection conn) {
		String sql = "select id,name,email,birth from customers";
		List<Customer> list = getListInstance(conn, Customer.class, sql);
		return list;
	}

	@Override
	public Long getCount(Connection conn) {
		String sql = "select count(*) from customers";
		Long count = getValue(conn, sql);
		return count;
	}

	@Override
	public Date getMaxBirth(Connection conn) {
		String sql = "select max(birth) from customers";
		return getValue(conn, sql);
	}

}

测试类

public class Customers {
	private CustomerDaoImpl dao = new CustomerDaoImpl();
	@Test
	public void testInsert() {
		Connection conn = null;
		try {
			conn = JdbcUtils.getConnection();
			Customer cust = new Customer(1,"新垣结衣","xin@qq.com",new Date(12312312L));
			dao.insert(conn, cust);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JdbcUtils.closeResource(conn, null);	
		}
	}

	@Test
	public void testDeleteById() {
		Connection conn = null;
		try {
			conn = JDBCUtil.getConnection1();
			dao.deleteById(conn, 33);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JdbcUtils.closeResource(conn, null);
		}
	}

	@Test
	public void testUpdate() {
		Connection conn = null;
		try {
			conn = JdbcUtils.getConnection();
			Customer cust = new Customer(32,"有村架纯","you@qq.com",new Date(21312312312L));
			dao.update(conn, cust);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JdbcUtils.closeResource(conn, null);
		}
	}

	@Test
	public void testGetCustomerById() {
		Connection conn = null;
		try {
			conn = JDBCUtil.getConnection3();
			Customer cuss = dao.getCustomerById(conn, 34);
			System.out.println(cuss);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JdbcUtils.closeResource(conn, null);
		}
	}

	@Test
	public void testGetAll() {
		Connection conn = null;
		try {
			conn = JdbcUtils.getConnection();
			List<Customer> all = dao.getAll(conn);
			Iterator<Customer> iterator = all.iterator();
			while(iterator.hasNext()) {
				System.out.println(iterator.next());
			}
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JdbcUtils.closeResource(conn, null);
		}
	}

	@Test
	public void testGetCount() {
		Connection conn = null;
		try {
			conn = JdbcUtils.getConnection();
			Long count = dao.getCount(conn);
			System.out.println(count);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JdbcUtils.closeResource(conn, null);
		}
	}

	@Test
	public void testGetMaxBirth() {
		Connection conn = null;
		try {
			conn = JdbcUtils.getConnection();
			Date maxBirth = dao.getMaxBirth(conn);
			System.out.println(maxBirth);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JdbcUtils.closeResource(conn, null);
		}
	}

}
已标记关键词 清除标记
相关推荐
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页