MyBatis学习笔记
|
35
|
0
|
未分类

1273 字
|
7 分钟

MyBatis

mybatis – MyBatis 3 | Getting started

配置MyBatis

导入Maven坐标

<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.5.5</version>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.49</version>
</dependency>
<dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
    <version>4.12</version>
    <scope>test</scope>
</dependency>
<dependency>
    <groupId>org.slf4j</groupId>
    <artifactId>slf4j-api</artifactId>
    <version>1.7.25</version>
</dependency>
<dependency>
    <groupId>ch.qos.logback</groupId>
    <artifactId>logback-classic</artifactId>
    <version>1.2.3</version>
</dependency>
<dependency>
    <groupId>ch.qos.logback</groupId>
    <artifactId>logback-core</artifactId>
    <version>1.2.3</version>
</dependency>

在resources中添加配置文件logback.xml

<?xml version="1.0" encoding="UTF-8"?>
<configuration>
    <!--    console表示当前日志信息是可以输出到控制台的-->
    <appender name="Console" class="ch.qos.logback.core.ConsoleAppender">
        <encoder>
            <pattern>[%level] %blue(%d{HH:mm:ss.SSS}) %cyan([%thread]) %boldGreen(%logger{15}) - %msg %n</pattern>
        </encoder>
    </appender>
    <logger name="com.Carter_x" level="DEBUG" additivity="false">
        <appender-ref ref="Console"/>
    </logger>
</configuration>

创建文件mybatis-config.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <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>
    <mappers>
        <mapper resource="org/mybatis/example/BlogMapper.xml"/>
    </mappers>
</configuration>

MyBatis快速入门

写一个mapper的xml配置文件，样例如下

userMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="test"><!--命名空间为test-->
    <select id="selectAll" resultType="com.tansor.pojo.User"><!--User类是自己定义的-->
        select * from userinfo
    </select>

</mapper>

在mybatis-config.xml引入这个mapper

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "https://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://114.55.132.52:3306/spring"/>
                <property name="username" value="spring"/>
                <property name="password" value="spring_password"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <!--加载sql映射文件-->
        <mapper resource="userMapper.xml"/><!--此处引入-->
    </mappers>
</configuration>

定义User类，内含一些字段，对应数据表每行数据的属性，自动生成getter，setter

package com.tansor.pojo;

public class User {
    private String user_id;
    private String user_name;
    private Integer user_age;

    public User(String user_id,String user_name,int user_age){      //一定要构造函数
        this.user_id=user_id;                                       //一定要构造函数
        this.user_name=user_name;                                   //一定要构造函数
        this.user_age=user_age;                                     //一定要构造函数
    }

    public String getUser_id() {
        return user_id;
    }

    public void setUser_id(String user_id) {
        this.user_id = user_id;
    }

    public String getUser_name() {
        return user_name;
    }

    public void setUser_name(String user_name) {
        this.user_name = user_name;
    }

    public Integer getUser_age() {
        return user_age;
    }

    public void setUser_age(Integer user_age) {
        this.user_age = user_age;
    }

    @Override
    public String toString() {
        return "User{" +
                "user_id='" + user_id + '\'' +
                ", user_name='" + user_name + '\'' +
                ", user_age=" + user_age +
                '}';
    }
}

测试测试mybatis查表

package com.tansor;

import com.tansor.pojo.User;
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;
import java.util.List;

public class MybatisDemo {
    public static void main(String[] args) throws IOException {
        //1.获取sqlSessionFactory
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        //2.获取sqlSession对象
        SqlSession sqlSession=sqlSessionFactory.openSession();
        //3.执行sql(selectList是查表，selectOne是查单条数据，根据mapper适应)
        List<User> users = sqlSession.selectList("test.selectAll");//使用命名空间.id去访问mapper
        System.out.println(users);
        //4.释放资源
        sqlSession.close();
    }
}

48b131df99c1babbe0d14ce8f4b0a79b

Mapper代理开发

  • 定义一个接口，名字要和Mapper文件一样，且两者要在同一目录下（不是真的放同一目录，而是在resources目录下创建和包结构一样的目录结构后，那么编译后mapper和那个接口就会位于同一个目录）

  • 设置SQL映射文件的namespace属性为Mapper接口全限定名

  • 在Mapper接口中定义方法，方法名与SQL映射Mapper文件中的sql的id一致，且参数类型和返回类型要一致

以下是Mapper映射文件
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.tansor.mapper.UserMapper">
    <select id="selectAll" resultType="com.tansor.pojo.User">
        select * from userinfo
    </select>

