以下操作环境基于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
