MySQL案例练习

一.创建数据库和数据库表,插入数据.

CREATE TABLE USER (
 uid VARCHAR(32) PRIMARY KEY,  -- 用户ID
 username VARCHAR(20) , -- 用户名
PASSWORD VARCHAR(20) , -- 密码
telephone VARCHAR(20) , -- 电话
birthday DATE , -- 生日
sex VARCHAR(10) -- 性别
);
INSERT INTO USER VALUES 
('001','渣渣辉','123456','13511112222','2015-11-04','男'),
 ('002','药水哥','123456','13533334444','1990-02-01','男'),
 ('003','大明白','123456','13544445555','2015-11-03','男'),
 ('004','长海','123456','13566667777','2000-02-01','男'),
 ('005','乔杉','123456','13588889999','2000-02-01','男');
CREATE TABLE orders (
  oid VARCHAR(32)   PRIMARY KEY, -- 订单id
  ordertime DATETIME ,  -- 下单时间 
  total DOUBLE , -- 总金额
  NAME VARCHAR(20), -- 收货人姓名
  telephone VARCHAR(20) , -- 电话
  address VARCHAR(30) , -- 地址
  state INT(11) ,  -- 订单状态
  uid VARCHAR(32), -- 外键字段 对应用户表id
  CONSTRAINT ofk_0001 FOREIGN KEY (uid) REFERENCES USER 
(uid)
 );
 
 -- 插入一条订单数据
