09_MySQL多表 & JDBC

MySQL多表

外键

• 主表主键和从表外键 ==(从表的外键引用主表的主键)==
• 从表的外键类型必须和主表的主键类型一样
• 主键被外键引用的时候不能删(主表不能删除已经被从表引用的记录)
  • 不然从表的数据到主表里查不到
• 从表外键不能添加主表不存在的记录
• 外键作用
  • 保证数据的完整性

-- 分类表
create table category(
    cid varchar(32) primary key,
    cname varchar(100)
);

-- 商品表
create table product(
    pid varchar(32) primary key,
    pname varchar(40),
    price double,
    category_id varchar(32)
);

-- 添加外键
alter table 从表 add [constraint] [外键名] foreign key (从表外键字段名) references 主表 (主表主键);
alter table product add foreign key(category_id) references category(cid);
alter table product add constraint product_fk foreign key(category_id) references category(cid);

表与表之间关系

• 一对多关系
  • 主表主键被多个从表的外键引用
  • 部门--员工，分类--商品，客户--订单
• 多对多关系
  • 多对多关系需要创建第三者表,表中至少2个字段,这2个字段分别作为外键指向各自一方的主键
  • 学生--课程
• 一对一关系(了解)
  • 开发中不常用,可以做成一张表
  • 主表的主键唯一,主表的外键也唯一unique

创建表实现

• 代码实现多对多的关系
• product 和 订单表

--多对多
-- 订单表
CREATE TABLE orders(
    oid VARCHAR(32) PRIMARY KEY,
    totalprice DOUBLE
);

-- 订单项表
CREATE TABLE orderitem(
    oid VARCHAR(50),
    pid VARCHAR(50)
);

-- 商品表
create table product(
    pid varchar(32) primary key,
    pname varchar(40),
    price double,
    category_id varchar(32)
);

-- 联合主键(可省略)
alter table orderitem add primary key(oid,pid);

-- 订单表和订单项表的主外键关系
alter table orderitem add constraint orderitem_orders_fk foreign key(oid) references orders(oid);

-- 商品表和订单项表的主外键关系
alter table orderitem add constraint orderitem_product_fk foreign key(pid) references product(pid);

查询操作

• 查询分类
  1. 交叉连接查询(基本不会使用,得到的是两个表的乘积)
     • select * from A,B;
     • A表有n条记录,B表有m条记录,得到的结果为n*m条记录
  2. 内连接查询(使用关键字inner join --inner可以省略)
     • 隐式内连接: select * from A,B where 条件;
     • 显式内连接：select * fom A inner join B on 条件;
  3. 外连接查询(使用关键字outer join --outer可以省略)
     • 左外连接:left outer join
     • 右外连接:right outer join
     • 区别:
       • 左外连接:左表的全部以及两个表的交集(内连接)
       • 右外连接:右表的全部以及两个表的交集(内连接)

-- 隐式内链接查询
SELECT * FROM category,product WHERE category_id=cid;
SELECT * FROM category AS c,product AS p WHERE c.cid = p.category_id;
-- 显示内链接查询
SELECT * FROM category INNER JOIN product ON cid = category_id;
SELECT * FROM category AS c INNER JOIN product AS p ON c.cid = p.category_id;

-- 左右外连接查询
SELECT * FROM category LEFT OUTER JOIN product ON cid = category_id;
SELECT * FROM category AS c LEFT OUTER JOIN product AS p ON c.cid = p.category_id;

SELECT * FROM category RIGHT OUTER JOIN product ON cid = category_id;
SELECT * FROM category AS c RIGHT OUTER JOIN product AS p ON c.cid = p.category_id;

子查询

• 将一条select语句结果作为另一条select语法的一部分(查询条件,查询结果,表等)
  • 根据category表的"化妆品"字段搜索到它的cid,然后根据cid去product表里搜索所有的化妆品产品
• 开发中子查询用的概率没有内连接和外连接用的多

JDBC

抽取工具类

• 在数据库连接中,加载驱动,获取连接,释放资源的代码都是重复的,如果对数据库操作的方法多,就会使代码冗余量多

public class JDBCUtils {
    private static Connection conn = null;
    static {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://localhost:3306/mybase";
            String user = "root";
            String password = "root";
            conn = DriverManager.getConnection(url, user, password);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            throw new RuntimeException("加载数据库驱动失败!");
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException("连接数据库异常");
        }
    }
    private JDBCUtils(){}
    public static Connection getConnection() {
        return conn;
    }
}

使用properties配置文件

• 在开发中遇到的四个参数:驱动类型,数据库url,用户名,密码通常写在配置文件中,方便后期的维护和更新数据库
• 如果不使用配置文件,通过硬编码的形式获取数据库连接,交付工程后客户如果需要修改数据库类型或者修改数据库信息,则需要重新给一份bin/class文件
• 通过配置文件,就不需要动代码了
• 位置: src文件下
  • 如果是web程序,则放在classpath文件夹下
• 文件按扩展名必须是properties
• 文件内容:
  • 一行一组数据: key=value
  • 不要有空格
  • 不支持中文,只能使用英文

db.properties文件内容:

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/dbname
user=root
password=root

通过ResourceBundle加载配置文件

• ResourceBundle专门用于处理properties文件的
• 读取的文件名不需要加后缀

ResourceBundle rb = ResourceBundle.getBundle("fileName");//不需要加后缀
String value = rb.getString("key");

通过Properties加载配置文件

• Properties类也用于处理properties文件的
• 读取的文件名需要加后缀

//获取类加载器
static {
    InputStream is = currentClass.class.getClassLoader().getRescoreAsStream("fileName.propertyes");
    Properties prop = new Propertyes();
    prop.load(is);
    String value = prop.getProperties("key");
}