mybatis偷懒笔记（配置文件，拷贝可用）

Powered By JamPang

QQ:847885907

https://jampang.cn/

Mybatis笔记

1、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绑定一个对应的Dao/Mapper接口 -->
<mapper namespace="com.jam.dao.UserMapper">

    <!--结果集映射-->
    <resultMap id="UserMap" type="User">
        <!--column数据库中的字段，property实例类中的属性-->
        <result column="pwd" property="password"/>
    </resultMap>

    <!-- id查询 -->
    <select id="getUserById" parameterType="int" resultMap="UserMap">
        select * from mybatis.user where id = #{id}
    </select>
</mapper>

2、MybaitsUtils.java

public class MybatisUtils {

    private static SqlSessionFactory sqlSessionFactory;

    static {
        try {
            //使用Mybatis第一步：获取sqlSessionFactory对象
            String resource = "mybatis-config.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        }catch (IOException e){
            e.printStackTrace();
        }
    }

    //既然有了 SqlSessionFactory，顾名思义，我们可以从中获得 SqlSession 的实例。
    // SqlSession 提供了在数据库执行 SQL 命令所需的所有方法。

    public static SqlSession getSqlSession(){
        //SqlSession sqlSession = sqlSessionFactory.openSession();
        //return  sqlSession;
        return  sqlSessionFactory.openSession(true);
    }

}

3、数据库

3.1、db.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mybatis?userSSL=true&useUnicode=true&characterEncoding=UTF-8
username=root
password=1234

3.2、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>

    <!-- 引入外部配置文件 -->
    <properties resource="db.properties" />

    <!-- settings -->
    <settings>
        <setting name="logImpl" value="STDOUT_LOGGING"/>
    </settings>
    
    <!-- 可以给实体类起别名 -->
    <typeAliases>
        <typeAlias type="com.jam.pojo.User" alias="User" />

    </typeAliases>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>

    <!-- 每一个Mapper.xml都需要在Mybatis核心配置文件中注册！ -->
    <mappers>
         <mapper resource="com/jam/dao/UserMapper.xml"/>
    </mappers>
</configuration>

3.3、分页查询

3.3.1、Limit

接口

//分页
List<User> getUserByLimit(Map<String,Integer> map);

Mapper.xml

<!--分页-->
<select id="getUserByLimit" parameterType="map" resultMap="UserMap">
    select * from mybatis.user limit #{startIndex},#{pageSize}
</select>

测试

 @Test
public void getUserByLimit(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);

    HashMap<String, Integer> map = new HashMap<String, Integer>();
    map.put("startIndex",1);
    map.put("pageSize",2);

    List<User> userList = mapper.getUserByLimit(map);
    for (User user : userList) {
        System.out.println(user);
    }

    sqlSession.close();
}

3.3.2、RowBounds

不再使用SQL实现分页

接口

//分页2
List<User> getUserByRowBounds();

Mapper.xml

<!--分页2-->
<select id="getUserByRowBounds" resultMap="UserMap">
    select * from mybatis.user
</select>

测试

@Test
public void getUserByRowBounds(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();

    //RowBounds实现
    RowBounds rowBounds = new RowBounds(1, 2);

    //通过Java代码层面实现分页
    List<User> userList = sqlSession.selectList("com.jam.dao.UserMapper.getUserByRowBounds",null,rowBounds);

    for (User user : userList) {
        System.out.println(user);
    }

    sqlSession.close();
}

