Mybatis之从零单排

maven项目

1. file --> new --> project -->maven ,新建好以后删除src目录，在pom.xml文件中添加以下的依赖：

<dependencies>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.46</version>
    </dependency>
    <dependency>
        <groupId>org.mybatis</groupId>
        <artifactId>mybatis</artifactId>
        <version>3.5.2</version>
    </dependency>
    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.12</version>
    </dependency>
</dependencies>

2. 在新建好的目录下添加module，形成一个子的maven项目,然后再src-->resources目录下新建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>
    <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/jdbc?useSSL=true&amp;useUnicode=true&amp;characterEncoding=UTF-8"/>
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>

</configuration>

<!--下面的代码是为了导出src/main/java下的相关配置文件，在父子pom.xml文件中都添加上，防止有的子module忘了添加-->
<build>
    <resources>
        <resource>
            <directory>src/main/resources</directory>
            <includes>
                <include>**/*.properties</include>
                <include>**/*.xml</include>
            </includes>
            <filtering>true</filtering>
        </resource>
        <resource>
            <directory>src/main/java</directory>
            <includes>
                <include>**/*.properties</include>
                <include>**/*.xml</include>
            </includes>
            <filtering>true</filtering>
        </resource>
    </resources>
</build>

3. 创建操作mysql工具的静态类，方便后期操作mysql

package com.leo.utils;

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 java.io.IOException;
import java.io.InputStream;

public class MybatisUtils {
    private static SqlSessionFactory sqlSessionFactory;
    static {
        String resource = "mybatis-config.xml";
        InputStream inputStream = null;
        try {
            inputStream = Resources.getResourceAsStream(resource);
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

    public static SqlSession getSqlSession(){
        return sqlSessionFactory.openSession();
    }
}

4. 创建pojo类

package com.leo.pojo;

public class User {
    private int id;
    private String name;
    private int age;
    private String email;

    public User() {
    }

    public User(int id, String name, int age, String email) {
        this.id = id;
        this.name = name;
        this.age = age;
        this.email = email;
    }

    public int getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", name='" + name + ''' +
                ", age=" + age +
                ", email='" + email + ''' +
                '}';
    }
}

5. 创建UserDao接口

package com.leo.dao;

import com.leo.pojo.User;

import java.util.List;

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

6. 创建 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.leo.dao.UserDao">
    <select id="getUserList" resultType="com.leo.pojo.User">
        select * from user
    </select>
</mapper>

7. 把UserMapper.xml文件添加到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>
    <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:///jdbc?useSSL=true&amp;useUnicode=true&amp;characterEncoding=UTF-8"/>
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>
    <!--注意写完mapper添加到配置文件中-->
    <mappers>
        <mapper resource="com/leo/dao/UserMapper.xml"/>
    </mappers>
</configuration>

8. 在test目录下新建com.leo.dao 这个包，与源码文件的目录对应，然后些测试类测试结果

package com.leo.dao;

import com.leo.pojo.User;
import com.leo.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.List;

public class UserDaoTest {
    @Test
    public void test(){
        // 获取sqlSession
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        // 使用sqlSession获取mapper
        UserDao mapper = sqlSession.getMapper(UserDao.class);
        // 使用mapper调用方法
        List<User> userList = mapper.getUserList();
        userList.forEach(System.out::println);

        sqlSession.close();
    }
}

9. 输出结果

User{id=1, name='Jone', age=18, email='test1@baomidou.com'}
User{id=2, name='Jack', age=20, email='test2@baomidou.com'}
User{id=3, name='Tom', age=28, email='test3@baomidou.com'}
User{id=4, name='Sandy', age=21, email='test4@baomidou.com'}
User{id=5, name='Billie', age=24, email='test5@baomidou.com'}

Process finished with exit code 0

10. 如果接口中的方法是有参数的

package com.leo.dao;

import com.leo.pojo.User;

import java.util.List;

public interface UserDao {
    List<User> getUserList();
    
