2. 概述
MyBatis 是一款优秀的持久层框架,它支持定制化 SQL、存储过程以及高级映射。MyBatis 避免了几乎所有的 JDBC 代码和手动设置参数以及获取结果集。MyBatis 可以使用简单的 XML 或注解来配置和映射原生信息, 将接口和 Java 的 POJOs(Plain Old Java Objects,普通的 Java对象)映射成数据库中的记录。
在本章中详细说明了 KingbaseES 对 MyBatis的支持情况,以及使用时应该注意的问题。
本章内容主要是基于 MyBatis 3 及后续版本支持情况的详细描述。
3. MyBatis配置说明
Mybatis的jar包可以从官方网站下载,Mybatis所使用的JDBC包kingbase8-8.6.0.jar位于$KINGBASE_HOME/Interface/jdbc目录下。使用时将Mybatis包和JDBC包导入到项目的Libraries中并定义相关配置项即可。
定义Mybatis配置文件,根据用户选择,更改一下配置文件。
-
在config.xml中配置JDBC的驱动信息参数,数据库服务器信息参数和登陆用户信息参数。当然,这些参数也可以根据用户应用需求, 单独生成property文件,针对不用的应用场景,导入不同的属性文件。这里以property为例说明下config.xml的配置使用。
在property 中增加如下声明:
jdbc.driverClassName=com.kingbase8.Driver jdbc.url=jdbc:kingbase8://localhost:54321/TEST jdbc.username=登录名 jdbc.password=登录密码在config.xml 中增加如下声明:
<environments default="development"><environment id="development"><transactionManager type="JDBC"/><dataSource type="POOLED"><property name="driver" value="${jdbc.driverClassName}"/><property name="url" value="${jdbc.url}"/><property name="username" value="${jdbc.username}" /><property name="password" value="${jdbc.password}" /></dataSource></environment> </environments>
-
在mapper.xml中,主要是配置SQL映射关系,根据用户的需求,自定义映射关系。这里以一个简单的配置为例。
在mapper.xml 中增加如下声明:
<mapper namespace="com.test.interfaces.MybatisDao"><update id="dropTable">DROP TABLE IF EXISTS ${sql} CASCADE</update><update id="createNewTableMap" parameterType="java.util.HashMap">${sql}</update><resultMap id="NumTableMap" type="NumEntity"><result column="id" property="userId" /><result column="age" property="userAge" /><result column="name" property="userName" /><result column="wage" property="userWage" /><result column="cost" property="userCost" /></resultMap><select id="selectNumTable" resultMap="NumTableMap">select * from t_num</select><select id="selectNumTableByID" parameterType="int"resultMap="NumTableMap">select * from t_num where id=#{value}</select><select id="selectNumTableByAge" parameterType="int"resultMap="NumTableMap">select * from t_num where age=#{value}</select><select id="selectNumTableByName" parameterType="string"resultMap="NumTableMap">select * from t_num where name=#{value}</select><insert id="insertNumTable" parameterType="NumEntity"keyProperty="userId">insert into t_num(id,age,name,wage,cost)values(#{userId},#{userAge},#{userName},#{userWage},#{userCost})</insert><update id="updateNumTableById" parameterType="NumEntity" >update t_num set age=#{userAge},name=#{userName},wage=#{userWage},cost=#{userCost}where id=#{userId}</update><delete id="deleteNumTableById" parameterType="int">delete from t_num where id=#{value}</delete><delete id="deleteNumTable">delete from t_num</delete> </mapper>至此,MyBatis的属性文件和映射文件的配置工作已经完成,接下来是来完成Java中对应的接口和实例的开发工作。示例如下:
-
在Java中读取property和config属性,配置到sqlSessionFactory:
@BeforeClass public static void setUpClass() throws Exception {InputStream fis = null;InputStream inputStream = null;try {/* 创建一个 sqlSessionFactory */Properties prop = new Properties();fis = Resources.getResourceAsStream("propertty");prop.load(fis);inputStream = Resources.getResourceAsStream("MyConfig.xml");sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream,"development", prop);} catch(Exception e) {e.printStackTrace();} }
-
在Java中配置mapper.xml对应的namespace,为Interface类型:
public interface MybatisDao {int createNewTableMap(Map<String, Object> param);int dropTable(Map<String, Object> param);/** new number table*/public List<NumEntity> selectNumTable();public NumEntity selectNumTableByID(int id);public List<NumEntity> selectNumTableByAge(int age);public List<NumEntity> selectNumTableByName(String name);public void insertNumTable(NumEntity user);public void updateNumTableById(NumEntity user);public void deleteNumTableById(int id);public void deleteNumTable(); }
-
在Java中进行表的查询操作:
public void testNumTable() {SqlSession session = sqlSessionFactory.openSession();int i = 1;try {MybatisDao userOperation = session.getMapper(MybatisDao.class);List<NumEntity> users = userOperation.selectNumTable();for (NumEntity user : users) {System.out.println(user);Assert.assertEquals(i, user.getId());Assert.assertEquals(AGE+i, user.getUserAge());Assert.assertEquals(name[i], user.getUserName());Assert.assertEquals("yes",WAGE+(double)(i*100)/(double)(3.0),user.getUserWage(),0.000001);Assert.assertEquals("yes",COST+(double)(i*100)/(double)(3.0),user.getUserCost(),0.01);i++;}} finally {session.close();} }
-
在Java中进行表的插入操作:
public void testInsertNumTable() {int i = 1 ;SqlSession session = sqlSessionFactory.openSession();try {MybatisDao userOperation = session.getMapper(MybatisDao.class);NumEntity user = new NumEntity();name[1] = "张三";name[2] = "李四";name[3] = "王五";name[4] = "赵二";name[5] = "小北";name[6] = "小东";name[7] = "小西";name[8] = "小南";name[9] = "小明";for (i=1;i<10;i++) {user.setId(i);user.setUserAge(AGE+i);user.setUserName(name[i]);user.setUserWage(WAGE+(double)(i*100)/(double)(3.0));user.setUserCost(COST+(double)(i*100)/(double)(3.0));userOperation.insertNumTable(user);session.commit();System.out.println(user);}} finally {session.close();} }
-
在Java中进行表的更新操作:
public void testUpdateNumTable() {int i = 1 ;SqlSession session = sqlSessionFactory.openSession();try {MybatisDao userOperation = session.getMapper(MybatisDao.class);NumEntity user = new NumEntity();/* 更新一行新的数据id=1 */user = userOperation.selectNumTableByID(1);user.setUserAge(AGE+i);user.setUserName(name[0]);user.setUserWage(WAGE+(double)(i*100)/(double)(3.0));user.setUserCost(COST+(double)(i*100)/(double)(3.0));userOperation.updateNumTableById(user);session.commit();} finally {session.close();} }
-
在Java中进行表的删除操作:
public void testDeleteNumTable() {int i = 1 ;SqlSession session = sqlSessionFactory.openSession();try {MybatisDao userOperation = session.getMapper(MybatisDao.class);userOperation.deleteNumTable();session.commit();} finally {session.close();} }到这里为止,在Java中对数据库的表操作就完成了,这里只是简单的示例,可能实际的应用场景的业务逻辑比较复杂,SQL的语句也比较复杂,但是MyBatis的使用精髓就是这些,可以在这些基础上去实现丰富的应用。