</mapper>
以下是Mapper接口
package com.tansor.mapper;

import com.tansor.pojo.User;

import java.util.List;

public interface UserMapper {

    List<User> selectAll();

}
加载Mapper映射
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "https://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/spring"/>
                <property name="username" value="spring"/>
                <property name="password" value="spring_password"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <!--加载sql映射文件-->
        <mapper resource="com/tansor/mapper/UserMapper.xml"/>

        <!--如果Mapper映射的文件名和mapper接口类名相同，直接用以下方法全自动扫描映射-->
        <package name="com.tansor.mapper"/>
    </mappers>
</configuration>
测试类
package com.tansor;

import com.tansor.mapper.UserMapper;
import com.tansor.pojo.User;
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;
import java.util.List;

public class MybatisDemo2 {
    public static void main(String[] args) throws IOException {
        //1.获取sqlSessionFactory
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        //2.获取sqlSession对象
        SqlSession sqlSession=sqlSessionFactory.openSession();
        //3.执行sql
        //3.1获取UserMapper接口代理对象
        UserMapper userMapper=sqlSession.getMapper(UserMapper.class);
        List<User> users = userMapper.selectAll();
        System.out.println(users);
        //4.释放资源
        sqlSession.close();
    }
}

环境准备

安装MyBatisX插件

案例，查询用户信息

配置总览

image-20240311124145683

UserInfoMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.tansor.mapper.UserInfoMapper">
    <select id="selectAll" resultType="com.tansor.pojo.UserInfo">
        select * from userinfo
    </select>

    <select id="selectById" resultType="com.tansor.pojo.UserInfo">
        select *
        from userinfo
        where user_id='#'
    </select>

</mapper>

UserInfoMapper.java

package com.tansor.mapper;

import com.tansor.pojo.UserInfo;

import java.util.List;

public interface UserInfoMapper {
    List<UserInfo> selectAll();

    UserInfo selectById(String user_id);
}

UserInfo.java

package com.tansor.pojo;

public class UserInfo {
    private String user_id;
    private String user_name;
    private int user_age;

    public String getUser_id() {
        return user_id;
    }

    public void setUser_id(String user_id) {
        this.user_id = user_id;
    }

    public String getUser_name() {
        return user_name;
    }

    public void setUser_name(String user_name) {
        this.user_name = user_name;
    }

    public int getUser_age() {
        return user_age;
    }

    public void setUser_age(int user_age) {
        this.user_age = user_age;
    }
}

MyBatis查询操作

用户信息类字段使用驼峰命名，而数据库是下划线，如何解决？

插入sql片段，but白雪
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.tansor.mapper.UserInfoMapper">
    <sql id="brand_colum"><!--创建sql片段-->
        user_id as userId,user_name as userName,user_age as userAge
    </sql>

    <select id="selectAll" resultType="com.tansor.pojo.UserInfo">
        select
        <include refid="brand_colum" /><!--引入sql片段-->
        from userinfo
    </select>

    <select id="selectById" resultType="com.tansor.pojo.UserInfo">
        select *
        from userinfo
        where user_id='#'
    </select>

</mapper>
使用resultMap映射
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.tansor.mapper.UserInfoMapper">
    <resultMap id="userInfoResultMap" type="com.tansor.pojo.UserInfo"><!--定义映射-->
        <id column="user_id" property="userId"/><!--id标签用来映射主键-->
        <result column="user_name" property="userName"/><!--result映射一般标签-->
        <result column="user_age" property="userAge"/><!--colum指的是原来的属性名，property指的是映射后的名字-->
    </resultMap>

    <select id="selectAll" resultMap="userInfoResultMap"><!--引入映射，使用resultMap属性替换resultType属性-->
        select *
        from userinfo
    </select>

    <select id="selectById" resultType="com.tansor.pojo.UserInfo">
        select *
        from userinfo
        where user_id='#'
    </select>

</mapper>

根据id查询

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.tansor.mapper.UserInfoMapper">
    <resultMap id="userInfoResultMap" type="com.tansor.pojo.UserInfo">
        <id column="user_id" property="userId"/>
        <result column="user_name" property="userName"/>
        <result column="user_age" property="userAge"/>
    </resultMap>

    <select id="selectAll" resultMap="userInfoResultMap">
        select *
        from userinfo
    </select>
    <select id="selectById" resultMap="userInfoResultMap">
        select *
        from userinfo
        where user_id = #{userId}
    </select>
    <!--
        参数占位符
        1. #{}:会将其替换为?，防止SQL注入
        2. ${}:可能会被注入
        3. 使用时机
            参数传递时使用 #{}
            表明或列名不固定时使用 ${}

        参数类型：parameterType基本不写

        特殊字符处理
        1. 转义字符，小于号用 &lt 代替
        2. CDATA区
            <![CDATA[
                <   //这里写小于号
            ]]>
    -->