    User getUserById(int id);
}

在配置文件中需要添加以下的配置

<select id="getUserById" parameterType="int" resultType="com.leo.pojo.User">
    select * from user where id = #{id}
</select>

<insert id="getUserById" parameterType="com.leo.pojo.User">
    insert into user(id,name,age,email) values(#{id},#{name},#{age},#{email});
</insert>
<!--user map 万能用法，可以不限制字段的多少和对象key值，如果使用的是对象，values的值必须是对象的属性-->
<insert id="getUserById" parameterType="map" >
    insert into user(id,name,age,email) values(#{userid},#{username},#{userage},#{useremail});
</insert>
<!--sql注入风险-->
List<User> userList = mapper.getUserList("%李%")；
select * from user where name like “%”#{value}"%";

测试方法

@Test
public void test1(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    UserDao mapper = sqlSession.getMapper(UserDao.class);
    User u= mapper.getUserById(1);
    System.out.println(u);
    sqlSession.close();

}

11. 增删改相关的功能的时候一定要提交事务： sqlSession.commit();

12. 使用db.properties ,把数据库相关的连接放到这个配置文件，然后再mybatis-config.xml配置文件中引入配置文件，再mybatis-config.xml配置文件中properties标签的添加有先后顺序，个人理解是因为配置文件从上到下解读，如果在后面添加应该是environment标签中的属性无法解读到数据。

<?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>
    <properties resource="db.properties">
    </properties>
    <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://110.42.230.125:3306/jdbc?useSSL=true&amp;useUnicode=true&amp;characterEncoding=UTF-8"/>
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="com/leo/dao/UserMapper.xml"/>
    </mappers>
</configuration>

13. 配置多个环境如dev、test：

<?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>
    <properties resource="db.properties"></properties>
    <typeAliases>
      <typeAlias alias="user" type="com.leo.pojo.User"/>
  
    </typeAliases>
    <!--db环境可以配置多个，默认的是development-->
    <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://110.42.230.125:3306/jdbc?useSSL=true&amp;useUnicode=true&amp;characterEncoding=UTF-8"/>
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
        <environment id="test">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://110.42.230.125:3306/jdbc?useSSL=true&amp;useUnicode=true&amp;characterEncoding=UTF-8"/>
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper resource="com/leo/dao/UserMapper.xml"/>
    </mappers>
</configuration>

• 首先读取在 properties 元素体内指定的属性。
• 然后根据 properties 元素中的 resource 属性读取类路径下属性文件，或根据 url 属性指定的路径读取属性文件，并覆盖之前读取过的同名属性。
• 最后读取作为方法参数传递的属性，并覆盖之前读取过的同名属性。

14. 查询的时候使用resultMap,结果集映射

<!--userMap.xml-->
<resultMap id="userMap" type="user">
    <result column="pwd" property="pwd"/>
</resultMap>
<select id="getUserById" parameterType="int" resultMap="userMap">
    select * from user where id=#{id}
</select>

15. Log4j的使用，先pom.xml引入log4j，然后mybatis-config.xml文件引入 mubatis-config.xml

<dependency>
    <groupId>log4j</groupId>
    <artifactId>log4j</artifactId>
    <version>1.2.17</version>
</dependency>

db.properties

    <properties resource="db.properties"></properties>
    <settings>
        <setting name="logImpl" value="LOG4J"/>
    </settings>
    <typeAliases>
        <typeAlias alias="user" type="com.leo.pojo.User"/>

    </typeAliases>

新建log4j.properties文件：

#将等级为DEBUG的日志信息输出到console和file这两个目的地，console和file的定义在下面的代码
log4j.rootLogger=DEBUG,console,file

#控制台输出的相关设置
log4j.appender.console = org.apache.log4j.ConsoleAppender
log4j.appender.console.Target = System.out
log4j.appender.console.Threshold=DEBUG
log4j.appender.console.layout = org.apache.log4j.PatternLayout
log4j.appender.console.layout.ConversionPattern=[%c]-%m%n

#文件输出的相关设置
log4j.appender.file = org.apache.log4j.RollingFileAppender
log4j.appender.file.File=./log/logFile.log
log4j.appender.file.MaxFileSize=10mb
log4j.appender.file.Threshold=DEBUG
log4j.appender.file.layout=org.apache.log4j.PatternLayout
log4j.appender.file.layout.ConversionPattern=[%p][%d{yy-MM-dd}][%c]%m%n

#日志输出级别
log4j.logger.org.mybatis=DEBUG
log4j.logger.java.sql=DEBUG
log4j.logger.java.sql.Statement=DEBUG
log4j.logger.java.sql.ResultSet=DEBUG
log4j.logger.java.sql.PreparedStatement=DEBUG

然后在使用的类中实例化：

import org.apache.log4j.Logger;

public class UserDaoTest {
     static Logger logger = Logger.getLogger(UserDaoTest.class);
     }

16. limit 分页

public interface UserMapper {
     List<User> getUserList(Map<String,Object> u);
}

UserMapper.xml

<select id="getUserList" parameterType="map" resultType="user">
    select * from user limit #{startIndex},#{number}
</select>

测试类测试：

17. 使用注解，调用方法和使用配置文件一样

public interface UserMapper {
    
    @Select("select * from user")
    List<User> getUsers();
    @Select("select * from user where id = #{id} and name=#{name}")
    List<User> getUsers(@Param("id") int id,@Param("name") String name);


}