Mybatis学习

Mybatis

第一个mybatis程序

1、搭建环境

搭建数据库——>新建项目，普通maven项目——>删除src目录（作为父工程）——>导入maven依赖

<!--    导入依赖-->
    <dependencies>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
        </dependency>
        <!-- mybatis驱动 -->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.2</version>
        </dependency>
        <!-- junit测试包 -->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>
        <!-- log4j日志 -->
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>
        <!-- lombok -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.12</version>
        </dependency>
    </dependencies>

2、创建子模块

编写mybatis核心配置文件，注意注册mapper

<?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 核心配置文件-->
<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/demo?useUnicode=true&amp;characterEncoding=utf-8&amp;useSSL=true&amp;serverTimezone=UTC"/>
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>
</configuration>

注册mapper

<!--    每一个mapper.xml都需要在这个mybatis核心文件中注册-->
    <mappers>
        <mapper resource="com/luo/dao/UserMapper.xml"></mapper>
    </mappers>

编写mybatis工具类

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;

//sqlsessionFactory构建sqlsession
public class MyBatisUtils {
    private static SqlSessionFactory factory=null;
    static {
        try {
            String resource = "mybatis-config.xml";
            InputStream in= Resources.getResourceAsStream(resource);
            //创建SqlSessionFactory对象
            factory=new SqlSessionFactoryBuilder().build(in);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    //获取SqlSession的方法
    public static SqlSession getSqlSession() {
        SqlSession sqlSession=null;
        if(factory!=null) {
            sqlSession=factory.openSession();//非自动提交事务
        }
        return sqlSession;
    }
}

3、编写代码

实体类

package com.luo.pojo;

public class User {
    private int id;
    private String name;
    private String pwd;

    public User() {
    }

    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 String getPwd() {
        return pwd;
    }

    public void setPwd(String pwd) {
        this.pwd = pwd;
    }

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

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

Dao接口

package com.luo.dao;

import com.luo.pojo.User;

import java.util.List;

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

接口实现类,由原来的UserDaoImpl转化为一个mapper配置文件

<?xml version="1.0" encoding="UTF8"?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--namespace绑定一个对应的Dao/mapper接口-->
<mapper namespace="com.luo.dao.UserDao">
<!--    select查询语句-->
    <select id="getUserList" resultType="com.luo.pojo.User">
        select *from user
    </select>
</mapper>

4、测试

junit测试

package com.luo.dao;

import com.luo.pojo.User;
import com.luo.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();
        //getMapper()
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        List<User> userList = userDao.getUserList();
        for (User user : userList) {
            System.out.println(user);
        }
        //关闭sqlSession
        sqlSession.close();
    }
}

注意：如果遇到资源无法被导出的问题，在pom.xml中配置resources

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

CRUD

1、在接口中写方法

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

    //根据id查询用户
    User getUserById(int id);

    //添加用户
    int addUser(User user);

    //修改用户
    int updateUser(User user);

    //删除用户
    int deleteUser(int id);
}

2、在xml文件中写标签

