MySQL多表&JDBC

程序员文章站 2022-05-09 13:14:37

...

1、外键
主表：主键。从表：外键。
从表外键类型，必须与主表主键类型一致。
从表外键的值是对主表主键的引用。

注意：
从表外键不能添加主表中不存在的记录；
主表不能删除从表中已经引用的记录。

数据准备：

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)
);

insert into category(cid,cname) values('c001','家电');
insert into category(cid,cname) values('c002','服饰');
insert into category(cid,cname) values('c003','化妆品');

insert into product(pid,pname,price,category_id) values('p001','联想','5000','c001');
insert into product(pid,pname,price,category_id) values('p002','海尔','5000','c001');
insert into product(pid,pname,price,category_id) values('p003','雷神','5000','c001');
insert into product(pid,pname,price,category_id) values('p004','JACK JONES','800','c002');
insert into product(pid,pname,price,category_id) values('p005','真维斯','200','c002');
insert into product(pid,pname,price,category_id) values('p006','花花公子','440','c002');
insert into product(pid,pname,price,category_id) values('p007','劲霸','2000','c002');
insert into product(pid,pname,price,category_id) values('p008','香奈儿','800','c003');
insert into product(pid,pname,price,category_id) values('p009','相宜本草','200','c003');

添加约束：

alter table product add foreign key(category_id) references category(cid);

2、表和表的关系
一对多：主表和从表；
多对多：两个主表和一个从表。

//订单表
create table orders(
    oid varchar(32) primary key,
    totalprice double
);

//订单项表
create table orderitem(
    oid varchar(50),
    pid varchar(50)
);

//联合主键
alter table orderitem add primary key(oid,pid);

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

//订单表和product表的主外键关系
alter table orderitem add constraint orderitem1_product_fk foreign key(pid) references product(pid);

3、查询
3.1、多表查询
MySQL多表&JDBC
内连接：

SELECT * FROM category INNER JOIN product ON cid=category_id;

或者：

SELECT * FROM category c,product p WHERE c.cid = p.category_id;

外连接：

SELECT * FROM category LEFT JOIN product ON cid=category_id;
SELECT * FROM category RIGHT JOIN product ON cid=category_id;

左外连接：左表全部及两个表的交集；
内连接：查询两个表交集；
右外连接：右表全部及两个表的交集。

3.2、子查询
一条select语句结果作为另一条select语法一部分。

SELECT * FROM product WHERE category_id = (SELECT cid FROM category WHERE cname='化妆品');

4、工具类

