MyBatis

最新推荐文章于 2023-04-22 15:27:27 发布

Java是最好的语言，可以接受反驳

最新推荐文章于 2023-04-22 15:27:27 发布

阅读量353

点赞数

分类专栏： maven mybatis 文章标签： maven java 数据库

本文链接：https://blog.csdn.net/weixin_40652498/article/details/102641602

版权

maven 同时被 2 个专栏收录

7 篇文章 0 订阅

订阅专栏

mybatis

4 篇文章 0 订阅

订阅专栏

ORM ：对象关系映射

#mybatis.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>
    <!-- 数据库连接信息  下面就可以用 ${xx} 替换  -->
    <!--<properties resource="mysqlConn.properties" ></properties> -->
    <!-- mybatis的环境配置,此信息在开发中只需照搬即可 -->
    <environments default="development">
        <environment id="development">
            <!-- 配置JDBC事务,此事务由mybatis管理 -->
            <transactionManager type="JDBC"></transactionManager>
            <!-- 配置连接池,此连接池为mybatis连接池 -->
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis"/>
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>

    <!--映射配置文件的路径-->
    <mappers>
       <mapper resource="mapper/usersMapper.xml"/>

    </mappers>
</configuration>

#user.java

public class Users {

    private Integer id ;
    private String username;
    private String userpass;
    private String nickname;
}

#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>com.zx.mybatis</groupId>
    <artifactId>demo01</artifactId>
    <version>1.0-SNAPSHOT</version>

    <dependencies>

        <!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.2</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.48</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/junit/junit -->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>

    </dependencies>


    <!--  maven 项目，资源文件没有存放到resources中，子项目路径映射  -->
    <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>

</project>

#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接口(UserMapper类)的全限定名要和mapper映射文件的namespace值一致。 -->
<mapper namespace="com.zx.entity.Users">
    <select id="selectUserList" resultType="com.zx.entity.Users">
        select * from Users
    </select>
</mapper>

#获取SqlSessionFactory 工厂类

由于SqlSessionFactory本来就是单例类，所以可以构造一个静态类，直接获取


public class MybatisUtils {

    private  static SqlSessionFactory factory = null;

    static {
        String fileName="mybatisConfig.xml";
        try {
            InputStream inputStream= Resources.getResourceAsStream(fileName);
            factory = new SqlSessionFactoryBuilder().build(inputStream);
        }catch (IOException e){
            e.printStackTrace();
        }
    }

    public static SqlSession getSession (){
        return factory.openSession();
    }
}

测试

 public void test(){
        SqlSession session =null;
        try {

            session= MybatisUtils.getSession();
            //获取当前类对象
            UserMapper userMapper = session.getMapper(UserMapper.class);
            //调用接口，自动执行xml中的sql
            List<User> userList = userMapper.selectUserList();
            for(User user : userList){
                System.out.println(user);
            }
        }
        catch (Exception e){

        }
        finally {
            if(session==null){
                session.close();
            }
        }
    }

