【JavaWeb】03-使用apache的commons-dbutils操作数据库

1. 主要的类：QueryRunner类的使用

    query():查询
    update()：更新（插入、删除、修改）
    insert()：插入（返回主键）

2. 接受返回值的ResultSetHander接口

    ArrayHandler：把结果集中的第一行数据存放到数组
    ArrayListHandler：把结果集中的每一行数据存放到数组集合
    BeanHandler：把结果集中的第一行数据封装到指定的实体类中
    BeanListHandler：把结果集中的每一行数据封装到指定的实体类集合中
    ColumnListHandler：把结果集中的某一列数据存放到list集合中
    ScalarHandler：一般用于获取聚合函数的统计结果

3. 以一个数据库中的Teacher表为例

这里注意：使用dbutils需要保证实体类和数据库的字段一致，不能使用驼峰命名

3.1 获取全部数据

	public List<Teacher> selectAll() throws SQLException {
		
		// 使用数据源作为参数创建QueryRunner对象
		QueryRunner qr = new QueryRunner(DataSourceUtil.getDataSource());
		
		// 使用query方法实现查询
		String sql = "select * from teacher";
		List<Teacher> teacherList = qr.query(sql, new BeanListHandler<>(Teacher.class));
		
		return teacherList;
	}

3.2 获取单条数据

	public Teacher selectOne() throws SQLException {
		
		QueryRunner qr = new QueryRunner(DataSourceUtil.getDataSource());
		
		String sql = "select * from teacher limit 0,1";
		Teacher result = qr.query(sql, new BeanHandler<>(Teacher.class));
		
		return result;
	}

3.3 统计数据库数据数量

	// 统计有多少老师
	public long selectCount() throws SQLException {
		QueryRunner qr = new QueryRunner(DataSourceUtil.getDataSource());
		
		String sql = "select count(*) from teacher";
		
		Object count = qr.query(sql, new ScalarHandler<>(2));
		
		return (Long)count;
	}

3.4 多条件查询

	public List<Teacher> selectAll(Teacher teacher) throws SQLException {
		QueryRunner qr = new QueryRunner(DataSourceUtil.getDataSource());
		
		String sql = "select * from teacher where 1 = 1";
		List<Object> params = new ArrayList<>();
		
		if (teacher.getId() != null) {
			sql += " and id = ?";
			params.add(teacher.getId());
		}
		if (teacher.getName() != null) {
			sql += " and name = ?";
			params.add(teacher.getName());
		}
		if (teacher.getSex() != null) {
			sql += " and sex = ?";
			params.add(teacher.getSex());
		}
		if (teacher.getClass_id() != null) {
			sql += " and class_id = ?";
			params.add(teacher.getClass_id());
		}
		
		List<Teacher> result = qr.query(sql, new BeanListHandler<>(Teacher.class), params.toArray());
		
		return result;
	}

3.5 插入数据使用主键返回

	public Teacher insert(Teacher teacher) throws SQLException {
		QueryRunner qr = new QueryRunner(DataSourceUtil.getDataSource());
		
		String sql = "insert into teacher(name,sex,birthday,class_id) value(?,?,?,?)";
		
		// 保存数据并实现主键返回，注意：返回的主键是BigInteger类型
		BigInteger id = (BigInteger)qr.insert(sql, new ScalarHandler<>(1), teacher.getName(), teacher.getSex(), teacher.getBirthday(), teacher.getClass_id());
		
		teacher.setId(id.longValue());
		
		return teacher;
	}

3.6 插入数据只返回插入结果

	public int insert(Teacher teacher) throws SQLException {
		QueryRunner qr = new QueryRunner(DataSourceUtil.getDataSource());
		
		String sql = "insert into teacher(name,sex,birthday,class_id) value(?,?,?,?)";
		
		int result = qr.update(sql, teacher.getName(), teacher.getSex(), teacher.getBirthday(), teacher.getClass_id());
		
		return result;
	}