mybatis偷懒笔记(配置文件,拷贝可用)

Jammm
2020-05-02 / 0 评论 / 200 阅读 / 正在检测是否收录...

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

  1. 接口

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

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

     @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实现分页

  1. 接口

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

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

    @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、测试环境搭建

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

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、小结

  1. 关联 - association 【多对一】
  2. 集合 - collection 【一对多】
  3. javaType & ofType

    1. JavaType:用来指定实体类中属性的类型
    2. ofType:用来指定映射到List或者集合中的pojo类型,泛型中的约束类型

注意点:

  • 保证SQL的可读性,尽量保证通俗易懂
  • 注意一对多和多对一中,属性名和字段的问题
  • 如果问题不好排查错误,可以使用日志,建议使用Log4j
本文共 677 个字数,平均阅读时长 ≈ 2分钟
0

打赏

海报

正在生成.....

评论 (0)

取消