简单增删改查实现

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.zxit.dao.UserMapper"> <!--绑定接口-->

    <select id="selectUserList" resultType="com.zxit.entity.User"> <!--绑定标签-->
      select * from userInfo
    </select>

    <select id="selectUserOne" parameterType="int" resultType="com.zxit.entity.User">
      select * from userInfo where id=#{id}
    </select>

    <update id="updateUser" parameterType="com.zxit.entity.User" >
        update userInfo set name=#{name},sex=#{sex},birthday=#{birthday},address=#{address},remarks=#{remarks} where id=#{id}
    </update>

    <insert id="addUser" parameterType="com.zxit.entity.User" >
        insert into userInfo (name,sex,birthday,address,remarks) values(#{name},#{sex},#{birthday},#{address},#{remarks})
    </insert>

    <delete id="deleteUser" parameterType="int" >
        delete from userInfo where id =#{id}
    </delete>


</mapper>

UserMapper.interface(没有实现类）

package com.zxit.dao;

import com.zxit.entity.User;

import java.util.List;
import java.util.Map;

public interface UserMapper {
     List<User> selectUserList();
     User selectUserOne(int id);
     int updateUser(User user);
     int addUser(User user);
     int deleteUser(int id);
}

Test


    @Test
    /**
     * 查询全部用户
     */
    public void selectUserList(){
        SqlSession session =null;
        try {

            session= MybatisUtils.getSession();
            UserMapper userMapper = session.getMapper(UserMapper.class);
            List<User> userList = userMapper.selectUserList();
            for(User user : userList){
                System.out.println(user);
            }

        }
        catch (Exception e){

        }
        finally {
            if(session==null){
                session.close();
            }
        }
    }

    @Test
    /**
     * 查询一个用户
     */
    public void selectUserOne(){
        String fileName = "mybatisConfig.xml";
        InputStream inputStream = null;
        try {
            inputStream = Resources.getResourceAsStream(fileName);
        }
        catch (IOException e){

        }
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = factory.openSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        User user  = userMapper.selectUserOne(2);
        System.out.println(user);
        sqlSession.close();
    }

    @Test
    public void updateUser(){
        String fileName = "mybatisConfig.xml";
        InputStream inputStream = null;
        try {
            inputStream= Resources.getResourceAsStream(fileName);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
        }
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession session = factory.openSession();
        UserMapper userMapper = session.getMapper(UserMapper.class);
        DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");

        int i= 0;
        try {
            Date data = dateFormat.parse("1998-9-3");
            System.out.println(data);
             i= userMapper.updateUser(new User(2,"456",true,data,"1213","备注"));
        } catch (ParseException e) {
            e.printStackTrace();
        }
        session.commit();
        System.out.println(i );
        session.close();

    }

    @Test
    public void addUser(){
        String fileName ="mybatisConfig.xml";
        InputStream inputStream = null;
        try {
            inputStream=Resources.getResourceAsStream(fileName);
        } catch (IOException e) {
            e.printStackTrace();
        }
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession session = sessionFactory.openSession();
        UserMapper userMapper = session.getMapper(UserMapper.class);

        DateFormat dateFormat = null;
        try {
            dateFormat =new  SimpleDateFormat("yyyy-MM-dd");
            Date date = dateFormat.parse("1998-09-05");
            User users = new User(22,"姓名",true,date,"地址","备注");
            users.setName(null);
            userMapper.addUser(users);

        } catch (Exception e) {
            e.printStackTrace();
        }
        session.commit();


    }

    @Test
    public void deleteUser(){
        String fileName="mybatisConfig.xml";
        InputStream inputStream = null;
        try {
            inputStream = Resources .getResourceAsStream(fileName);
        } catch (IOException e) {
            e.printStackTrace();
        }
        SqlSessionFactory sessionFactory =  new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sessionFactory.openSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        int i = userMapper.deleteUser(5);
        sqlSession.commit();
        System.out.println(i );
    }

传递多个参数

//传递一个参数用parameterType="int"
//传递对象用parameterType="com.zxit.entity.User"
//传递多个参数用parameterType="map"
//map示例（不用对象，要传递多个参数，使用map）

public interface UserMapper {
     int mapUpdate(Map<String,Object> map);
}

<?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.zxit.dao.UserMapper"> <!--绑定接口-->

    <update id="mapUpdate" parameterType="map">
        update userInfo set name =#{nameMap},sex=#{sexMap} where id = #{idMap}
    </update>

</mapper>

public void mapUpdate(){
        SqlSession session = MybatisUtils.getSession();
        UserMapper userMapper = session.getMapper(UserMapper.class);
        Map<String,Object> map = new HashMap<String,Object>();
        map.put("nameMap","熊");
        map.put("sexMap",false);
        map.put("idMap",4);
        userMapper.mapUpdate(map);
        session.commit();

    }

数据库字段与实体类不一致

在mapper文件值配置映射

    <resultMap id="userResultMap" type="com.zxit.entity.User">
        <!--column 为数据库中的字段   property  为实体类字段-->
        <id property="userId" column="id"/>
        <result property="userName" column="name"/>
        <result property="userAddress" column="address"/>
    </resultMap>

    <select id="selectUserList"  resultMap="userResultMap">
        select * from userInfo
    </select>

在mapper.xml中不写全路径，使用别名

在mybatisConfi.xml中配置

    <typeAliases>
        <typeAlias type="com.zxit.entity.Student" alias="Student"/>
    </typeAliases>

日志

标准日志-->mybatis配置文件

<configuration>    
    <settings>
        <setting name="logImpl" value="STDOUT_LOGGING"/><!--标准日志-->
    </settings>
</configuration>

LOG4J -->mybatis配置文件

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

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

applicationTest.java

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

@Test
public void selectUser() {
    logger.info("info：进入selectUser方法");
    logger.debug("debug：进入selectUser方法");
    logger.error("error: 进入selectUser方法");
}

分页

sql实现：limit 用法（可以先进行排序）

 select * from user limit 0,2;

在mybatis 使用分页，利用map传递参数

UserMapper.java

public interface UserMapper {
     public List<User> selectUserLimit(Map<String,Integer> map);
}

UserMapper.xml

<mapper namespace="com.zxit.dao.UserMapper">

    <resultMap id="userResultMap" type="com.zxit.entity.User">
        <!--column 为数据库中的字段   property  为实体类字段-->
        <id property="userId" column="id"/>
        <result property="userName" column="name"/>
        <result property="userAddress" column="address"/>
    </resultMap>

    <select id="selectUserLimit" parameterType="map" resultMap="userResultMap">
        select * from userInfo limit  #{startIndex}, #{pageSize}
    </select>

</mapper>

TestLimit

    @Test
    //使用limit  与 map集合  查询分页
    public void selectUserLimit(){
        SqlSession session = MybatisUtils.getSession();
        UserMapper mapper = session.getMapper(UserMapper.class);
        Map<String, Integer> limitMap = new HashMap<String, Integer>();
        limitMap.put("startIndex",1);
        limitMap.put("pageSize",7);
        List<User> users = mapper.selectUserLimit(limitMap);
        for (User user : users){
            System.out.println(user);
        }
    }

注解开发

1.在mybaits中注解用的较少，因为局限性太大，只支持简单的注解

public interface UserMapper{
    @Select("select * from UserInfo")
    public List<User> selectUser();
}

mybatisConfig.xml

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

此时就可以不用写Mapper配置文件

2.@Param使用

public interface UserMapper{
    @Select("select * from UserInfo where id = #{sid}")
    public List<User> selectUser(@Param("sid") int id);
}

Param的意思是，传过来的id参数，当映射到sql上时，参数名会用sid去匹配

3.@Update,@Delete,@Insert 依此类推

多对一查询


CREATE TABLE `teacher`  (
  `id` int(11) PRIMARY KEY,
  `name` varchar(20) 
) 

CREATE TABLE `student`  (
  `id` int(11) PRIMARY KEY ,
  `name` varchar(20),
  `sex` bit(1) ,
  `age` int(11) ,
  `teacher` int(11),
  FOREIGN KEY (`teacher`) REFERENCES `teacher` (`id`)   #外键
)

注意实体类关系
public class Student{
    private int id;
    private String name;
    private blloean sex;
    private int age;
    private Teacher teacher;
}

<mapper namespace="com.kangshesi.dao.IUserDao">
    <!--思路：1.查询所有的学生信息，根据查出来的id，寻找对应老师-->
    <select id="selectStudent" resultMap="resultTeacher">
        select * from student
    </select>
    <resultMap id="resultTeacher" type="com.zxit.entity.Student">
        <result property="id" column="id" />
        <result property="name" column="name" />
        <result property="sex" column="sex" />
        <result property="age" column="age" />
        <!--复杂的属性要单独处理
            对象： association
            集合：collection
            -->
        <association property="teacher" column="teacher" javaType="com.zxit.entity.Teacher" select="selectTeacher"/>
    </resultMap>
    <select id="selectTeacher" resultType="com.zxit.entity.Teacher">
        select * from teacher where id = #{id}
    </select>


    <!--思路2  按照结果嵌套处理(了解，实在是看不懂）-->
    <select id="selectStudent2" resultMap="selectTeacher2">
        select s.id sid ,s.name sname,t.name tname
          form student s , teacher t
          where s.teacher = t.id;
    </select>
    <resultMap id="selectTeacher2" type="com.zxit.entity.Student">
        <result property="id" column="id" />
        <result property="name" column="name" />
        <result property="sex" column="sex" />
        <result property="age" column="age" />
        <!--复杂的属性要单独处理
            对象： association
            集合：collection
            -->
        <association property="teacher"  javaType="com.zxit.entity.Teacher">
            <result property="name" column="tname"/>
        </association>

    </resultMap>
</mapper>

设置数据库字段自动映射对象字段

如果说数据库字段与对象字段一样就不需要映射，但是由于数据库间隔是用下划线（_)

而我们对象中是用驼峰来区分间隔，所以这就出现了分歧

直接在mybatis中配置这个属性，就不用配置数据库与对象之间的关系

<setting name="mapUnderscoreToCamelCase" value="true"/>

动态SQL标签

1 . <if> 当某个字段为空时，可以排除掉

    <select id="selectCompantIf" parameterType="map" resultType="com.zxit.entity.Compant">
        select * from compant where 1=1
        <if test="name != null">
            and name = #{name}
        </if>
        <if test="peopleNum != null">
            and peopleNum = #{peopleNum}
        </if>
    </select>

2.<where> 当sql有where 存在时，用 where标签，可以有效的去除多余的and

    <select id="selectCompantIf" parameterType="map" resultType="com.zxit.entity.Compant">
        select * from compant
        <where>
            <if test="name != null">
                and name = #{name}
            </if>
            <if test="peopleNum != null">
                and peopleNum = #{peopleNum}
            </if>
        </where>
    </select>

3.<choose><when><otherwise> 类似与程序中的switch，case，default 作用，只会在when中成立一个，如果没有，则执行<otherwise>

    <select id="selectCompantChooseWhenOtherwiseTest" parameterType="map" resultType="com.zxit.entity.Compant">
        select * from compant
        <where>
            <choose>
                <when test="name!=null">
                    and name = #{name}
                </when>
                <when test="peopleNum!=null">
                    and peopleNum = #{peopleNum}
                </when>
                <otherwise>
                    and address=#{address}
                </otherwise>
            </choose>
        </where>
    </select>

4.<sql><include> sql片段，类似于函数调用的功能（可以代码块复用）


    <sql id="if_name_peopleNum">
        <if test="name!=null">
            name=#{name},
        </if>
        <if test="peopleNum!=null">
            peopleNum=#{peopleNum},
        </if>
    </sql>

    <update id="updateCompantSet" parameterType="map">
        update compant
        <set>
            <include refid="if_name_peopleNum"/>
        </set>
        where id=#{id}
    </update>

5.<foreach>循环拼接SQL

    
    <select id="selectCompantIf" parameterType="map" resultType="com.zxit.entity.Compant">
        select * from compant
        <where>
           <foreach collection="list" item="id" open="and (" close=")" separator="or">
                id=#{id}
            </foreach>
        </where>
    </select>


调用方法：Map map = new HashMap();
        List list = new ArrayList();
        list.add(1); list.add(2); list.add(3);
        map.put(list);
        xx.xx(map);
结果：select * from compant where id=1 or id=2 or id=3

mybatis逆向工程之生成文件解释

一、mapper接口中的方法解析

mapper接口中的函数及方法

方法	功能说明
int countByExample(UserExample example) thorws SQLException	按条件计数
int deleteByPrimaryKey(Integer id) thorws SQLException	按主键删除
int deleteByExample(UserExample example) thorws SQLException	按条件删除
String/Integer insert(User record) thorws SQLException	插入数据（返回值为ID）
User selectByPrimaryKey(Integer id) thorws SQLException	按主键查询
List selectByExample(UserExample example) thorws SQLException	按条件查询
ListselectByExampleWithBLOGs(UserExample example) thorws SQLException	按条件查询（包括BLOB字段）。只有当数据表中的字段类型有为二进制的才会产生。
int updateByPrimaryKey(User record) thorws SQLException	按主键更新
int updateByPrimaryKeySelective(User record) thorws SQLException	按主键更新值不为null的字段
int updateByExample(User record, UserExample example) thorws SQLException	按条件更新
int updateByExampleSelective(User record, UserExample example) thorws SQLException	按条件更新值不为null的字段

二、example实例解析

mybatis的逆向工程中会生成实例及实例对应的example，example用于添加条件，相当where后面的部分
xxxExample example = new xxxExample();
Criteria criteria = new Example().createCriteria();

方法	说明
example.setOrderByClause(“字段名 ASC”);	添加升序排列条件，DESC为降序
example.setDistinct(false)	去除重复，boolean型，true为选择不重复的记录。
criteria.andXxxIsNull	添加字段xxx为null的条件
criteria.andXxxIsNotNull	添加字段xxx不为null的条件
criteria.andXxxEqualTo(value)	添加xxx字段等于value条件
criteria.andXxxNotEqualTo(value)	添加xxx字段不等于value条件
criteria.andXxxGreaterThan(value)	添加xxx字段大于value条件
criteria.andXxxGreaterThanOrEqualTo(value)	添加xxx字段大于等于value条件
criteria.andXxxLessThan(value)	添加xxx字段小于value条件
criteria.andXxxLessThanOrEqualTo(value)	添加xxx字段小于等于value条件
criteria.andXxxIn(List<？>)	添加xxx字段值在List<？>条件
criteria.andXxxNotIn(List<？>)	添加xxx字段值不在List<？>条件
criteria.andXxxLike(“%”+value+”%”)	添加xxx字段值为value的模糊查询条件
criteria.andXxxNotLike(“%”+value+”%”)	添加xxx字段值不为value的模糊查询条件
criteria.andXxxBetween(value1,value2)	添加xxx字段值在value1和value2之间条件
criteria.andXxxNotBetween(value1,value2)	添加xxx字段值不在value1和value2之间条件

mybatisPlus

https://mp.baomidou.com/guide/

<dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.3.1</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.46</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <scope>runtime</scope>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>

Java是最好的语言，可以接受反驳

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MyBatis

ORM ：对象关系映射#mybatis.xml<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-...
复制链接

扫一扫