Java 之mybatis简单使用（增删改查与两表关联查询）、配置与工具类封装-CSDN博客

本文链接：https://blog.csdn.net/qq_27781261/article/details/136014107

Mybatis是一个优秀的持久层框架，它对JDBC操作数据库的过程进行封装，使开发者只需要关注sql本身。
我们原来使用JDBC操作数据库，需要手动的写代码去注册驱动、获取connection、获取statement等等，现在Mybaits帮助我们把这些事情做了，我们只需要关注我们的业务sql即可，这样可以提高我们的开发效率。
MyBatis属于半自动的ORM框架。

一、基本配置

在线文档：https://mybatis.org/mybatis-3/zh/index.html

导入jar包方式

1、下载： https://github.com/mybatis/mybatis-3/releases

2、将mybatis-3.4.3.jar和lib下的jar包导入项目

3、创建 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>

    <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/jrbac?characterEncoding=utf-8&amp;useSSL=false"/>
                <property name="username" value="jrbac"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>

    <!-- 将我们写好的Sql映射文件，一定要注册到全局配置文件中。-->
    <mappers>
        <mapper resource="com/mapper/adminMapper.xml" />
    </mappers>

</configuration>

4、创建Sql映射文件、实体对象Admin，如：adminMapper.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.admin">
    <!--
        namespace, 名称空间。
        id, 唯一标识，同Spring的Bean标签id一样。
        resultType： 返回值类型。我这里期望他给我返回一个员工对象。
        -->

    <select id="selectAdmin" resultType="com.entity.Admin">
        select * from j_admin
    </select>

</mapper>

5、设置sql输出日志

<settings>
    <!--标准的日志工厂实现-->
    <setting name="logImpl" value="STDOUT_LOGGING" />
</settings>

maven方式

二、SQL操作示例

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

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 com.entity.Admin;

public class Mybatis {
    public Mybatis() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream resourceAsStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory build = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = build.openSession();
        
         List<Admin> list = sqlSession.selectList("selectAdmin");
         for(Admin i : list){
             System.out.println(i);
         }
         
         sqlSession.close();
         resourceAsStream.close();
    }
}

实体类如下：

package com.entity;

public class Test {
    private int id;
    private String name;
    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;
    }
    @Override
    public String toString() {
        return "Test [id=" + id + ", name=" + name + "]";
    }
    
}

package com.entity;

public class Admin {
    private int id;
    private String username;
    private String password;
    private String salt;
    private String last_login_entered;
    private String login_entered;
    private String date_entered;
    private String login_ip;
    private String role_id;
    private Role role;
    private int status;
    private int times;
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getUsername() {
        return username;
    }
    public void setUsername(String username) {
        this.username = username;
    }
    public String getPassword() {
        return password;
    }
    public void setPassword(String password) {
        this.password = password;
    }
    public String getSalt() {
        return salt;
    }
    public void setSalt(String salt) {
        this.salt = salt;
    }
    public String getLast_login_entered() {
        return last_login_entered;
    }
    public void setLast_login_entered(String lastLoginEntered) {
        last_login_entered = lastLoginEntered;
    }
    public String getLogin_entered() {
        return login_entered;
    }
    public void setLogin_entered(String loginEntered) {
        login_entered = loginEntered;
    }
    public String getDate_entered() {
        return date_entered;
    }
    public void setDate_entered(String dateEntered) {
        date_entered = dateEntered;
    }
    public String getLogin_ip() {
        return login_ip;
    }
    public void setLogin_ip(String loginIp) {
        login_ip = loginIp;
    }
    public String getRole_id() {
        return role_id;
    }
    public void setRole_id(String roleId) {
        role_id = roleId;
    }
    public Role getRole() {
        return role;
    }
    public void setRole(Role role) {
        this.role = role;
    }
    public int getStatus() {
        return status;
    }
    public void setStatus(int status) {
        this.status = status;
    }
    public int getTimes() {
        return times;
    }
    public void setTimes(int times) {
        this.times = times;
    }
    @Override
    public String toString() {
        return "Admin [date_entered=" + date_entered + ", id=" + id
                + ", last_login_entered=" + last_login_entered
                + ", login_entered=" + login_entered + ", login_ip=" + login_ip
                + ", password=" + password + ", role=" + role + ", role_id="
                + role_id + ", salt=" + salt + ", status=" + status
                + ", times=" + times + ", username=" + username + "]";
    }
    
}

package com.entity;

public class Role {
    private int id;
    private String name;
    private int is_delete;
    private String note;
    private String date_entered;
    private String update_entered;
    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 int getIs_delete() {
        return is_delete;
    }
    public void setIs_delete(int isDelete) {
        is_delete = isDelete;
    }
    public String getNote() {
        return note;
    }
    public void setNote(String note) {
        this.note = note;
    }
    public String getDate_entered() {
        return date_entered;
    }
    public void setDate_entered(String dateEntered) {
        date_entered = dateEntered;
    }
    public String getUpdate_entered() {
        return update_entered;
    }
    public void setUpdate_entered(String updateEntered) {
        update_entered = updateEntered;
    }
    @Override
    public String toString() {
        return "Role [date_entered=" + date_entered
                + ", id=" + id + ", is_delete=" + is_delete + ", name=" + name + ", note=" + note
                + ", update_entered=" + update_entered + "]";
    }
    
}

