ibatis持久化框架相关配置

冷不防 2022-08-07 01:57 135阅读 0赞

1、MySQL 表主键

<insert id="Application.insert" parameterClass="Application">  
        <selectKey resultClass="java.lang.Long" keyProperty="appId" type="post">  
            SELECT LAST\_INSERT\_ID() as value  
        </selectKey>  
        INSERT INTO t\_application  
              (app\_id,  
               app\_name,  
               app\_url,  
               app\_desc,  
               status,  
               app\_review,  
               updater,  
               update\_time  
              )  
        VALUES  (\#appId\#,  
                \#appName\#,  
                \#appUrl\#,  
                \#appDesc\#,  
                \#status\#,  
                \#appReview\#,  
                \#updater\#,  
                 NOW()  
                )  
    </insert>

2、oracle 表主键

<insert id="Application.insert" parameterClass="Application">  
        <selectKey resultClass="long" keyProperty="appId" type="pre">  
            <!\[CDATA\[SELECT SEQ\_APP\_ID.NEXTVAL AS appId FROM DUAL\]\]>  
        </selectKey>  
        INSERT INTO t\_application  
              (app\_id,  
               app\_name,  
               app\_url,  
               app\_desc,  
               status,  
               app\_review,  
               updater,  
               update\_time  
              )  
        VALUES  (\#appId\#,  
                \#appName\#,  
                \#appUrl\#,  
                \#appDesc\#,  
                \#status\#,  
                \#appReview\#,  
                \#updater\#,  
                SYSDATE  
                )  
    </insert>

//调用存储过程重现排序  传栏目ID  
        this.getSqlMapClientTemplate().update("Category.changeOrderReset",obj);

存储过程调用

oracle：

create or replace procedure p\_res\_cloumn\_rank(columnid in number)  is  
\--更新数据  
 begin  
  for rec in (  select rowid, rownum, t1.\* from (  
      select m.\*  
      from  T\_RES\_CLOUMN\_MAP m  
      where m.column\_id = columnid  
      order by m.resource\_rank, m.id) t1)  
  loop  
    update T\_RES\_CLOUMN\_MAP t  
       set t.resource\_rank = rec.rownum  
     where rowid = rec.rowid;  
  end loop;  
  commit;  
end p\_res\_cloumn\_rank;  
/  
commit;

MySQL：

DELIMITER $$  
DROP PROCEDURE IF EXISTS \`p\_res\_cloumn\_rank\`$$  
CREATE PROCEDURE \`p\_res\_cloumn\_rank\`(str\_in\_columnid VARCHAR(100))    
BEGIN    
    DECLARE str\_l\_column\_ref\_resource\_id VARCHAR(50);  
      
    DECLARE i\_l\_count INT DEFAULT 1;  
    DECLARE i\_l\_done  INT DEFAULT FALSE;  
    DECLARE cur\_l\_resort CURSOR FOR SELECT t.id FROM (SELECT id  
                      FROM t\_res\_cloumn\_map m  
                     WHERE m.column\_id = str\_in\_columnid  
                     ORDER BY m.resource\_rank, m.id) t;  
  
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET i\_l\_done = TRUE;  
    OPEN cur\_l\_resort;    
  
       read\_loop: LOOP  
            FETCH cur\_l\_resort INTO str\_l\_column\_ref\_resource\_id;  
            IF i\_l\_done THEN  
                LEAVE read\_loop;  
            END IF;  
              
            UPDATE t\_res\_cloumn\_map t SET t.resource\_rank = i\_l\_count WHERE id = str\_l\_column\_ref\_resource\_id;  
            SET i\_l\_count = i\_l\_count + 1;  
        END LOOP;  
          
        CLOSE cur\_l\_resort;    
  
    COMMIT;  
END;  
$$  
  
  
commit;