DBUtiles中的简单使用(QueryRunner和ResultSetHandler的手动实现)

link

//通过返回影响的行数---判断是否插入成功

    public void addUser(User user) throws SQLException {
        String sql = "insert into user(username,PASSWORD,gender,email,telephone,introduce)values(?,?,?,?,?,?)";
        QueryRunner runner  = new QueryRunner(DataSourceUtils.getDataSource());
        int row=runner.update(sql,user.getUsername(),user.getPassword(),
                user.getGender(),user.getEmail(),user.getTelephone(),
                user.getIntroduce());
        if(row==0){
            throw new RuntimeException();
        }
    }

DBUtils已经帮我们实现了很多ResultSetHandler的实现类。
通过这些类可以很方便的对结果集进行封装。

ResultSetHandler的实现类

//返回一个JavaBean，看来是查询了，返回一个结果
//BeanHandler,将查询结果的第一行转换为一个JavaBean对象返回
//BeanHandler<JavaBean>
Account acc = runner.query("select * from account where name=?",new BeanHandler<Account>(Account.class) , "c");
System.out.println(acc);

//
public User login(String username, String password) throws SQLException {
        //-----login就是判断登陆是否成功，也就是看用户名和密码是否匹配
        //和找一条具体的product操作差不多
        String sql="select * from user where username=? and password=?";
        QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
        return runner.query(sql,new BeanHandler<User>(User.class),username,password);
    }
//
public Notice getRecentNotice() throws SQLException {
        String sql="select * from notice order by n_time desc limit 0,1";
        QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
        return runner.query(sql,new BeanHandler<Notice>(Notice.class));
    }

public Product findProductById(String id) throws SQLException {
        String sql = "select * from products where id=?";
        QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
        return runner.query(sql,new BeanHandler<Product>(Product.class), id);
    }

		//2.ArrayListHandler 将查询结果的每一行转换为一个Object[]数组，然后装入一个ArrayList集合
		//ResultSetHandler implementation that converts the ResultSet into a List of Object[]s. This class is thread safe.
		List<Object[]> list = runner.query("select * from account",new ArrayListHandler() );
		System.out.println(list);
 
		//手动实现ArrayListHandler的功能
		public static List test2() throws Exception{
		QueryRunner runner = new QueryRunner(new ComboPooledDataSource());
		return runner.query("select * from account where name=?",new ResultSetHandler<List<Account>>(){
			public List<Account> handle(ResultSet rs) throws SQLException {
				List<Account> list = new ArrayList<Account>();
				while(rs.next()){
					Account acc = new Account();
					acc.setId(rs.getInt("id"));
					acc.setName(rs.getString("name"));
					acc.setMoney(rs.getDouble("money"));
					list.add(acc);
				}
				return list;
			}
		} , "a");
	}

 public List<Order> findOrderByUser(User user) throws SQLException {
        String sql="select * from orders where user_id=?";
        QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
        return runner.query(sql, new ResultSetHandler<List<Order>>() {
            @Override
            public List<Order> handle(ResultSet rs) throws SQLException {
                List<Order> orders = new ArrayList<>();
                while(rs.next()){
                    Order order = new Order();
                    order.setId(rs.getString("id"));
                    order.setMoney(rs.getDouble("money"));
                    order.setOrdertime(rs.getDate("ordertime"));
                    order.setPaystate(rs.getInt("paystate"));
                    order.setReceiverAddress(rs.getString("receiverAddress"));
                    order.setReceiverName(rs.getString("receiverName"));
                    order.setReceiverPhone(rs.getString("receiverPhone"));
                    order.setUser(user);
                    orders.add(order);//list
                }
                return orders;
            }
        },user.getId());
    }



 //前台，获取本周热销商品
    public List<Object[]> getWeekHotProduct() throws SQLException {
        String sql = "SELECT products.id,products.name, "+
                " products.imgurl,SUM(orderitem.buynum) totalsalnum "+
                " FROM orderitem,orders,products "+
                " WHERE orderitem.order_id = orders.id "+
                " AND products.id = orderitem.product_id "+
                " AND orders.paystate=1 "+
                " AND orders.ordertime > DATE_SUB(NOW(), INTERVAL 7 DAY) "+
                " GROUP BY products.id,products.name,products.imgurl "+
                " ORDER BY totalsalnum DESC "+
                " LIMIT 0,2 ";
        QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
        return runner.query(sql, new ArrayListHandler());
    }