4、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>org.example</groupId>
    <artifactId>mybatis</artifactId>
    <packaging>pom</packaging>
    <version>1.0-SNAPSHOT</version>

    <!-- 导入依赖 -->
   <dependencies>
    <!--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>
           <scope>test</scope>
       </dependency>
       <dependency>
           <groupId>org.mybatis</groupId>
           <artifactId>mybatis</artifactId>
           <version>3.5.2</version>
       </dependency>
   </dependencies>

    <!-- build中配置resources，来防止我们资源导出失败的问题 -->
    <build>
        <resources>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.xml</include>
                    <include>**/*.properties</include>
                </includes>
            </resource>
            <resource>
                <directory>src/main/resources</directory>
                <includes>
                    <include>**/*.xml</include>
                    <include>**/*.properties</include>
                </includes>
            </resource>
        </resources>
    </build>

</project>

5、依赖包

5.1、mysql-connector-java

<dependency>
   <groupId>mysql</groupId>
   <artifactId>mysql-connector-java</artifactId>
   <version>5.1.47</version>
</dependency>

5.2、junit

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

5.3、mybatis

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

5.4、log4j

pom.xml

<!-- https://mvnrepository.com/artifact/log4j/log4j -->
<dependency>
    <groupId>log4j</groupId>
    <artifactId>log4j</artifactId>
    <version>1.2.17</version>
</dependency>

mybaits-config.xml

<!-- settings -->
    <settings>
        <setting name="logImpl" value="LOG4J"/>
    </settings>

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/jamlog.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

test.java

public class UserDaoTest {

    static Logger logger = Logger.getLogger(UserDaoTest.class);

    @Test
    public void testLoh4j(){
        logger.info("info:进入了testLog4j");
        logger.debug("debug:进入了testLog4j");
        logger.error("error:进入了testLog4j");
    }

}

5.5、Lombok

1.在IDEA中安装插件

2.导入Maven包

<!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.10</version>
</dependency>

3.注解

@Getter and @Setter
@FieldNameConstants
@ToString
@EqualsAndHashCode
@AllArgsConstructor, @RequiredArgsConstructor and @NoArgsConstructor
@Log, @Log4j, @Log4j2, @Slf4j, @XSlf4j, @CommonsLog, @JBossLog, @Flogger, @CustomLog
@Data
@Builder
@SuperBuilder
@Singular
@Delegate
@Value
@Accessors
@Wither
@With
@SneakyThrows
@val
@var
experimental @var
@UtilityClass

6、日志

6.1、标准日志实现[mybatis-config.xml]

<!-- settings -->
    <settings>
        <setting name="logImpl" value="STDOUT_LOGGING"/>
    </settings>

6.2、log4j 【参考5.4】

7、多对一处理

多个学生对应一个老师
对于学生而言，关联多个学生，关联一个老师【多对一】
对于老师而言，一个老师，有很多学生【一对多】

数据库

CREATE TABLE `teacher` (
    `id` INT(10) NOT NULL,
    `name` VARCHAR(30) DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO teacher(`id`, `name`) VALUES (1, '庞老师');

CREATE TABLE `student` (
    `id` INT(10) NOT NULL,
    `name` VARCHAR(30) DEFAULT NULL,
    `tid` INT(10) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `fktid` (`tid`),
    CONSTRAINT `fktid` FOREIGN KEY (`tid`) REFERENCES `teacher` (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;


INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('1', '小明', '1');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('2', '小红', '1');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('3', '小张', '1');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('4', '小李', '1');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('5', '小王', '1');

7.1、测试环境搭建

导入Lombok
新建实体类Teacher，Student
建立Mapper接口
建立Mapper.xml文件【在Resources目录下需要一级一级建立文件夹】
在核心配置中绑定注册我们的Mapper接口或者文件
测试查询是否能成功

7.2、按照查询嵌套处理

<!--
   思路：
        1. 查询所有的学生信息
        2. 根据查询出来的学生的tid，寻找对应的老师  子查询
-->

<select id="getStudent" resultMap="StudentTeacher">
    select * from student
</select>

<resultMap id="StudentTeacher" type="Student">
    <result property="id" column="id"/>
    <result property="name" column="name"/>
    <!--复杂的属性
        对象：association
        集合：collection
    -->
    <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
</resultMap>

<select id="getTeacher" resultType="Teacher">
    select * from teacher where id = #{id}
</select>

7.3、按照结果嵌套处理

<!--按照结果处理-->
<select id="getStudent2" resultMap="StudentTeacher2">
    select s.id sid,s.name sname,t.name tname
    from student s,teacher t
    where s.tid = t.id;

</select>

<resultMap id="StudentTeacher2" type="Student">
    <result property="id" column="sid"/>
    <result property="name" column="sname"/>
    <association property="teacher" javaType="Teacher">
        <result property="name" column="tname"/>
    </association>

</resultMap>

回顾Mysql 多对一查询方式：

子查询
联表查询

8、一对多处理

比如：一个老师拥有多个学生！

对于老师而言，就是一对多的关系！

8.1、搭建环境

实体类

//Teacher类
public class Teacher {
    private int id;
    private String name;

    //一个老师拥有多个学生
    private List<Student> students;
    
}

//Student类
public class Student {
    private int id;
    private String name;
    private int tid;

}

8.2、按照查询嵌套处理

<select id="getTeacher2" resultMap="TeacherStudent2">
    select * from mybatis.teacher where id = #{tid}
</select>

<resultMap id="TeacherStudent2" type="Teacher">
    <collection property="students" javaType="ArrayList" ofType="Student" select="getStudentByTeacherId" column="id"/>
</resultMap>

<select id="getStudentByTeacherId" resultType="Student">
    select * from mybatis.student where tid = #{tid}
</select>

8.3、按照结果嵌套处理

<!--按结果嵌套查询-->
<select id="getTeacher" resultMap="TeacherStudent">
    select s.id sid,s.name sname,t.name tname,t.id tid
    from student s,teacher t
    where s.tid = t.id and t.id = #{tid}
</select>

<resultMap id="TeacherStudent" type="Teacher">
    <result property="id" column="tid"/>
    <result property="name" column="tname"/>
    <!--复杂的属性
    对象：association
    集合：collection
    集合中的泛型信息，使用ofType获取
    -->
    <collection property="students" ofType="Student">
        <result property="id" column="sid"/>
        <result property="name" column="sname"/>
        <result property="tid" column="tid"/>
    </collection>
</resultMap>

8.4、小结

关联 - association 【多对一】
集合 - collection 【一对多】
javaType & ofType
1. JavaType：用来指定实体类中属性的类型
2. ofType：用来指定映射到List或者集合中的pojo类型，泛型中的约束类型

注意点：

保证SQL的可读性，尽量保证通俗易懂
注意一对多和多对一中，属性名和字段的问题
如果问题不好排查错误，可以使用日志，建议使用Log4j

本文共 677 个字数,平均阅读时长 ≈ 2分钟

mybatis偷懒笔记（配置文件，拷贝可用）

Powered By JamPang

QQ:847885907

https://jampang.cn/

Mybatis笔记

1、Mapper.xml

2、MybaitsUtils.java

3、数据库

3.1、db.properties

3.2、mybatis-config.xml

3.3、分页查询

3.3.1、Limit

3.3.2、RowBounds

4、pom.xml

5、依赖包

5.1、mysql-connector-java

5.2、junit

5.3、mybatis

5.4、log4j

5.5、Lombok

6、日志

6.1、标准日志实现[mybatis-config.xml]

6.2、log4j 【参考5.4】

7、多对一处理

7.1、测试环境搭建

7.2、按照查询嵌套处理

7.3、按照结果嵌套处理

8、一对多处理

8.1、搭建环境

8.2、按照查询嵌套处理

8.3、按照结果嵌套处理

8.4、小结

评论 (0)

旧梦未眠

绿水本无忧，因风皱面

青山原不老，为雪白头