目录

一、分页查询

二、小结

一、分页查询

创建数据库

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.set<Int64>(1, 0)
    statement1.set<Int64>(2, 5)
    println('基础分页查询')
    println('序号\t 姓名\t 年龄\t 性别\t 年级')
    // 返回结果集
    let resultSet1 = statement1.query()
    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}')
    }


    let sql2 = """
        select *
        from student
        where id > ?
        order by id
        limit ?
    """
    let statement2 = connection.prepareStatement(sql2)
    statement2.set<Int64>(1, 0)
    statement2.set<Int64>(2, 5)
    println('\n优化后分页查询')
    println('序号\t 姓名\t 年龄\t 性别\t 年级')
    // 返回结果集
    let resultSet2 = statement2.query()
    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()
}

运行结果

二、小结

本章为大家详细的介绍了分页查询的内容，下一章为大家介绍仓颉Server的内容。最后，创作不易，如果大家觉得我的文章对学习仓颉Web基础编程有帮助的话，就动动小手，点个免费的赞吧！收到的赞越多，我的创作动力也会越大哦，谢谢大家🌹🌹🌹！！！