都是习惯用 Mybatis 在 XML 里面拼 SQL ? (看了隔壁关于 SQL 的讨论产生了这个疑问
1
namebyrant 2018-11-12 21:45:25 +08:00
XML 拼接对维护比较好 比较灵活
|
2
letitbesqzr 2018-11-12 21:58:06 +08:00 4
querydsl + jpa 多爽,搞不懂 mybatis 党,一天天哪有那么多复杂的 sql 需要丢给数据库处理,宁愿多查几次用 stream 去处理。
|
3
springmarker 2018-11-12 22:30:37 +08:00 via Android
用 mybatis-plus,复杂和简单的都能搞定
|
4
rayingecho 2018-11-12 22:57:25 +08:00
jOOQ 搭配 flyway, 基本上碰不到 SQL 异常了
|
5
wysnylc 2018-11-12 22:57:57 +08:00 1
@letitbesqzr #2 多查几次丢 stream 处理这个赞同. 然后推荐一个通用 Mapper 绝对比 jpa 好用而且语法优雅
|
6
mysunshinedreams 2018-11-12 23:38:04 +08:00
研究过,不过很多人连 mybatis 都用不好,别提新框架了。。。
|
7
Cbdy 2018-11-13 05:16:55 +08:00 via Android
jooq 要代码生成,不喜欢,要是能直接生成字节码就好了
|
8
godoway 2018-11-13 08:19:58 +08:00
我也在观望 jooq,不过 jooq 的 pojo 不支持关系,需要自己添加关系。
|
9
EricFuture 2018-11-13 08:20:08 +08:00 via iPhone
好吧,第一次听说 jooq (汗颜)
|
10
Suddoo 2018-11-13 08:32:54 +08:00 via Android 1
我也是第一次听说 jooq😂
|
11
changhe626 2018-11-13 08:40:08 +08:00
我也是第一次听说 jooq😂
|
13
sagaxu 2018-11-13 08:54:04 +08:00 via Android
简单的用 spring data jpa,复杂的在代码里直接拼 sql,mybatis 就是个鸡肋,简单的做不好,复杂的做不了,美其名曰提高可维护性,你约定好 JAVA 拼 sql 的文件名和路径规则,效果也是一样的。
|
14
iamniconico 2018-11-13 09:00:44 +08:00 via Android
用 ourbatis
|
15
loongwang 2018-11-13 09:04:00 +08:00
还在用 hibernate 的 criteria....会被鄙视吗
|
16
lixm 2018-11-13 09:16:09 +08:00
我用 ebean, 是不是太小众了?但是对 kotlin 支持很好啊
|
17
tatelucky 2018-11-13 09:33:55 +08:00
公司一半都是自研
|
18
KingOfUSA 2018-11-13 09:49:20 +08:00
两年前已经在生产环境上使用过 jooq. 很爽.
|
19
tonyl4 2018-11-13 09:50:53 +08:00
一直在用
|
20
519718366 2018-11-13 10:06:46 +08:00 1
@letitbesqzr 赞同多次单表查询,然后 stream 去处理, 但是复杂的分页列表是不是只能乖乖去写 join join 的 sql 了?
|
21
letitbesqzr 2018-11-13 10:24:19 +08:00
@519718366 #20 对,如果到 join 已经严重影响性能的地步,其实更好的选择是进行冗余,像很多高迸发的程序肯定是不允许进行 join 操作的,多加几个字段进行冗余会比较好。
|
22
letitbesqzr 2018-11-13 10:24:59 +08:00
@wysnylc #5 jpa 的语法的确很难用,所以配合了 querydsl,那个 api 设计的就很不错了。
|
23
clearbug 2018-11-13 10:33:34 +08:00 via Android
第一次听说,之前比较喜欢 mybatis
|
24
wysnylc 2018-11-13 11:00:26 +08:00
|
25
BQsummer 2018-11-13 12:49:22 +08:00 via Android
难道不是每个公司都封装了通用 mapper 吗
|
26
xypcn 2018-11-13 12:57:18 +08:00 1
https://github.com/ecdiy/goserver 看看这个项目,后台程序员的福音,苦逼的 Mybatis
|
27
specita 2018-11-13 13:27:54 +08:00
我其实是比较讨厌 xml 的,但是又觉得在代码写着 table().where().find()这种代码还不如 xml 里直接看 sql 来得直观.....
|
28
mineqiqi 2018-11-13 14:16:05 +08:00
我觉得不管什么框架 如果需要在代码里写 sql 或者类似 sql 的函数 都不如直接在 xml 写 sql 直观和更好维护
|
29
jorneyr 2018-11-13 15:01:38 +08:00
看一个我们的 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> ... ``` |
30
zcsz 2018-11-13 15:09:30 +08:00
后端用 Mybatis,SQL 都写 Dao 里,还算简单明了,讲道理现在心思都在前端上,后端快速搭建就成
|
31
hsuvee 2018-11-13 15:39:36 +08:00
自研超爽,性能堪比 jdbc,使用感觉堪比....堪比什么想不到,反正剩下的都是垃圾,说 jpa 好的看看性能分析 0.0
|
34
godoway 2018-11-13 16:04:37 +08:00
面对着 Oracle,想用 jooq 都用不了。
面对着一堆 Oracle 自定义函数,jpa 又很麻烦(貌似还不能在插入的时候调用,其实是我不懂...) 最后只能上 mybatis 了,蛋疼... |
37
applehater 2019-01-15 04:29:26 +08:00 via iPhone
@letitbesqzr 发现工作一年多,还真没写过 join 语句。。
|
38
letitbesqzr 2019-01-15 09:07:26 +08:00
@applehater #37 互联网产品吧? 企业产品几乎不可能的,那几千上万张表的业务,不关联很麻烦。。
|