Mybatis的简单使用，连接数据库，增删改查的实现

最新推荐文章于 2023-03-10 20:48:28 发布

WanWannnnn

最新推荐文章于 2023-03-10 20:48:28 发布

阅读量209

点赞数 1

分类专栏： Java 文章标签： mybatis java mysql

本文链接：https://blog.csdn.net/weixin_54597685/article/details/119697077

版权

Java 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

Mybatis的简单使用

搭建环境
CRUD
Map实现任意匹配
使用别名
结果集映射
使用注解

搭建环境

1、新建一个maven工程，qucikstart

文件结构：
请添加图片描述

2、修改Maven pom文件，添加所需依赖

<dependencies>
    <!-- mybatis依赖 -->
    <dependency>
        <groupId>org.mybatis</groupId>
        <artifactId>mybatis</artifactId>
        <version>3.5.6</version>
    </dependency>
    <!-- mysql驱动 -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.47</version>
    </dependency>
</dependencies>

3、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>
    <!-- 属性：配置文件路径 -->
    <properties resource="datasource.properties"/>

    <!--  配置文件  -->
    <environments default="blog">
        <environment id="blog">
            <!-- 事务管理  -->
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${mysql.driver}"/>
                <property name="url" value="${mysql.url}"/>
                <property name="username" value="${mysql.username}"/>
                <property name="password" value="${mysql.password}"/>
            </dataSource>
        </environment>
    </environments>
    <!--每一个Mapper.xml都需要在Mybatis核心配置文件中注册-->
    <mappers>
        <package name="xyz.wan.mapper"/>
    </mappers>
</configuration>

4、datasource.properties

mysql.driver=com.mysql.jdbc.Driver
mysql.url=jdbc:mysql://192.168.158.130:3306/test?useSSL=false
mysql.username=root
mysql.password=kb1248kb12

5、userMapper.xml

namespace中包名要和Mapper中接口的包名一致

<?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绑定对应的mapper接口-->
<mapper namespace="xyz.wan.mapper.UserMapper" >
	<!--mapper接口中方法的具体sql语句实现-->
</mapper>

6、工具类封装

package xyz.wan.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 {
        try {
            //获取sqlSessionFactory对象
            final String RESOURCE = "mybatis.xml";
            InputStream inputStream= Resources.getResourceAsStream(RESOURCE);
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    //sqlSession包含了面向数据库执行SQL命令的所有方法
    public static SqlSession getSqlSession(){
        //在这里设置为true	以后的增删改就不用提交事务  默认为false
        return sqlSessionFactory.openSession(true);
    }
}

7、测试类

package xyz.wan;

import org.apache.ibatis.session.SqlSession;
import xyz.wan.entity.User;
import xyz.wan.mapper.UserMapper;
import xyz.wan.utils.MybatisUtils;

import java.io.IOException;
import java.util.List;

public class App 
{
    public static void main(String[] args) throws IOException {
        //获取sqlSession对象
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        
        //执行SQL
        //方式一：getMapper
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

        /*
        执行代码后续CRUD中给出
        */
        
        //关闭
        sqlSession.close();
    }
}

实体对象和接口在后续给出

后续只需操作这三个文件

请添加图片描述

CRUD

UserMapper.java

package xyz.wan.mapper;

import xyz.wan.entity.User;

import java.util.List;

public interface UserMapper {

    //查询全部用户
    List<User> selectAll();

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

    //插入用户
    boolean insertUser(List<User> list);

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

    //删除用户
    boolean deleteUser(int[] array);

}

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">
<!--namespace绑定对应的mapper接口-->
<mapper namespace="xyz.wan.mapper.UserMapper" >
    <!-- select 查询语句-->
   <!--
        id => namespace中的方法名 => 接口中的方法名
        resultType:Sql语句执行的返回值！
        parameterType:参数类型
   -->
    <select id="selectAll" resultType="xyz.wan.entity.User">
        select * from studentinfo
    </select>

    <select id="getUserById" parameterType="int" resultType="xyz.wan.entity.User">
        select * from studentinfo where stuId = #{id}
    </select>

    <insert id="insertUser" parameterType="xyz.wan.entity.User">
        insert into studentinfo (stuName,stuAge,stuGender,mobile,tuition,fkClassId) values
        <foreach collection="list" item="user" separator=",">
        (#{user.stuName},#{user.stuAge},#{user.stuGender},#{user.mobile},#{user.tuition},#{user.fkClassId})
        </foreach>
    </insert>

    <update id="updateUser" parameterType="xyz.wan.entity.User">
        update studentinfo
        <set>
            <if test="null != stuName">
                stuName=#{stuName},
            </if>
            <if test="null != stuAge">
                stuAge=#{stuAge},
            </if>
            <if test="null != stuGender">
                stuGender=#{stuGender},
            </if>
            <if test="null != mobile">
                mobile=#{mobile},
            </if>
            <if test="null != tuition">
                tuition=#{tuition},
            </if>
            <if test="null != fkClassId">
                fkClassId=#{fkClassId}
            </if>
        </set>
        where stuId=#{stuId}
    </update>

    <delete id="deleteUser" parameterType="xyz.wan.entity.User">
        delete from studentinfo where stuId in
        <foreach collection="array" item="id" open="(" close=")" separator=",">
            #{id}
        </foreach>
    </delete>

</mapper>

App.java

package xyz.wan;

import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import xyz.wan.entity.User;
import xyz.wan.mapper.UserMapper;
import xyz.wan.utils.MybatisUtils;

import java.util.ArrayList;
import java.util.List;

/**
 * Hello world!
 *
 */
public class App 
{
    @Test
    public void selectAll(){
        //获取sqlSession对象
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        //执行SQL
        //方式一：getMapper
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        List<User> userList = userMapper.selectAll();

        //方式二:不推荐使用
        //List<User> userList = sqlSession.selectList("xyz.wan.mapper.UserMapper.selectAll");

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

        //关闭
        sqlSession.close();
    }

    @Test
    public void getUserById(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        User user = userMapper.getUserById(6);
        System.out.println(user);
        sqlSession.close();
    }

    //增删改需要提交事务
    @Test
    public void insertUser(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

        ArrayList<User> userList = new ArrayList<>();
        userList.add(new User("Wan3", 21, "男", "18932227693", 23333.00, 1));
        userList.add(new User("MOO3",22,"女","11011310611",32222.00,2));

        boolean flag = userMapper.insertUser(userList);
        System.out.println(flag);

        //提交事务
        //sqlSession.commit();
        //或者 Utils中 sqlSessionFactory.openSession(true)

        sqlSession.close();
    }

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

        User user = new User();
        user.setStuId(75);
        user.setStuName("Wann");
        user.setTuition(6666.66);
        user.setFkClassId(5);
        boolean flag = userMapper.updateUser(user);
        System.out.println(flag);

        sqlSession.close();
    }


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


        boolean flag = userMapper.deleteUser(new int[]{58,59,62,63,70,71,74,75,76,77});
        System.out.println(flag);

        sqlSession.close();
    }

}

