Oracle数据库、Mybatis、MySql常用

Oracle设置id自增长

首先创建序列
CREATE SEQUENCE SEQ_SMS_BACK_LOG    #SEQ_SMS_BACK_LOG为序列名称
  START WITH 1
  INCREMENT BY 1
  NOCACHE
  NOCYCLE;

然后在插入时给id赋值SEQ_SMS_TEMPLATE_IMPORT.nextval即可

mybatis使用Oracle序列批量插入

int templateImportAddBatch(@Param("list") List<ExcelOpt> excelOptList);

<insert id="templateImportAddBatch" parameterType="java.util.List" useGeneratedKeys="false">
        insert into sms_msg_template_import(
        ID,
        TEMPNAME,
        STATUS
        )
        select SEQ_SMS_TEMPLATE_IMPORT.nextval, A.*  from(//select SEQ_SMS_TEMPLATE_IMPORT.nextval这段对应ID
        <foreach collection="list" item="item" index="index"//collection="list"对应@Param("list")
                 separator="UNION ALL">
            SELECT
            #{item.tempName,jdbcType=VARCHAR},//这里的对应ID后面的其他字段，和上面字段的顺序保持一致
            #{item.status,jdbcType=BIGINT}
            from dual
        </foreach>
        ) A
    </insert>

Oracle创建触发器

CREATE OR REPLACE TRIGGER sms_back_log_trigger  #sms_back_log_trigger为触发器名称
BEFORE INSERT ON sms_back_log    #sms_back_log为表名
FOR EACH ROW
BEGIN
  IF :NEW.id IS NULL THEN
    SELECT SEQ_SMS_BACK_LOG.NEXTVAL INTO :NEW.id FROM DUAL;   #SEQ_SMS_BACK_LOG为序列名称
  END IF;
END;

在mybatis中模糊查询字段

and s.instruct_name like CONCAT('%', #{instructName}, '%')
或者
and b.corp_name like '%' || #{corpName} || '%'

在mybatis中范围查询

AND INSTRUCT_TIME <![CDATA[  >= ]]> #{startTime}

在mybatis中获取MySql插入的id

    <insert id="addSchool" parameterType="com.yhyx.mallxdfzx.model.School">
        insert into sys_school
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="schoolName != null and schoolName != ''">
                SCHOOL_NAME,
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="schoolName != null and schoolName != ''">
                #{schoolName,jdbcType=VARCHAR},
            </if>
        </trim>
        <selectKey keyProperty="id" order="AFTER" resultType="java.lang.Integer">
            SELECT LAST_INSERT_ID()
        </selectKey>
    </insert>

在mybatis中获取Oracle插入的id

<insert id="insertSelective" parameterType="com.yhyx.smsbus.model.sys.SmsMsgTemplate" useGeneratedKeys="true"
            keyColumn="iTemplate">
        <selectKey keyProperty="iTemplate" resultType="long" order="BEFORE">
                select SEQ_SMS_MSG_TEMPLATE.nextval as iRole from dual
        </selectKey>
        insert into SMSGATEWAY.T_SMS_MSG_TEMPLATE
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="iTemplate != null">
                I_TEMPLATE,
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="iTemplate != null">
                #{iTemplate,jdbcType=DECIMAL},
            </if>
        </trim>
    </insert>

备份表

CREATE TABLE 新表名 SELECT * FROM  旧表名

B树结构类型的数据递归查询并拼接父子节点

SELECT
  SUBSTR(SYS_CONNECT_BY_PATH(a.org_name, '>'), 2) AS concatenated_path,--拼接org_name字段
  a.org_code,
  a.org_name,
  a.p_org_id,
  a.org_id
FROM
  tb_upms_org a
START WITH org_id = '2021030900031799'   --这里的值的最大的父节点的id
CONNECT BY PRIOR org_id = p_org_id    --这里是递归条件，p_org_id是父id，org_id 是子id

mybatis执行任意sql

dao接口
    //执行任意sql
    List<HashMap> qryAny(@Param("sql")String sql);
    
对应的XML
    <select id="qryAny" resultType="java.util.HashMap">
        ${sql}
    </select>