</mapper>
原始符号 转义字符
> &gt
< &lt
& &amp
“ &quot
’ &apos

条件查询

package com.tansor.mapper;

import com.tansor.pojo.UserInfo;
import org.apache.ibatis.annotations.Param;

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

/*
    多条件查询
    1. 散装参数传递，前面加@Param指定参数名
    2. 将参数封装为对象传过去，sql语句会自动寻找字段
    3. 使用Map封装参数，无需指定Map映射类型，键值对映射
 */

public interface UserInfoMapper {
    List<UserInfo> selectByCondition(
            @Param("userId")String userId,
            @Param("userName")String userName,
            @Param("userAge")int userAge
    );

    List<UserInfo> selectByCondition(
            UserInfo userinfo
    );

    List<UserInfo> selectByCondition(Map map);
}
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.tansor.mapper.UserInfoMapper">
    <resultMap id="userInfoResultMap" type="com.tansor.pojo.UserInfo">
        <id column="user_id" property="userId"/>
        <result column="user_name" property="userName"/>
        <result column="user_age" property="userAge"/>
    </resultMap>

    <select id="selectByCondition" resultMap="userInfoResultMap">
        select *
        from userinfo
        where user_id LIKE #{userId}
            and user_name LIKE #{userName}
            and user_age LIKE #{userAge}
    </select>
</mapper>

动态条件查询

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.tansor.mapper.UserInfoMapper">
    <resultMap id="userInfoResultMap" type="com.tansor.pojo.UserInfo">
        <id column="user_id" property="userId"/>
        <result column="user_name" property="userName"/>
        <result column="user_age" property="userAge"/>
    </resultMap>

    <select id="selectByCondition" resultMap="userInfoResultMap">
        select *
        from userinfo
        <where><!--利用where标签解决and问题-->
            <if test="userId!=null and userId!=''">
                user_id LIKE #{userId}
            </if>
            <if test="userName!=null and userName!=''">
                and user_name LIKE #{userName}
            </if>
            <if test="userAge!=null">
                and user_age LIKE #{userAge}
            </if>
        </where>

    </select>
</mapper>

单条件动态条件查询

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.tansor.mapper.UserInfoMapper">
    <resultMap id="userInfoResultMap" type="com.tansor.pojo.UserInfo">
        <id column="user_id" property="userId"/>
        <result column="user_name" property="userName"/>
        <result column="user_age" property="userAge"/>
    </resultMap>

    <select id="selectByConditionSingle" resultMap="userInfoResultMap">
        select *
        from userinfo
        <where><!--如果一个都没选，那就直接不要where-->
            <choose><!--只选择一个条件-->
                <when test="userId!=null and userId!=''">
                    user_id LIKE #{userId}
                </when>
                <when test="userName!=null and userName!=''">
                    user_name LIKE #{userName}
                </when>
                <when test="userAge!=null">
                    user_age LIKE #{userAge}
                </when>
            </choose>
        </where>
    </select>
</mapper>