INSERT INTO orders 
VALUES('order001','2019-10-11',5500,'乔
杉','15512342345','皇家洗浴',0,'001');

 CREATE TABLE category (
  cid VARCHAR(32) PRIMARY KEY,
  cname VARCHAR(20)
 ); 
 
 INSERT INTO `category` VALUES ('1','手机数码'),('2','电脑办
公'),('3','运动鞋服'),('4','图书音像');`category`


CREATE TABLE product (
  pid VARCHAR(32)  PRIMARY KEY, -- 商品id
  pname VARCHAR(50) , -- 商品名称 
  price DOUBLE, -- 商品价格
  pdesc VARCHAR(255), -- 商品描述
  pflag INT(11) , -- 商品状态 1 上架 ,0 下架
  cid VARCHAR(32) , -- 外键对应 分类表id
  KEY sfk_0001 (cid), 
  CONSTRAINT sfk_0001 FOREIGN KEY (cid) REFERENCES 
category (cid)
 );
 
 INSERT INTO `product` VALUES 
('1','小米6',2200,'小米 移动联通电信4G手机 双卡双待',0,'1'),
 ('2','华为Mate9',2599,'华为 双卡双待 高清大屏',0,'1'),
 ('3','OPPO11',3000,'移动联通 双4G手机',0,'1'),
 ('4','华为荣耀',1499,'3GB内存标准版 黑色 移动4G手机',0,'1'),
 ('5','华硕台式电脑',5000,'爆款直降，满千减百',0,'2'),
 ('6','MacBook',6688,'128GB 闪存',0,'2'),
 ('7','ThinkPad',4199,'轻薄系列1)',0,'2'),
 ('8','联想小新',4499,'14英寸超薄笔记本电脑',0,'2'),
 ('9','李宁音速6',500,'实战篮球鞋',0,'3'),
 ('10','AJ11',3300,'乔丹实战系列',0,'3'),
 ('11','AJ1',5800,'精神小伙系列',0,'3');
 
 
 
 
 -- 订单项表
CREATE TABLE orderitem (
  itemid VARCHAR(32) PRIMARY KEY, -- 订单项ID
  pid VARCHAR(32),  -- 外键 对应商品表 id
  oid VARCHAR(32), -- 外键 对应订单表 id
  KEY fk_0001 (pid),
  KEY fk_0002 (oid),
  CONSTRAINT fk_0001 FOREIGN KEY (pid) REFERENCES product 
(pid),
  CONSTRAINT fk_0002 FOREIGN KEY (oid) REFERENCES orders 
(oid)
 );
 
 -- 向中间表中插入两条数据
INSERT INTO orderitem VALUES('item001','1','order001');
 INSERT INTO orderitem VALUES('item002','11','order001');

二.分析表和表的关系

用户表和订单表(一对多):

分类表和产品表(一对多):

订单表和产品表(多对多,需要中间表(中间项表)):

订单项表记录一个订单有多少商品

总关系图:

三.环境搭建

创建项目结构

导入jar包

导入配置文件以及工具类

四.JavaBean类创建

1.1 设计用户与订单

User类:

package com.entity;

public class User {
    private String uid;
    private String username;
    private String password;
    private String telephone;
    private String birthday;
    private String sex;

    public String getUid() {
        return uid;
    }

    public void setUid(String uid) {
        this.uid = uid;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getTelephone() {
        return telephone;
    }

    public void setTelephone(String telephone) {
        this.telephone = telephone;
    }

    public String getBirthday() {
        return birthday;
    }

    public void setBirthday(String birthday) {
        this.birthday = birthday;
    }

    public String getSex() {
        return sex;
    }

    @Override
    public String toString() {
        return "User{" +
                "uid='" + uid + ''' +
                ", username='" + username + ''' +
                ", password='" + password + ''' +
                ", telephone='" + telephone + ''' +
                ", birthday='" + birthday + ''' +
                ", sex='" + sex + ''' +
                '}';
    }

    public void setSex(String sex) {
        this.sex = sex;

    }
}

Orders类:

package com.entity;

public class Orders {
    private String oid; //订单号
    private String ordertime; //下单时间
    private double total; //订单的总金额
    private String name; //收货人姓名
    private String telephone; //收货人电话
    private String address;  //收货人地址
    private int state; //订单状态 1 代表已支付 , 0 代表未支付
    //订单属于哪个用户呢 ?
    private String uid; // 表示外键
    private User user; //用来保存订单对应的详细用户信息
    //提供 get set toString方法


    @Override
    public String toString() {
        return "Orders{" +
                "oid='" + oid + ''' +
                ", ordertime='" + ordertime + ''' +
                ", total=" + total +
                ", name='" + name + ''' +
                ", telephone='" + telephone + ''' +
                ", address='" + address + ''' +
                ", state=" + state +
                ", uid='" + uid + ''' +
                '}';
    }

    public String getOid() {
        return oid;
    }

    public void setOid(String oid) {
        this.oid = oid;
    }

    public String getOrdertime() {
        return ordertime;
    }

    public void setOrdertime(String ordertime) {
        this.ordertime = ordertime;
    }

    public double getTotal() {
        return total;
    }

    public void setTotal(double total) {
        this.total = total;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getTelephone() {
        return telephone;
    }

    public void setTelephone(String telephone) {
        this.telephone = telephone;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public int getState() {
        return state;
    }

    public void setState(int state) {
        this.state = state;
    }

    public String getUid() {
        return uid;
    }

    public void setUid(String uid) {
        this.uid = uid;
    }

    public User getUser() {
        return user;
    }

    public void setUser(User user) {
        this.user = user;
    }
}

1.2 设计商品与分类

package com.entity;

public class Product {
    private String pid;
    private String pname;
    private double price;
    private String pdesc;
    private int pflag; //是否上架 1 上架 ,0 下架
    private String cid; //外键 对应分类表主键
    private Category category; //用于保存Category的详细数据

    @Override
    public String toString() {
        return "Product{" +
                "pid='" + pid + ''' +
                ", pname='" + pname + ''' +
                ", price=" + price +
                ", pdesc='" + pdesc + ''' +
                ", pflag=" + pflag +
                ", cid='" + cid + ''' +
                ", category=" + category +
                '}';
    }

    public String getPid() {
        return pid;
    }

    public void setPid(String pid) {
        this.pid = pid;
    }

    public String getPname() {
        return pname;
    }

    public void setPname(String pname) {
        this.pname = pname;
    }

    public double getPrice() {
        return price;
    }

    public void setPrice(double price) {
        this.price = price;
    }

    public String getPdesc() {
        return pdesc;
    }

    public void setPdesc(String pdesc) {
        this.pdesc = pdesc;
    }

    public int getPflag() {
        return pflag;
    }

    public void setPflag(int pflag) {
        this.pflag = pflag;
    }

    public String getCid() {
        return cid;
    }

    public void setCid(String cid) {
        this.cid = cid;
    }

    public Category getCategory() {
        return category;
    }

    public void setCategory(Category category) {
        this.category = category;
    }
    //提供 get set toString方法
}

1.3 设计订单项

package com.entity;

/**
 * 订单项表 表示的是 一个订单中所有的商品的信息的
 */
public class Orderitem {

    private String itemid; //订单项ID
    private String pid;//外键 指向商品表的主键
    private String old;//外键 指向了订单表的主键
    private Product product;//保存订单项中商品信息
    private Orders orders;//订单项所属的 订单的详细信息

    @Override
    public String toString() {
        return "Orderitem{" +
                "itemid='" + itemid + ''' +
                ", pid='" + pid + ''' +
                ", old='" + old + ''' +
                ", product=" + product +
                ", orders=" + orders +
                '}';
    }

    public String getItemid() {
        return itemid;
    }

    public void setItemid(String itemid) {
        this.itemid = itemid;
    }

    public String getPid() {
        return pid;
    }

    public void setPid(String pid) {
        this.pid = pid;
    }

    public String getOld() {
        return old;
    }

    public void setOld(String old) {
        this.old = old;
    }

    public Product getProduct() {
        return product;
    }

    public void setProduct(Product product) {
        this.product = product;
    }

    public Orders getOrders() {
        return orders;
    }

    public void setOrders(Orders orders) {
        this.orders = orders;
    }
}

五.编写DAO类

1.1 编写UserDao

用户注册和登陆的DAO类

package com.dao;

import com.entity.User;
import com.utils.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;

import java.sql.SQLException;

public class UserDao {
    //需求一:编写一个注册用户的方法,接收的参数是一个user对象
    public int register(User user) throws SQLException {
        //1.获取QueryRunner对象
        QueryRunner qr = new QueryRunner(DruidUtils.dataSource);
        //2.编写SQL
        String sql = "insert into user values(?,?,?,?,?,?)";
        Object[] param = {user.getUid(),
                user.getUsername(),
                user.getPassword(),
                user.getTelephone(),
                user.getBirthday(),
                user.getSex()};
        //3.执行插入操作
        int update = qr.update(sql, param);
        return update;


    }

    //需求二:编写一个用户登录的方法,接受的参数是 用户名 和 密码, 返回值是User对象
    public User login(String username,String password) throws SQLException {
        //1.获取QueryRunner对象
        QueryRunner qr =new QueryRunner(DruidUtils.dataSource);
        //2.编写SQL
        String sql = "select * from user where username = ? and password = ?";
        //3.执行查询 使用BeanHandLer来封装结果集,获取结果集中的第一条数据
        User user = qr.query(sql,new BeanHandler<User>(User.class),username,password);
        return user;




    }
}

测试登录和注册

package com.ceshi;

import com.dao.UserDao;
import com.entity.User;
import com.utils.DateUtils;
import com.utils.UUIDUtils;

import java.sql.SQLException;

public class TestUserDao {
    public static void main(String[] args) {
        try {
            new TestUserDao().testlogin();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    UserDao userDao = new UserDao();
    //测试注册用户
    public void testRegister() throws SQLException {
        //1.创建User
        User user = new User();
        user.setUid(UUIDUtils.getUUID());
        user.setUsername("武松");
        user.setPassword("123123");
        user.setTelephone("123456789");
        user.setSex("男");
        user.setBirthday(DateUtils.getDateFormart());
        //2.执行注册
        int register = userDao.register(user);
        if(register > 0){
            System.out.println("注册成功!"+user.getUsername());


        }else {
            System.out.println("注册失败!");
        }

    }
    //2.测试用户登录
    public void testlogin() throws SQLException {
        //1.调用UserDao的Login的方法 传入用户名和密码
        User user = userDao.login("武松","123123");
        //2.判断User是否为空
        if(user != null){
            System.out.println("欢迎您!:"+user.getUsername());
        }else{
            System.out.println("用户名或密码错误!登陆失败!");
        }


    }
}

1.2 编写ProductDao

package com.dao;

import com.entity.Category;
import com.entity.Product;
import com.utils.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

public class ProductDao {
    //需求1:根据商品ID 获取商品名称 商品价格 以及商品所属分类的名称
    public Product findProductById(String pid) throws SQLException {
        //1.创建QueryRunner
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
        //2.编写SQL
        String sql= "select * from product where pid = ?";
        //3.执行查询
        Product product = qr.query(sql, new BeanHandler<Product>(Product.class), pid);
        //4.获取外键的值  对应分类的ID
        String cid = product.getCid();
        //商品对应的分类信息
        Category category = findCategoryById(cid);

        product.setCategory(category);
        return product;

    }
    //根据分类ID 查询分类的详细信息
    public Category findCategoryById(String cid) throws SQLException {
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());

        String sql = "select * from category where cid = ?";

        Category category = qr.query(sql,new BeanHandler<Category>(Category.class),cid);
        return category;




    }

    //需求3:查询指定分类ID下的商品个数
    public int getCount(String cid) throws SQLException {
            QueryRunner qr =new QueryRunner(DruidUtils.getDataSource());
            String sql  ="select count(*) from product where cid = ?";
        //获取单列数据 使用ScalarHandLer 来封装
        Long count = qr.query(sql, new ScalarHandler<>(), cid);
        //将Long类型的包装类 转换为int类型 并返回
        return count.intValue();


    }
    //需求4：查询指定分类ID下的所有商品信息
    public List<Product> findProductByCid(String cid) throws SQLException {
        QueryRunner qr =new QueryRunner(DruidUtils.getDataSource());
        String sql = "select * from product where cid = ?";
        //查询结果是一个list集合,使用BeanListHandler来封装
        List<Product> list = qr.query(sql, new BeanListHandler<Product>(Product.class), cid);
        return list;
    }
}

测试

package com.ceshi;

import com.dao.ProductDao;
import com.entity.Product;

import java.sql.SQLException;
import java.util.List;

public class TestProductDao {
    public static void main(String[] args) {
        try {
            new TestProductDao().testfindProductByCid();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    ProductDao productDao = new ProductDao();
    //测试 根据商品ID 获取商品名称 商品价格 以及商品所属分类的名称
    public void testfindProductById() throws SQLException {
        //1.调用方法获取商品对象
        Product product = productDao.findProductById("1");
        //2.打印信息
        System.out.println(product.getPname()+" "+product.getPrice()+" "+ product.getCategory().getCname());
    }

            //测试 查询指定分类ID下的商品个数
    public void testgetCount() throws SQLException {
        //查询分类ID为3的分类下有几个商品
        int count = productDao.getCount("3");
        System.out.println("分类ID为3的商品个数是:"+count);



    }
    //测试 指定分类id下的所有商品信息
    public void testfindProductByCid() throws SQLException {
        //查询类ID为2的所有商品信息
        List<Product> list = productDao.findProductByCid("2");
        for (Product product : list) {
            System.out.println(product);

        }


    }
}

六.多对一关系

七.编写OrderDao

package com.dao;

import com.entity.Orderitem;
import com.entity.Orders;
import com.entity.Product;
import com.utils.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class OrdersDao {

    //需求1:获取uid为001的用户的所有订单信息
    public List<Orders> findALLOrders(String uid) throws SQLException {
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
        String sql = "select * from orders where uid = ?";
        List<Orders> ordersList = qr.query(sql, new BeanListHandler<Orders>(Orders.class), uid);
        return ordersList;

    }
    //需求2:获取订单编号为order001的订单中的所有商品信息

    public List<Product> findOrderById(String oid) throws SQLException {
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
        //1.查询订单项表 获取订单详表中 订单id为order001的数据
        String sql = "SELECT pid FROM orderitem WHERE oid = ?";
        //2.查询的结果是 多条 订单项数据
        List<Orderitem> list = qr.query(sql,new BeanListHandler<Orderitem>(Orderitem.class),oid);
        //3.创建集合保存商品信息
        List<Product> productList = new ArrayList<>();
        ProductDao productDao = new ProductDao();
        //4.遍历订单项集合 获取Pid
        for (Orderitem orderitem : list) {
            //4.1从orderitem中获取pid
            String pid = orderitem.getPid();
            // 4.2调用productDao
            Product product = productDao.findProductById(pid);
            //4.3 保存到集合
            productList.add(product);

        }
        //返回 订单中对应的信息
            return productList;
    }

}

测试代码：

package com.ceshi;

import com.dao.OrdersDao;
import com.entity.Orders;
import com.entity.Product;

import java.sql.SQLException;
import java.util.List;

public class TestOrdersDao {
    public static void main(String[] args) {
        try {
            new TestOrdersDao().testFindOrderById();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

        OrdersDao ordersDao = new OrdersDao();
        public void testFindAllOrders() throws SQLException {
            List<Orders> allOrders = ordersDao.findALLOrders("001");
            for (Orders Orders : allOrders) {
                System.out.println(Orders);
            }


        }
            //测试 获取订单编号为order001的订单中的所有商品信息
    public void testFindOrderById() throws SQLException {
        List<Product> order001 = ordersDao.findOrderById("order001");
        for (Product product : order001) {
            System.out.println(product);

        }

    }

    }