跟我一起学“仓颉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 varchar(20) DEFAULT NULL, PRIMARY KEY (id) );给数据表添加数据INSERT INTO student VALUES (1,白小黑,24,1,研二), (2,刘小辉,21,1,大四), (3,胡小静,25,0,研三), (4,漆小孟,18,0,大一), (5,林小茂,23,1,研一), (6,陈小雨,20,0,大三), (7,周小宇,19,1,大二), (8,王小帆,25,1,研三), (9,宋小冉,22,0,大四), (10,陆小凯,24,1,研二), (11,姜小楠,18,0,大一), (12,冯小哲,23,1,研一), (13,温小妍,20,0,大三), (14,董小泽,19,1,大二), (15,许小诺,22,0,大四), (16,邹小恒,25,1,研三), (17,彭小茜,23,0,研一), (18,崔小川,18,1,大一), (19,尹小玥,19,0,大二), (20,方小腾,24,1,研二);分页SQL# 基础查询语句 # SELECT 字段 FROM 表 LIMIT (n-1) * 每页显示的条数, 每页显示的条数 SELECT * FROM student LIMIT 0, 5; # 修改后的较为高效的查询语句 # SELECT 字段 # FROM 表 # WHERE id (n-1) * 每页显示的条数 # ORDER BY id 根据 id 升序排列 # LIMIT 每页显示的条数 SELECT * FROM student WHERE id 15 ORDER BY id LIMIT 5;核心代码package WebStudy import std.database.sql.* import mariadb.cdbc.* main(): Unit { pageQuery() } // 分页查询 public func pageQuery(): 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() // 基础分页查询 let sql1 select * from student limit ?,? let statement1 connection.prepareStatement(sql1) statement1.setInt64(1, 0) statement1.setInt64(2, 5) println(基础分页查询) println(序号\t 姓名\t 年龄\t 性别\t 年级) // 返回结果集 let resultSet1 statement1.query() while (resultSet1.next()) { let id resultSet1.getInt64(1) let name resultSet1.getString(2) let age match (resultSet1.getOrNullInt64(3)) { case Some(v) v case None 0 } let sex if(resultSet1.getOrNullInt64(4) 1){男}else{女} let grade resultSet1.getString(5) println(${id}\t ${name}\t ${age}\t ${sex}\t ${grade}) } let sql2 select * from student where id ? order by id limit ? let statement2 connection.prepareStatement(sql2) statement2.setInt64(1, 0) statement2.setInt64(2, 5) println(\n优化后分页查询) println(序号\t 姓名\t 年龄\t 性别\t 年级) // 返回结果集 let resultSet2 statement2.query() while (resultSet2.next()) { let id resultSet2.getInt64(1) let name resultSet2.getString(2) let age match (resultSet2.getOrNullInt64(3)) { case Some(v) v case None 0 } let sex if(resultSet2.getOrNullInt64(4) 1){男}else{女} let grade resultSet2.getString(5) println(${id}\t ${name}\t ${age}\t ${sex}\t ${grade}) } // 资源释放 有顺序 resultSet1.close() resultSet2.close() statement1.close() statement2.close() connection.close() dataSource.close() }运行结果二、小结本章为大家详细的介绍了分页查询的内容下一章为大家介绍仓颉Server的内容。最后创作不易如果大家觉得我的文章对学习仓颉Web基础编程有帮助的话就动动小手点个免费的赞吧收到的赞越多我的创作动力也会越大哦谢谢大家