Mybatis 增，删，改，查

一、User
package com.wlb.pojo;

import com.sun.javafx.beans.IDProperty;
import lombok.Data;

@Data
public class User {

    private Integer userid;
    private String username;
    private String address;
    private String sex;
    private Integer age;
}
二、UserDao

package com.wlb.Dao;

import com.wlb.pojo.User;

import java.util.List;

public interface UserDao {

    //根据id批量查询用户信息
    List<User> findByIds(List<Integer> ids);

    //多条件查询
    List<User> findByCondition(User user);

    //查(基于ID 查询用户)
    User findById(Integer id) ;

    //增
    void save(User id);

    //删
    void deleteById(Integer id);

    //改
    void update(User id);

    //查(查询所有用户)
    List<User> findAll();

    //查(基于name 模糊查询用户)
    List<User> findByName(String name);
}

三、 UserDao.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.wlb.Dao.UserDao">
    <!--SQ片段查询-->
    <sql id="selectUser">
        userid,username,address,sex,age
    </sql>

    <!--插入数据-->
    <insert id="save" >
        insert into user values (#{userid},#{username},#{address},#{sex},#{age})
        <selectKey keyProperty="userid" order="AFTER" resultType="integer">
            select LAST_INSERT_ID()
        </selectKey>
    </insert>
    <!--更新数据-->
    <update id="update">
        update user set username = #{username},address = #{address},sex = #{sex},age= #{age}  where userid=#{userid}
    </update>
    <!--删除数据-->
    <delete id="deleteById" parameterType="integer">
        delete from user where userid=#{id}
    </delete>
    <!--查询数据-->
    <select id="findById" resultType="com.wlb.pojo.User">
    select*from user where userid=#{id}
    </select>
    <!--根据Id批量查询用户信息-->
    <select id="findByIds" resultType="com.wlb.pojo.User">
        select* from user
     <where>
         <foreach collection="list" open="userid in(" close=")" item="userid" separator=",">
             #{userid}
         </foreach>
     </where>

    </select>
    <!--多条件查询--> <!--动态sql if查询-->
    <select id="findByCondition" resultType="com.wlb.pojo.User">
       select *from user
       <where>
          <if test="username !=null">
              username like CONCAT('%',#{username},'%')
          </if>
           <if test="sex !=null">
               and sex=#{sex}
           </if>
           <if test="age !=null">
               and age=#{age}
           </if>
           <if test="address !=null">
               and address=#{address}
           </if>
       </where>
    </select>
    <!--查询所有用户信息-->
    <select id="findAll" resultType="com.wlb.pojo.User">
        select <include refid="selectUser"></include> from user
    </select>
    <!--模糊查询，基于name模糊查询-->
    <select id="findByName" resultType="com.wlb.pojo.User">
        select*from user username like CONCAT('%',#{username},'%')
    </select>
</mapper>

四、jdbc.properties

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/mysql_01?useUnicode=true&characterEncoding=UTF-8&useSSL=false&use&serverTimezone=Asia/Shanghai
jdbc.username=root
jdbc.password=root

五、log4j.properties

#log4j日志级别如下：
#A：off     最高等级，用于关闭所有日志记录。
#B：fatal   指出每个严重的错误事件将会导致应用程序的退出。
#C：error   指出虽然发生错误事件，但仍然不影响系统的继续运行。
#D：warn    表明会出现潜在的错误情形。
#E：info    一般和在粗粒度级别上，强调应用程序的运行全程。
#F：debug   一般用于细粒度级别上，对调试应用程序非常有帮助。
#G：all     最低等级，用于打开所有日志记录。
#但log4j只建议使用4个级别，优先级从高到低分别是:
#error>warn>info>debug
log4j.rootLogger=debug,systemOut,logFile
#输出到控制台
log4j.appender.systemOut=org.apache.log4j.ConsoleAppender
log4j.appender.systemOut.layout=org.apache.log4j.PatternLayout
log4j.appender.systemOut.layout.ConversionPattern=%d [%t] %-5p %c - %m%n
log4j.appender.systemOut.Target=System.out
#输出到文件
log4j.appender.logFile=org.apache.log4j.FileAppender
log4j.appender.logFile.layout=org.apache.log4j.PatternLayout
log4j.appender.logFile.layout.ConversionPattern=%d{ABSOLUTE} %5p %c{1}:%L - %m%n
log4j.appender.logFile.File=log/log4j.log

六、MybatisConfig.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>
    <!--加载jdbc.properties的配置文件-->
    <properties resource="jdbc.properties"></properties>
    <!--类型别名-->
    <typeAliases>
      <package name="com.wlb.pojo"/>  
    </typeAliases>
    <!--分页配置-->
    <plugins>
        <!--分页助手-->
        <plugin interceptor="com.github.pagehelper.PageInterceptor"></plugin>
    </plugins>
    <!--配置数据的运行环境-->
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>
    <!--加载关联msql的映射文件-->
    <mappers>
        <mapper resource="com/wlb/Dao/UserDao.xml"/>
    </mappers>
</configuration>

七、UserDaoTest

package com.wlb.Dao;

import com.sun.org.apache.bcel.internal.generic.NEW;
import com.wlb.pojo.User;
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.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

public class UserDaoTest {

    @Test
    public void findByIds() throws IOException {
        //加载Mybatis核心配置文件
        InputStream inputStream = Resources.getResourceAsStream("MybatisConfig.xml");

        //构造SqSessionFactory
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserDao mapper = sqlSession.getMapper(UserDao.class);

        List<Integer> list = new ArrayList<>();
        Collections.addAll(list, 1, 2, 3);
        List<User> userList = mapper.findByIds(list);
        for (User u : userList) {
            System.out.println("u=" + u);
        }
    }

    @Test
    public void findByCondition() throws IOException {
        //加载Mybatis核心配置文件
        InputStream inputStream = Resources.getResourceAsStream("MybatisConfig.xml");

        //构造SqSessionFactory
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        //构造SqlSession对象
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserDao mapper = sqlSession.getMapper(UserDao.class);
        User user = new User();
        //user .setUsername("安");
        //user.setSex("男");
        //user.setAddress("山西");
        user.setAge(20);
        List<User> userList = mapper.findByCondition(user);
        for (User users : userList) {
            System.out.println("users=" + users);
        }
    }

    @Test
    public void findById() throws IOException {
        //加载Mybatis核心配置文件
        InputStream inputStream = Resources.getResourceAsStream("MybatisConfig.xml");
        //构造SqSessionFactory
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserDao mapper = sqlSession.getMapper(UserDao.class);
        User user = mapper.findById(1);
        System.out.println(user);
    }

    @Test
    public void save() throws IOException {
        //加载mybatis的核心配置文件；
        InputStream inputStream = Resources.getResourceAsStream("MybatisConfig.xml");
        //构造sqlSessionFactory对象；
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        //构造SqlSession对象；
        SqlSession sqlSession = sqlSessionFactory.openSession();
        //通过sqSession 获取Dao的接口对象；
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        User user = new User();
        user.setUserid(11);
        user.setUsername("欧阳修");
        user.setAddress("江西");
        user.setSex("男");
        user.setAge(28);
        userDao.save(user);
        sqlSession.commit();
        sqlSession.close();

    }


    @Test
    public void deleteById() throws IOException {
        //加载mybatis的核心配置文件；
        InputStream inputStream = Resources.getResourceAsStream("MybatisConfig.xml");
        //构造SqlSessionFactor工厂；
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        //构造SqlSession对象；
        SqlSession sqlSession = sqlSessionFactory.openSession();
        //通过SqlSession工具，获取Dao的接口对象；
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        //执行sql语句；
        userDao.deleteById(11);
        //提交事务；
        sqlSession.commit();
        //释放资源；
        sqlSession.close();

    }

    @Test
    public void update() throws IOException {

        InputStream inputStream = Resources.getResourceAsStream("MybatisConfig.xml");
        //构造SqSessionFactory
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        //通过⼯具类, 获取SQLSession对象
        SqlSession sqlSession = sqlSessionFactory.openSession();
        //通过SQLSession 获取指定dao接口的 代理对象
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        //通过dao接口的 代理对象, 调用执⾏dao层的⽅法
        User user = new User();
        user.setUserid(3);
        user.setUsername("李白");
        user.setAddress("甘肃");
        user.setSex("男");
        user.setAge(30);

        userDao.update(user);
        //释放资源
        sqlSession.commit();
        sqlSession.close();
    }

    @Test
    public void findAll() throws IOException {
        //加载Mybatis核心配置文件
        InputStream inputStream = Resources.getResourceAsStream("MybatisConfig.xml");

        //构造SqSessionFactory
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserDao mapper = sqlSession.getMapper(UserDao.class);

        List<User> userlist = mapper.findAll();
        for (User user : userlist) {
            System.out.println("user=" + user);
        }

    }

    @Test
    public void findByName() {

    }
}

八、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.wlb</groupId>
    <artifactId>CRUD_Mybatis</artifactId>
    <version>1.0-SNAPSHOT</version>
    <properties>
        <maven.compiler.source>1.8</maven.compiler.source>
        <maven.compiler.target>1.8</maven.compiler.target>
        <maven.compiler.compilerVersion>1.8</maven.compiler.compilerVersion>
    </properties>
    <dependencies>
        <!--mybatis坐标-->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.4.5</version>
        </dependency>

        <!--mysql驱动坐标-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
        </dependency>

        <!--单元测试坐标-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>

        <!--lombok坐标-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.16.20</version>
        </dependency>

        <!--日志坐标-->
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>RELEASE</version>
            <scope>compile</scope>
        </dependency>
        <!--分页助手-->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper</artifactId>
            <version>5.1.2</version>
        </dependency>
    </dependencies>
</project>