Jex
V2EX  ›  Java

有多少 Java 程序员用 jOOQ ?

  •  3
     
  •   Jex ·
    CJex · Nov 12, 2018 · 10040 views
    This topic created in 2927 days ago, the information mentioned may be changed or developed.

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

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