# [18🈲]增删改查大法-六合篇-2022(不限语言)

@todo 总结一下, 大致分5种思路

拼接占位字符串 --> golang与php的主流方法, java的nutz

快速灵活
但不方便维护, 重构火葬场

代码与SQL分离 @nice --> java的Mybatis与BeetlSQL(太小众不敢用)

四两拨千斤, 最划算的实现方法
已经会SQL的学习成本很低
但不方便迁移数据库和重构

===
@ps 但我不能接受写在xml里
@ps 直接在字符串里写SQL, 就没有语法高亮了

设计查询规约 @nice --> nodejs的GraphQL或者json对象, java的apijson(GraphQL感觉)

分摊了前后端压力
但设计规约不通用并难推广

===
@ps 相当于重新设计了SQL

JPA也可以算属于这种, 为什么会有这种诡异的脑洞...
findByUserIdAndTotal(String userid, BigDecimal total)

扩展实体功能 --> C#与Python, java的jooq(繁琐)

便于维护类型与重构, 不易出错, 可以扩展更多功能
但代码量瞬间上去了, 没啥美感

运算符开挂或重载 @nice @peek --> C#与kotlin是天然支持的, java有objectivesql(但不好用)

技术上最赏心悦目
但一般是新兴的语言才用

# 工作语言

java ->>
python
javascript

# 熟悉语言 @sub

golang
kotlin
scala

# 陌生语言 @other

c#
php
ruby

# 风格 @diff

Active Record
Mapper
Example @old

Java前期吃了静态语言不够灵活的亏

# Python

SQLAlchemy

instances = db.session.query(Instance.name, Instance.sep_status, User.email)
  .outerjoin( User, Instance.user_id==User.id)
  .filter(Instance.is_terminated==0)

# JavaScript

prisma

typeorm

sequelize
- https://www.eggjs.org/zh-CN/tutorials/sequelize (opens new window)

const results = await this.app.mysql.select('posts', { // 搜索 post 表
  where: { status: 'draft', author: ['author1', 'author2'] }, // WHERE 条件
  columns: ['author', 'title'], // 要查询的表字段
  orders: [['created_at','desc'], ['id','desc']], // 排序方式
  limit: 10, // 返回数据量
  offset: 0, // 数据偏移量
});

=> SELECT `author`, `title` FROM `posts`
  WHERE `status` = 'draft' AND `author` IN('author1','author2')
  ORDER BY `created_at` DESC, `id` DESC LIMIT 0, 10;

denodb
- https://github.com/eveningkid/denodb (opens new window)
- https://eveningkid.com/denodb-docs/docs/api/model-methods#left... (opens new window)

await Flight.where(Flight.field('departure'), 'Paris')
  .leftJoin(Airport, Airport.field('id'), Flight.field('airportId'))
  .get();

# Kotlin

Ktorm @nice
- @code https://github.com/vincentlauvlwj/Ktorm (opens new window)
- @doc https://ktorm.liuwj.me/ (opens new window)

val employees = Employees
    .joinReferencesAndSelect()
    .whereWithConditions {
        if (someCondition) {
            it += Employees.managerId.isNull()
        }
        if (otherCondition) {
            it += Employees.departmentId eq 1
        }
    }
    .orderBy(Employees.id.asc())
    .limit(0, 10)
    .map { Employees.createEntity(it) }

easysql
- https://github.com/wz7982/easysql/ (opens new window)

兼容java, 但不建议  

===
val select = db
    .select(case(User.gender eq 1 then "男") elseIs "女" alias "gender")
    .select(sum(User.id) * 100 alias "sum_id")
    .from(User)
    .groupBy(case(User.gender eq 1 then "男") elseIs "女")

# golang

gorm

# Scala

slick
- https://github.com/playframework/play-slick (opens new window)
- https://scala-slick.org/docs/ (opens new window)

函数式编程
@ps 看不懂

# C#

efcore @nice

db.Set<UserInfor>().ToList();
 
//查询表达式
var account = (
  from u in _context.Users
  where u.Id == id
  select u.Account
).ToList();
 
//查询单个
_context.Movie.FirstOrDefaultAsync(m => m.ID == id);
_context.Movie.FindAsync(id); 
//查询指定列
_context.Set<User>().AsNoTracking().Where(t=>t.Id=="11").Select(t => new { t.Account, t.PassWord }).FirstOrDefaultAsync();
 
// 预先加载查询
var blogs = context.Blogs.Include(blog => blog.Posts).ToList();
// 包含多个层级的查询
var blogs = context.Blogs.Include(blog => blog.Posts).ThenInclude(post => post.Author).ToList();

Dapper @nice
freesql @nice
- https://github.com/dotnetcore/FreeSql (opens new window)
- http://freesql.net/ (opens new window)

fsql.Select<Topic, Category, CategoryType>()
  .LeftJoin((a,b,c) => a.CategoryId == b.Id)
  .LeftJoin((a,b,c) => b.ParentId == c.Id)
  .Where((a,b,c) => c.Id > 0)
  .ToList((a,b,c) => new { a,b,c });

sqlsugar
- https://www.donet5.com/Home/Doc (opens new window)

# PHP

laravel
- https://laravel.com/ (opens new window)

thinkphp
- https://www.kancloud.cn/manual/thinkphp6_0/1037479 (opens new window)

// 简单查询
Db::table('think_user')
    ->where('status',1)
    ->order('create_time')
    ->limit(10)
    ->select();

// 关联
Db::table('think_user')
  ->alias('a')
  ->join(['think_work'=>'w'],'a.id=w.artist_id')
  ->join(['think_card'=>'c'],'a.card_id=c.id')
  ->select();

# Ruby

rails
- https://www.jianshu.com/p/722ed2608124 (opens new window)

# 简单的更新
user = User.find_by(name: 'David')
user.name = 'Dave'
user.save

# 用update方法
user = User.find_by(name: 'David')
user.update(name: 'Dave')

# user = User.find_by(name: 'David')
user.update(name: 'Dave')

# 批量修改
User.update_all "name = 'cwh'"

← [18🈲]增删改查大法-八荒篇-2020(Java生态) [轮子]piccolo →