background

Xxl-job supports MySQL database by default. In order to solve the compatibility problem of multiple databases, Oracle and Postgre verification tests were conducted last week. This article records the process and problems of Oracle transformation.

There are two difficult questions:

  1. Oracle is case-sensitive, MySQL is case-insensitive, and data is transferred directly from the MySQL database to the Oracle connection library. All queries are invalidated. I used MySQL all the time and forgot about the basic feature of Oracle.
  2. If the time of the Oracle database server is not synchronized with that of the Executor deployment machine, the system automatically detects offline failures. Clock synchronization is a basic guarantee for a distributed system.

First, data transmission

First, data transfer. Use Navicat’s data transfer function. The advantage is that you can ensure that the statements are consistent with the current xxL-job version. Although there are many cases on the Internet to modify xxL-job to support other databases, but the version is not consistent, the latest version is 2.3.0, I also choose this version, so it is safe to organize the initial SQL statement by myself.

Second, export the database construction clause from Oracle, named xxl-job-oracle.sql. MySQL > insert into MySQL database; SQL > insert into MySQL database; SQL > insert into MySQL database;

Third, format xxl-job-oracle. SQL file, open it with EditPlus, edit it directly and replace it with uppercase. After all fields and table names are capitalized, the MyBatis mapping query in the project will be capitalized by default, and the application will run correctly.

Select * from XXL_JOB_GROUP where XXL_JOB_USRR = XXL_JOB_USRR = XXL_JOB_USRR;

Fifth, edit each table, there are several numeric fields, and identified as Not null fields, must set the default value. The default value is set in the official MySQL table builder, but this information is missing when data is transferred to other databases, so you must adjust it yourself.

Sixth, create an increment sequence with seven tables, requiring seven of the following sequence statements.

create sequence XXL_JOB_GROUP_ID
minvalue 1
maxvalue 999999999999
start with 2
increment by 1
cache 20
cycle;
Copy the code

Once you’ve got the construction sentence, it’s basically simple.

The second step is XML mapping of MyBatis

Then modify MyBatis mapping file to support Oracle syntax, mainly reflected in:

  1. Table aliasAS tI need to replace it witht.
  2. MySQL escape characters’ are replaced with Spaces.
  3. findLogReportTo ensure that the SQL statement is correct, use double quotation marks to escape the fields that contain aliases of lowercase letters.
  4. All of theupdateStatement, set for the fieldjdbcTypeProperties.
  5. All of theinsertInsert column with ID as sequence value, take Group as an example:
<insert id="save" parameterType="com.xxl.job.admin.core.model.XxlJobGroup" useGeneratedKeys="true" keyColumn="id" keyProperty="id" >
		INSERT INTO xxl_job_group (id, app_name, title, address_type, address_list, update_time)
		values ( XXL_JOB_GROUP_SEQ.NEXTVAL,#{appname}, #{title}, #{addressType}, #{addressList}, #{updateTime} )
	</insert>
Copy the code
  1. RegistryMapper time operation, MySQLDATE_ADDWith Oracleto_char.
<select id="findDead" parameterType="java.util.HashMap" resultType="java.lang.Integer" > SELECT t.id FROM xxl_job_registry t WHERE to_char(t.update_time,'yyyy-mm-dd HH24:MI:SS') <! [CDATA[ < ]]>to_char(sysdate-#{timeout} /24/60/60,'yyyy-mm-dd HH24:MI:SS') </select> <select id="findAll" parameterType="java.util.HashMap" resultMap="XxlJobRegistry"> SELECT <include refid="Base_Column_List" /> FROM xxl_job_registry t WHERE to_char(t.update_time,'yyyy-mm-dd HH24:MI:SS') <! [CDATA[ > ]]>to_char(sysdate-#{timeout} /24/60/60,'yyyy-mm-dd HH24:MI:SS') </select>Copy the code
  1. A paging query wraps a layer before, for example, a Group tablepageList
SELECT * FROM ( select row_number() over(ORDER BY id DESC) r,<include refid="Base_Column_List" /> FROM xxl_job_group t <trim prefix="WHERE" prefixOverrides="AND | OR" > <if test="appname ! = null and appname ! = ''"> AND t.app_name like CONCAT(CONCAT('%', #{appname}), '%') </if> <if test="title ! = null and title ! = ''"> AND t.title like CONCAT(CONCAT('%', #{title}), '%') </if> </trim> ) where r between #{offset}+1 and #{offset}+#{pagesize}Copy the code

Step 3, JDBC connection modification

First, pom.xml adds Oracle driver dependencies:

Oracle </groupId> <artifactId>ojdbc6</artifactId> <version>11.2.0.3</version> </dependency>Copy the code

Second, modify the application.properties configuration, mainly with connection addresses and Query statements:

spring.datasource.url=jdbc:oracle:thin:@IP:1521:orcl
spring.datasource.username=username
spring.datasource.password=password
spring.datasource.driver-class-name=oracle.jdbc.driver.OracleDriver
spring.datasource.hikari.connection-test-query=SELECT 1 FROM DUAL
Copy the code

At this point, the transformation is complete and the complete XML and table building sentences have been uploaded to the CSDN resource. Click Download.

The revelation of

Compatibility transformation, the process is actually very simple, but involves many aspects, need to check and revise one by one. I also encountered a low-level mistake. When TRANSFORMING SQL, I did not delete the original SQL and casually used the annotation of IDEA, which caused unexpected problems.

Yesterday I tracked the up-down problem of xxL-job automatic sensor actuator, and found that it was caused by inconsistent clocks. The code for auto-registration and heartbeat detection is pretty neat, and I’ll look at the JobRegistryHelper class at some point. It’s only a couple of hundred lines, but it’s worth a look.

Sorting out the cost article, hoping to give some enlightenment to the peer in need of transformation.