期末大作业基于Javaweb开发的美妆商城系统源码+数据库sql.zip

package cn.store.dao; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import org.apache.commons.dbutils.QueryRunner; import org.apache.commons.dbutils.ResultSetHandler; import cn.store.domain.Order; import cn.store.domain.User; import cn.store.utils.DataSourceUtils; /** * 订单 * @author admin * */ public class OrderDao { /** * 生成订单 * @param order * @throws SQLException */ public void addProduct(Order order) throws SQLException { // 1.生成Sql语句 String sql = "insert into orders values(?,?,?,?,?,0,null,?)"; // 2.生成执行sql语句的QueryRunner,不传递参�? QueryRunner runner = new QueryRunner(); // 3.执行update()方法插入数据 runner.update(DataSourceUtils.getConnection(), sql, order.getId(), order.getMoney(), order.getReceiverAddress(), order .getReceiverName(), order.getReceiverPhone(), order .getUser().getId()); } /** * 查找用户�?��订单 */ public List<Order> findOrderByUser(final 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>>() { public List<Order> handle(ResultSet rs) throws SQLException { List<Order> orders = new ArrayList<Order>(); 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); } return orders; } }, user.getId()); } /** * 根据id查找订单信息 * @param id * @return * @throws SQLException */ public Order findOrderById(String id) throws SQLException { String sql = "select * from orders,user where orders.user_id=user.id and orders.id=?"; QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource()); return runner.query(sql, new ResultSetHandler<Order>() { public Order handle(ResultSet rs) throws SQLException { Order order = new Order(); while (rs.next()) { order.setId(rs.getString("orders.id")); order.setMoney(rs.getDouble("orders.money")); order.setOrdertime(rs.getDate("orders.ordertime")); order.setPaystate(rs.getInt("orders.paystate")); order.setReceiverAddress(rs.getString("orders.receiverAddress")); order.setReceiverName(rs.getString("orders.receiverName")); order.setReceiverPhone(rs.getString("orders.receiverPhone")); User user = new User(); user.setId(rs.getInt("user.id")); user.setGender(rs.getString("user.gender")); user.setIntroduce(rs.getString("user.introduce")); user.setPassword(rs.getString("user.password")); user.setRegistTime(rs.getDate("user.registtime")); user.setRole(rs.getString("user.role")); user.setTelephone(rs.getString("user.telephone")); user.setUsername(rs.getString("user.username")); order.setUser(user); } return order; } }, id); } /** * 查找�?��订单 * @return * @throws SQLException */ public List<Order> findAllOrder() throws SQLException { //1.创建sql String sql = "select orders.*,user.* from orders,user where user.id=orders.user_id order by orders.user_id"; //2.创建QueryRunner对象 QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource()); //3.返回QueryRunner对象query()方法的查询结�? return runner.query(sql, new ResultSetHandler<List<Order>>() { public List<Order> handle(ResultSet rs) throws SQLException { //创建订单集合 List<Order> orders = new ArrayList<Order>(); //循环遍历订单和用户信�? while (rs.next()) { Order order = new Order(); order.setId(rs.getString("orders.id")); order.setMoney(rs.getDouble("orders.money")); order.setOrdertime(rs.getDate("orders.ordertime")); order.setPaystate(rs.getInt("orders.paystate")); order.setReceiverAddress(rs.getString("orders.receiverAddress")); order.setReceiverName(rs.getString("orders.receiverName")); order.setReceiverPhone(rs.getString("orders.receiverPhone")); orders.add(order); User user = new User(); user.setId(rs.getInt("user.id")); user.setGender(rs.getString("user.gender")); user.setIntroduce(rs.getString("user.introduce")); user.setPassword(rs.getString("user.password")); user.setRegistTime(rs.getDate("user.registtime")); user.setRole(rs.getString("user.role")); user.setTelephone(rs.getString("user.telephone")); user.setUsername(rs.getString("user.username")); order.setUser(user); } return orders; } }); } /** * 根据订单号修改订单状�? * @param id * @throws SQLException */ public void updateOrderState(String id) throws SQLException { String sql = "update orders set paystate=1 where id=?"; QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource()); runner.update(sql, id); System.out.println(runner.update(sql, id)); } /** * 多条件查�? * @param id * @param receiverName * @return * @throws SQLException */ public List<Order> findOrderByManyCondition(String id, String receiverName) throws SQLException { //1.创建集合对象 List<Object> objs = new ArrayList<Object>(); //2.定义查询sql String sql = "select orders.*,user.* from orders,user where user.id=orders.user_id "; //3.根据参数拼接sql语句 if (id != null && id.trim().length() > 0) { sql += " and orders.id=?"; objs.add(id); } if (receiverName != null && receiverName.trim().length() > 0) { sql += " and receiverName=?"; objs.add(receiverName); } sql += " order by orders.user_id"; //4.创建QueryRunner对象 QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource()); //5.返回QueryRunner对象query方法的执行结�? return runner.query(sql, new ResultSetHandler<List<Order>>() { public List<Order> handle(ResultSet rs) throws SQLException { List<Order> orders = new ArrayList<Order>(); //循环遍历出订单和用户信息 while (rs.next()) { Order order = new Order(); order.setId(rs.getString("orders.id")); order.setMoney(rs.getDouble("orders.money")); order.setOrdertime(rs.getDate("orders.ordertime")); order.setPaystate(rs.getInt("orders.paystate")); order.setReceiverAddress(rs .getString("orders.receiverAddress")); order.setReceiverName(rs.getString("orders.receiverName")); order.setReceiverPhone(rs.getString("orders.receiverPhone")); orders.add(order); User user = new User(); user.setId(rs.getInt("user.id")); user.setGender(rs.getString("user.gender")); user.setIntroduce(rs.getString("user.introduce")); user.setPassword(rs.getString("user.password")); user.setRegistTime(rs.getDate("user.registtime")); user.setRole(rs.getString("user.role")); user.setTelephone(rs.getString("user.telephone")); user.setUsername(rs.getString("user.username")); order.setUser(user); } return orders; } }, objs.toArray()); } /** * 根据id删除订单 * @param id * @throws SQLException */ public void delOrderById(String id) throws SQLException { String sql="delete from orders where id=?"; QueryRunner runner = new QueryRunner(); runner.update(DataSourceUtils.getConnection(),sql,id); } }