MyBatis(七)：mybatis Java API编程实现增、删、改、查的用法

最近工作中用到了mybatis的Java API方式进行开发，顺便也整理下该功能的用法，接下来会针对基本部分进行学习：

Mybatis官网给了具体的文档，但是并没有对以上用法具体介绍，因此在这里整理下，以便以后工作用到时，可以参考。

本章主要增、删、改、查的用法进行学习（本章将结合Spring自动注入《Spring（二十三）：Spring自动注入的实现方式》），下边文章分为以下几个步骤：

1）新建maven，并引入spring/mybatis/mybatis-spring/mysql/druid/junit包，新增配置spring-config.xml/mybaits-config.xml/jdbc.properties配置文件；

2）在mysql中新建mydb，并创建article,article_category两张表；

3）新增用法；

4）修改用法；

5）查询用法；

6）删除用法。

1）新建maven，并引入spring/mybatis/mybatis-spring/mysql/druid/junit包，新增配置spring-config.xml/mybaits-config.xml/jdbc.properties配置文件

新建一个maven项目(Learn-Spring-01，源码以上处至github：https://github.com/478632418/mybatis-spring-auto-annonation/tree/master/Learn-Spring-01)，项目pom.xml中引入以下包：

spring相关包：

        。。。。。

        <org.springframework.version>5.2.0.RELEASE</org.springframework.version>
        <org.mybatis.version>3.4.6</org.mybatis.version>
        <com.alibaba.version>1.1.21</com.alibaba.version>
        <mysql.version>8.0.11</mysql.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-webmvc</artifactId>
            <version>${org.springframework.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-tx</artifactId>
            <version>${org.springframework.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>${org.springframework.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-core</artifactId>
            <version>${org.springframework.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-beans</artifactId>
            <version>${org.springframework.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>${org.springframework.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context-support</artifactId>
            <version>${org.springframework.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-aop</artifactId>
            <version>${org.springframework.version}</version>
        </dependency>
        <dependency>
            <groupId>aspectj</groupId>
            <artifactId>aspectjweaver</artifactId>
            <version>1.5.4</version>
        </dependency>
        <dependency>
            <groupId>aspectj</groupId>
            <artifactId>aspectjrt</artifactId>
            <version>1.5.4</version>
        </dependency>
        <!-- Sping Test相关依赖 -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-test</artifactId>
            <version>${org.springframework.version}</version>
        </dependency>
。。。

mybatis、mybatis-spring包

        <!--MyBatis -->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>${org.mybatis.version}</version>
        </dependency>
        <!-- Mybatis自身实现的Spring整合依赖 -->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis-spring</artifactId>
            <version>2.0.3</version>
        </dependency>

mysql和druid包

        <!--MySql数据库驱动 -->
        <!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>${com.alibaba.version}</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>${mysql.version}</version>
        </dependency>

junit包：

        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>

新建以下类：

实体类：

com.dx.test.model.Article.java

package com.dx.test.model;

import java.util.Date;

/**
 * 文章详情
 */
public class Article {
    private Long id; // 文章id
    private String title; // 文章标题
    private String content; // 文章内容
    private Integer categoryId; // 文章分类id

    private String createUser; // 新建用户
    private String createUserId;// 新建用户id
    private Date createTime; // 新建时间
    private String updateUser; // 修改用户
    private String updateUserId;// 修改用户id
    private Date updateTime; // 修改时间
    private Integer version; // 乐观锁版本号

    private ArticleCategory articleCategory; // [扩展字段]文章分类实体

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }
    public String getContent() {
        return content;
    }

    public void setContent(String content) {
        this.content = content;
    }

    public Integer getCategoryId() {
        return categoryId;
    }

    public void setCategoryId(Integer categoryId) {
        this.categoryId = categoryId;
    }

    public String getCreateUser() {
        return createUser;
    }

    public void setCreateUser(String createUser) {
        this.createUser = createUser;
    }

    public String getCreateUserId() {
        return createUserId;
    }

    public void setCreateUserId(String createUserId) {
        this.createUserId = createUserId;
    }

    public Date getCreateTime() {
        return createTime;
    }

    public void setCreateTime(Date createTime) {
        this.createTime = createTime;
    }

    public String getUpdateUser() {
        return updateUser;
    }

    public void setUpdateUser(String updateUser) {
        this.updateUser = updateUser;
    }

    public String getUpdateUserId() {
        return updateUserId;
    }

    public void setUpdateUserId(String updateUserId) {
        this.updateUserId = updateUserId;
    }

    public Date getUpdateTime() {
        return updateTime;
    }

    public void setUpdateTime(Date updateTime) {
        this.updateTime = updateTime;
    }

    public Integer getVersion() {
        return version;
    }

    public void setVersion(Integer version) {
        this.version = version;
    }

    public ArticleCategory getArticleCategory() {
        return articleCategory;
    }

    public void setArticleCategory(ArticleCategory articleCategory) {
        this.articleCategory = articleCategory;
    }

    @Override
    public String toString() {
        return "Article [id=" + id + ", title=" + title + ", content=" + content
                + ", categoryId=" + categoryId + ", articleCategory=" + articleCategory + "]";
    }
}

View Code

com.dx.test.model.ArticleCategory.java

package com.dx.test.model;

import java.util.Date;

/**
 * 文章分类
 * */
public class ArticleCategory {
    private Integer id; //文章分类id
    private String title; // 文章分类名称
    private String imgSrc; // 文章分类banner图片
    private String description; // 文章分类描述

    private String createUser; // 新建用户
    private String createUserId;// 新建用户id
    private Date createTime; // 新建时间
    private String updateUser; // 修改用户
    private String updateUserId;// 修改用户id
    private Date updateTime; // 修改时间
    private Integer version; // 乐观锁版本号
    
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getTitle() {
        return title;
    }
    public void setTitle(String title) {
        this.title = title;
    }
    public String getImgSrc() {
        return imgSrc;
    }
    public void setImgSrc(String imgSrc) {
        this.imgSrc = imgSrc;
    }
    public String getDescription() {
        return description;
    }
    public void setDescription(String description) {
        this.description = description;
    }
    
    public String getCreateUser() {
        return createUser;
    }
    public void setCreateUser(String createUser) {
        this.createUser = createUser;
    }
    public String getCreateUserId() {
        return createUserId;
    }
    public void setCreateUserId(String createUserId) {
        this.createUserId = createUserId;
    }
    public Date getCreateTime() {
        return createTime;
    }
    public void setCreateTime(Date createTime) {
        this.createTime = createTime;
    }
    public String getUpdateUser() {
        return updateUser;
    }
    public void setUpdateUser(String updateUser) {
        this.updateUser = updateUser;
    }
    public String getUpdateUserId() {
        return updateUserId;
    }
    public void setUpdateUserId(String updateUserId) {
        this.updateUserId = updateUserId;
    }
    public Date getUpdateTime() {
        return updateTime;
    }
    public void setUpdateTime(Date updateTime) {
        this.updateTime = updateTime;
    }
    public Integer getVersion() {
        return version;
    }
    public void setVersion(Integer version) {
        this.version = version;
    }
    @Override
    public String toString() {
        return "ArticleCategory [id=" + id + ", title=" + title + ", imgSrc=" + imgSrc + ", description=" + description
                + "]";
    }
}

View Code

Mybatis Java API mapper类：
com.dx.test.mapper.sqlprovider.ArticleMapper.java
com.dx.test.mapper.sqlprovider.ArticleCategoryMapper.java

Mybatis Java API mapper的sql生成类：
com.dx.test.mapper.sqlprovider.ArticleSqlProvder.java
com.dx.test.mapper.sqlprovider.ArticleCategorySqlProvder.java

Repository层（类上都使用@Repository注解）：
com.dx.test.repository.ArticleDao.java
com.dx.test.repository.ArticleCategoryDao.java

Service层（类上都使用@Service注解）：
com.dx.test.service.ArticleService.java
com.dx.test.service.ArticleCategoryService.java

包含main入口类：
com.dx.test.App.java

在/src/main/resources下新建jdbc.properties、mybatis-config.xml、spring-config.xml配置文件：

1）jdbc.properties中配置mysql的user/pwd/url/driver/其他先关配置；

2）mybatis-config.xml配置mybatis配置；

3）spring-config.xml配置spring自动注入，以及mybatis整合。

jdbc.properties

#jdbc settings
jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/mydb?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&useSSL=false
jdbc.username=root
jdbc.password=123456

#pool settings
jdbc.pool.init=1
jdbc.pool.minIdle=3
jdbc.pool.maxActive=20

#jdbc.testSql=SELECT 'x'
jdbc.testSql=SELECT 'x' FROM DUAL

mybatis-config.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>
    <!--配置全局属性-->
    <settings>
        <!-- 打开延迟加载的开关 -->
        <setting name="lazyLoadingEnabled" value="true"/>
        <!-- 将积极加载改为消极加载（即按需加载） -->
        <setting name="aggressiveLazyLoading" value="false"/>
        <!-- 打开全局缓存开关（二级缓存）默认值就是 true -->
        <setting name="cacheEnabled" value="true"/>
        <!--使用jdbc的getGeneratekeys获取自增主键值-->
        <setting name="useGeneratedKeys" value="true"/>
        <!--使用列别名替换别名　　默认true select name as title form table; -->
        <setting name="useColumnLabel" value="true"/>
        <!--开启驼峰命名转换-->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
        <!--打印sql日志-->
        <setting name="logImpl" value="STDOUT_LOGGING" />
    </settings>
    
    <!-- 引用db.properties配置文件 -->
    <!-- 
    <properties resource="db.properties"/> 
    <typeAliases>
        <package name="com.dx.test.model"/>
    </typeAliases>
    -->
    <!-- 对事务的管理和连接池的配置 -->
    <!-- 
    <environments default="mysql_jdbc">
        <environment id="oracle_jdbc">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${name}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>

        <environment id="mysql_jdbc">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${name}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>
    -->
    
    <!--
    <mappers>
        <mapper resource="resources/mapper/TaskAutoExecutePlanMapper.xml"/>
    </mappers>
    -->
    <mappers>
        <mapper class="com.dx.test.mapper.ArticleCategoryMapper"></mapper>
        <mapper class="com.dx.test.mapper.ArticleMapper"></mapper>
    </mappers>
</configuration>

备注：

1）因这里采用的是mybatis与spring整合，因此mysql先关数据源信息不在mybatis-config.xml中配置，而是配置spring-config.xml的dataSource的bean下；

2）mappers节点下的mapper需要指定mapper类，又因这里是采用的mybatis java api方式（只需要在ArticleCategoryMapper和ArticleMapper上添加上@Mapper注解即可），因此不再需要配置*Mapper.xml。

spring-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xmlns:context="http://www.springframework.org/schema/context"
 xsi:schemaLocation="http://www.springframework.org/schema/beans
 http://www.springframework.org/schema/beans/spring-beans-4.0.xsd
 http://www.springframework.org/schema/context
 http://www.springframework.org/schema/context/spring-context-4.0.xsd ">
 
    <!-- bean annotation driven -->
    <context:annotation-config />
    <context:component-scan base-package="com.dx.test.repository,com.dx.test.mapper,com.dx.test.service" />
    
    <!-- 配置整合Mybatis过程 -->
    <!-- 配置数据库相关参数 properties的属性：${url} -->
    <context:property-placeholder location="classpath:jdbc.properties"/>
    <!-- 
    <bean id="propertyPlaceholderConfigurer" class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
        <property name="locations">
            <list>
                <value>classpath:jdbc.properties</value>
            </list>
        </property>
    </bean>
    -->
    
    <!--2.配置连接池属性 -->
    <!-- 数据源配置, 使用 Druid 数据库连接池 -->
    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource"
          init-method="init" destroy-method="close">
        <!-- 数据源驱动类可不写，Druid默认会自动根据URL识别DriverClass -->
        <property name="driverClassName" value="${jdbc.driver}"/>

        <!-- 基本属性 url、user、password -->
        <property name="url" value="${jdbc.url}"/>
        <property name="username" value="${jdbc.username}"/>
        <property name="password" value="${jdbc.password}"/>

        <!-- 配置初始化大小、最小、最大 -->
        <property name="initialSize" value="${jdbc.pool.init}"/>
        <property name="minIdle" value="${jdbc.pool.minIdle}"/>
        <property name="maxActive" value="${jdbc.pool.maxActive}"/>

        <!-- 配置获取连接等待超时的时间 -->
        <property name="maxWait" value="60000"/>

        <!-- 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒 -->
        <property name="timeBetweenEvictionRunsMillis" value="60000"/>

        <!-- 配置一个连接在池中最小生存的时间，单位是毫秒 -->
        <property name="minEvictableIdleTimeMillis" value="300000"/>

        <property name="validationQuery" value="${jdbc.testSql}"/>
        <property name="testWhileIdle" value="true"/>
        <property name="testOnBorrow" value="false"/>
        <property name="testOnReturn" value="false"/>
        <!-- 打开PSCache，并且指定每个连接上PSCache的大小（Oracle使用） -->
        <!-- 
         <property name="poolPreparedStatements" value="true"/>
         <property name="maxPoolPreparedStatementPerConnectionSize" value="20"/>
        -->
        <!-- 配置监控统计拦截的filters -->
        <property name="filters" value="stat"/>
    </bean>

    <!--3.配置SqlSessionFactory对象 -->
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <!--注入数据库连接池 -->
        <property name="dataSource" ref="dataSource"/>
        <!--配置mybatis全局配置文件:mybatis-config.xml -->
        <property name="configLocation" value="classpath:mybatis-config.xml"/>
        <!-- 因为我们这采用的是Mybatis Java API方式，因此不需要配置 -->
        <property name="typeAliasesPackage" value="com.dx.test.model"/>
        <!--扫描entity包,使用别名,多个用;隔开 -->
        <!--扫描sql配置文件:mapper需要的xml文件 -->
        <!-- 
        <property name="mapperLocations" value="classpath:mapper/*.xml"/> 
        -->
    </bean>
    
    <!-- Mapper接口所在包名，Spring会自动查找其下的类 -->                                                       
    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">                                         
        <property name="basePackage" value="com.dx.test.mapper" />                                             
        <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"></property>                     
    </bean> 
    
    <!-- 如果想对单个mapper类关联上sqlSessionFactory，可以这么使用，具体参考：http://mybatis.org/spring/getting-started.html -->
    <!-- 
    <bean id="articlerMapper" class="org.mybatis.spring.mapper.MapperFactoryBean">
      <property name="mapperInterface" value="com.dx.test.dao.mapper.ArticleMapper" />
      <property name="sqlSessionFactory" ref="sqlSessionFactory" />
    </bean>   
    -->                                                                                            
                                                                                                              
    <!-- (事务管理)transaction manager, use JtaTransactionManager for global tx -->                          
    <bean id="transactionManager"                                                                            
        class="org.springframework.jdbc.datasource.DataSourceTransactionManager">                            
        <property name="dataSource" ref="dataSource" />                                                      
    </bean>     
</beans>

备注：

上边配置文件spring-config.xml配置包含两大部分配置：

1）spring自动注入配置；

2）spring注入sqlSessionFatcory到mybatis：

2.1）引入jdbc.properties配置信息到配置文件中；

2.2）dataSource bean的定义，这里采用的是com.alibaba.druid.pool.DruidDataSource作为dataSource实例；

2.3）使用sqlSessionFactory bean，需要引入mybatis-config.xml配置、dataSource bean实例；

2.4）使用org.mybatis.spring.mapper.MapperScannerConfigurer试下扫描mapper包路径下mapper，并关联上sqlSesssionFactory；

2.5）事务管理。

2）在mysql中新建mydb，并创建article,article_category两张表

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for article
-- ----------------------------
DROP TABLE IF EXISTS `article`;
CREATE TABLE `article` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增id',
  `category_id` int(11) NOT NULL COMMENT '文章分类id',
  `title` varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '文章标题',
  `content` varchar(2048) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '文章内容',
  `create_user` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '新建人',
  `create_user_id` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '新建人id',
  `create_time` date NOT NULL COMMENT '新建时间',
  `update_user` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '修改人',
  `update_user_id` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '修改人id',
  `update_time` date NOT NULL COMMENT '修改时间',
  `version` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_category_id` (`category_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for article_category
-- ----------------------------
DROP TABLE IF EXISTS `article_category`;
CREATE TABLE `article_category` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增id',
  `title` varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '文章分类名称',
  `img_src` varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '文章分类banner图片',
  `description` varchar(512) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '文章分类描述',
  `create_user` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '新建人',
  `create_user_id` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '新建人id',
  `create_time` date NOT NULL COMMENT '新建时间',
  `update_user` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '修改人',
  `update_user_id` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '修改人id',
  `update_time` date NOT NULL COMMENT '修改时间',
  `version` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8;

新建数据库mydb，并新建article（文章表）、article_category（文章分类表）

3）新增用法

Mybatis Java API mapper类：
com.dx.test.mapper.sqlprovider.ArticleMapper.java
com.dx.test.mapper.sqlprovider.ArticleCategoryMapper.java

ArticleMapper.java

package com.dx.test.mapper;
...
@Mapper
public interface ArticleMapper {
    /**
     * 入库分类
     * 
     * @param articleCategory 待入库实体
     * @return 影响条数
     */
    @Options(useCache = true, flushCache = Options.FlushCachePolicy.TRUE, useGeneratedKeys = true, keyProperty = "id", keyColumn = "id")
    @InsertProvider(type = ArticleSqlProvider.class, method = "insert")
    int insert(Article article);
}

ArticleCategoryMapper.java

package com.dx.test.mapper;
...
@Mapper
public interface ArticleCategoryMapper {
    /**
     * 入库文章分类
     * 
     * @param articleCategory 待入库实体
     * @return 影响条数
     */
    @Options(useCache = true, flushCache = Options.FlushCachePolicy.TRUE, useGeneratedKeys = true, keyProperty = "id", keyColumn = "id")
    @InsertProvider(type = ArticleCategorySqlProvider.class, method = "insert")
    int insert(ArticleCategory articleCategory);
}

Mybatis Java API mapper的sql生成类：
com.dx.test.mapper.sqlprovider.ArticleSqlProvder.java
com.dx.test.mapper.sqlprovider.ArticleCategorySqlProvder.java

ArticleSqlProvider.java

package com.dx.test.mapper.sqlprovider;
。。。
public class ArticleSqlProvider {
    /**
     * 生成文章入库SQL
     * 
     * @param article 待插入文章实体
     * @return 生成SQL
     */
    public String insert(final Article article) {
        return new SQL() {
            {
                INSERT_INTO("article");
                INTO_COLUMNS("title", "content", "category_id", "create_user", "create_user_id", "create_time","update_user", "update_user_id", "update_time", "version");
                INTO_VALUES("#{title}", "#{content}", "#{categoryId}", "#{createUser}", "#{createUserId}","now()", "#{updateUser}", "#{updateUserId}", "now()", "0");
            }
        }.toString();
    }
}

ArticleCategorySqlProvider.java

package com.dx.test.mapper.sqlprovider;
。。。
public class ArticleCategorySqlProvider {
    /**
     * 生成插入文章分类的SQL
     * @param articleCategory文章分类
     * @return 返回插入文章SQL
     * */
    public String insert(ArticleCategory articleCategory) {
        return new SQL() {{
            INSERT_INTO("article_category");
            INTO_COLUMNS("title","img_src","description", "create_user", "create_user_id", "create_time","update_user", "update_user_id", "update_time", "version");
            INTO_VALUES("#{title}","#{imgSrc}","#{description}","#{createUser}", "#{createUserId}","now()", "#{updateUser}", "#{updateUserId}", "now()", "0");
        }}.toString();
    }
}

Repository层（类上都使用@Repository注解）：
com.dx.test.repository.ArticleDao.java
com.dx.test.repository.ArticleCategoryDao.java

ArticleDao.java

package com.dx.test.repository;
。。。
@Repository
public class ArticleDao implements ArticleMapper {
    @Autowired()
    private ArticleMapper articleMapper;

    @Override
    public int insert(Article article) {
        return this.articleMapper.insert(article);
    }
}

ArticleCategoryDao.java

package com.dx.test.repository;
。。。
@Repository
public class ArticleCategoryDao implements ArticleCategoryMapper {
    @Autowired
    private ArticleCategoryMapper articleCategoryMapper;
    /**
     * 插入文章分类
     * @param articleCategory 插入文章分类
     * @return 相应条数
     * */
    @Override
    public int insert(ArticleCategory articleCategory) {
        int result= this.articleCategoryMapper.insert(articleCategory);
        return result;
    }
}

Service层（类上都使用@Service注解）：
com.dx.test.service.ArticleService.java
com.dx.test.service.ArticleCategoryService.java

ArticleService.java

package com.dx.test.service;
...
@Service
public class ArticleService implements ArticleMapper{
    @Autowired
    private ArticleDao articleDao;

    @Override
    public int insert(Article article) {
        return this.articleDao.insert(article);
    }
}

ArticleCategoryService.java

package com.dx.test.service;
...
@Service
public class ArticleCategoryService implements ArticleCategoryMapper {
    @Autowired
    private ArticleCategoryDao articleCategoryDao;
    
    @Override
    public int insert(ArticleCategory articleCategory) {
        return this.articleCategoryDao.insert(articleCategory);
    }
}

4）修改用法

Mybatis Java API mapper类：
com.dx.test.mapper.sqlprovider.ArticleMapper.java
com.dx.test.mapper.sqlprovider.ArticleCategoryMapper.java

ArticleMapper.java

package com.dx.test.mapper;
...
@Mapper
public interface ArticleMapper {
    /**
     * 根据文章id，删除文章
     * 
     * @param id 文章id
     * @return 影响条数
     */
    @Options(useCache = true, flushCache = Options.FlushCachePolicy.TRUE)
    @UpdateProvider(type = ArticleSqlProvider.class, method = "update")
    int update(Article article);
}

ArticleCategoryMapper.java

package com.dx.test.mapper;
...
@Mapper
public interface ArticleCategoryMapper {
    /**
     * 根据文章id，删除文章
     * 
     * @param id 文章id
     * @return 影响条数
     */
    @Options(useCache = true, flushCache = Options.FlushCachePolicy.TRUE)
    @UpdateProvider(type = ArticleCategorySqlProvider.class, method = "update")
    int update(ArticleCategory articleCategory);
}

Mybatis Java API mapper的sql生成类：
com.dx.test.mapper.sqlprovider.ArticleSqlProvder.java
com.dx.test.mapper.sqlprovider.ArticleCategorySqlProvder.java

ArticleSqlProvder.java

package com.dx.test.mapper.sqlprovider;
。。。
public class ArticleSqlProvider {
    public String update(final Article article) {
        StringBuilder sql = new StringBuilder();

        sql.append("update `article` set `id`=#{id}");
        if (article.getCategoryId() != null) {
            sql.append(", `category_id`=#{categoryId}");
        }
        if (article.getTitle() != null) {
            sql.append(", `title`=#{title}");
        }
        if (article.getContent() != null) {
            sql.append(", `content`=#{content}");
        }
        if (article.getCreateUser() != null) {
            sql.append(", `create_user` = #{createUser}");
        }
        if (article.getCreateUserId() != null) {
            sql.append(", `create_user_id` = #{createUserId}");
        }
        sql.append(", `update_time` = now()");
        if (article.getUpdateUser() != null) {
            sql.append(", `update_user` = #{updateUser}");
        }
        if (article.getUpdateUserId() != null) {
            sql.append(", `update_user_id` = #{updateUserId}");
        }

        sql.append(", `version` = `version` + 1");
        sql.append(" WHERE `id` = #{id} AND `version` = #{version}");

        return sql.toString();
    }
}

ArticleCategorySqlProvder.java

package com.dx.test.mapper.sqlprovider;
。。。
public class ArticleCategorySqlProvider {
    /**
     * 生成修改文章分类SQL
     * @param articleCategory 文章分类实体
     * @return 返回修改文章分类SQL
     * */
    public String update(ArticleCategory articleCategory) {
        StringBuilder sql=new StringBuilder();

        sql.append("update `article_category` set id=#{id}");
        if (articleCategory.getTitle() != null) {
            sql.append(", `title`=#{title}");
        }
        if (articleCategory.getImgSrc() != null) {
            sql.append(", `img_src`=#{imgSrc}");
        }
        if(articleCategory.getDescription()!=null) {
            sql.append(", `description`=#{description}");
        }
        if (articleCategory.getCreateUser() != null) {
            sql.append(", `create_user` = #{createUser}");
        }
        if (articleCategory.getCreateUserId() != null) {
            sql.append(", `create_user_id` = #{createUserId}");
        }
        sql.append(", `update_time` = now()");
        if (articleCategory.getUpdateUser() != null) {
            sql.append(", `update_user` = #{updateUser}");
        }
        if (articleCategory.getUpdateUserId() != null) {
            sql.append(", `update_user_id` = #{updateUserId}");
        }

        sql.append(", `version` = `version` + 1");
        sql.append(" WHERE `id` = #{id} AND `version` = #{version}");

        return sql.toString();
    }
}

5）查询用法

Mybatis Java API mapper类：
com.dx.test.mapper.sqlprovider.ArticleMapper.java
com.dx.test.mapper.sqlprovider.ArticleCategoryMapper.java

ArticleMapper.java

package com.dx.test.mapper;
...
@Mapper
public interface ArticleMapper {
    /**
     * 根据文章id,查询文章详情
     * 
     * @param id 文章id
     * @return 返回查询到的文章详情
     */
    @Options(useCache = true, flushCache = Options.FlushCachePolicy.FALSE, timeout = 60000)
    @Results(id = "articleResultWithCategory", value = { @Result(property = "id", column = "id", id = true),
            @Result(property = "categoryId", column = "category_id"),
            @Result(property = "articleCategory", javaType = ArticleCategory.class, one = @One(select = "com.dx.test.mapper.ArticleCategoryMapper.getById"), column = "category_id"),
            @Result(property = "title", column = "title"), @Result(property = "content", column = "content"),
            @Result(property = "createUser", column = "create_user"),
            @Result(property = "createUserId", column = "create_user_id"),
            @Result(property = "createTime", column = "create_time"),
            @Result(property = "updateUser", column = "update_user"),
            @Result(property = "updateUserId", column = "update_user_id"),
            @Result(property = "updateTime", column = "update_time"),
            @Result(property = "version", column = "version") })
    @Select({ "select * from article where id=#{id}" })
    Article getById(@Param("id") Long id);

    /**
     * 根据文章id,查询文章详情
     * 
     * @param id 文章id
     * @return 返回查询到的文章详情
     */
    @Options(useCache = true, flushCache = Options.FlushCachePolicy.FALSE, timeout = 60000)
    @Results(id = "articleResultWithoutCategory", value = { @Result(property = "id", column = "id", id = true),
            @Result(property = "categoryId", column = "category_id"), @Result(property = "title", column = "title"),
            @Result(property = "content", column = "content"), @Result(property = "createUser", column = "create_user"),
            @Result(property = "createUserId", column = "create_user_id"),
            @Result(property = "createTime", column = "create_time"),
            @Result(property = "updateUser", column = "update_user"),
            @Result(property = "updateUserId", column = "update_user_id"),
            @Result(property = "updateTime", column = "update_time"),
            @Result(property = "version", column = "version") })
    @Select({ "select * from article where id=#{id}" })
    Article getByIdWithoutCategory(@Param("id") Long id);

    /**
     * 根据条件查询文章列表
     * 
     * @param article 查询条件
     * @return 查询到的文章列表
     */
    @Options(useCache = true, flushCache = Options.FlushCachePolicy.FALSE, timeout = 60000)
    @SelectProvider(type = ArticleSqlProvider.class, method = "queryList")
    @ResultMap(value = "articleResultWithoutCategory")
    List<Article> queryList(Article article);
}

ArticleCategoryMapper.java

package com.dx.test.mapper;
...
@Mapper
public interface ArticleCategoryMapper {
    /**
     * 根据文章分类id,查询文件分类详情
     * @param id 文章分类id
     * @return 查询到的文章分类详情
     * */
    @Options(useCache = true, flushCache = Options.FlushCachePolicy.FALSE, timeout = 60000)
    @Results(id="articleCategoryResult",value = {
            @Result(property = "id",column = "id",id = true),
            @Result(property = "title",column = "title"),
            @Result(property = "imgSrc",column = "img_src"),
            @Result(property = "description",column = "description"),
            @Result(property = "createUser",column = "create_user"),
            @Result(property = "createUserId",column = "create_user_id"),
            @Result(property = "createTime",column = "create_time"),
            @Result(property = "updateUser",column = "update_user"),
            @Result(property = "updateUserId",column = "update_user_id"),
            @Result(property = "updateTime",column = "update_time"),
            @Result(property = "version",column = "version")
    })
    @Select("select * from article_category where id=#{id}")
    ArticleCategory getById(Integer id);
    
    @Options(useCache = true, flushCache = Options.FlushCachePolicy.FALSE, timeout = 60000)
    @ResultMap(value ="articleCategoryResult")
    @SelectProvider(type = ArticleCategorySqlProvider.class, method = "queryList")
    List<ArticleCategory> queyrList(ArticleCategory articleCategory);
}

Mybatis Java API mapper的sql生成类：
com.dx.test.mapper.sqlprovider.ArticleSqlProvder.java
com.dx.test.mapper.sqlprovider.ArticleCategorySqlProvder.java

ArticleSqlProvder.java

package com.dx.test.mapper.sqlprovider;
...
public class ArticleSqlProvider {
    public String queryList(Article article) {
        StringBuilder sql=new StringBuilder();
        sql.append("select * from `article_category` where 1=1 ");
        
        if(article.getId()!=null) {
            sql.append(" AND `id`=#{id}");
        }
        if (article.getCategoryId() != null) {
            sql.append("  AND `category_id`=#{categoryId}");
        }
        if (article.getTitle() != null) {
            sql.append("  AND `title` like CONCAT(CONCAT('%', #{title}), '%')");
        }
        if (article.getCreateTime() != null) {
            sql.append("  AND `create_time` = #{createTime}");
        }
        if (article.getCreateUser() != null) {
            sql.append("  AND `create_user` = #{createUser}");
        }
        if (article.getCreateUserId() != null) {
            sql.append("  AND `create_user_id` = #{createUserId}");
        }
        if (article.getUpdateTime() != null) {
            sql.append("  AND `update_time` = #{updateTime}");
        }
        if (article.getUpdateUser() != null) {
            sql.append("  AND `update_user` = #{updateUser}");
        }
        if (article.getUpdateUserId() != null) {
            sql.append("  AND `update_user_id` = #{updateUserId}");
        }
        if (article.getVersion() != null) {
            sql.append("  AND `version` = #{version}");
        }
        sql.append("  ORDER BY `id` DESC");
        
        return sql.toString();
    }
}

ArticleCategorySqlProvder.java

package com.dx.test.mapper.sqlprovider;
...
public class ArticleCategorySqlProvider {
    public String queryList(ArticleCategory articleCategory) {
        StringBuilder sql=new StringBuilder();
        sql.append("select * from `article_category` where 1=1 ");
        
        if(articleCategory.getId()!=null) {
            sql.append(" AND `id`=#{id}");
        }
        if (articleCategory.getTitle() != null) {
            sql.append("  AND `title` like CONCAT(CONCAT('%', #{title}), '%')");
        }
        if (articleCategory.getCreateTime() != null) {
            sql.append("  AND `create_time` = #{createTime}");
        }
        if (articleCategory.getCreateUser() != null) {
            sql.append("  AND `create_user` = #{createUser}");
        }
        if (articleCategory.getCreateUserId() != null) {
            sql.append("  AND `create_user_id` = #{createUserId}");
        }
        if (articleCategory.getUpdateTime() != null) {
            sql.append("  AND `update_time` = #{updateTime}");
        }
        if (articleCategory.getUpdateUser() != null) {
            sql.append("  AND `update_user` = #{updateUser}");
        }
        if (articleCategory.getUpdateUserId() != null) {
            sql.append("  AND `update_user_id` = #{updateUserId}");
        }
        if (articleCategory.getVersion() != null) {
            sql.append("  AND `version` = #{version}");
        }
        sql.append("  ORDER BY `id` DESC");
        
        return sql.toString();
    }
}

6）删除用法

Mybatis Java API mapper类：
com.dx.test.mapper.sqlprovider.ArticleMapper.java
com.dx.test.mapper.sqlprovider.ArticleCategoryMapper.java

ArticleMapper.java

package com.dx.test.mapper;
。。。
@Mapper
public interface ArticleMapper {
    /**
     * 根据文章id，删除文章
     * 
     * @param id 文章id
     * @return 影响条数
     */
    @Options(useCache = true, flushCache = Options.FlushCachePolicy.TRUE)
    @Delete("delete from article where id=#{id}")
    int delete(Long id);
}

ArticleCategoryMapper.java

package com.dx.test.mapper;
...
@Mapper
public interface ArticleCategoryMapper {
    /**
     * 根据文章分类id，删除文章分类
     * 
     * @param id 文章分类id
     * @return 影响条数
     */
    @Options(useCache = true, flushCache = Options.FlushCachePolicy.TRUE)
    @Delete("delete from article_category where id=#{id}")
    int delete(Integer id);
}

7）入口App.java类和测试类AppTest.java

App.java

package com.dx.test;

import java.util.Date;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import com.dx.test.model.Article;
import com.dx.test.model.ArticleCategory;
import com.dx.test.service.ArticleCategoryService;
import com.dx.test.service.ArticleService;

/**
 * Hello SpringFramework!
 */
public class App {
    public static void main(String[] args) {
        @SuppressWarnings("resource")
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("classpath:spring-config.xml");
        ArticleService articleService = applicationContext.getBean(ArticleService.class);
        ArticleCategoryService articleCategoryService = applicationContext.getBean(ArticleCategoryService.class);
        
        ArticleCategory articleCategory=new ArticleCategory();
        articleCategory.setTitle("test category");
        articleCategory.setImgSrc("http://im.dic/img/abd.gif");
        articleCategory.setDescription("description");
        articleCategory.setCreateUser("test");
        articleCategory.setCreateUserId("11");
        articleCategory.setCreateTime(new Date());
        articleCategoryService.insert(articleCategory);
        
        Article waitingInsertArticle=new Article();
        waitingInsertArticle.setCategoryId(articleCategory.getId());
        waitingInsertArticle.setTitle("test");
        waitingInsertArticle.setContent("test content");
        waitingInsertArticle.setCreateUser("test");
        waitingInsertArticle.setCreateUserId("11");
        waitingInsertArticle.setCreateTime(new Date());
        
        int result=articleService.insert(waitingInsertArticle);
        System.out.println(result);
        
        Article article = articleService.getById(waitingInsertArticle.getId());
        System.out.println(article);
    }
}

AppTest.java

package com.dx.test;

import java.util.Date;
import java.util.List;
import java.util.Random;

import org.junit.After;
import org.junit.Assert;
import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import com.dx.test.model.ArticleCategory;
import com.dx.test.service.ArticleCategoryService;

/**
 * Unit test for simple App.
 */
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration({ "classpath:spring-config.xml" })
public class AppTest {
    @Autowired
    private ArticleCategoryService articleCategoryService;
    private ArticleCategory articleCategory;
    private final String title = "category test title";
    private final String titleNew = "category test title new";

    @Before
    public void testArticleCategoryCreate() {
        ArticleCategory articleCategory = new ArticleCategory();
        articleCategory.setTitle(title);
        articleCategory.setDescription("category description");
        articleCategory.setImgSrc("http://www.test.com/img/category/img-" + new Random().nextInt(1000) + ".gif");
        articleCategory.setCreateTime(new Date());
        articleCategory.setCreateUser("create user");
        articleCategory.setCreateUserId("user-" + new Random().nextInt(1000));

        int result = this.articleCategoryService.insert(articleCategory);
        this.articleCategory = articleCategory;
        Assert.assertEquals(result, 1);
    }

    @Test
    public void testSelectById() {
        ArticleCategory articleCategory = this.articleCategoryService.getById(this.articleCategory.getId());
        Assert.assertEquals(articleCategory.getId(), this.articleCategory.getId());
        Assert.assertEquals(articleCategory.getTitle(), this.title);
    }

    @Test
    public void testQueryList() {
        ArticleCategory queryArticleCategory = new ArticleCategory();
        queryArticleCategory.setTitle("test");

        List<ArticleCategory> queryResultList = this.articleCategoryService.queyrList(queryArticleCategory);
        Assert.assertFalse(queryResultList.size() == 0);
    }

    @Test
    public void testUpdate() {
        ArticleCategory articleCategory = this.articleCategoryService.getById(this.articleCategory.getId());
        Assert.assertEquals(articleCategory.getId(), this.articleCategory.getId());
        Assert.assertEquals(articleCategory.getTitle(), this.title);

        articleCategory.setTitle(this.titleNew);
        int result=this.articleCategoryService.update(articleCategory);
        Assert.assertEquals(result, 1);
        
        articleCategory = this.articleCategoryService.getById(this.articleCategory.getId());
        Assert.assertEquals(articleCategory.getId(), this.articleCategory.getId());
        Assert.assertEquals(articleCategory.getTitle(), this.title);
    }

    @After
    public void testDelete() {
        int result = this.articleCategoryService.delete(this.articleCategory.getId());
        Assert.assertEquals(result, 1);
    }
}

posted @ 2019-11-16 11:27 cctext 阅读(1562) 评论(0) 编辑收藏举报

刷新页面返回顶部

yy

基础才是编程人员应该深入研究的问题，警告自己问题解决不了时，多从运行原理底层研究后再考虑方案。

MyBatis(七)：mybatis Java API编程实现增、删、改、查的用法

1）新建maven，并引入spring/mybatis/mybatis-spring/mysql/druid/junit包，新增配置spring-config.xml/mybaits-config.xml/jdbc.properties配置文件

2）在mysql中新建mydb，并创建article,article_category两张表

3）新增用法

4）修改用法

5）查询用法

6）删除用法

7）入口App.java类和测试类AppTest.java

公告