mybatis之高级语法练习二

一、使用动态标签库完成如下方法的编写，所写代码尽可能通用。Selective 表示实体属性为空时常用数据库默认值。

int deleteByPrimaryKey(Integer id);
int insert(User user);
int insertSelective(User user);
User selectByPrimaryKey(Integer id);
List<User> selectByPrimaryKeys(Collection<Integer> ids);
List<User> selectAll();
List<User> selectAllByPage(@Param("start") int start, @Param("size") int size);
int updateByPrimaryKeySelective(User user);
int updateByPrimaryKey(User user);

二、主体框架

①User实体类

package com.allen.entity;


import java.util.Date;

public class User {

  private Integer id;
  private String name;
  private String loginName;
  private String loginPassword;
  private Integer roleId;
  private Date createTime;
  private Date updateTime;

  public Integer getId() {
    return id;
  }

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

  public String getName() {
    return name;
  }

  public void setName(String name) {
    this.name = name;
  }

  public String getLoginName() {
    return loginName;
  }

  public void setLoginName(String loginName) {
    this.loginName = loginName;
  }

  public String getLoginPassword() {
    return loginPassword;
  }

  public void setLoginPassword(String loginPassword) {
    this.loginPassword = loginPassword;
  }

  public Integer getRoleId() {
    return roleId;
  }

  public void setRoleId(Integer roleId) {
    this.roleId = roleId;
  }

  public Date getCreateTime() {
    return createTime;
  }

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

  public Date getUpdateTime() {
    return updateTime;
  }

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

  @Override
  public String toString() {
    return "\nUser{" +
            "id=" + id +
            ", name='" + name + '\'' +
            ", loginName='" + loginName + '\'' +
            ", loginPassword='" + loginPassword + '\'' +
            ", roleId=" + roleId +
            ", createTime=" + createTime +
            ", updateTime=" + updateTime +
            '}';
  }
}

②UserMapper接口文件

package com.allen.mapper;

import com.allen.entity.User;
import org.apache.ibatis.annotations.Param;

import java.util.Collection;
import java.util.List;

public interface UserMapper {
    int deleteByPrimaryKey(Integer id);
    int insert(User user);
    int insertSelective(User user);
    User selectByPrimaryKey(Integer id);
    List<User> selectByPrimaryKeys(@Param("ids") Collection<Integer> ids);
    List<User> selectAll();
    List<User> selectAllByPage(@Param("start") int start, @Param("size") int size);
    int updateByPrimaryKeySelective(User user);
    int updateByPrimaryKey(User user);
}

③userMapper.xml配置文件