<?xml version="1.0" encoding="UTF8"?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--namespace绑定一个对应的Dao/mapper接口-->
<mapper namespace="com.luo.dao.UserDao">
<!--    select查询语句-->
    <select id="getUserList" resultType="com.luo.pojo.User">
        select *from user
    </select>

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

    <select id="addUser" parameterType="com.luo.pojo.User">
        insert into user values (#{id},#{name},#{pwd})
    </select>

    <update id="updateUser" parameterType="com.luo.pojo.User">
        update user
        set id = #{id},name=#{name},pwd = #{pwd}
        where id=4;
    </update>

    <delete id="deleteUser" parameterType="int">
        delete
        from user
        where id = #{id};
    </delete>
</mapper>

namespace中的包名要和mapper/dao接口的包名一致
id:就是对应的namespace中的方法名
resultType:sql语句中的返回值
parameterType:参数类型
注意：需要idea连接数据库，写sql语句才会有提示

3、写测试代码

public class UserDaoTest {
    @Test
    public void test(){
        //获取sqlSession对象
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        //getMapper()
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        List<User> userList = userDao.getUserList();
        for (User user : userList) {
            System.out.println(user);
        }
        //关闭sqlSession
        sqlSession.close();
    }

    @Test
    public void getUserById(){
        //获取sqlSession对象
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        //getMapper()
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        User user = userDao.getUserById(1);
        System.out.println(user);
        //关闭sqlSession
        sqlSession.close();
    }

    @Test
    public void addUser(){
        //获取sqlSession对象
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        //getMapper()
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        userDao.addUser(new User(4,"赵4","123"));
        //增删改需要提交事务
        sqlSession.commit();
        //关闭sqlSession
        sqlSession.close();
    }

    @Test
    public void updateUser(){
        //获取sqlSession对象
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        //getMapper()
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        userDao.updateUser(new User(4,"赵四","123456"));
        //增删改需要提交事务
        sqlSession.commit();
        //关闭sqlSession
        sqlSession.close();
    }

    @Test
    public void deleteUser(){
        //获取sqlSession对象
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        //getMapper()
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        userDao.deleteUser(4);
        //增删改需要提交事务
        sqlSession.commit();
        //关闭sqlSession
        sqlSession.close();
    }
}

Map和模糊查询拓展

Map

假设我们的实体类或者数据库中的表，字段或参数过多，我们可以用map来传递参数;

//只修改密码，使用map传参
int updatePwd(Map<String,Object> map);

<update id="updatePwd" parameterType="Map">
    update user
    set pwd = #{pwd}
    where id = #{id};
</update>

@Test
public void updatePwd(){
    //获取sqlSession对象
    SqlSession sqlSession = MyBatisUtils.getSqlSession();
    //getMapper()
    UserDao userDao = sqlSession.getMapper(UserDao.class);
    Map<String, Object> map = new HashMap<String, Object>();
    //只想修改密码，就不用把每个字段都写上
    map.put("id",3);
    map.put("pwd",11111);
    userDao.updatePwd(map);
    //增删改需要提交事务
    sqlSession.commit();
    //关闭sqlSession
    sqlSession.close();
}

模糊查询拓展

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

//模糊查询
List<User> getUserLike(String name);

<select id="getUserLike" resultType="com.luo.pojo.User">
    select *from user where name like #{value}
</select>

@Test
public void getUserLike(){
    //获取sqlSession对象
    SqlSession sqlSession = MyBatisUtils.getSqlSession();
    //getMapper()
    UserDao userDao = sqlSession.getMapper(UserDao.class);
    //查询所有姓李的人
    List<User> userList = userDao.getUserLike("%李%");
    for (User user : userList) {
        System.out.println(user);
    }
    //关闭sqlSession
    sqlSession.close();
}

配置解析

核心配置文件：mybatis-config.xml

属性优化

我们可以通过properties属性来实现引用配置文件

编写一个配置文件 db.properties

driver = com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/demo?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC
username=root
password=123456

在核心配置文件里面引入
```
<properties resource="db.properties"/>
```

别名优化（typeAliases）

给实体类写别名

<typeAliases>
    <typeAlias type="com.luo.pojo.User" alias="User"></typeAlias>
</typeAliases>

这样在需要使用com.luo.pojo.User时，可以用User代替

给包别名

<typeAliases>
    <package name="com.luo.pojo"/>
</typeAliases>

这样在需要使用这个包的实体类时间，默认别名为这个类的类名，首字母小写。比如需要使用com.luo.pojo.User时，可以用user代替。也可以在实体类前通过注解指定别名@Alias。

映射器说明（mappers）

方式一:使用相对于类路径的资源引用[推荐使用]

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

方式二：使用映射器接口实现类的完全限定类名

    <mappers>
        <mapper class="com.luo.dao.UserMapper"/>
    </mappers>

注意：接口和他的mapper配置文件必须同名和在同一个包下

方式三：通过pakage

  <mappers>
        <package name="com.luo.dao"/>
    </mappers>

注意：接口和他的mapper配置文件必须同名和在同一个包下

解决属性名和字段名不一致的问题

如果实体类的属性名和数据库中的字段名不一致的话，查询出来可能会为空。

解决方法：

改sql语句，起别名：pwd as “password”

resultMap（结果集映射）：

<resultMap id="UserMap" type="User">
   <result column="pwd" property="password"/> 
</resultMap>

日志

如果一个数据库操作出了异常，日志可以帮助我们排错。在核心配置文件中配置,具体使用哪个日志。

标准日志,可以直接使用

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

log4j

1、导包

<!-- log4j日志 -->
<dependency>
    <groupId>log4j</groupId>
    <artifactId>log4j</artifactId>
    <version>1.2.17</version>
</dependency>

2、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/luo.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

3、配置log4j为日志的实现

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

4、log4j的使用

分页

使用Limit分页

SELECT *from user limit startIndex,pageSize;
SELECT *from user limit 3;#[0,n]

使用mybatis实现分页

接口

List<User> getUserLimit(Map<String,Integer> map);

mapper.xml

<select id="getUserLimit" resultType="User" parameterType="map">
   select *from user limit #{startIndex},#{pageSize}
</select>

测试

@Test
public void getUserLimit(){
    //获取sqlSession对象
    SqlSession sqlSession = MyBatisUtils.getSqlSession();
    //getMapper()
    UserMapper userDao = sqlSession.getMapper(UserMapper.class);
    Map<String,Integer> map = new HashMap<String,Integer>();
    map.put("startIndex",0);
    map.put("pageSize",2);
    List<User> userList = userDao.getUserLimit(map);
    for (User user : userList) {
        System.out.println(user);
    }
    //关闭sqlSession
    sqlSession.close();
}

Lombok

1、安装插件

2、导包

<!-- lombok -->
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.12</version>
</dependency>

3、使用

@Data
@AllArgsConstructor
@NoArgsConstructor
public class User {
    private int id;
    private String name;
    private String pwd;
}

@Data:无参构造,get,set,tostring,hashcode,equals

@AllArgsConstructor:有参构造

@NoArgsConstructor:无参构造

复杂查询环境搭建

步骤

新建表teacher,student
写对应实体类Teacher,Student
写实体类对应接口TeacherMapper,StudentMapper
写对应xml文件
测试运行

多对一处理：查询所有学生及对应的老师信息

方式一：按照查询嵌套处理（子查询）

    //查询所有学生及对应的老师信息
    List<Student> getStudentInfo();

<!--    思路：1、查询所有学生2、查询tid对应的老师-->
    <resultMap id="StudentInfo" type="student">
<!--        对象用association,javaType说明teacher是一个对象,再嵌套查询-->
        <association property="teacher" column="tid" javaType="teacher" select="getTeacher"></association>
    </resultMap>
    <select id="getStudentInfo" resultMap="StudentInfo">
        select *from student
    </select>
    <select id="getTeacher" resultType="teacher">
        ##{tid}里面这个tid可以换成任意值，mybatis会自动匹配
        select *from teacher where id = #{tid}
    </select>

@Test
public void test(){
    SqlSession sqlSession = MyBatisUtils.getSqlSession();
    StudentMapper Mapper = sqlSession.getMapper(StudentMapper.class);
    List<Student> studentList = Mapper.getStudentInfo();
    for (Student student : studentList) {
        System.out.println(student);
    }
    sqlSession.close();
}

方式二：按照结果嵌套处理（联表查询）

       <select id="getStudentInfo" resultMap="StudentInfo">
           #注意：这里必须取别名
           select s.id sid,s.name sname,t.name tname
           from student s,teacher t
           where s.tid = t.id
       </select>
       <resultMap id="StudentInfo" type="student">
           <result property="id" column="sid"/>
           <result property="name" column="sname"></result>
<!--           使用association映射-->
           <association property="teacher" javaType="teacher">
               <result property="name" column="tname"/>
           </association>
       </resultMap>

一对多处理：查询指定老师及对应的学生信息

方式一：按照结果嵌套处理（联表查询）

//查询指定老师及其所有学生
Teacher getTeacher(int id);

<select id="getTeacher" resultMap="teacherInfo">
    select t.id tid,t.name tname,s.id sid,s.name sname
    from teacher t,student s
    where t.id = #{id} and t.id = s.tid
</select>
 <resultMap id="teacherInfo" type="teacher">
     <result property="id" column="tid"/>
     <result property="name" column="tname"/>
     <!--       集合中的泛型信息用ofType获取-->
     <collection property="studentList" ofType="student" >
         <result property="id" column="sid"/>
         <result property="name" column="sname"/>
     </collection>
 </resultMap>

@Test
public void test(){
    SqlSession sqlSession = MyBatisUtils.getSqlSession();
    TeacherMapper teacherMapper = sqlSession.getMapper(TeacherMapper.class);
    System.out.println(teacherMapper.getTeacher(1));
    sqlSession.close();
}

方式二：按照查询嵌套处理（子查询）

  <select id="getTeacher" resultMap="teacherInfo">
     select *from teacher where id = #{id}
  </select>
   <resultMap id="teacherInfo" type="teacher">
<!--       这里要写column,javaType两个属性,方式一无需写-->
      <collection property="studentList" column="id" javaType="ArrayList" ofType="student" select="getStudentByTeacherId"/>
   </resultMap>
    <select id="getStudentByTeacherId" resultType="student">
        select *from student where tid = #{tid}
    </select>

小结

1、关联-association 多对一

2、集合-collection 一对多

3、javaType & ofType

javaType:用来指定实体类中属性的集合

ofType:用来指定映射到List或集合中的pojo类型

动态SQL

动态sql就是根据不同的条件生成不同的sql语句

搭建环境

与前面一致，可以添加一个生成随机id的工具类

//生成随机id的工具类
public class IDutils {
    public static String getId(){
        return UUID.randomUUID().toString().replaceAll("-","");
    }

    @Test
    public void test(){
        System.out.println(IDutils.getId());
    }
}

IF语句

例子：有条件的查询

//查询
List<Blog> queryBlog(Map map);

 <select id="queryBlog" parameterType="map" resultType="blog">
        select *from blog 
        <where>
            #查特定title的blog
            <if test="title != null">
                title = #{title}
            </if >
            <if test="author != null">
                and author=#{author}
            </if>
        </where>
    </select>

public void test(){
    SqlSession sqlSession = MyBatisUtils.getSqlSession();
    BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
    Map map = new HashMap();
    map.put("title","Mybatis");
    List<Blog> blogs = mapper.queryBlog(map);
    for (Blog blog : blogs) {
        System.out.println(blog);
    }
    sqlSession.close();
}

choose,when,otherwise（相当于switch）

例子：给了什么就按什么查询

<select id="queryBlogChoose" resultType="blog" parameterType="map">
    select *from blog
    <where>
        <choose>
            <when test="title != null">
                title = #{title}
            </when>
            <when test="views != null">
                and views = #{views}
            </when>
            <otherwise>
                and 1 = 1
            </otherwise>
        </choose>
    </where>
</select>

trim(set where)

sql片段

有的时候，我们可以把一些功能的部分抽取出来，方便复用！

1、使用sql标签抽取公共部分

2、使用include调用

foreach

posted @ 2023-03-21 15:33 奚罗罗阅读(34) 评论(0) 收藏举报

刷新页面返回顶部