使用xml · JAVA

[TOC] # 配置 ~~~ mybatis.config-location=classpath:mybatis/mybatis-config.xml # 指定路徑 mybatis.mapper-locations=classpath:mybatis/mapper/*.xml mybatis.type-aliases-package=com.jdxia.model spring.datasource.url=jdbc:mysql://localhost:3306/app?serverTimezone=PRC&useUnicode=true&characterEncoding=utf-8 spring.datasource.username=root spring.datasource.password=root spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver logging.level.com.jdxia.mapper=debug ~~~ * `mybatis.config-location`，配置 `mybatis-config.xml` 路徑，`mybatis-config.xml` 中配置 MyBatis 基礎屬性; * `mybatis.mapper-locations`，配置 Mapper 對應的 XML 文件路路徑; * `mybatis.type-aliases-package`，配置項?目中實體類包路徑; * `spring.datasource.*`，數據源配置。 Spring Boot 啟動時數據源會自動注入到 SqlSessionFactory 中，使用 SqlSessionFactory 構建 SqlSessionFactory，再自動注?到 Mapper 中，最后我們直接使用 Mapper 即可。 # 啟動類在啟動類中添加對 Mapper 包掃描 @MapperScan，Spring Boot 啟動的時候會自動加載包路徑下的 Mapper. 或者直接在 Mapper 類上面添加注解 @Mapper，建議使用上面那種，不然每個 mapper 加個注解也挺麻煩的。 ~~~ @MapperScan("com.jdxia.mapper") @SpringBootApplication public class UserApplication { ~~~ # 例子 ## 準備表 ~~~ DROP TABLE IF EXISTS `users`; CREATE TABLE `users` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主鍵id', `userName` varchar(32) DEFAULT NULL COMMENT '用戶名', `passWord` varchar(32) DEFAULT NULL COMMENT '密碼', `user_sex` varchar(32) DEFAULT NULL, `nick_name` varchar(32) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; ~~~ ## 準備實體 **枚舉屬性** ~~~ public enum UserSexEnum { MAN, WOMAN } ~~~ **User實體** ~~~ @Data @AllArgsConstructor @NoArgsConstructor public class User implements Serializable { private static final long serialVersionUID = 1L; private Long id; private String userName; private String passWord; private UserSexEnum userSex; private String nickName; } ~~~ ## 分頁查詢 Web 層的參數會以 param 為后綴的對象進行傳參，以 result 結尾的實體類封裝返回的數據 **分頁的基礎類** ~~~ @Data public class PageParam { //起始行 private int beginLine; //默認每頁3條 private Integer pageSize = 3; // 當前頁 private Integer currentPage = 0; //自動計算起始行 public int getBeginLine() { return pageSize * currentPage; } } ~~~ user查詢條件參數類繼承分頁基礎類 ~~~ @Data public class UserParam extends PageParam{ private String userName; private String userSex; } ~~~ xml中要配置從分頁對象中獲取信息 ~~~  <select id="getList" resultMap="BaseResultMap" parameterType="com.jdxia.param.UserParam"> select <include refid="Base_Column_List" /> from users where 1=1 <include refid="Base_Where_List" /> order by id desc limit #{beginLine} , #{pageSize} </select> ~~~ ## 定義接口 ~~~ public interface UserMapper { //這里的?方法名需要和 XML配置中的id屬性一致,不然會找不到方法去對應執行的sql List<User> getAll(); List<User> getList(UserParam userParam); int getCount(UserParam userParam); User getOne(Long id); void insert(User user); int update(User user); int delete(Long id); } ~~~ ## mybatis公共屬性 mybatis-config.xml主要配置常用的typeAliases,設置類型別名,為java類型設置一短的名字. 它只和xml配置相關,存在的意義是用來減少類完全限定名稱的冗余 **注意文件的路徑,和配置文件中一致** ~~~ <?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration> <typeAliases> <typeAlias alias="Integer" type="java.lang.Integer" /> <typeAlias alias="Long" type="java.lang.Long" /> <typeAlias alias="HashMap" type="java.util.HashMap" /> <typeAlias alias="LinkedHashMap" type="java.util.LinkedHashMap" /> <typeAlias alias="ArrayList" type="java.util.ArrayList" /> <typeAlias alias="LinkedList" type="java.util.LinkedList" /> </typeAliases> </configuration> ~~~ 這樣我們在使用 Mapper.xml 的時候，需要引入可以直接這樣寫: ~~~ resultType="Integer" //或者 parameterType="Long" ~~~ ## map的配置文件 ~~~ <?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >  <mapper namespace="com.jdxia.mapper.UserMapper" >  <resultMap id="BaseResultMap" type="com.jdxia.model.User" > <id column="id" property="id" jdbcType="BIGINT" /> <result column="userName" property="userName" jdbcType="VARCHAR" /> <result column="passWord" property="passWord" jdbcType="VARCHAR" />  <result column="user_sex" property="userSex" javaType="com.jdxia.enums.UserSexEnum"/> <result column="nick_name" property="nickName" jdbcType="VARCHAR" /> </resultMap>  <sql id="Base_Column_List" > id, userName, passWord, user_sex, nick_name </sql> <sql id="Base_Where_List"> <if test="userName != null and userName != ''"> and userName = #{userName} </if> <if test="userSex != null and userSex != ''"> and user_sex = #{userSex} </if> </sql>  <select id="getAll" resultMap="BaseResultMap" > SELECT <include refid="Base_Column_List" /> FROM users </select>  <select id="getList" resultMap="BaseResultMap" parameterType="com.jdxia.param.UserParam"> select <include refid="Base_Column_List" /> from users where 1=1 <include refid="Base_Where_List" /> order by id desc limit #{beginLine} , #{pageSize} </select> <select id="getCount" resultType="Integer" parameterType="com.jdxia.param.UserParam"> select count(1) from users where 1=1 <include refid="Base_Where_List" /> </select> <select id="getOne" parameterType="Long" resultMap="BaseResultMap" > SELECT <include refid="Base_Column_List" /> FROM users WHERE id = #{id} </select> <insert id="insert" parameterType="com.jdxia.model.User" > INSERT INTO users (userName,passWord,user_sex) VALUES (#{userName}, #{passWord}, #{userSex}) </insert> <update id="update" parameterType="com.jdxia.model.User" > UPDATE users SET <if test="userName != null">userName = #{userName},</if> <if test="passWord != null">passWord = #{passWord},</if> nick_name = #{nickName} WHERE id = #{id} </update> <delete id="delete" parameterType="Long" > DELETE FROM users WHERE id =#{id} </delete> </mapper> ~~~ ## 測試 ~~~ @Resource private UserMapper userMapper; @Test public void testUser() { userMapper.insert(new User("aa", "a123456", UserSexEnum.MAN)); } ~~~ # 多數據源 ## 配置文件 ~~~ mybatis.config-location=classpath:mybatis/mybatis-config.xml spring.datasource.one.jdbc-url=jdbc:mysql://localhost:3306/test1?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=true spring.datasource.one.username=root spring.datasource.one.password=root spring.datasource.one.driver-class-name=com.mysql.cj.jdbc.Driver spring.datasource.two.jdbc-url=jdbc:mysql://localhost:3306/test2?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=true spring.datasource.two.username=root spring.datasource.two.password=root spring.datasource.two.driver-class-name=com.mysql.cj.jdbc.Driver ~~~ 第一個數據源以 `spring.datasource.one.* `為前綴連接數據庫 test1，第二個數據源以 `spring.datasource.two.*`為前綴連接數據庫 test2。同時需要將上述的 UserMapper.xml 文件復制兩份到 `resources/mybatis/mapper/one` 和`resources/mybatis/mapper/two` 目錄下各一份。數據源配置為兩個數據源創建不同的 Mapper 包路路徑，將以前的 UserMapper 復制到包 com.neo.mapper.one 和 com.neo.mapper.two 路路徑下，并且分別重命名為:User1Mapper、User2Mapper。 ## 配置數據源配置第一個數據源，新建 DataSource1Config **在多數據源中只能指定一個 @Primary作為默認的數據源** * basePackages 指明 Mapper 地址 * sqlSessionTemplateRef 指定 Mapper 路徑下注?的 sqlSessionTemplate ~~~ import org.apache.ibatis.session.SqlSessionFactory; import org.mybatis.spring.SqlSessionFactoryBean; import org.mybatis.spring.SqlSessionTemplate; import org.mybatis.spring.annotation.MapperScan; import org.springframework.beans.factory.annotation.Qualifier; import org.springframework.boot.context.properties.ConfigurationProperties; import org.springframework.boot.jdbc.DataSourceBuilder; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import org.springframework.context.annotation.Primary; import org.springframework.core.io.support.PathMatchingResourcePatternResolver; import org.springframework.jdbc.datasource.DataSourceTransactionManager; import javax.sql.DataSource; //把創建的SqlSessionTemplate注入到對應的Mapper包路徑下,這樣包下面的Mapper都會使用第一個數據源來進行數據庫操作 @Configuration @MapperScan(basePackages = "com.jdxia.mapper.one", sqlSessionTemplateRef = "oneSqlSessionTemplate") public class DataSource1Config { // 加載配置的數據源 @Bean(name = "oneDataSource") @ConfigurationProperties(prefix = "spring.datasource.one") @Primary public DataSource testDataSource() { return DataSourceBuilder.create().build(); } // 根據創建的數據源，構建對應的SqlSessionFactory,代碼中指定需要加載的Mapper xml文件 @Bean(name = "oneSqlSessionFactory") @Primary public SqlSessionFactory testSqlSessionFactory(@Qualifier("oneDataSource") DataSource dataSource) throws Exception { SqlSessionFactoryBean bean = new SqlSessionFactoryBean(); bean.setDataSource(dataSource); bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mybatis/mapper/one/*.xml")); return bean.getObject(); } //把數據源加到事務中 @Bean(name = "oneTransactionManager") @Primary public DataSourceTransactionManager testTransactionManager(@Qualifier("oneDataSource") DataSource dataSource) { return new DataSourceTransactionManager(dataSource); } //把上面創建的SqlSessionFactory注入,創建我們在Mapper中需要使用的SqlSessionTemplate @Bean(name = "oneSqlSessionTemplate") @Primary public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("oneSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception { return new SqlSessionTemplate(sqlSessionFactory); } } ~~~ **另一個數據源也同理** 第?個數據源配置, DataSource2Con?g 的配置和上?類似，?法上需要去掉 @Primary 注解，替換對應的數據源和 Mapper 路徑即可創建多數據源的過程就是：?先創建 DataSource，注?到 SqlSessionFactory 中，再創建事務，將 SqlSessionFactory 注?到創建的 SqlSessionTemplate 中，最后將 SqlSessionTemplate 注?到對應的 Mapper 包路徑下。其中需要指定分庫的 Mapper 包路徑。注意，在多數據源的情況下，我們不需要在啟動類添加：`@MapperScan("com.xxx.mapper")` 的注解。這樣 MyBatis 多數據源的配置就完成了 ## 測試 ~~~ @Autowired private User1Mapper userMapper; @Test public void testInsert() throws Exception { userMapper.insert(new User("aammx", "a123456", UserSexEnum.MAN)); userMapper.insert(new User("bbmmx", "b123456", UserSexEnum.WOMAN)); userMapper.insert(new User("ccmmx", "b123456", UserSexEnum.WOMAN)); // Assert.assertEquals(3, userMapper.getAll().size()); } ~~~