Mybatis use Oracle to add data

Time:2021-6-10

  This time, the blogger mainly develops Oracle database. For a long time, he didn’t use Oracle, and he has forgotten many knowledge points. This time, he mainly reviews some SQL statements used in his work;

query

All query statements are normal, but it should be noted that when querying Oracle database, when using alias for table name, please do not use as keyword. Only MySQL database can be used. Oracle only supports as keyword for field name alias.

increase

When adding data, our background is likely to use the added primary key ID, which is also different from mysql. Mybatis only needs to configure the insert attribute, for example:

   1  

However, Oracle is not supported because Oracle has no self increasing primary key and can only assign a value to the primary key ID by its own self-defined self increasing sequence; The premise is to know the sequence name, such as:

select SEQ_Table.nextval from dual
        
        insert into SA_0511_1IS(id)values(#{receiptId})

  The sequence is created as follows:

create sequence SEQ_ Table \ \ sequence name
Increment by 1 \ \ step by 1
Start with 1 \ \ start with 1
maxvalue 999999999; \\ Maximum

  So we can use our custom sequence

When batch adding, the primary key ID will not be assigned. You can only insert it. There are two methods:

First, there is no primary key ID in the table, which is all other basic information and external association ID,

insert all
        
        into SA_T(
            RECEIPT_ID,GOODS_NAME,
            REG_USR_ID,REG_DT,UPDT_USR_ID,UPDT_DT
        )values
            (#{entity.receiptId},#{entity.goodsName},
            #{entity.regUsrId},#{entity.regDt},#{entity.updtUsrId},#{entity.updtDt})
        
        select 1 from dual

  The second method: when a single table is added, it needs to be added according to the sequence value. The first method is not feasible, for example:

insert all
        
        into SA_T(
            RECEIPT_ID,GOODS_NAME,
            REG_USR_ID,REG_DT,UPDT_USR_ID,UPDT_DT
        )values
            (SEQ_T.nextval,#{entity.goodsName},
            #{entity.regUsrId},#{entity.regDt},#{entity.updtUsrId},#{entity.updtDt})
        
        select 1 from dual

  After that, only the same primary key ID will appear, instead of gradually increasing. Therefore, the following method should be used:

insert
            into SA_0511_1IS(RECEIPT_ID,DEALER_CODE)
 select SEQ_SA_0511_1IS.nextval,t.* from (
        
select #{entity.dealerCode} from dual

        ) t

  This is a normal increase, and some insert into.. select。。 If you query directly from other tables like from, you should also note that the field names must be the same, because you do not specify which fields need to be added, so they must be the same

insert into SA_T_LOG
        select * from SA_T
        where VIN in
        
            #{vin}

  The rest is business logic, and I don’t care. Knowledge is to consolidate their own review, or will forget all, just contact, even the sequence is forgotten; For quite a few years, I have been able to declare objects in Oracle, and I have forgotten some operations

 

Recommended Today

I want to discuss canvas 2D and webgl with you

background This article is included inData visualization and graphicsspecial column As mentioned above, I wrote my first column and realized a simple program for the cognition of graphics and visualization. It was originally intended that the follow-up sequence has focused on the algorithm and rendering direction. However, based on the students’ feedback on obscure problems […]