MyBatis轻松接入金仓数据库：从零搭建到生产实践

MyBatis轻松接入金仓数据库从零搭建到生产实践接手国产化改造任务去年做一个政务系统的国产化替换客户明确要求数据库必须换成金仓。团队里没人用过心里都有点打鼓。我当时先用Spring Boot JdbcTemplate跑了个demo发现挺顺利。后来好奇MyBatis能不能也这么丝滑地接进去就专门搭了个项目验证。结果从配依赖到跑通CRUD一个小时不到全搞定。今天就把这次实战经历整理出来。一、准备工作配置文件里需要填连接信息先找DBA要到这四项数据库IP和端口默认54321数据库名用户名默认system密码顺便用命令行验证一下能不能连上./ksql-Usystem-dtest-h192.168.1.100-p54321连上了再继续下一步。二、项目搭建2.1 目录结构mybatis-kingbase/ ├── pom.xml ├── src/main/ │ ├── java/com/kingbase/ │ │ ├── mapper/ │ │ │ ├── IUserMapper.java # XML方式 │ │ │ └── IAppMapper.java # 注解方式 │ │ └── pojo/ │ │ ├── User.java │ │ └── App.java │ └── resources/ │ ├── jdbc.properties │ ├── mybatis-config.xml │ └── IUserMapper.xml └── src/test/java/ └── TestMybatis.java用了两种Mapper方式对比IUserMapper是XML配置IAppMapper是纯注解方便看看哪种更适合自己的项目。2.2 pom.xml依赖?xml version1.0 encodingUTF-8?projectxmlnshttp://maven.apache.org/POM/4.0.0xmlns:xsihttp://www.w3.org/2001/XMLSchema-instancexsi:schemaLocationhttp://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsdmodelVersion4.0.0/modelVersiongroupIdcom.kingbase.example/groupIdartifactIdjava-kingbase-mybatis/artifactIdversion1.0-SNAPSHOT/versionpropertiesmaven.compiler.source8/maven.compiler.sourcemaven.compiler.target8/maven.compiler.target/propertiesdependencies!-- 金仓JDBC驱动 --dependencygroupIdcn.com.kingbase/groupIdartifactIdkingbase8/artifactIdversion9.0.0/version/dependency!-- MyBatis核心 --dependencygroupIdorg.mybatis/groupIdartifactIdmybatis/artifactIdversion3.5.9/version/dependency!-- 分页插件 --dependencygroupIdcom.github.pagehelper/groupIdartifactIdpagehelper/artifactIdversion5.3.0/version/dependency!-- 单元测试 --dependencygroupIdjunit/groupIdartifactIdjunit/artifactIdversion4.10/versionscopetest/scope/dependency/dependencies/project关键点金仓驱动groupId是cn.com.kingbase我刚用时写成com.kingbase8一直拉不下来。2.3 配置文件jdbc.propertiesjdbc.drivercom.kingbase8.Driver jdbc.urljdbc:kingbase8://192.168.1.100:54321/test?useServerPrepStmtstruerewriteBatchedStatementstrue jdbc.usernamesystem jdbc.password123456URL后面加了两个参数useServerPrepStmts开启预编译、rewriteBatchedStatements优化批量插入正式环境建议加上。mybatis-config.xml?xml version1.0 encodingUTF-8?!DOCTYPEconfigurationPUBLIC-//mybatis.org//DTD Config 3.0//ENhttp://mybatis.org/dtd/mybatis-3-config.dtdconfigurationpropertiesresourcejdbc.properties/settingssettingnamecacheEnabledvaluetrue/settingnamelazyLoadingEnabledvaluetrue/settingnamemapUnderscoreToCamelCasevaluetrue//settingspluginsplugininterceptorcom.github.pagehelper.PageInterceptorpropertynamehelperDialectvaluekingbase8/propertynamereasonablevaluefalse//plugin/pluginsenvironmentsdefaultdevelopmentenvironmentiddevelopmenttransactionManagertypeJDBC/dataSourcetypePOOLEDpropertynamedrivervalue${jdbc.driver}/propertynameurlvalue${jdbc.url}/propertynameusernamevalue${jdbc.username}/propertynamepasswordvalue${jdbc.password}//dataSource/environment/environmentsmappersmapperresourceIUserMapper.xml/mapperclasscom.kingbase.mapper.IAppMapper//mappers/configuration两个地方需要注意mapUnderscoreToCamelCase开启后数据库字段user_name能自动映射到Java的userName分页插件dialect要写成kingbase8才能生成正确分页SQL。三、代码实现3.1 实体类User.java和App.java代码一样packagecom.kingbase.pojo;publicclassUser{privateLongid;privateStringname;publicUser(){}publicUser(Longid,Stringname){this.idid;this.namename;}// getter/setter省略...OverridepublicStringtoString(){returnUser{idid, namename};}}3.2 XML方式IUserMapper接口packagecom.kingbase.mapper;importcom.kingbase.pojo.User;importorg.apache.ibatis.annotations.Param;importjava.util.List;publicinterfaceIUserMapper{IntegerinsertUser(Useruser);IntegerdeleteUser(Longid);IntegerupdateUser(Useruser);ListUserselectUsers();ListUserselectUserByPage(Param(pageNum)IntegerpageNum,Param(pageSize)IntegerpageSize);}XML映射文件?xml version1.0 encodingUTF-8?!DOCTYPEmapperPUBLIC-//mybatis.org//DTD Mapper 3.0//ENhttp://mybatis.org/dtd/mybatis-3-mapper.dtdmappernamespacecom.kingbase.mapper.IUserMapperinsertidinsertUserparameterTypecom.kingbase.pojo.UserINSERT INTO test_user (id, name) VALUES (#{id}, #{name})/insertdeleteiddeleteUserparameterTypelongDELETE FROM test_user WHERE id #{id}/deleteupdateidupdateUserparameterTypecom.kingbase.pojo.UserUPDATE test_user SET name #{name} WHERE id #{id}/updateselectidselectUsersresultTypecom.kingbase.pojo.UserSELECT id, name FROM test_user/selectselectidselectUserByPageresultTypecom.kingbase.pojo.UserSELECT id, name FROM test_user LIMIT #{pageNum}, #{pageSize}/select/mapper用LIMIT做分页代码比Oracle的rownum简洁多了。3.3 注解方式IAppMapperpackagecom.kingbase.mapper;importcom.kingbase.pojo.App;importorg.apache.ibatis.annotations.*;importjava.util.List;publicinterfaceIAppMapper{Insert(insert into test_app(id,name) values(#{id},#{name}))IntegerinsertApp(Appapp);Delete(delete from test_app where id #{id})IntegerdeleteApp(Longid);Update(update test_app set name #{name} where id #{id})IntegerupdateApp(Appapp);Select(select * from test_app)Results({Result(idtrue,columnid,propertyid),Result(columnname,propertyname)})ListAppselectApps();}简单SQL用注解方便复杂查询还是XML更清晰看个人习惯。四、测试验证测试类覆盖了XML和注解两种Mapper、以及分页功能importcom.kingbase.mapper.IAppMapper;importcom.kingbase.mapper.IUserMapper;importcom.kingbase.pojo.App;importcom.kingbase.pojo.User;importcom.github.pagehelper.PageHelper;importcom.github.pagehelper.PageInfo;importorg.apache.ibatis.io.Resources;importorg.apache.ibatis.session.SqlSession;importorg.apache.ibatis.session.SqlSessionFactory;importorg.apache.ibatis.session.SqlSessionFactoryBuilder;importorg.junit.Test;importjava.io.IOException;importjava.sql.SQLException;importjava.sql.Statement;importjava.util.List;publicclassTestMybatis{TestpublicvoidtestUserMapper()throwsIOException,SQLException{SqlSessionFactoryfactorynewSqlSessionFactoryBuilder().build(Resources.getResourceAsStream(mybatis-config.xml));SqlSessionsqlSessionfactory.openSession();StatementstmtsqlSession.getConnection().createStatement();// 建表try{stmt.execute(drop table if exists test_user);}catch(SQLExceptione){}stmt.execute(create table test_user(id number(20) primary key, name varchar(100)));IUserMappermappersqlSession.getMapper(IUserMapper.class);// 插入10条for(inti1;i10;i){mapper.insertUser(newUser((long)i,insert));}// 删除id1mapper.deleteUser(1L);// 更新id2mapper.updateUser(newUser(2L,update));// 查询所有ListUseruserListmapper.selectUsers();userList.forEach(System.out::println);// 分页查询ListUserusersByPagemapper.selectUserByPage(0,3);System.out.println(分页结果: usersByPage);stmt.close();sqlSession.commit();sqlSession.close();}TestpublicvoidtestAppMapper()throwsIOException,SQLException{SqlSessionFactoryfactorynewSqlSessionFactoryBuilder().build(Resources.getResourceAsStream(mybatis-config.xml));SqlSessionsqlSessionfactory.openSession();StatementstmtsqlSession.getConnection().createStatement();// 建表try{stmt.execute(drop table if exists test_app);}catch(SQLExceptione){}stmt.execute(create table test_app(id number(20) primary key, name varchar(100)));IAppMappermappersqlSession.getMapper(IAppMapper.class);// 插入10条for(inti1;i10;i){mapper.insertApp(newApp((long)i,inserti));}// 删除id1mapper.deleteApp(1L);// 更新id2mapper.updateApp(newApp(2L,update));// 分页测试PageHelper.startPage(2,3);ListAppappListmapper.selectApps();PageInfoApppageInfonewPageInfo(appList);System.out.println(分页后: pageInfo.getList());sqlSession.commit();sqlSession.close();}}运行结果User{id2, nameupdate} User{id3, nameinsert} ... 分页后: [App{id6, nameinsert6}, App{id7, nameinsert7}, App{id8, nameinsert8}]从输出能看出建表成功、数据正确、分页按预期工作。五、两种Mapper方式对比维度XML配置注解SQL与代码分离混在一起复杂SQL清晰字符串拼接动态SQL天然支持需要用Provider调试有语法检查编译期不检查适用场景项目工程简单demo建议简单CRUD用注解省事复杂业务查询用XML更好维护。六、生产环境踩坑记录驱动类名com.kingbase8.Driver不是com.kingbase.Driver。分页方言PageHelper的helperDialect要写成kingbase8写成mysql语法不对写成postgresql也能用但不如kingbase8稳定。KingbaseMode参数金仓有Oracle和MySQL两种兼容模式连接URL加上KingbaseModemysql可以用MySQL方言的LIMIT不加就用Oracle的rownum。示例用的是默认模式分页用了LIMIT说明数据库用MySQL模式创建的。批量操作优化连接URL加上rewriteBatchedStatementstrue批量插入性能能提升不少。大小写问题表名和字段名建议全部小写用双引号会导致大小写敏感各种莫名奇妙的麻烦。七、总结从MySQL切到金仓MyBatis项目改的地方很少pom.xml换驱动、配置改URL、分页方言改成kingbase8。如果SQL里没用数据库特有语法基本无缝迁移。我的建议是先跑通demo验证可行性再考虑分库分表、读写分离这些高级特性。金仓对MyBatis的兼容性整体做得不错踩坑的概率比你想象的低。