Mybatis

配置

mybatis.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>
    <properties resource="db.properties"> </properties>
    <settings>
        <setting name="logImpl" value="LOG4J"/>
    </settings>
    <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="mapper/GoodsMapper.xml"/>
        <mapper resource="mapper/UserMapper.xml"/>
    </mappers>
</configuration>

db.properties

DRIVER=com.mysql.cj.jdbc.Driver
URL=jdbc:mysql://localhost:3306/db_study?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC
USERNAME=root
PASSWORD=root

log4j.properties

log4j.rootLogger=DEBUG, stdout
log4j.logger.cn.xiaohu.mapper=TRACE

log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n

pom.xml dependencies

<dependencies>
    <dependency>
        <groupId>org.mybatis</groupId>
        <artifactId>mybatis</artifactId>
        <version>3.5.10</version>
    </dependency>
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <version>1.18.36</version>
    </dependency>
      <dependency>
          <groupId>org.junit.jupiter</groupId>
          <artifactId>junit-jupiter</artifactId>
          <version>RELEASE</version>
          <scope>test</scope>
      </dependency>
      <dependency>
          <groupId>com.mysql</groupId>
          <artifactId>mysql-connector-j</artifactId>
          <version>26.7.0</version>
      </dependency>

      <dependency>
          <groupId>org.apache.logging.log4j</groupId>
          <artifactId>log4j-core</artifactId>
          <version>2.26.1</version>
      </dependency>
      <dependency>
          <groupId>commons-logging</groupId>
          <artifactId>commons-logging</artifactId>
          <version>1.2</version>
      </dependency>
      <dependency>
          <groupId>log4j</groupId>
          <artifactId>log4j</artifactId>
          <version>1.2.17</version>
      </dependency>

  </dependencies>

Mapper.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">

<!--namespace:指定当前Mapper.xml文件对应的Mapper接口-->
<mapper namespace="cn.xiaohu.mapper.UserMapper">

    <!--    SQL片段  字段过多时可以减少重复-->
    <sql id="userCol">
        uid,uname,gender
    </sql>
    <!--    查询所有用户-->
    <select id="selectUsers" resultType="cn.xiaohu.domain.User">
        select 
        <include refid="userCol" />
        from tb_users
    </select>

    <!--    根据 id 查用户-->
    <select id="selectUserById" resultType="cn.xiaohu.domain.User">
        select
        <include refid="userCol"/>
        from tb_users where uid = #{uid}
    </select>

    <!--根据 ID 删除用户-->
    <delete id="deleteUserById">
        delete from tb_users where uid = #{uid}
    </delete>

    <!--新增用户-->
    <insert id="insertUser" parameterType="cn.xiaohu.domain.User">
        insert into tb_users(uname,gender) values(#{uname},#{gender})
    </insert>

    <!--根据 ID 修改用户-->
    <update id="updateUserById" parameterType="cn.xiaohu.domain.User">
        update tb_users set uname = #{uname},gender = #{gender} where uid = #{uid}
    </update>
</mapper>

Mapper 接口

package cn.xiaohu.mapper;

import cn.xiaohu.domain.User;
import org.apache.ibatis.annotations.Param;

import java.util.List;


public interface UserMapper {
    List<User> selectUsers();
    int deleteUserById(@Param("uid") Integer id);

    int updateUserById(User user);

    int insertUser(User user);

    User selectUserById(Integer id);


//    int insertUser(@Param(uname) String name,@Param(age) Integer age);
}

分页查询

pageNum 页码
pageSize 每页条数
limit((pageNum - 1 )* pageSize,截取条数pageSize)

分页插件

pom.xml

    <dependency>
          <groupId>com.github.pagehelper</groupId>
          <artifactId>pagehelper</artifactId>
          <version>6.1.1</version>
      </dependency>

mybatis.xml

    <plugins>
          <plugin interceptor="com.github.pagehelper.PageInterceptor"/>
    </plugins>

'#{}'和'${}'

防止 SQL 注入

{} :占位符形式传参 会在参数两边加"" 【不会改变 SQL 结构】 字符串比较 效率低

${} :直接拼接参数 不会在参数两边加""
模糊查询