testMapper.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.entity.Test">
    <select id="selectTest" resultType="com.entity.Test">
        select * from j_test
    </select>

    <insert id="addTest" useGeneratedKeys="true" keyProperty="id">
        insert into j_test (name) values (#{name});
    </insert>
    
    <delete id="delTest" parameterType="Integer">
        delete from j_test where id=#{id}
    </delete>
    
    <update id="updateTest">
        update j_test set name=#{name} where id=#{id}
    </update>
    
</mapper>

adminMapper.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.admin">
    <!--
        namespace, 名称空间。
        id, 唯一标识，同Spring的Bean标签id一样。
        resultType： 返回值类型。我这里期望他给我返回一个员工对象。
        -->
        
   <resultMap type="com.entity.Admin" id="AdminRoleResultMap">
           <id property="id" column="id" />
           <result property="username" column="username"/>
           <result property="password" column="password"/>
           <result property="date_entered" column="date_entered"/>
           <association property="role" javaType="com.entity.Role">
               <id property="id" column="role_id"/>
               <result property="name" column="name"/>
               <result property="date_entered" column="date_entered"/>
           </association>
   </resultMap>

    <select id="selectAdmin" resultMap="AdminRoleResultMap">
        select a.*,r.name from j_admin a left join j_role r on r.id=a.role_id
    </select>
    
    <select id="selectAdminPage" resultType="com.entity.Admin">
        select * from j_admin 
        <if test="where!=''">
            where 1=1 and id=#{where}
        </if>
        limit #{offset},#{pagesize};
    </select>
    
    <select id="getList"  resultType="com.entity.Admin">
        select * from j_admin where 1=1
        <if test="search!=''">
            and username like #{search}
        </if>
        <if test="orderBy!=''">
            order by id desc
        </if>
        limit #{limit}, #{offset}
    </select>
    
    <select id="getListCount" resultType="java.lang.Integer">
        select count(*) from j_admin where 1=1
        <if test="search!=''">
            and search like #{search}
        </if>
    </select>
    
</mapper>

运行代码：

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

import org.apache.ibatis.session.SqlSession;

import com.entity.Admin;
import com.entity.Test;
import com.utils.MyBatisUntil;


public class test {

    /**
     * @param args
     * @throws IOException 
     */
    public static void main(String[] args) throws IOException {
        SqlSession sqlSession = MyBatisUntil.getSqlSession(false);
        
        //增
        HashMap<String, Object> map = new HashMap<String, Object>();
        map.put("name", "test");
        Test test = new Test();
        test.setName("test");
        System.out.println(sqlSession.insert("addTest", test));  //成功返回1，失败返回0
        map.clear();
        
        //删
        System.out.println(sqlSession.delete("delTest", 2)); //成功返回1，失败返回0
        
        //改
        map.put("id", 3);
        map.put("name", "yy");
        System.out.println(sqlSession.delete("updateTest", map)); //成功返回1，失败返回0
        
        //查
        List<Test> list = sqlSession.selectList("selectTest");
        for(Test a : list){
            System.out.println(a);
        }
        
        
        List<Admin> list1 = sqlSession.selectList("selectAdmin");
        for(Admin a : list1){
            System.out.println(a);
        }
        
        map.clear();
        map.put("where", "");
        map.put("offset", 0);
        map.put("pagesize", 2);
        List<Admin> list2 = sqlSession.selectList("selectAdminPage", map);
        for(Admin a : list2){
            System.out.println(a);
        }
        
        map.clear();
        map.put("search", "");
        map.put("orderBy", "id");
        map.put("sortOrder", "desc");
        map.put("limit", 0);
        map.put("offset", 10);
        List<Admin> list3 = sqlSession.selectList("getList", map);
        for(Admin a : list3){
            System.out.println(a);
        }
        
        System.out.println(sqlSession.selectOne("getListCount",map));
    }

}

resultMap详细讲解：

<resultMap type="映射的Admin对象,带上包名" id="唯一的标识">
           <id property="表的主键字段，或者可以为查询语句中的别名字段" jdbcType="字段类型" property="映射Admin对象的主键属性"/>
           <result jdbcType="字段类型" property="映射到Admin对象的一个属性(须为type定义的Admin对象中的一个属性)" column="表的一个字段(可以为任意表的一个字段)"/>
 
           <association property="Admin的一个对象属性" javaType="com.entity.Role">
               <id property="关联Admin对象的主键属性" column="关联Admin对象的主键字段"/>
               <result property="role的属性" column="任意表的字段"/>
           </association>
           
   </resultMap>

Mybatis执行原生jdbc_sql语句

Connection conn = MyBatisUntil.getSqlSession(true).getConnection();
PreparedStatement pst = conn.prepareStatement("Optimize  TABLE " + table);
pst.execute();

#{}与${}的区别

#{}是占位符，相当于jdbc的PreparedStatement，可以防sql注入

${}是字符串替换，有sql注入风险，可以用于sql语句拼接，如：order by ${order}

三、mybatis工具类

package com.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;
 
/**
 * @author 25754
 */
public class MyBatisUntil {
    //封装会话工厂
    private static SqlSessionFactory Factory;
    /**在进行对象的跨层传递的时候，使用ThreadLocal可以避免多次传递，打破层次间的约束
     */
    private static ThreadLocal<SqlSession> local = new ThreadLocal<SqlSession>();
 
    static {
        try {
            InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
            SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
            Factory = sqlSessionFactoryBuilder.build(is);
 
        } catch (
                IOException e)
 
        {
            e.printStackTrace();
        }
    }
    //封装factory方法
    public  static SqlSessionFactory getFactory(){
        return Factory;
    }
 
    //封装sqlSession会话
    public static SqlSession getSqlSession(boolean IsAutoComiit) {
        SqlSession sqlSession = local.get();
        if (sqlSession == null) {
            sqlSession = Factory.openSession(IsAutoComiit);
            local.set(sqlSession);
        }
        return sqlSession;
    }
 
    //使用泛型封装getMapper
    public static <T extends Object> T getMapper(Class<T> c) {
        SqlSession sqlSession = getSqlSession(true);
        return sqlSession.getMapper(c);
    }
}