以下操作环境基于MySQL8.0.27，JUint4单元测试

解决IDEA中无法在JUint单元测试运行时，从键盘读取字符的方法：找到【Help】–> 【Edit Custom VM Options】

再添加如下语句，重启IDEA就生效：

-Deditable.java.test.console=true

1、创建数据库test01_bookstore

2、创建如下表格

（1）图书表books

（2）用户表users

（3）订单表orders

4）订单明细表order_items

3、使用sql语句在命令行或SQLyog中添加一些模拟数据

表books：

表users：

表orders：

表order_items：

#创建数据库
CREATE DATABASE `test01_bookstore` CHARACTER SET utf8;
USE `test01_bookstore`;
#创建表
CREATE TABLE `books` (
  `id` INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(100) NOT NULL,
  `author` VARCHAR(100) NOT NULL,
  `price` DOUBLE(11,2) NOT NULL,
  `sales` INT(11) NOT NULL,
  `stock` INT(11) NOT NULL,
  `img_path` VARCHAR(100) NOT NULL
) ENGINE=INNODB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8;
/*Data for the table `books` */
INSERT  INTO `books`(`id`,`title`,`author`,`price`,`sales`,`stock`,`img_path`) VALUES 
(1,'解忧杂货店','东野圭吾',27.20,102,98,'upload/books/解忧杂货店.jpg'),
(2,'边城','沈从文',23.00,102,98,'upload/books/边城.jpg'),
(3,'中国哲学史','冯友兰',44.50,101,99,'upload/books/中国哲学史.jpg'),
(4,'忽然七日',' 劳伦',19.33,101,99,'upload/books/忽然七日.jpg'),
(5,'苏东坡传','林语堂',19.30,100,100,'upload/books/苏东坡传.jpg'),
(6,'百年孤独','马尔克斯',29.50,100,100,'upload/books/百年孤独.jpg'),
(7,'扶桑','严歌苓',19.80,100,100,'upload/books/扶桑.jpg'),
(8,'给孩子的诗','北岛',22.20,100,100,'upload/books/给孩子的诗.jpg'),
(9,'为奴十二年','所罗门',16.50,100,100,'upload/books/为奴十二年.jpg'),
(10,'平凡的世界','路遥',55.00,100,100,'upload/books/平凡的世界.jpg'),
(11,'悟空传','今何在',14.00,100,100,'upload/books/悟空传.jpg'),
(12,'硬派健身','斌卡',31.20,100,100,'upload/books/硬派健身.jpg'),
(13,'从晚清到民国','唐德刚',39.90,100,100,'upload/books/从晚清到民国.jpg'),
(14,'三体','刘慈欣',56.50,100,100,'upload/books/三体.jpg'),
(15,'看见','柴静',19.50,100,100,'upload/books/看见.jpg'),
(16,'活着','余华',11.00,100,100,'upload/books/活着.jpg'),
(17,'小王子','安托万',19.20,100,100,'upload/books/小王子.jpg'),
(18,'我们仨','杨绛',11.30,100,100,'upload/books/我们仨.jpg'),
(19,'生命不息,折腾不止','罗永浩',25.20,100,100,'upload/books/生命不息.jpg'),
(20,'皮囊','蔡崇达',23.90,100,100,'upload/books/皮囊.jpg'),
(21,'恰到好处的幸福','毕淑敏',16.40,100,100,'upload/books/恰到好处的幸福.jpg'),
(22,'大数据预测','埃里克',37.20,100,100,'upload/books/大数据预测.jpg'),
(23,'人月神话','布鲁克斯',55.90,100,100,'upload/books/人月神话.jpg'),
(24,'C语言入门经典','霍尔顿',45.00,100,100,'upload/books/C语言入门经典.jpg'),
(25,'数学之美','吴军',29.90,100,100,'upload/books/数学之美.jpg'),
(26,'Java编程思想','埃史尔',70.50,100,100,'upload/books/Java编程思想.jpg'),
(27,'设计模式之禅','秦小波',20.20,100,100,'upload/books/设计模式之禅.jpg'),
(28,'图解机器学习','杉山将',33.80,100,100,'upload/books/图解机器学习.jpg'),
(29,'艾伦图灵传','安德鲁',47.20,100,100,'upload/books/艾伦图灵传.jpg'),
(30,'教父','马里奥普佐',29.00,100,100,'upload/books/教父.jpg');
CREATE TABLE `users` (
  `id` INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(100) UNIQUE KEY NOT NULL,
  `password` VARCHAR(100) NOT NULL,
  `email` VARCHAR(100) DEFAULT NULL
) ENGINE=INNODB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
/*Data for the table `users` */
INSERT  INTO `users`(`id`,`username`,`password`,`email`) VALUES
 (1,'admin1',SHA('123456'),'admin1@gmail.com'),
 (2,'admin2',SHA('123456'),'admin2@gmail.com'),
 (3,'admin3',SHA('123456'),'admin3@gmail.com'),
 (4,'chai',SHA('123456'),'chai@gmail.com');
