跟我一起学“仓颉Web”基础编程-多表查询和事务-编程实验室

一、多表查询

二、事务

三、小结

一、多表查询

创建数据库

create database web_study; use web_study;

创建数据表

CREATE TABLE `student` ( `id` int NOT NULL AUTO_INCREMENT, `name` varchar(20) NOT NULL, `age` int DEFAULT NULL, `sex` int DEFAULT NULL, `grade` int DEFAULT NULL, PRIMARY KEY (`id`) ); CREATE TABLE `grade` ( `id` int NOT NULL AUTO_INCREMENT, `grade` varchar(20) NOT NULL, PRIMARY KEY (`id`) );

给数据表添加数据

INSERT INTO `student` VALUES (1,'白小黑',18,1,1), (2,'刘小辉',19,1,1), (3,'胡小静',20,0,2), (4,'漆小孟',19,0,2), (5,'林小茂',21,1,2); INSERT INTO `grade` VALUES (1,'大一'), (2,'大二'), (3,'大三'), (4,'大四');

核心代码

package WebStudy import std.database.sql.* import mariadb.cdbc.* main(): Unit { moreTable() } // 多表查询 public func moreTable(): Unit { // 创建驱动 let driver = DriverManager.getDriver('mariadb').getOrThrow() // 数据库的链接 let url = 'mariadb://127.0.0.1:3306' // 数据库用户 let username = ('username', 'root') // 数据库密码 let password = ('password', 'YGBG372S1') // 需要连接数据库 let database = ('database', 'web_study') // 启动驱动获取数据资源 let dataSource = driver.open(url, [username, password, database]) // 创建连接 let connection = dataSource.connect() // 查询大二的同学(内连接) println("查询大二的同学(内连接)") let sql1 = """ select s.id, s.name, s.age, s.sex, g.grade fromstudent s joingrade g on s.grade = g.id where g.grade=? """ let statement1 = connection.prepareStatement(sql1) statement1.set<String>(1, '大二') let resultSet1 = statement1.query() println('序号\t 姓名\t 年龄\t 性别\t 年级\t') while (resultSet1.next()) { let id = resultSet1.get<Int64>(1) let name = resultSet1.get<String>(2) let age = match (resultSet1.getOrNull<Int64>(3)) { case Some(v) => v case None => 0 } let sex = if(resultSet1.getOrNull<Int64>(4) == 1){'男'}else{'女'} let grade = resultSet1.get<String>(5) println('${id}\t ${name}\t ${age}\t ${sex}\t ${grade}') } // 查询大二的同学(子查询) println("\n查询大二的同学(子查询)") let sql2 = """ select s.id, s.name, s.age, s.sex, g.grade from student s, (select id, grade from grade where grade=?) g where s.grade = g.id; """ let statement2 = connection.prepareStatement(sql2) statement2.set<String>(1, '大二') let resultSet2 = statement1.query() println('序号\t 姓名\t 年龄\t 性别\t 年级\t') while (resultSet2.next()) { let id = resultSet2.get<Int64>(1) let name = resultSet2.get<String>(2) let age = match (resultSet2.getOrNull<Int64>(3)) { case Some(v) => v case None => 0 } let sex = if(resultSet2.getOrNull<Int64>(4) == 1){'男'}else{'女'} let grade = resultSet2.get<String>(5) println('${id}\t ${name}\t ${age}\t ${sex}\t ${grade}') } // 资源释放 有顺序 resultSet1.close() resultSet2.close() statement1.close() statement2.close() connection.close() dataSource.close() }

运行结果

二、事务

事务：把多个数据库操作（增 / 删 / 改）打包成一个整体，要么全部成功，要么全部失败。

事务的特性：原子性、一致性、隔离性、持久性。

原子性：多个操作看成一步，成功就全部成功，失败就全部撤回。

一致性：操作前后数据都是合法、正确的，不会出现中间状态、脏数据。

隔离性：多人同时操作数据库，互不干扰，你改你的，我改我的，不会互相影响。

持久性：一旦提交成功，永久保存！断电、重启都不会丢。

为什么要用事务？

同时操作多张表，或者连续执行多个增删改，必须保证数据不出错、不混乱。

举个栗子，当前年级表里只有大一到大四，可以突然有一天，来了一个新同学，肖婕婕（女，25岁），她是研二的大学姐，为了可以灵活且高效的添加肖婕婕的信息，即同时操作学生表和年级表，这个时候，为了保证添加的信息的原子性、一致性、持久性，就必须用到事务。

核心代码

package WebStudy import std.database.sql.* import mariadb.cdbc.* main(): Unit { transaction() } // 事务 public func transaction(): Unit { // 创建驱动 let driver = DriverManager.getDriver('mariadb').getOrThrow() // 数据库的链接 let url = 'mariadb://127.0.0.1:3306' // 数据库用户 let username = ('username', 'root') // 数据库密码 let password = ('password', 'YGBG372S1') // 需要连接数据库 let database = ('database', 'web_study') // 启动驱动获取数据资源 let dataSource = driver.open(url, [username, password, database]) // 创建连接 let connection = dataSource.connect() // 事务 var transaction: Transaction // 创建事务 transaction = connection.createTransaction() // 开启事务 transaction.begin() // 新增年级 let sql1 = 'insert into grade (id, grade) values (?, ?)' let statement1 = connection.prepareStatement(sql1) statement1.set<Int64>(1, 5) statement1.set<String>(2, '研二') // 新增学生 let sql2 = 'insert into student (name, age, sex, grade) values (?, ?, ?, ?)' let statement2 = connection.prepareStatement(sql2) statement2.set<String>(1, '肖婕婕') statement2.set<Int64>(2, 25) statement2.set<Int64>(3, 0) statement2.set<Int64>(4, 5) try { let row1 = statement1.update() println('新增年级成功! row: ${row1.rowCount}') let row2 = statement2.update() println('新增学生成功! row: ${row2.rowCount}') // 提交事务 transaction.commit() } catch (e: SqlException) { // 事务回滚 transaction.rollback() println('新增失败, ${e.message}') } finally { statement1.close() statement2.close() } // 资源释放 有顺序 connection.close() dataSource.close() }

运行结果

数据库结果