工具类一

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class JDBCUtils_V1 {
    /**
     * 获取连接方法
     * @return
     */
    public static Connection getConnection() {
        Connection conn = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/web", "root", "******");
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }

    public static void release(Connection conn, PreparedStatement pstmt, ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (pstmt != null) {
            try {
                pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

工具类一的使用：

/**
 * 根据id查询用户信息
 */
@Test
public void testFindUserById() {
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
        // 1.获取连接
        conn = JDBCUtils_V1.getConnection();
        // 2.编写sql语句
        String sql = "select * from tbl_user where uid=?";
        // 3.获取执行sql语句对象
        pstmt = conn.prepareStatement(sql);
        // 4.设置参数
        pstmt.setInt(1, 2);
        // 5.执行查询操作
        rs = pstmt.executeQuery();
        // 6.处理结果集
        while (rs.next()) {
            System.out.println(rs.getString(2) + "----" + rs.getString("upassword"));
        }
        // 释放资源放在此处行么？【不行滴！】
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        // 7.释放资源
        JDBCUtils_V1.release(conn, pstmt, rs);
    }
}

工具类二
新建xx.properties文件：

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/web?useUnicode=true&characterEncoding=utf8
username=root
password=****

properties文件建议放在src下，一行一组数据，格式是key=value,value不支持中文。不要加空格。

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ResourceBundle;
public class JDBCUtils_V2 {
    private static String driver;
    private static String url;
    private static String username;
    private static String password; 
    /**
     * 静态代码块加载配置文件信息
     */
    static{
        ResourceBundle bundle = ResourceBundle.getBundle("xx");
        driver = bundle.getString("driver");
        url = bundle.getString("url");
        username = bundle.getString("username");
        password = bundle.getString("password");
    }

    /**
     * 获取连接方法
     * 
     * @return
     */
    public static Connection getConnection() {
        Connection conn = null;
        try {
            Class.forName(driver);
            conn = DriverManager.getConnection(url, username, password);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }

    public static void release(Connection conn, PreparedStatement pstmt, ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (pstmt != null) {
            try {
                pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

使用:

/**
 * 添加用户信息方法
 */
@Test
public void testAdd() {
    Connection conn = null;
    PreparedStatement pstmt = null;
    try {
        // 1.获取连接
        conn = JDBCUtils_V2.getConnection();
        // 2.编写sql语句
        String sql = "insert into tbl_user values(null,?,?)";
        // 3.获取执行sql语句对象
        pstmt = conn.prepareStatement(sql);
        // 4.设置参数
        pstmt.setString(1, "lisi");
        pstmt.setString(2, "hehe");
        // 5.执行插入操作
        int row = pstmt.executeUpdate();
        if (row > 0) {
            System.out.println("添加成功!");
        } else {
            System.out.println("添加失败!");
        }
    } catch (Exception e) {
        throw new RuntimeException(e);
    } finally {
        // 6.释放资源
        JDBCUtils_V2.release(conn, pstmt, null);
    }
}

工具类三
进一步对properties文件处理

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
import java.util.ResourceBundle;

public class JDBCUtils_V3 {
    private static String driver;
    private static String url;
    private static String username;
    private static String password;
    /**
     * 静态代码块加载配置文件信息
     */
    static {
        try {
            // 1.通过当前类获取类加载器
            ClassLoader classLoader = JDBCUtils_V3.class.getClassLoader();
            // 2.通过类加载器的方法获得一个输入流
            InputStream is = classLoader.getResourceAsStream("xx.properties");
            // 3.创建一个properties对象
            Properties props = new Properties();
            // 4.加载输入流
            props.load(is);
            // 5.获取相关参数的值
            driver = props.getProperty("driver");
            url = props.getProperty("url");
            username = props.getProperty("username");
            password = props.getProperty("password");
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取连接方法
     * 
     * @return
     */
    public static Connection getConnection() {
        Connection conn = null;
        try {
            Class.forName(driver);
            conn = DriverManager.getConnection(url, username, password);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }

    public static void release(Connection conn, PreparedStatement pstmt, ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (pstmt != null) {
            try {
                pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

使用

/**
 * 根据id更新用户信息方法
 */
@Test
public void testUpdateById() {
    Connection conn = null;
    PreparedStatement pstmt = null;
    try {
        // 1.获取连接
        conn = JDBCUtils_V3.getConnection();
        // 2.编写sql语句
        String sql = "update tbl_user set upassword=? where uid=?";
        // 3.获取执行sql语句对象
        pstmt = conn.prepareStatement(sql);
        // 4.设置参数
        pstmt.setString(1, "999");
        pstmt.setInt(2, 3);
        // 5.执行更新操作
        int row = pstmt.executeUpdate();
        if (row > 0) {
            System.out.println("更新成功!");
        } else {
            System.out.println("更新失败!");
        }
    } catch (Exception e) {
        throw new RuntimeException(e);
    } finally {
        // 6.释放资源
        JDBCUtils_V3.release(conn, pstmt, null);
    }
}

/**
 * 根据id删除信息方法
 */
@Test
public void testDeleteById() {
    Connection conn = null;
    PreparedStatement pstmt = null;
    try {
        // 1.获取连接
        conn = JDBCUtils_V3.getConnection();
        // 2.编写sql语句
        String sql = "delete from tbl_user where uid=?";
        // 3.获取执行sql语句对象
        pstmt = conn.prepareStatement(sql);
        // 4.设置参数
        pstmt.setInt(1, 4);
        // 5.执行删除操作
        int row = pstmt.executeUpdate();
        if (row > 0) {
            System.out.println("删除成功!");
        } else {
            System.out.println("删除失败!");
        }
    } catch (Exception e) {
        throw new RuntimeException(e);
    } finally {
        // 6.释放资源
        JDBCUtils_V3.release(conn, pstmt, null);
    }
}

MySQL多表&JDBC

spring boot tomcat jdbc pool的属性绑定

mysql认证都有什么（mysql数据库图形化界面）

Mysql存储引擎详解

mysql 存储过程判断重复的不插入数据

Mysql单文件存储删除数据文件容量不会减少的bug与解决方法

浅谈mysqldump使用方法(MySQL数据库的备份与恢复)

MySQL5.6 数据库主从同步安装与配置详解（Master/Slave）

浅谈MySQL数据库的备份与恢复

MySQL修改表一次添加多个列(字段)和索引的方法

mysql 行列动态转换的实现(列联表,交叉表)