MyBatis插入操作

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.tansor.mapper.UserInfoMapper">
    <resultMap id="userInfoResultMap" type="com.tansor.pojo.UserInfo">
        <id column="user_id" property="userId"/>
        <result column="user_name" property="userName"/>
        <result column="user_age" property="userAge"/>
    </resultMap>

    <insert id="add" useGeneratedKeys="true" keyProperty="user_id"><!--useGeneratedKeys和user_id设置使主键返回-->
        insert into userinfo (user_id,user_name,user_age)
        values(#{userId},#{userName},#{userAge})
    </insert>
</mapper>

MyBatis编辑数据

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.tansor.mapper.UserInfoMapper">
    <resultMap id="userInfoResultMap" type="com.tansor.pojo.UserInfo">
        <id column="user_id" property="userId"/>
        <result column="user_name" property="userName"/>
        <result column="user_age" property="userAge"/>
    </resultMap>

    <update id="update"><!--传一个UserInfo对象过来-->
        update userinfo
        set
        user_name = #{userName},
        user_age = #{userAge}
        where user_id = #{userId}
    </update>
</mapper>

动态更新数据

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.tansor.mapper.UserInfoMapper">
    <resultMap id="userInfoResultMap" type="com.tansor.pojo.UserInfo">
        <id column="user_id" property="userId"/>
        <result column="user_name" property="userName"/>
        <result column="user_age" property="userAge"/>
    </resultMap>

    <select id="selectByConditionSingle" resultMap="userInfoResultMap">
        select *
        from userinfo
        <where><!--如果一个都没选，那就直接不要where-->
            <choose><!--只选择一个条件-->
                <when test="userId!=null and userId!=''">
                    user_id LIKE #{userId}
                </when>
                <when test="userName!=null and userName!=''">
                    user_name LIKE #{userName}
                </when>
                <when test="userAge!=null">
                    user_age LIKE #{userAge}
                </when>
            </choose>
        </where>
    </select>

    <insert id="add" useGeneratedKeys="true" keyProperty="user_id">
        insert into userinfo (user_id,user_name,user_age)
        values(#{userId},#{userName},#{userAge})
    </insert>

    <update id="update">
        update userinfo
        <set>
            <if test="userName!=null and userName!=''">
                user_name = #{userName},
            </if>
            <if test="userAge!=null">
                user_age = #{userAge}
            </if>
        </set>
        where user_id = #{userId}
    </update>
</mapper>

MyBatis删除操作

单个删除

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.tansor.mapper.UserInfoMapper">
    <resultMap id="userInfoResultMap" type="com.tansor.pojo.UserInfo">
        <id column="user_id" property="userId"/>
        <result column="user_name" property="userName"/>
        <result column="user_age" property="userAge"/>
    </resultMap>

    <delete id="deleteById">
        delete from userinfo where user_id=#{userId};
    </delete>
</mapper>

批量删除

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.tansor.mapper.UserInfoMapper">
    <resultMap id="userInfoResultMap" type="com.tansor.pojo.UserInfo">
        <id column="user_id" property="userId"/>
        <result column="user_name" property="userName"/>
        <result column="user_age" property="userAge"/>
    </resultMap>

    <delete id="deleteById">
        delete from userinfo where user_id=#{userId};
    </delete>
    <delete id="deleteByIds">
        delete from userinfo where user_id
        in(
            <foreach collection="ids" item="userId" separator=","><!--传了一个数组过来，循环遍历数组，每项称为item，使用separator=","作为分隔符，ids是在mapper接口中指定过的数组名@Param("ids")-->
                #{userId}
            </foreach>
        )
    </delete>
</mapper>
package com.tansor.mapper;

import com.tansor.pojo.UserInfo;
import org.apache.ibatis.annotations.Param;

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

public interface UserInfoMapper {
    void deleteByIds(@Param("ids")String[] ids);
}
暂无评论

发送评论 编辑评论 


				

			

						

				

					

						

							

								
							

							
						

					

				

				

					

						

							

								
							

							
						

					

				

				

					

						

							

								
							

							
							
													

					

				

			

			
			

				

					
				

			

				

											

							
							
						

																					

							
							
						

										
					
											
						

	

		
|´・ω・)ノ
ヾ(≧∇≦*)ゝ
(☆ω☆)
（╯‵□′）╯︵┴─┴
￣﹃￣
(/ω＼)
∠( ᐛ 」∠)＿
(๑•̀ㅁ•́ฅ)
→_→
୧(๑•̀⌄•́๑)૭
٩(ˊᗜˋ*)و
(ノ°ο°)ノ
(´இ皿இ｀)
⌇●﹏●⌇
(ฅ´ω`ฅ)
(╯°A°)╯︵○○○
φ(￣∇￣o)
ヾ(´･ ･｀｡)ノ"
( ง ᵒ̌皿ᵒ̌)ง⁼³₌₃
(ó﹏ò｡)
Σ(っ °Д °;)っ
( ,,´･ω･)ﾉ"(´っω･｀｡)
╮(╯▽╰)╭
o(*////▽////*)q
＞﹏＜
( ๑´•ω•) "(ㆆᴗㆆ)
😂
😀
😅
😊
🙂
🙃
😌
😍
😘
😜
😝
😏
😒
🙄
😳
😡
😔
😫
😱
😭
💩
👻
🙌
🖕
👍
👫
👬
👭
🌚
🌝
🙈
💊
😶
🙏
🍦
🍉
😣
Source: github.com/k4yt3x/flowerhd
	

	

		
颜文字
Emoji
小恐龙
花!
	


									

			

			
			
		

	






上一篇
下一篇