Map实现任意匹配

UserMapper.java

//Map
User insertUserMap(Map<String,Object> map);

userMapper.xml

<!--野路子：使用Map,传递map的key-->
<insert id="insertUserMap" parameterType="map">
    insert into studentinfo (stuName,stuAge,stuGender,mobile,tuition,fkClassId) values
    <foreach collection="list" item="user" separator=",">
        (#{Name},#{Age},#{Gender},#{mobile},#{tuition},#{ClassId})
    </foreach>
</insert>

Test类

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

    Map<String, Object> map = new HashMap<>();
    map.put("Name","WanTEST");
    map.put("Age",21);
    map.put("Gender","男");
    map.put("mobile","18572227693");
    map.put("tuition",23333.00);
    map.put("ClassId",2);
    mapper.insertUserMap(map);

    sqlSession.close();
}

使用别名

<!--别名-->
    <typeAliases>
<!--        <package name="xyz.wan.entity"/>-->
        <typeAlias type="xyz.wan.entity.User" alias="User"/>
    </typeAliases>

<select id="getUserByNameLike" resultType="User">
    select * from studentinfo where stuName like "%"#{value}"%"
</select>

List<User> getUserByNameLike(String name);

@Test
public void getUserByNameLike(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    List<User> users = mapper.getUserByNameLike("张");
    for (User user : users) {
        System.out.println(user);
    }

    sqlSession.close();
}

结果集映射

UserResultMap.java

字段名不匹配与数据库中字段不匹配

package xyz.wan.entity;

//@Alias("userTest")
public class UserResultMap {
    private Integer Id;
    private String Name;
    private Integer Age;
    private String Gender;
    private String phoneNum;
    private Double Tut;
    private Integer ClassId;

    public UserResultMap() {
    }

    public UserResultMap(Integer id, String name, Integer age, String gender, String phoneNum, Double tut, Integer classId) {
        Id = id;
        Name = name;
        Age = age;
        Gender = gender;
        this.phoneNum = phoneNum;
        Tut = tut;
        ClassId = classId;
    }

    public Integer getId() {
        return Id;
    }

    public void setId(Integer id) {
        Id = id;
    }

    public String getName() {
        return Name;
    }

    public void setName(String name) {
        Name = name;
    }

    public Integer getAge() {
        return Age;
    }

    public void setAge(Integer age) {
        Age = age;
    }

    public String getGender() {
        return Gender;
    }

    public void setGender(String gender) {
        Gender = gender;
    }

    public String getPhoneNum() {
        return phoneNum;
    }

    public void setPhoneNum(String phoneNum) {
        this.phoneNum = phoneNum;
    }

    public Double getTut() {
        return Tut;
    }

    public void setTut(Double tut) {
        Tut = tut;
    }

    public Integer getClassId() {
        return ClassId;
    }

    public void setClassId(Integer classId) {
        ClassId = classId;
    }

    @Override
    public String toString() {
        return "UserResultMap{" +
                "Id=" + Id +
                ", Name='" + Name + '\'' +
                ", Age=" + Age +
                ", Gender='" + Gender + '\'' +
                ", phoneNum='" + phoneNum + '\'' +
                ", Tut=" + Tut +
                ", ClassId=" + ClassId +
                '}';
    }
}

userMapper.xml

<resultMap id="UserMap" type="xyz.wan.entity.UserResultMap">
    <result column="stuId" property="Id"/>
    <result column="stuName" property="Name"/>
    <result column="stuAge" property="Age"/>
    <result column="stuGender" property="Gender"/>
    <result column="mobile" property="phoneNum"/>
    <result column="tuition" property="Tut"/>
    <result column="fkClassId" property="ClassId"/>
</resultMap>

<!--结果集映射-->
<select id="getUserById2" parameterType="int" resultMap="UserMap">
    select * from studentinfo where stuId = #{id}
</select>

UserMapper.java

//根据id查询用户2   类型不匹配问题
UserResultMap getUserById2(int id);

Test

@Test
public void getUserById2(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
    UserResultMap userById2 = userMapper.getUserById2(6);
    System.out.println(userById2);
    sqlSession.close();
}

使用注解

UserMapper.java 注解写在接口上

//查询全部用户
@Select("select * from studentinfo")
List<User> selectAll();

mybatis.xml 绑定mapper

<mappers>
    <package name="xyz.wan.mapper"/>
</mappers>

Test测试

后续只需操作这两个文件
请添加图片描述

WanWannnnn

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录