Several changes to migrating a database from oracle to mysql using ibatis

Using ibatis to migrate databases from oracle to mysql. Our project needs to migrate data from oracle to mysql under the company's big strategy. Our project uses ibatis, some modifications required on the ibatis layer are as follows: the unit test framework uses jtester. [Java] 1. when you insert a primary key to generate an Oracle insert statement, the primary key id is sequence: <insert id = "MS-BRANDMEMBER-INSERT" parameterClass = "TA-brandMember"> <selectKey resultClass = "long" keyProperty = "id"> SELECT distinct from dual </selectKey> insert into industry_brand_member (id, gmt_create, gmt_modified, member_id, bu, active, brand_id, cat_ids, auth_date_from, auth_date_to, auth_level, area, status, operato R, original) values (# id #, sysdate, sysdate, # memberId #, # mallId #, # active #, # brandId #, # catIds #, # authDateFrom #, # authDateTo #, # authLevel #, # area #, # status #, # operator #, # original #) </insert> mysql needs to be modified: <insert id = "MS-BRANDMEMBER-INSERT" parameterClass = "TA-brandMember"> insert into industry_brand_member (gmt_create, gmt_modified, member_id, mall_id, active, brand_id, cat_ids, auth_date_from, aut H_date_to, auth_level, area, status, operator, original) values (now (), now (), # memberId #, # mallId #, # active #, # brandId #, # catIds #, # authDateFrom #, # authDateTo #, # authLevel #, # area #, # status #, # operator #, # original #) <selectKey resultClass = "java. lang. long "keyProperty =" id "> SELECT LAST_INSERT_ID () as id </selectKey> </insert> 2. paging Oracle uses a three-tier nested paging method, using the offset of the minimum and maximum get paging content: <select id = "SELECT-BRANDID LIST-BY-MALLID "resultClass =" long "parameterClass =" java. util. map "> select brandId from (select brandId, rownum as no from (select distinct (brand_id) as brandId from industry_brand_member where <isNotNull property =" mallId "> bu = # mallId: VARCHAR # AND </isNotNull> <isNotNull property = "status"> status = # status: VARCHAR # AND </isNotNull> brand_id is not null order by brand_id asc) <! [CDATA [where no >=# start # and no <# end #]> </select> Mysql uses the limit and offset syntax for paging, use offset and number of entries per page <select id = "SELECT-BRANDIDLIST-BY-MALLID" resultClass = "long" parameterClass = "java. util. map "> select distinct (brand_id) as brandId from industry_brand_member where <isNotNull property =" mallId "> mall_id = # mallId: VARCHAR # AND </isNotNull> <isNotNull property = "status"> status = # status: VARCHAR # AND </isNotNull> brand _ Id is not null order by brand_id asc limit # start #, # pageSize # </select> 3. the date APIs of Oracle and mysql are quite different. You need to check all the date operations. We only use the following three date methods: Oracle Mysql to_char (last_offer_paytime, 'yyyy/MM/dd') date_format (last_offer_paytime, '% Y/% m/% D ') convert the date to the string to_date (# searchDate #, 'yyyy/MM/dd') str_to_date (# searchDate #, '% Y/% m/% D ') convert string to date to_date (# searchDate #, 'yyyy/MM/dd') + 1 date_add (str_to_date (# searchDate #, '% Y/% m/% D '), interval 1 day) date addition 4. quality Assurance most SQL statements may be modified more or less. How can we ensure the quality? We have conducted unit tests on each SQL statement. We may have done some work that we did not do before, cheng DA has done a lot of unit tests, This greatly guarantees the migration quality and even discovers some hidden problems. For migration, unit testing is the best quality assurance method. Run the DbFit encapsulated by jtester to perform the DAO unit Test @ DbFit (when = "wiki/sampleshow/BuyerDAO. insert. when. wiki ", then =" wiki/sampleshow/BuyerDAO. insert. then. wiki ") public void TestInsertSampleShowBuyer () {SampleShowBuyer = new SampleShowBuyer (); SampleShowBuyer. setMemberId ("yanhandle"); SampleShowBuyer. setPhoneNumber ("13512345678"); Long id = sampleShowBuyerDAO. insertSampleShowBuyer (SampleShowBuyer); wa Nt. object (id ). notNull (); want. number (id ). greaterThan (0l);} The unit test workload accounts for 2/3 of the de-O workload. On average, each table requires 0.8 person-days. Of course, this depends on the number and complexity of SQL statements. We recommend that you perform unit tests to ensure the quality. 5. For differences in data types, you can create a table using idb. However, brand has a configuration field that uses the Oracle CLOB type, and mysql uses the text type.