有多少 Java 程序员用 jOOQ ？

[Jex]

都是习惯用 Mybatis 在 XML 里面拼 SQL ？ （看了隔壁关于 SQL 的讨论产生了这个疑问

[namebyrant]

XML 拼接对维护比较好 比较灵活

[letitbesqzr]

querydsl + jpa 多爽，搞不懂 mybatis 党，一天天哪有那么多复杂的 sql 需要丢给数据库处理，宁愿多查几次用 stream 去处理。

[springmarker]

用 mybatis-plus，复杂和简单的都能搞定

[rayingecho]

jOOQ 搭配 flyway, 基本上碰不到 SQL 异常了

[wysnylc]

@letitbesqzr #2 多查几次丢 stream 处理这个赞同. 然后推荐一个通用 Mapper 绝对比 jpa 好用而且语法优雅

[mysunshinedreams]

研究过，不过很多人连 mybatis 都用不好，别提新框架了。。。

[Cbdy]

jooq 要代码生成，不喜欢，要是能直接生成字节码就好了

[godoway]

我也在观望 jooq，不过 jooq 的 pojo 不支持关系，需要自己添加关系。

[EricFuture]

好吧，第一次听说 jooq （汗颜）

[Suddoo]

我也是第一次听说 jooq😂

[changhe626]

我也是第一次听说 jooq😂

[wmhack]

@wysnylc 可以发一下 mapper 吗？

[sagaxu]

简单的用 spring data jpa，复杂的在代码里直接拼 sql，mybatis 就是个鸡肋，简单的做不好，复杂的做不了，美其名曰提高可维护性，你约定好 JAVA 拼 sql 的文件名和路径规则，效果也是一样的。

[iamniconico]

用 ourbatis

[loongwang]

还在用 hibernate 的 criteria....会被鄙视吗

[lixm]

我用 ebean， 是不是太小众了？但是对 kotlin 支持很好啊

[tatelucky]

公司一半都是自研

[KingOfUSA]

两年前已经在生产环境上使用过 jooq. 很爽.

[tonyl4]

一直在用

[519718366]

@letitbesqzr 赞同多次单表查询，然后 stream 去处理， 但是复杂的分页列表是不是只能乖乖去写 join join 的 sql 了？

[letitbesqzr]

@519718366 #20 对，如果到 join 已经严重影响性能的地步，其实更好的选择是进行冗余，像很多高迸发的程序肯定是不允许进行 join 操作的，多加几个字段进行冗余会比较好。

[letitbesqzr]

@wysnylc #5 jpa 的语法的确很难用，所以配合了 querydsl，那个 api 设计的就很不错了。

[clearbug]

第一次听说，之前比较喜欢 mybatis

[wysnylc]

@wmhack #12 https://www.oschina.net/p/mybatis-mapper

[BQsummer]

难道不是每个公司都封装了通用 mapper 吗

[xypcn]

https://github.com/ecdiy/goserver 看看这个项目，后台程序员的福音，苦逼的 Mybatis

[specita]

我其实是比较讨厌 xml 的，但是又觉得在代码写着 table().where().find()这种代码还不如 xml 里直接看 sql 来得直观.....

[mineqiqi]

我觉得不管什么框架 如果需要在代码里写 sql 或者类似 sql 的函数 都不如直接在 xml 写 sql 直观和更好维护

[jorneyr]

看一个我们的 mapper 吧, 绝大部分都不是单表语句, 觉得 MyBatis 比较合适:

```xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<!--namespace 非常重要：必须是 Mapper 类的全路径-->
<mapper namespace="ebag.mapper.ClazzMapper">
<!-- 老师或者学生的列 -->
<sql id="student_or_teacher_columns">
user.id AS id,
user.username AS username,
user.nickname AS nickname,
user.avatar AS avatar,
user.school_id AS school_id,
user.is_enabled AS is_enabled,
user.gender AS gender,
clazz.name AS clazz_name,
clazz.code AS clazz_code,
clazz.phase AS clazz_phase,
clazz.id AS clazz_id,
cts.subject AS clazz_subject
</sql>

<!-- 查找学校的班级 -->
<select id="findClazzesBySchoolId" resultType="Clazz">
SELECT id, school_id AS schoolId, code, name, phase, grade, type,
enrollment_year AS enrollmentYear, graduation_year AS graduationYear
FROM clazz
WHERE school_id = #{schoolId} AND is_history=#{history}
</select>

<!-- 查询指定 ID 的班级 -->
<select id="findClazzById" parameterType="long" resultType="Clazz">
SELECT id, school_id AS schoolId, code, name, phase, grade, type,
enrollment_year AS enrollmentYear, graduation_year AS graduationYear
FROM clazz WHERE id = #{clazzId}
</select>

<!-- 查找学校的老师 -->
<select id="findTeachersBySchoolId" resultMap="teacherResultMap">
SELECT <include refid="student_or_teacher_columns"/>
FROM (
SELECT * FROM user
WHERE school_id = #{schoolId} AND role = 'ROLE_TEACHER'
LIMIT #{offset}, #{count}
) AS user
LEFT JOIN clazz_teacher_subject AS cts ON cts.teacher_id = user.id
LEFT JOIN clazz ON clazz.id = cts.clazz_id
</select>

<!-- 使用账号查找学校的老师 -->
<select id="findTeachersBySchoolIdAndUsernameLike" resultMap="teacherResultMap">
SELECT <include refid="student_or_teacher_columns"/>
FROM (
SELECT * FROM user
WHERE school_id = #{schoolId} AND role = 'ROLE_TEACHER' AND username LIKE CONCAT('%', #{username}, '%')
LIMIT #{offset}, #{count}
) AS user
LEFT JOIN clazz_teacher_subject AS cts ON cts.teacher_id = user.id
LEFT JOIN clazz ON clazz.id = cts.clazz_id
</select>

<!-- 使用昵称查找学校的老师 -->
<select id="findTeachersBySchoolIdAndNicknameLike" resultMap="teacherResultMap">
SELECT <include refid="student_or_teacher_columns"/>
FROM (
SELECT * FROM user
WHERE school_id = #{schoolId} AND role = 'ROLE_TEACHER' AND nickname LIKE CONCAT('%', #{nickname}, '%')
LIMIT #{offset}, #{count}
) AS user
LEFT JOIN clazz_teacher_subject AS cts ON cts.teacher_id = user.id
LEFT JOIN clazz ON clazz.id = cts.clazz_id
</select>

<!-- 查找学校的学生 -->
<select id="findStudentsBySchoolId" resultMap="studentResultMap">
SELECT <include refid="student_or_teacher_columns"/>
FROM (
SELECT * FROM user
WHERE school_id = #{schoolId} AND role='ROLE_STUDENT'
LIMIT #{offset}, #{count}
) AS user
LEFT JOIN clazz_student AS cs ON cs.student_id = user.id
LEFT JOIN clazz ON clazz.id = cs.clazz_id
LEFT JOIN clazz_teacher_subject cts ON cts.clazz_id = cs.clazz_id
</select>

<!-- 使用账号查找学校的学生 -->
<!-- 注意 LIMIT 必须放到子查询内部，否则数量限制不对 -->
<select id="findStudentsBySchoolIdAndUsernameLike" resultMap="studentResultMap">
SELECT <include refid="student_or_teacher_columns"/>
FROM (
SELECT * FROM user
WHERE school_id = #{schoolId} AND role='ROLE_STUDENT' AND username LIKE CONCAT('%', #{username}, '%')
LIMIT #{offset}, #{count}
) AS user
LEFT JOIN clazz_student AS cs ON cs.student_id = user.id
LEFT JOIN clazz ON clazz.id = cs.clazz_id
LEFT JOIN clazz_teacher_subject cts ON cts.clazz_id = cs.clazz_id
</select>

<!-- 使用名字查找学校的学生 -->
<select id="findStudentsBySchoolIdAndNicknameLike" resultMap="studentResultMap">
SELECT <include refid="student_or_teacher_columns"/>
FROM (
SELECT * FROM user
WHERE school_id = #{schoolId} AND role='ROLE_STUDENT' AND nickname LIKE CONCAT('%', #{nickname}, '%')
LIMIT #{offset}, #{count}
) AS user
LEFT JOIN clazz_student AS cs ON cs.student_id = user.id
LEFT JOIN clazz ON clazz.id = cs.clazz_id
LEFT JOIN clazz_teacher_subject cts ON cts.clazz_id = cs.clazz_id
</select>

<!-- 查找班级下的老师 -->
<select id="findTeachersByClazzId" resultMap="teacherResultMap">
SELECT <include refid="student_or_teacher_columns"/>
FROM clazz_teacher_subject cts
LEFT JOIN clazz ON clazz.id = cts.clazz_id
LEFT JOIN user ON user.id = cts.teacher_id
WHERE cts.clazz_id = #{clazzId}
</select>

<!-- 查找班级下的所有学生 -->
<select id="findStudentsByClazzId" resultMap="studentResultMap">
SELECT <include refid="student_or_teacher_columns"/>
FROM clazz_student cs
LEFT JOIN user ON user.id = cs.student_id
LEFT JOIN clazz ON clazz.id = cs.clazz_id
LEFT JOIN clazz_teacher_subject cts ON cts.clazz_id = cs.clazz_id
WHERE cs.clazz_id=#{clazzId}
ORDER BY cs.student_username
</select>

<!-- 查找指定 ID 的老师 -->
<select id="findTeacherById" resultMap="teacherResultMap">
SELECT <include refid="student_or_teacher_columns"/>
FROM clazz_teacher_subject cts
LEFT JOIN clazz ON clazz.id = cts.clazz_id
LEFT JOIN user ON user.id = cts.teacher_id
WHERE cts.teacher_id = #{teacherId}
</select>

<!-- 查找指定 ID 的老师 -->
<select id="findTeacherByClazzIdAndSubject" resultMap="teacherResultMap">
SELECT <include refid="student_or_teacher_columns"/>
FROM clazz_teacher_subject cts
LEFT JOIN clazz ON clazz.id = cts.clazz_id
LEFT JOIN user ON user.id = cts.teacher_id
WHERE cts.clazz_id = #{clazzId} AND cts.subject = #{subject}
</select>

<!-- 查找指定 ID 的学生 -->
<select id="findStudentById" parameterType="long" resultMap="studentResultMap">
SELECT <include refid="student_or_teacher_columns"/>
FROM clazz_student cs
LEFT JOIN clazz ON clazz.id = cs.clazz_id
LEFT JOIN clazz_teacher_subject cts ON cts.clazz_id = cs.clazz_id
LEFT JOIN user ON user.id = cs.student_id
WHERE cs.student_id = #{studentId}
</select>

<!--查询班级学生人数-->
<select id="findStudentCount" resultType="int">
SELECT count(*) FROM clazz_student WHERE clazz_student.clazz_id = #{clazzId}
</select>

<!-- 启用或禁用老师 -->
<update id="enableTeacher">
UPDATE clazz_teacher_subject SET is_enabled=#{enabled} WHERE teacher_id=#{teacherId};
UPDATE user SET is_enabled=#{enabled} WHERE id=#{teacherId};
</update>

<!-- 启用或禁用学生 -->
<update id="enableStudent">
UPDATE clazz_student SET is_enabled=#{enabled} WHERE student_id=#{studentId};
UPDATE user SET is_enabled=#{enabled} WHERE id=#{studentId};
</update>

<!-- 删除老师 -->
<delete id="deleteTeacher">
DELETE FROM user WHERE id = #{teacherId};
DELETE FROM clazz_teacher_subject WHERE teacher_id = #{teacherId};
</delete>

<!-- 删除学生 -->
<delete id="deleteStudent">
DELETE FROM user WHERE id = #{studentId};
DELETE FROM clazz_student WHERE student_id = #{studentId};
</delete>

<!-- 插入或更新已有班级 -->
<insert id="insertOrUpdateClazz" parameterType="Clazz">
INSERT INTO clazz (id, school_id, code, name, phase, grade, type, enrollment_year, graduation_year, created_time)
VALUES (#{id}, #{schoolId}, #{code}, #{name}, #{phase}, #{grade}, #{type}, #{enrollmentYear}, #{graduationYear}, now())

ON DUPLICATE KEY
UPDATE name = #{name}, phase = #{phase}, grade = #{grade}, type = #{type},
enrollment_year = #{enrollmentYear}, graduation_year = #{graduationYear}
</insert>

<!-- 插入班级学生关系 -->
<insert id="insertClazzStudent">
INSERT INTO clazz_student (school_id, clazz_code, student_username, created_time)
SELECT #{schoolId}, #{clazzCode}, #{studentUsername}, now()
FROM dual
WHERE NOT EXISTS (
SELECT 1 FROM clazz_student
WHERE school_id = #{schoolId} AND clazz_code = #{clazzCode} AND student_username = #{studentUsername}
)
</insert>

<!-- 插入班级老师学科关系 -->
<insert id="insertClazzTeacherSubject">
INSERT INTO clazz_teacher_subject (school_id, clazz_code, teacher_username, subject, created_time)
SELECT #{schoolId}, #{clazzCode}, #{teacherUsername}, #{subject}, now()
FROM dual
WHERE NOT EXISTS (
SELECT 1 FROM clazz_teacher_subject
WHERE school_id = #{schoolId} AND clazz_code = #{clazzCode} AND teacher_username = #{teacherUsername} AND subject = #{subject}
)
</insert>
...
```

[zcsz]

后端用 Mybatis，SQL 都写 Dao 里，还算简单明了，讲道理现在心思都在前端上，后端快速搭建就成

[hsuvee]

自研超爽，性能堪比 jdbc，使用感觉堪比....堪比什么想不到，反正剩下的都是垃圾，说 jpa 好的看看性能分析 0.0

[Jex]

@jorneyr 我回复你一下，只为让评论早点翻页让你那坨 XML 消失

[qiyuey]

@jorneyr 你这个 xml 感觉不具备可读性

[godoway]

面对着 Oracle，想用 jooq 都用不了。
面对着一堆 Oracle 自定义函数，jpa 又很麻烦(貌似还不能在插入的时候调用,其实是我不懂...)
最后只能上 mybatis 了,蛋疼...

[jorneyr]

@qiyuey 有更好的办法吗?

[Jex]

@jorneyr 在 XML 里面拼 SQL 跟直接在 Java 里面拼 SQL 有多大区别？

[applehater]

@letitbesqzr 发现工作一年多，还真没写过 join 语句。。

[letitbesqzr]

@applehater #37 互联网产品吧？ 企业产品几乎不可能的，那几千上万张表的业务，不关联很麻烦。。