<?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.allen.mapper.UserMapper">
    <resultMap id="BaseResultMap" type="com.allen.entity.User">
        <id column="id" jdbcType="INTEGER" property="id"/>
        <result column="name" jdbcType="VARCHAR" property="name"/>
        <result column="login_name" jdbcType="VARCHAR" property="loginName"/>
        <result column="login_password" jdbcType="VARCHAR" property="loginPassword"/>
        <result column="role_id" jdbcType="INTEGER" property="roleId"/>
        <result column="create_time" jdbcType="TIMESTAMP" property="createTime"/>
        <result column="update_time" jdbcType="TIMESTAMP" property="updateTime"/>
    </resultMap>
    <sql id="User_List">
        id, `name`, login_name, login_password, role_id, create_time, update_time
    </sql>
    <select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
        select
        <include refid="User_List"/>
        from user
        where id = #{id}
    </select>
    <select id="selectByPrimaryKeys" parameterType="java.lang.Integer" resultMap="BaseResultMap">
        select
        <include refid="User_List"/>
        from user
        where id in
        <foreach collection="ids" item="id" open="(" close=")" separator=",">
            #{id}
        </foreach>
    </select>
    <select id="selectAll" resultType="com.allen.entity.User" resultMap="BaseResultMap">
        select
        <include refid="User_List"/>
        from user
    </select>
    <select id="selectAllByPage" resultMap="BaseResultMap">
        select
        <include refid="User_List"/>
        from user
        limit #{start},#{size}
    </select>
    <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
        delete from user
        where id = #{id}
    </delete>
    <insert id="insert" keyColumn="id" keyProperty="id" parameterType="com.allen.entity.User" useGeneratedKeys="true">
        insert into user (`name`, login_name, login_password,
            role_id, create_time
        )
        values (#{name}, #{loginName}, #{loginPassword},
            #{roleId}, now()
        )
    </insert>
    <insert id="insertSelective" keyProperty="id" parameterType="com.allen.entity.User"
            useGeneratedKeys="true">
        insert into user
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="name != null">
                `name`,
            </if>
            <if test="loginName != null">
                login_name,
            </if>
            <if test="loginPassword != null">
                login_password,
            </if>
            <if test="roleId != null">
                role_id,
            </if>
            create_time
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="name != null">
                #{name},
            </if>
            <if test="loginName != null">
                #{loginName},
            </if>
            <if test="loginPassword != null">
                #{loginPassword},
            </if>
            <if test="roleId != null">
                #{roleId},
            </if>
            now()
        </trim>
    </insert>
    <update id="updateByPrimaryKeySelective" parameterType="com.allen.entity.User">
        update user
        <set>
            <if test="name != null">
                `name` = #{name},
            </if>
            <if test="loginName != null">
                login_name = #{loginName},
            </if>
            <if test="loginPassword != null">
                login_password = #{loginPassword},
            </if>
            <if test="roleId != null">
                role_id = #{roleId},
            </if>
            update_time = now()
        </set>
        where id = #{id}
    </update>
    <update id="updateByPrimaryKey" parameterType="com.allen.entity.User">
        update user
        set `name` = #{name},
          login_name = #{loginName},
          login_password = #{loginPassword},
          role_id = #{roleId},
          update_time = now()
        where id = #{id}
  </update>

</mapper>

④mybatis13-conf.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>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC" />
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver" />
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis"/>
                <property name="username" value="root" />
                <property name="password" value="123456" />
            </dataSource>
        </environment>
    </environments>
    <mappers>
       <mapper resource="mapper/userMapper.xml"></mapper>
    </mappers>
</configuration>

⑤AppTest测试类文件

package com.allen;

import com.allen.entity.User;
import com.allen.mapper.UserMapper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.io.IOException;
import java.io.Reader;
import java.util.Date;
import java.util.HashSet;
import java.util.List;

public class AppTest {
    SqlSession session;
    @Before
    public void init() throws IOException {
        Reader reader = Resources.getResourceAsReader("mybatis13-conf.xml");
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
        session = sessionFactory.openSession(true);
    }
    @After
    public void close(){
        session.close();
    }
    @Test
    public void insert(){
        UserMapper mapper = session.getMapper(UserMapper.class);
        User user=new User();
        user.setName("普通用户");
        user.setLoginName("user666");
        user.setLoginPassword("123456");
        user.setRoleId(5);
        user.setCreateTime(new Date());
        int insert = mapper.insert(user);
        System.out.println(insert);
    }
    @Test
    public void insertSelective(){
        UserMapper mapper = session.getMapper(UserMapper.class);
        User user=new User();
        user.setName("普通用户");
        user.setLoginName("user777");
        user.setLoginPassword("123");
        user.setRoleId(5);
        user.setCreateTime(new Date());
        int i = mapper.insertSelective(user);
        System.out.println(i);
    }
    @Test
    public void deleteByPrimaryKey(){
        UserMapper mapper = session.getMapper(UserMapper.class);
        int i = mapper.deleteByPrimaryKey(93);
        System.out.println(i);
    }
    @Test
    public void selectByPrimaryKey(){
        UserMapper mapper = session.getMapper(UserMapper.class);
        User user = mapper.selectByPrimaryKey(92);
        System.out.println(user);
    }

