Mybatis基础教程

MyBatis是一个可以自定义SQL、存储过程和高级映射的持久层框架

1.Mybatis基本配置

1.1引入Mybatis

要使用 MyBatis， 只需将 mybatis-x.x.x.jar 文件置于类路径（classpath）中即可。

如果使用 Maven 来构建项目，则需将下面的依赖代码置于 pom.xml 文件中：

<dependency>
  <groupId>org.mybatis</groupId>
  <artifactId>mybatis</artifactId>
  <version>x.x.x</version>
</dependency>

1.2导入Mybatis相关jar包

<!--mysql驱动-->
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>8.0.21</version>
    </dependency>

  <!--mybatis-->
  <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis</artifactId>
      <version>3.5.6</version>
  </dependency>

  <!--junit-->
  <dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
    <version>4.13</version>
  </dependency>

1.3编写resources下的文件

1.3.1编写Mybatis核心配置文件

MyBatis的配置文件是一个XML文件，包含了MyBatis的大部分配置信息，例如数据库连接信息、映射文件位置、缓存配置等，放在resources下面。

<?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="com.zaxxer.hikari.HikariDataSource">
                <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
                <property name="jdbcUrl" value="jdbc:mysql://localhost:3306/test?useSSL=false&amp;serverTimezone=Asia/Shanghai"/>
                <property name="username" value="root"/>
                <property name="password" value="root"/>
            </dataSource>
        </environment>
    </environments>
    <!-- 扫描映射文件 -->
    <mappers>
        <mapper resource="com/example/demo/mapper/UserMapper.xml"/>
    </mappers>
</configuration>

1.3.2编写log4j.properties

# Global logging configuration
log4j.rootLogger=DEBUG, stdout
# Console output...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n

2.简单实例的编写

2.1创建实体类

创建实体类，属性最好与数据库属性对应

package com.by.pojo;

import java.io.Serializable;
import java.util.Date;

public class User implements Serializable {
    private Integer id;
    private String username;
    private Date birthday;
    private String sex;
    private String address;

    public Integer getId() {
        return id;
    }

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

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", birthday=" + birthday +
                ", sex='" + sex + '\'' +
                ", address='" + address + '\'' +
                '}';
    }
}

2.2编写Mapper接口

public interface UserDao {
    List<User> findAll();
}

2.3编写Mapper.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">
<!--namespace：唯一，接口全类名-->
<mapper namespace="com.by.dao.UserDao">
    <!--
        id：和接口方法名保持一致
        resultType：和接口返回类型保持一致
    -->
    <select id="findAll" resultType="com.by.pojo.User">
        select * from user
    </select>
</mapper>

2.4编写测试类

package com.by.test;

import com.by.dao.UserDao;
import com.by.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.List;

public class MyBatisTest {

    @Test
    public void testFindAll() throws IOException {
        //加载配置文件
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);

        //创建sqlSessionFactory
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        //获得数据的会话实例
        SqlSession sqlSession = sessionFactory.openSession();

        //返回接口的代理类
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        List<User> userList = userDao.findAll();
        for (User user : userList) {
            System.out.println(user);
        }
        System.out.println(userDao);
    }
}

运行结果：

3.增删改查

在xml文件里编写增删改查语句

为了方便我们把测试类的重用代码这样写：

 @Before
    public void inter() throws IOException {
        String resource = "mybatis-config.xml";
        inputStream = Resources.getResourceAsStream(resource);

        //创建sqlSessionFactory
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        //获得数据的会话实例
        sqlSession = sessionFactory.openSession();
    }

   @After
    public void close() throws IOException {
        sqlSession.close();
        inputStream.close();
    }
}

3.1删除操作

删除操作比较简单，可以这样编写SQL语句：

<delete id="deleteById" parameterType="Long">
    DELETE FROM user
    WHERE id = #{id}
</delete>

id与接口方法名一致

测试代码:

    public void crud1() throws IOException {
        //返回接口的代理类
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        userDao.deleteById(42);
        sqlSession.commit();
    }

3.2更新操作

 <update id="UpdateUserById" parameterType="com.by.pojo.User">
        update user set username=#{username},password=#{password},birthday=#{birthday},sex=#{sex},address=#{address}
        where id=#{id}
    </update>

测试类代码：

   @Test
    public void crud2() throws IOException {
        //返回接口的代理类
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        User user=new User();
        user.setSex("男");
        user.setAddress("河北石家庄");
        user.setBirthday(new Date());
        user.setUsername("张起灵");
        user.setPassword(817);
        user.setId(45);
        userDao.UpdateUserById(user);
        sqlSession.commit();
    }

3.3添加操作

    <insert id="insertUserById" parameterType="com.by.pojo.User">
        insert into user(username,password,birthday,sex,address) values (#{username},#{password},#{birthday},#{sex},#{address})
    </insert>

测试类代码：

    @Test
    public void crud3() throws IOException {
        //返回接口的代理类
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        User user=new User();
        user.setSex("男");
        user.setAddress("江苏杭州");
        user.setBirthday(new Date());
        user.setUsername("吴邪");
        user.setPassword(817);
        userDao.insertUserById(user);
        sqlSession.commit();
    }

3.4模糊查询

java代码执行的时候，传递通配符%

List<User> userList = userDao.getUserLike("胡%");

在Sql拼接中使用通配符

select * from mybatis.user where name like "%"#{value}"%"

3.5mybatis的主键回填

方法一：

<insert id="" useGeneratedKeys="true" keyProperty="id" parameterType="">

方法二：

<selectKey keyProperty="id" order="AFTER" resultType="java.lang.Integer">
            SELECT LAST_INSERT_ID()
        </selectKey>

4.#{}和${}的区别

{}是预编译处理，${}是字符串替换。

Mybatis在处理#{}时，会将sql中的#{}替换为?号，调用PreparedStatement的set方法来赋值；

Mybatis在处理${}时，就是把${}替换成变量的值。

使用#{}可以有效的防止SQL注入，提高系统安全性。

	sql注入	底层	jdbc类型转换	单个简单类型的参数
$	不防止	Statement	不转换	value
#	防止	preparedStatement	转换	任意

5.Mybatis传递多个参数

5.1序号传递多个参数

WHERE id=#{arg0} AND username=#{arg1}
        WHERE id=#{param1} AND username=#{param2}

5.2注解传递多个参数---【推荐】

    User getUser2(@Param("id") Integer id, @Param("username") String username);
        select * from user where id=#{id} and username=#{username}

5.3pojo传递多个参数---【推荐】

  User getUser3(User user);
        select * from user where id=#{id} and username=#{username}

5.4map传递多个参数

User getUser3(Map user);
        select * from user where id=#{id} and username=#{username}