CREATE TABLE `orders` (
  `id` VARCHAR(100) PRIMARY KEY NOT NULL,
  `order_time` DATETIME NOT NULL,
  `total_count` INT(11) NOT NULL,
  `total_amount` DOUBLE(11,2) NOT NULL,
  `state` INT(11) NOT NULL,
  `user_id` INT(11) NOT NULL,
  CONSTRAINT `orders_users_id_fk` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
/*Data for the table `orders` */
INSERT  INTO `orders`(`id`,`order_time`,`total_count`,`total_amount`,`state`,`user_id`) VALUES 
('15275760194821','2018-05-29 14:40:19',4,114.03,2,1),
('15294258455691','2018-06-20 00:30:45',2,50.20,0,1);
CREATE TABLE `order_items` (
  `id` INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `count` INT(11) NOT NULL,
  `amount` DOUBLE(11,2) NOT NULL,
  `title` VARCHAR(100) NOT NULL,
  `author` VARCHAR(100) NOT NULL,
  `price` DOUBLE(11,2) NOT NULL,
  `img_path` VARCHAR(100) NOT NULL,
  `order_id` VARCHAR(100) NOT NULL,
  CONSTRAINT `order_items_orders_id_fk` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE
) ENGINE=INNODB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
/*Data for the table `order_items` */
INSERT  INTO `order_items`
(`id`,`count`,`amount`,`title`,`author`,`price`,`img_path`,`order_id`) VALUES 
(1,1,27.20,'解忧杂货店','东野圭吾',27.20,'static/img/default.jpg','15275760194821'),
(2,1,23.00,'边城','沈从文',23.00,'static/img/default.jpg','15275760194821'),
(3,1,44.50,'中国哲学史','冯友兰',44.50,'static/img/default.jpg','15275760194821'),
(4,1,19.33,'忽然七日',' 劳伦',19.33,'static/img/default.jpg','15275760194821'),
(5,1,27.20,'解忧杂货店','东野圭吾',27.20,'static/img/default.jpg','15294258455691'),
(6,1,23.00,'边城','沈从文',23.00,'static/img/default.jpg','15294258455691');

4、使用JDBC实现往用户表中添加1个用户，注意密码存储使用mysql的SHA()函数进行加密

jdbc1.properties文件位于src目录下

user=root
password=root
url=jdbc:mysql://localhost:3306/test01_bookstore
driverClass=com.mysql.jdbc.Driver

    //TODO 使用JDBC实现往用户表中添加1个用户，注意密码存储使用mysql的SHA()函数进行加密
    @Test
    public void test1() throws Exception {
        Scanner s = new Scanner(System.in);
        System.out.println("username:");
        String username = s.next();
        System.out.println("password:");
        String pwd = s.next();
        System.out.println("email:");
        String email = s.next();
        // 1.读取配置文件中的4个基本信息
        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc1.properties");
        Properties pros = new Properties();
        pros.load(is);
        String user = pros.getProperty("user");
        String password = pros.getProperty("password");
        String url = pros.getProperty("url");
        String driverClass = pros.getProperty("driverClass");
        // 2.加载驱动
        Class.forName(driverClass);
        // 3.获取连接
        Connection conn = DriverManager.getConnection(url, user, password);
        String sql = "insert into users(id,username,password,email) values(null,?,SHA(?),?)";
        //4.预编译sql语句，返回PreparedStatement的实例
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, username);
        ps.setString(2, pwd);
        ps.setString(3, email);
        int i = ps.executeUpdate();
        System.out.println(i > 0 ? "添加成功" : "添加失败");
        System.out.println("录入成功！");
        ps.close();
        conn.close();
    }