    @Test
    public void selectByPrimaryKeys(){
        UserMapper mapper = session.getMapper(UserMapper.class);
        HashSet<Integer> set = new HashSet<>();
        set.add(88);
        set.add(92);
        List<User> list = mapper.selectByPrimaryKeys(set);
        System.out.println(list);
    }

    @Test
    public void selectAll(){
        UserMapper mapper = session.getMapper(UserMapper.class);
        List<User> list = mapper.selectAll();
        System.out.println(list);
    }

    @Test
    public void selectAllByPage(){
        UserMapper mapper = session.getMapper(UserMapper.class);
        List<User> list = mapper.selectAllByPage(0, 2);
        System.out.println(list);
    }
    @Test
    public void updateByPrimaryKeySelective(){
        UserMapper mapper = session.getMapper(UserMapper.class);
        User user=new User();
        user.setId(92);
        user.setLoginName("user777");
        int i = mapper.updateByPrimaryKeySelective(user);
        System.out.println(i);
    }

    @Test
    public void updateByPrimaryKey(){
        UserMapper mapper = session.getMapper(UserMapper.class);
        User user=new User();
        user.setId(92);
        user.setName("管理员");
        user.setLoginName("user666");
        user.setLoginPassword("123456");
        user.setRoleId(5);
        int i = mapper.updateByPrimaryKey(user);
        System.out.println(i);
    }
}

⑥pom.xml子模块配置文件

<?xml version="1.0" encoding="UTF-8"?>

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>

  <groupId>com.allen</groupId>
  <artifactId>mybatis13</artifactId>
  <version>1.0-SNAPSHOT</version>

  <name>mybatis13</name>
  <!-- FIXME change it to the project's website -->
  <url>http://www.example.com</url>

  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <maven.compiler.source>1.8</maven.compiler.source>
    <maven.compiler.target>1.8</maven.compiler.target>
  </properties>

  <dependencies>
    <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis</artifactId>
      <version>3.4.6</version>
    </dependency>
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.48</version>
    </dependency>
    <dependency>
      <groupId>ch.qos.logback</groupId>
      <artifactId>logback-classic</artifactId>
      <version>1.2.6</version>
    </dependency>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.11</version>
      <scope>test</scope>
    </dependency>
  </dependencies>

  <build>
    <pluginManagement><!-- lock down plugins versions to avoid using Maven defaults (may be moved to parent pom) -->
      <plugins>
        <!-- clean lifecycle, see https://maven.apache.org/ref/current/maven-core/lifecycles.html#clean_Lifecycle -->
        <plugin>
          <artifactId>maven-clean-plugin</artifactId>
          <version>3.1.0</version>
        </plugin>
        <!-- default lifecycle, jar packaging: see https://maven.apache.org/ref/current/maven-core/default-bindings.html#Plugin_bindings_for_jar_packaging -->
        <plugin>
          <artifactId>maven-resources-plugin</artifactId>
          <version>3.0.2</version>
        </plugin>
        <plugin>
          <artifactId>maven-compiler-plugin</artifactId>
          <version>3.8.0</version>
        </plugin>
        <plugin>
          <artifactId>maven-surefire-plugin</artifactId>
          <version>2.22.1</version>
        </plugin>
        <plugin>
          <artifactId>maven-jar-plugin</artifactId>
          <version>3.0.2</version>
        </plugin>
        <plugin>
          <artifactId>maven-install-plugin</artifactId>
          <version>2.5.2</version>
        </plugin>
        <plugin>
          <artifactId>maven-deploy-plugin</artifactId>
          <version>2.8.2</version>
        </plugin>
        <!-- site lifecycle, see https://maven.apache.org/ref/current/maven-core/lifecycles.html#site_Lifecycle -->
        <plugin>
          <artifactId>maven-site-plugin</artifactId>
          <version>3.7.1</version>
        </plugin>
        <plugin>
          <artifactId>maven-project-info-reports-plugin</artifactId>
          <version>3.0.0</version>
        </plugin>
      </plugins>
    </pluginManagement>
  </build>
</project>

⑦对应的数据库表user内容