首页   注册   登录
V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
V2EX  ›  Java

有多少 Java 程序员用 jOOQ ?

  •  3
     
  •   Jex · 2018-11-12 21:35:26 +08:00 · 5384 次点击
    这是一个创建于 389 天前的主题,其中的信息可能已经有所发展或是发生改变。

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

    38 回复  |  直到 2019-01-15 09:07:26 +08:00
        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😂
        12
    wmhack   2018-11-13 08:50:28 +08:00 via iPhone
    @wysnylc 可以发一下 mapper 吗?
        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
    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
        32
    Jex   2018-11-13 15:53:08 +08:00   ♥ 1
    @jorneyr 我回复你一下,只为让评论早点翻页让你那坨 XML 消失
        33
    qiyuey   2018-11-13 16:03:13 +08:00
    @jorneyr 你这个 xml 感觉不具备可读性
        34
    godoway   2018-11-13 16:04:37 +08:00
    面对着 Oracle,想用 jooq 都用不了。
    面对着一堆 Oracle 自定义函数,jpa 又很麻烦(貌似还不能在插入的时候调用,其实是我不懂...)
    最后只能上 mybatis 了,蛋疼...
        35
    jorneyr   2018-11-13 16:12:19 +08:00
    @qiyuey 有更好的办法吗?
        36
    Jex   2018-11-23 12:27:24 +08:00   ♥ 1
    @jorneyr 在 XML 里面拼 SQL 跟直接在 Java 里面拼 SQL 有多大区别?
        37
    applehater   326 天前 via iPhone
    @letitbesqzr 发现工作一年多,还真没写过 join 语句。。
        38
    letitbesqzr   326 天前
    @applehater #37 互联网产品吧? 企业产品几乎不可能的,那几千上万张表的业务,不关联很麻烦。。
    关于   ·   FAQ   ·   API   ·   我们的愿景   ·   广告投放   ·   感谢   ·   实用小工具   ·   1835 人在线   最高记录 5043   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.3 · 26ms · UTC 01:12 · PVG 09:12 · LAX 17:12 · JFK 20:12
    ♥ Do have faith in what you're doing.