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

有多少 Java 程序员用 jOOQ ?

  •  3
     
  •   Jex · 103 天前 · 3703 次点击
    这是一个创建于 103 天前的主题,其中的信息可能已经有所发展或是发生改变。

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

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