sqlsharp

一款灵感来自C#的ADO.NET+dapper设计的orm。

规范说明

1. 默认约定id为主键和标识列
2. 实体来源于上下文，所有DbSet<T>类型的属性。以及onModelCreating的配置。
3. 我们提供了实体自动生成工具：sqlsharp_utils

创建DbContext

public class TestDbContext <: DbContext {
    public prop students: DbSet<Student> {
        get() {
            set<Student>()
        }
    }

    public override func onConfiguring(optionsBuilder: DbContextOptionsBuilder): Unit {
        optionsBuilder.useMysql("mysql://localhost:3306?username=root&password=1024&database=test")
    }
}

@Entity
@Serialization
public class Student {
    private var _id: Int64 = 0
    private var _name: ?String = None
}

DbContext基本操作

更改跟踪

• 插入操作

let student = Student(name: "sqlsharp", age: 20)
context.add(student)
context.entry(student).state |> println // Added
context.saveChanges()    
context.entry(student).state |> println // Unchanged
student.id |> pritnln //打印自增列

• 更新操作

1. 更新部分列

let student = context.students.find(1).getOrThrow()
context.entry(student).state |> println // Unchanged
student.age = 20
context.entry(student).state |> println // Modified
context.saveChanges()    
context.entry(student).state |> println // Unchanged

2. 更新所有列

let student = Student(id: 1, name: "sqlsharp", age: 20)
context.update(student)
context.entry(student).state |> println // Modified
context.saveChanges()    
context.entry(student).state |> println // Unchanged

• 删除操作

let student = context.students.find(1).getOrThrow()
context.remove(student)
context.entry(student).state |> println // Deleted
context.saveChanges()    
context.entry(student).state |> println // Detached

基本查询

// 查询单个学生
let student = context.students
    .params([("id", 1)])
    .whereIf("id = @id")
    .single()

// 基于主键查询
let student = context.students.find(1)

// 查询学生列表
let list = context.students
    .params([("age", 1)])
    .whereIf("age >= @age")
    .toList()

// 分页查询：获取第一页，每页十条数据
var (list, total) = context.students
    .params([("age", 1)])
    .whereIf("age >= @age")
    .toPageResult(1, 10)   

// 计数查询
var count = context.students
    .params([("age", 1)])
    .whereIf("age >= @age")
    .count()    

// 存在性判定
var count = context.students
    .params([("age", 1)])
    .whereIf("age >= @age")
    .any()

// 投影操作
var studentDto = context.students
    .select<StudentDto>("id", "age")
    .toList()

// 求和
var sum = context.students
    .sum("id")

// 平均值
var avg = context.students
    .avg("id")

// 最大值
var max = context.students
    .max("id")

// 最小值
var min = context.students
    .min("id")

事务操作

try(transaction = context.beginTransaction()) {
    let student = context.students.find(1)
    student.age = 20
    context.saveChanges()
    throw UnsupportedException()
    let student = context.students.find(1)
    student.age = 40
    context.saveChanges()
}

执行SQL语句

更新操作

// 1. 使用动态参数
var values = DbParameters()
values.add("name", "zs")
values.add("age", 11)
values.add("createTime", 11)
context.execute("insert into student (name, age, creationTime) values (@name, @age, @creationTime)", values)

查询操作

我们对IN查询做了特殊的增强处理

// 查询id为：1，2，3的学生列表
var values = DbParameters([("ids", [1, 2, 3])])
let list = context.query<Student>("select id, age, name from student where id IN @ids", values)
list.toArray().serialize().toJson().toJsonString() |> println

SqlBuilder

我们演示一个动态查询

let sb = SqlBuilder()
sb.whereIf("age = @age")
sb.whereIf("locate(@name, name)", false) //通过判定前端是否传入该参数，来决定是否启用该筛选

let sql = "SELECT id, age, name, creationTime FROM student${sb.whereSql}"

var values = DbParameters([("age", 20), ("name", "cangjie")])
let list = context.query<Student>(sql, values)

配置模型

sqlsharp是基于属性的构建的。并且支持通过注解和Fluent Api两种方式来配置实体元信息。Fluent Api配置优先级高于注解。

1. 约定id为自增字段
2. 通过字段驱动设计，字段必须以下划线开头
3. 表名配置顺序为：fluent api > 注解 > DbContext.students

• Fluent Api配置方式

public class DemoDbContext <: DbContext {

    public prop students: DbSet<Student> {
        get() {
            set<Student>()
        }
    }

    protected override func onModelCreating(modelBuilder: ModelBuilder): Unit {
        modelBuilder.entity<Student>().toTable("student").property("name") {
            property => property.hasColumnName("stu_name")
        }
    }
}

• 注解配置方式

我们内置了序列化宏@Entity、@Serialization

@Serialization
@Table('t_student')
public class Student {
    
    private let _id: Int64 = 0

    @Column['stu_name']
    private let _name: ?String = None
}

配置实体

注解配置是可选了，只是为了演示特殊场景

@Entity
@Serialization
@Table["students"]
public class Student {

    @Column["id"]
    @DatabaseGenerated[DatabaseGeneratedOption.None] //取消`id`作为自增列的默认约定
    private let _id: Int64 = 0

    @Column["name"]
    private let _name: ?String = None
}