5、使用JDBC实现往图书表中添加1本图书

    //通用的增删改操作
    public static void update(String sql, Object... args) {
        Connection conn = null;
        PreparedStatement ps= null;
        try {
            // 1.读取配置文件中的4个基本信息
            InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc1.properties");
            Properties pros = new Properties();
            pros.load(is);
            String user = pros.getProperty("user");
            String password = pros.getProperty("password");
            String url = pros.getProperty("url");
            String driverClass = pros.getProperty("driverClass");
            // 2.加载驱动
            Class.forName(driverClass);
            // 3.获取连接
            conn = DriverManager.getConnection(url, user, password);
            //4.预编译sql语句，返回PreparedStatement的实例
ps= conn.prepareStatement(sql);
            //5.填充占位符
for (int i =0; i < args.length; i++) {
ps.setObject(i +1, args[i]);
            }
            //6.执行操作
ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //7.资源的关闭
            try {
if (ps != null)
ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
if (conn != null)
                    conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    //TODO 使用JDBC实现往图书表中添加1本图书
    @Test
    public void test2() {
        Scanner s = new Scanner(System.in);
        System.out.println("title:");
        String title = s.next();
        System.out.println("author:");
        String author = s.next();
        System.out.println("price:");
        String price = s.next();
        System.out.println("sales:");
        String sales = s.next();
        System.out.println("stock:");
        String stock = s.next();
        System.out.println("img_path:");
        String img_path = s.next();
        String sql ="insert into books(title,author,price,sales,stock,img_path)"+"values(?,?,?,?,?,?)";
        update(sql, title, author, price, sales, stock, img_path);
        System.out.println("录入成功！");
    }

6、从键盘输入用户名和密码，模拟登录，使用JDBC实现验证用户名和密码是否正确，如果正确，显示登录成功，否则显示用户名或密码错误

    // TODO 从键盘输入用户名和密码，模拟登录，使用JDBC实现验证用户名和密码是否正确，如果正确，显示登录成功，否则显示用户名或密码错误
    @Test
    public void test3() throws Exception {
        System.out.println("请登录");
        System.out.println("请输入用户名：");
        Scanner scanner = new Scanner(System.in);
        String username = scanner.next();
        System.out.println("请输入密码：");
        String pwd = scanner.next();
        // 1.读取配置文件中的4个基本信息
        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc1.properties");
        Properties pros = new Properties();
        pros.load(is);
        String user = pros.getProperty("user");
        String password = pros.getProperty("password");
        String url = pros.getProperty("url");
        String driverClass = pros.getProperty("driverClass");
        // 2.加载驱动
        Class.forName(driverClass);
        // 3.获取连接
        Connection conn = DriverManager.getConnection(url, user, password);
        String sql ="select * from users where username=? and password=SHA(?)";
        //4.预编译sql语句，返回PreparedStatement的实例
        PreparedStatement ps= conn.prepareStatement(sql);
ps.setString(1, username);
ps.setString(2, pwd);
        ResultSet resultSet =ps.executeQuery();
if (resultSet.next()) {
            System.out.println("登录成功");
        } else {
            System.out.println("登录失败，用户名或密码错误");
        }
        resultSet.close();
ps.close();
        conn.close();
        scanner.close();
    }

7、使用JDBC实现查询所有图书信息

    // TODO 7、使用JDBC实现查询所有图书信息
    @Test
    public void test4() throws Exception {
        // 1.读取配置文件中的4个基本信息
        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc1.properties");
        Properties pros = new Properties();
        pros.load(is);
        String user = pros.getProperty("user");
        String password = pros.getProperty("password");
        String url = pros.getProperty("url");
        String driverClass = pros.getProperty("driverClass");
        // 2.加载驱动
        Class.forName(driverClass);
        // 3.获取连接
        Connection conn = DriverManager.getConnection(url, user, password);
        String sql ="select `id`,`title`,`author`,`price`,`sales`,`stock`,`img_path` from books";
        //4.预编译sql语句，返回PreparedStatement的实例
        PreparedStatement ps= conn.prepareStatement(sql);
        //5、执行sql
        ResultSet rs =ps.executeQuery();
while (rs.next()) {
for (int i =1; i <=7; i++) {
                System.out.println(rs.getObject(i) +"\t");
            }
            System.out.println();
        }
        //6、关闭
        rs.close();
ps.close();
        conn.close();
    }

8、使用JDBC实现查询销量最大的图书信息

    // TODO 使用JDBC实现查询销量最大的图书信息
    @Test
    public void test5() throws Exception {
        // 1.读取配置文件中的4个基本信息
        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc1.properties");
        Properties pros = new Properties();
        pros.load(is);
        String user = pros.getProperty("user");
        String password = pros.getProperty("password");
        String url = pros.getProperty("url");
        String driverClass = pros.getProperty("driverClass");
        // 2.加载驱动
        Class.forName(driverClass);
        // 3.获取连接
        Connection conn = DriverManager.getConnection(url, user, password);
        String sql ="select * from books where sales = (select max(sales) from books)";
        //4.预编译sql语句，返回PreparedStatement的实例
        PreparedStatement ps= conn.prepareStatement(sql);
        //5、执行sql
        ResultSet rs =ps.executeQuery();
while (rs.next()) {
for (int i =1; i <=7; i++) {
                System.out.println(rs.getObject(i) +"\t");
            }
            System.out.println();
        }
        //6、关闭
        rs.close();
ps.close();
        conn.close();
    }

9、使用JDBC实现修改库存量小于10本的图书的库存量为100

    //TODO 使用JDBC实现修改库存量小于10本的图书的库存量为100
    @Test
    public void test6() throws Exception {
        // 1.读取配置文件中的4个基本信息
        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc1.properties");
        Properties pros = new Properties();
        pros.load(is);
        String user = pros.getProperty("user");
        String password = pros.getProperty("password");
        String url = pros.getProperty("url");
        String driverClass = pros.getProperty("driverClass");
        // 2.加载驱动
        Class.forName(driverClass);
        // 3.获取连接
        Connection conn = DriverManager.getConnection(url, user, password);
        String sql ="UPDATE books SET stock=100 WHERE stock<10";
        //4.预编译sql语句，返回PreparedStatement的实例
        PreparedStatement ps= conn.prepareStatement(sql);
        //5、执行sql
        int i =ps.executeUpdate();
        System.out.println(i > 0 ? "修改成功" : "修改失败");
        //6、关闭
ps.close();
        conn.close();
    }

10、从键盘输入用户名，实现查询该用户的订单和订单明细

    //TODO 从键盘输入用户名，实现查询该用户的订单和订单明细
    @Test
    public void test7() throws Exception {
        Scanner input = new Scanner(System.in);
        System.out.print("请输入用户名：");
        String username = input.next();
        // 1.读取配置文件中的4个基本信息
        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc1.properties");
        Properties pros = new Properties();
        pros.load(is);
        String user = pros.getProperty("user");
        String password = pros.getProperty("password");
        String url = pros.getProperty("url");
        String driverClass = pros.getProperty("driverClass");
        // 2.加载驱动
        Class.forName(driverClass);
        // 3.获取连接
        Connection conn = DriverManager.getConnection(url, user, password);
        String sql ="SELECT * FROM order_items INNER JOIN orders ON order_items.order_id = orders.id WHERE user_id = (SELECT id FROM users WHERE username = ?)";
        //4.预编译sql语句，返回PreparedStatement的实例
        PreparedStatement ps= conn.prepareStatement(sql);
        //5、执行sql
        ResultSet rs =ps.executeQuery();
while (rs.next()) {
for (int i =1; i <=14; i++) {
                System.out.println(rs.getObject(i) +"\t");
            }
            System.out.println();
        }
        //6、关闭
        rs.close();
ps.close();
        conn.close();
        input.close();
    }

11、使用JDBC实现删除订单“15275760194821”的相关信息，注意涉及到两张表

    //TODO 使用JDBC实现删除订单“15275760194821”的相关信息，注意涉及到两张表
    @Test
    public void test8() throws Exception {
        // 1.读取配置文件中的4个基本信息
        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc1.properties");
        Properties pros = new Properties();
        pros.load(is);
        String user = pros.getProperty("user");
        String password = pros.getProperty("password");
        String url = pros.getProperty("url");
        String driverClass = pros.getProperty("driverClass");
        // 2.加载驱动
        Class.forName(driverClass);
        // 3.获取连接
        Connection conn = DriverManager.getConnection(url, user, password);
        String sql ="DELETE FROM orders WHERE id=15275760194821";
        //4.预编译sql语句，返回PreparedStatement的实例
        PreparedStatement ps= conn.prepareStatement(sql);
        //5、执行sql
        int i =ps.executeUpdate();
        System.out.println(i > 0 ? "删除成功" : "删除失败");
        //6、关闭
ps.close();
        conn.close();
    }

END

JDBC与MySQL练习

1、创建数据库test01_bookstore

2、创建如下表格

（1）图书表books

（2）用户表users

（3）订单表orders

4）订单明细表order_items

3、使用sql语句在命令行或SQLyog中添加一些模拟数据

表books：

表users：

表orders：

表order_items：

4、使用JDBC实现往用户表中添加1个用户，注意密码存储使用mysql的SHA()函数进行加密

jdbc1.properties文件位于src目录下

5、使用JDBC实现往图书表中添加1本图书

6、从键盘输入用户名和密码，模拟登录，使用JDBC实现验证用户名和密码是否正确，如果正确，显示登录成功，否则显示用户名或密码错误

9、使用JDBC实现修改库存量小于10本的图书的库存量为100

10、从键盘输入用户名，实现查询该用户的订单和订单明细

11、使用JDBC实现删除订单“15275760194821”的相关信息，注意涉及到两张表

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

JDBC与MySQL练习

1、创建数据库test01_bookstore

2、创建如下表格

（1）图书表books

（2）用户表users

（3）订单表orders

4）订单明细表order_items

3、使用sql语句在命令行或SQLyog中添加一些模拟数据

表books：

表users：

表orders：

表order_items：

4、使用JDBC实现往用户表中添加1个用户，注意密码存储使用mysql的SHA()函数进行加密

jdbc1.properties文件位于src目录下

5、使用JDBC实现往图书表中添加1本图书

6、从键盘输入用户名和密码，模拟登录，使用JDBC实现验证用户名和密码是否正确，如果正确，显示登录成功，否则显示用户名或密码错误

9、使用JDBC实现修改库存量小于10本的图书的库存量为100

10、从键盘输入用户名，实现查询该用户的订单和订单明细

11、使用JDBC实现删除订单“15275760194821”的相关信息，注意涉及到两张表

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像