MyBatis从入门到多表关联

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://www.springframework.org/schema/beans 
  http://www.springframework.org/schema/beans/spring-beans-4.3.xsd">
  <!-- 1配置数据源 -->
  <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <!--数据库驱动 -->
    <property name="driverClassName" value="com.mysql.jdbc.Driver" />
    <!--连接数据库的url -->
    <property name="url" value="jdbc:mysql://localhost:3306/spring" />
    <!--连接数据库的用户名 -->
    <property name="username" value="root" />
    <!--连接数据库的密码 -->
    <property name="password" value="root" />
  </bean>
  <!-- 2配置JDBC模板 -->
  <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
    <!-- 默认必须使用数据源 -->
    <property name="dataSource" ref="dataSource" />
  </bean>
  <!--定义id为accountDao的Bean-->
  <bean id="accountDao" class="com.itheima.jdbc.AccountDaoImpl">
    <!-- 将jdbcTemplate注入到accountDao实例中 -->
    <property name="jdbcTemplate" ref="jdbcTemplate" />
  </bean>
</beans>

import java.util.List;
public interface AccountDao {
  // 通过id查询
  public Account findAccountById(int id);
  // 查询所有账户
  public List<Account> findAllAccount();
}

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import java.util.List;
public class AccountDaoImpl implements AccountDao {
  // 声明JdbcTemplate属性及其setter方法
  private JdbcTemplate jdbcTemplate;
  public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
    this.jdbcTemplate = jdbcTemplate;
  }
  // 通过id查询账户数据信息
  @Override
  public Account findAccountById(int id) {
      //定义SQL语句
      String sql = "select * from account where id = ?";
      // 创建一个新的BeanPropertyRowMapper对象
      RowMapper<Account> rowMapper = 
  new BeanPropertyRowMapper<Account>(Account.class);
      // 将id绑定到SQL语句中，并通过RowMapper返回一个Object类型的单行记录
      return this.jdbcTemplate.queryForObject(sql, rowMapper, id);
  }
  // 查询所有账户信息
  @Override
  public List<Account> findAllAccount() {
      // 定义SQL语句
      String sql = "select * from account";
      // 创建一个新的BeanPropertyRowMapper对象
      RowMapper<Account> rowMapper = 
  new BeanPropertyRowMapper<Account>(Account.class);
      // 执行静态的SQL查询，并通过RowMapper返回结果
      return this.jdbcTemplate.query(sql, rowMapper);
  }
}

import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import java.util.List;
public class JdbcTemplateTest {
    @Test
    public void findAccountByIdTest() {
        // 加载配置文件
        ApplicationContext applicationContext =
                new ClassPathXmlApplicationContext("applicationContext.xml");
        // 获取AccountDao实例
        AccountDao accountDao =
                (AccountDao) applicationContext.getBean("accountDao");
        // 执行findAccountById()方法
        Account account = accountDao.findAccountById(1);
        System.out.println(account);
    }
    @Test
    public void findAllAccountTest() {
        // 加载配置文件
        ApplicationContext applicationContext =
                new ClassPathXmlApplicationContext("applicationContext.xml");
        // 获取AccountDao实例
        AccountDao accountDao =
                (AccountDao) applicationContext.getBean("accountDao");
        // 执行findAllAccount()方法,获取Account对象的集合
        List<Account> account = accountDao.findAllAccount();
        // 循环输出集合中的对象
        for (Account act : account) {
            System.out.println(act);
        }
    }
}

public interface AccountDao {
  // 转账
  public void transfer(String outUser,String inUser,Double money);
}

public void transfer(String outUser, String inUser, Double money) {
      // 收款时，收款用户的余额=现有余额+所汇金额
      this.jdbcTemplate.update("update account set balance = balance +? "
              + "where username = ?",money, inUser);
      // 模拟系统运行时的突发性问题
      int i = 1/0;
      // 汇款时，汇款用户的余额=现有余额-所汇金额
      this.jdbcTemplate.update("update account set balance = balance-? "
              + "where username = ?",money, outUser);
  }

package com.lydms.jdbc;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import 
org.springframework.context.support.ClassPathXmlApplicationContext;
//测试类
public class TransactionTest {
  @Test
  public void xmlTest(){
    ApplicationContext applicationContext = 
       new ClassPathXmlApplicationContext("applicationContext.xml");
    // 获取AccountDao实例
    AccountDao accountDao = 
            (AccountDao)applicationContext.getBean("accountDao");
    // 调用实例中的转账方法
    accountDao.transfer("Jack", "Rose", 100.0);
      // 输出提示信息
      System.out.println("转账成功！");
  }
}

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
    xmlns:aop="http://www.springframework.org/schema/aop"
    xmlns:tx="http://www.springframework.org/schema/tx" 
    xmlns:context="http://www.springframework.org/schema/context"
    xsi:schemaLocation="http://www.springframework.org/schema/beans 
    http://www.springframework.org/schema/beans/spring-beans-4.3.xsd
    http://www.springframework.org/schema/tx 
    http://www.springframework.org/schema/tx/spring-tx-4.3.xsd
    http://www.springframework.org/schema/context 
    http://www.springframework.org/schema/context/spring-context-4.3.xsd
    http://www.springframework.org/schema/aop 
    http://www.springframework.org/schema/aop/spring-aop-4.3.xsd">
<!-- 1.配置数据源 -->
    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <!--数据库驱动 -->
    <property name="driverClassName" value="com.mysql.jdbc.Driver" />
    <!--连接数据库的url -->
    <property name="url" value="jdbc:mysql://localhost/spring" />
    <!--连接数据库的用户名 -->
    <property name="username" value="root" />
    <!--连接数据库的密码 -->
    <property name="password" value="root" />
   </bean>
<!-- 2.配置JDBC模板 -->
   <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
     <!-- 默认必须使用数据源 -->
     <property name="dataSource" ref="dataSource" />
   </bean>
<!--3.定义id为accountDao的Bean -->
   <bean id="accountDao" class="com.itheima.jdbc.AccountDaoImpl">
     <!-- 将jdbcTemplate注入到AccountDao实例中 -->
     <property name="jdbcTemplate" ref="jdbcTemplate" />
   </bean>  
<!-- 4.事务管理器，依赖于数据源 -->
   <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
    <property name="dataSource" ref="dataSource" />
   </bean>  
<!-- 5.编写通知：对事务进行增强(通知),需要编写对切入点和具体执行事务细节 -->
   <tx:advice id="txAdvice" transaction-manager="transactionManager">
    <tx:attributes>
      <!-- name：*表示任意方法名称 -->
      <tx:method name="*" propagation="REQUIRED" isolation="DEFAULT" read-only="false" />
    </tx:attributes>
  </tx:advice>
<!-- 6.编写aop，让spring自动对目标生成代理，需要使用AspectJ的表达式 -->
  <aop:config>
    <!-- 切入点 -->
    <aop:pointcut expression="execution(* com.lydms.jdbc.*.*(..))" id="txPointCut" />
    <!-- 切面：将切入点与通知整合 -->
    <aop:advisor advice-ref="txAdvice" pointcut-ref="txPointCut" />
  </aop:config>
</beans>

<!-- 5.注册事务管理器驱动 -->
  <tx:annotation-driven transaction-manager="transactionManager"/>

<!-- 5.注册事务管理器驱动 -->
  <tx:annotation-driven transaction-manager="transactionManager"/>

@Transactional(propagation = Propagation.REQUIRED, isolation = Isolation.DEFAULT, readOnly = false)
  public void transfer(String outUser, String inUser, Double money) {
      // 收款时，收款用户的余额=现有余额+所汇金额
      this.jdbcTemplate.update("update account set balance = balance +? where username = ?",money, inUser);
      // 模拟系统运行时的突发性问题
      int i = 1/0;
      // 汇款时，汇款用户的余额=现有余额-所汇金额
      this.jdbcTemplate.update("update account set balance = balance-? where username = ?",money, outUser);
  }

<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
    "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!--1.配置环境 ，默认的环境id为mysql-->
    <environments default="mysql">
        <!--1.2.配置id为mysql的数据库环境 -->
        <environment id="mysql">
            <!-- 使用JDBC的事务管理 -->
            <transactionManager type="JDBC" />
            <!--数据库连接池 -->
            <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="root" />
            </dataSource>
        </environment>
    </environments>
<!--2.配置Mapper的位置 -->
    <mappers>
    <mapper resource="com/itheima/mapper/CustomerMapper.xml" />
    </mappers>
</configuration>

public class Customer {
    private Integer id;
    private String username;
    private String jobs;
    private String phone;
}

<?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 namespace="com.lydms.mapper.CustomerMapper">
    <!--根据客户编号获取客户信息 -->
  <select id="findCustomerById" parameterType="Integer"
    resultType="com.lydms.po.Customer">
    select * from t_customer where id = #{id}
  </select>
  <!--根据客户名模糊查询客户信息列表-->
  <select id="findCustomerByName" parameterType="String" resultType="com.lydms.po.Customer">
      <!-- select * from t_customer where username like '%${value}%' -->
      select * from t_customer where username like concat('%',#{value},'%')
  </select>
  <!-- 添加客户信息 -->
  <insert id="addCustomer" parameterType="com.lydms.po.Customer">
      insert into t_customer(username,jobs,phone)
      values(#{username},#{jobs},#{phone})
  </insert>
  <!-- 更新客户信息 -->
  <update id="updateCustomer" parameterType="com.lydms.po.Customer">
      update t_customer set
      username=#{username},jobs=#{jobs},phone=#{phone}
      where id=#{id}
  </update>
  <!-- 删除客户信息 -->
  <delete id="deleteCustomer" parameterType="Integer">
      delete from t_customer where id=#{id}
  </delete>
</mapper>

package com.itheima.test;
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 org.junit.Test;
import com.itheima.po.Customer;
/**
 * ���ų��������
 */
public class MybatisTest {
  /**
   * 删除客户
   */
  @Test
  public void deleteCustomerTest() throws Exception{    
      // 1、读取配置文件
      String resource = "mybatis-config.xml";
      InputStream inputStream = Resources.getResourceAsStream(resource);
      // 2、根据配置文件构建 SqlSessionFactory
      SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
      // 3、通过SqlSessionFactory 创建 SqlSession
      SqlSession sqlSession = sqlSessionFactory.openSession();
      // 4、SqlSession执行删除操作
      // 4.1 执行SqlSession的删除方法，返回的是SQL语句影响的行数
      int rows = sqlSession.delete("com.lydms.mapper.CustomerMapper.deleteCustomer", 4);
      // 4.2 通过返回结果判断删除操作是否执行成功
      if(rows > 0){
          System.out.println("您成功删除了"+rows+"条数据");
      }else{
          System.out.println("执行删除操作失败");
      }
      // 4.3提交事务
      sqlSession.commit();
      // 5、关闭资源
      sqlSession.close();
  }
  /**
   * 根据客户编号查询客户信息
   */
  @Test
  public void findCustomerByIdTest() throws Exception {
    // 1、读取配置文件
    String resource = "mybatis-config.xml";
    InputStream inputStream = 
                     Resources.getResourceAsStream(resource);
    // 2、根据配置文件构建 SqlSessionFactory
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    // 3、通过SqlSessionFactory 创建 SqlSession
    SqlSession sqlSession = sqlSessionFactory.openSession();
    // 4.SqlSession执行
    Customer customer = sqlSession.selectOne("com.itheima.mapper"
          + ".CustomerMapper.findCustomerById", 1);
    // 
    System.out.println(customer.toString());
    // 5、关闭资源
    sqlSession.close();
  }
  /**
   * 根据用户名称来模糊查询用户信息列表
   */
  @Test
  public void findCustomerByNameTest() throws Exception{  
      // 1、读取配置文件
      String resource = "mybatis-config.xml";
      InputStream inputStream = Resources.getResourceAsStream(resource);
      // 2、根据配置文件构建 SqlSessionFactory
      SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
      // 3、通过SqlSessionFactory 创建 SqlSession
      SqlSession sqlSession = sqlSessionFactory.openSession();
      // 4、SqlSession执行映射文件汇总定义的SQL，并返回映射结果
      List<Customer> customers = sqlSession.selectList("com.itheima.mapper"
          + ".CustomerMapper.findCustomerByName", "j");
      for (Customer customer : customers) {
          //打印输出结果集
          System.out.println(customer);
      }
      // 5、关闭资源
      sqlSession.close();
  }
  /**
   * 添加客户
   */
  @Test
  public void addCustomerTest() throws Exception{   
      // 1、读取配置文件
      String resource = "mybatis-config.xml";
      InputStream inputStream = Resources.getResourceAsStream(resource);
      // 2、根据配置文件构建 SqlSessionFactory
      SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
      // 3、通过SqlSessionFactory 创建 SqlSession
      SqlSession sqlSession = sqlSessionFactory.openSession();
      // 4、SqlSession执行添加操作
      // 4.1创建Customer对象，并向对象中添加数据
      Customer customer = new Customer();
      customer.setUsername("rose");
      customer.setJobs("student");
      customer.setPhone("13333533092");
      // 4.2 执行SqlSession的插入方法，返回SQL影响的行数
    int rows = sqlSession.insert("com.itheima.mapper"
          + ".CustomerMapper.addCustomer", customer);
      // 4.3 通过返回结果判断插入操作是否执行成功
      if(rows > 0){
          System.out.println("您成功插入了"+rows+"条数据);
      }else{
          System.out.println("执行插入操作失败");
      }
      // 4. 提交事务
      sqlSession.commit();
      // 5、关闭资源
      sqlSession.close();
  }
  /**
   * 更新客户
   */
  @Test
  public void updateCustomerTest() throws Exception{    
      // 1、读取配置文件
      String resource = "mybatis-config.xml";
      InputStream inputStream = Resources.getResourceAsStream(resource);
      // 2、根据配置文件构建 SqlSessionFactory
      SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
      // 3、通过SqlSessionFactory 创建 SqlSession
      SqlSession sqlSession = sqlSessionFactory.openSession();
      // sqlSession执行更新操作
      // 4.1 创建Customer对象，对对象中的数据进行模拟更新
      Customer customer = new Customer();
      customer.setId(4);
      customer.setUsername("rose");
      customer.setJobs("programmer");
      customer.setPhone("13311111111");
      // 4.2 执行Sqlsession的更新方法，返回的是SQL语句影响的行数
      int rows = sqlSession.update("com.itheima.mapper"
              + ".CustomerMapper.updateCustomer", customer);
      // 4.3 通过返回结果判断更新操作是否执行成功
      if(rows > 0){
          System.out.println("您成功更新了"+rows+"条数据");
      }else{
          System.out.println("执行更新操作失败");
      }
      // 4.4 提交事务
      sqlSession.commit();
      // 5、关闭资源
      sqlSession.close();
  }
}

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/mybatis
jdbc.username=root
jdbc.password=root

<properties resource=db.properties>

<dataSource type="POOLED">
    <!-- 数据库驱动 -->
  <propertry name="driver" value="${jdbc.driver}"/>
    <!-- 连接数据的url -->
  <propertry name="url" value="${jdbc.url}"/>
    <!-- 连接数据库的用户名 -->
  <propertry name="username" value="${jdbc.username}"/>
    <!-- 连接数据库的密码 -->
  <propertry name="password" value="${jdbc.password}"/>
<dataSource/>

<settings>
    <!--  开启驼峰匹配：-->
    <setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>

<settings>
    <!--  开启延迟加载的开关：-->
    <setting name="lazyLoadingEnabled" value="true"/>
      <!--  将积极加载改为延迟加载，即按需加载：-->
    <setting name="aggressiveLazyLoading" value="true"/>
</settings>

<!-- 配置别名 -->
    <typeAliases>
        <!-- typeAlias:用来配置别名，方便映射文件使用，type:类的全限定类名,alias:别名 -->
        <typeAlias type="cn.lydms.pojo.User" alias="User"/>
    </typeAliases>

<typeAliases>
      <!-- 使用自动扫描包来定义别名 -->
        <package name="com.lydms.pojo"/>
    </typeAliases>

@Alias(value = "user")
public class User {
    private String name;
    private String address;
}

<typeHandlers>
        <!-- 1.注册一个类的类型处理 -->
        <typeHandler handler="com.lydms.domain.testFile"/>
        <!-- 2.注册一个包中所有类的类型处理 -->
        <typeHandler handler="com.lydms.domain"/>
    </typeHandlers>

<!-- 1. 配置环境：可以配置多个环境，default：配置某一个环境的唯一标识，表示默认使用哪个环境 -->
    <environments default="development">
        <!-- 1.1. 配置环境,id:环境的唯一标识 -->
        <environment id="development">
            <!-- 1.1.1. 事务管理器，type:使用jdbc的事务管理器 -->
            <transactionManager type="JDBC"/>
            <!-- 1.1.2 数据源，type:池类型的数据源 -->
            <dataSource type="POOLED">
                <!-- 1.1.2.1 配置连接信息 -->
                <property name="driver" value="${jdbc.driverClass}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>

<mappers>
    <mapper resource="com/lydms/mapper/UserMapper.xml">
</mappers>

<mappers>
    <mapper url="file://D://com/lydms/mapper/UserMapper.xml">
</mappers>

<mappers>
    <mapper class="com.lydms.mapper.UserMapper">
</mappers>

<mappers>
    <package name="com.lydms.mapper">
</mappers>

<select id="selectPerson" parameterType="int" resultType="hashmap">
  SELECT * FROM PERSON WHERE ID = #{id}
</select>

<insert
  id="insertAuthor"
  parameterType="domain.blog.Author"
  flushCache="true"
  statementType="PREPARED"
  keyProperty=""
  keyColumn=""
  useGeneratedKeys=""
  timeout="20">

<insert id="insertAuthor" useGeneratedKeys="true" keyProperty="id">
  insert into author (username,password,email,bio)
  values (#{username},#{password},#{email},#{bio})
</insert>

<insert id="insertAuthor">
  <selectKey keyProperty="id" resultType="Integer" order="BEFORE">
    select if(max(id)is null,1,max(id)+1)as newId from author
  </selectKey>
  insert into author
    (id, username, password, email,bio)
  values
    (#{id}, #{username}, #{password}, #{email}, #{bio})
</insert>

<selectKey
  keyProperty="id"
  resultType="int"
  order="BEFORE"
  statementType="PREPARED">

<update
  id="updateAuthor"
  parameterType="domain.blog.Author"
  flushCache="true"
  statementType="PREPARED"
  timeout="20">
<delete
  id="deleteAuthor"
  parameterType="domain.blog.Author"
  flushCache="true"
  statementType="PREPARED"
  timeout="20">

<update id="updateAuthor">
  update Author 
  set username = #{username}, password = #{password}
  where id = #{id}
</update>
<delete id="deleteAuthor">
  delete from Author where id = #{id}
</delete>

<sql id="customer">id,username,age<sql/>

<select id="selectUsers" resultType="map">
  select
    <include refid="customer">
  from some_table
</select>

<!-- 定义表前缀 -->
<sql id="tablename">
  ${prefix}customer
</sql>
<!-- 定义要查询的表 -->
<sql id="someinclude">
  from
    <include refid="${include_target}"/>
</sql>
<!-- 定义查询的列 -->
<sql id="customerColumn">
  id,username,phone
</sql>
<!-- 定义要查询的表 -->
<select id="select" resultType="map">
  select
  <include refid="customerColumn">
  <include refid="someinclude">
    <property name="prefix" value="t_"/>
    <property name="include_target" value="tablename"/>
  </include>
    where id = #{id}
</select>

select id,username,phone from t_customer where id = ?

<!-- resultMap的元素结构-->
<resultMap id="" type="">
  <constructor>  <!-- 类在实例化时，用来注入结果到构造方法中-->
      <idArg/>  <!-- ID参数；标记结果作为ID -->
      <arg/>    <!-- 注入到构造方法的一个普通结果 -->
  <constructor/>
  <id />    <!-- 用于表示哪个列是主键 -->
  <result />    <!-- 注入到字段或JavaBean属性的普通结果 -->
  <association property="" />   <!-- 用于一对一关联 -->
  <collection property="coAuthor" />   <!-- 用于一对多关联 -->
  <discriminator javaType="" >   <!-- 使用结果值来决定使用哪个结果映射 -->
    <case value="" />   <!-- 基于某些值的结果映射 -->
  <discriminator/>
</resultMap>

<resultMap id="resultMap" type="com.lydms.mapper.UserMapper">
  <id property="id" column="t_id"/>
  <result property="name" column="t_name"/>
  <result property="age" column="t_age"/>
</resultMap>
<select id="findAll" resultMap="resultMap">
  SELECT * FROM t_user
</select>

<select id="findCustomerByName" parameterType="com.lydms.po.Customer" resultType="com.lydms.po.Customer">
    select * from t_customer where 1=1
    <if test="username! = null and username ! =''">
        and username like concat('%',#{username},'%')
    </if>
    <if test="jobds ! = null and jobs ! =''">
        and jobs=#{jobs}
    </if>
</select>

public class CompanyCustomerVo{
  private String username;
  private String jobs;
}

<select id="findCustomerByName" parameterType="com.lydms.po.Customer" resultType="com.lydms.po.Customer">
    select * from t_customer where 1=1
    <choose>
        <when test="username !=null and username !=''">
            and username like concat('%',#{username},'%')
        </when>
        <when test="jobs !=null and jobs !=''">
            and jobs = #{jobs}
        </when>
        <otherwise>
            and phone is not null
        </otherwise>
    </choose>
</select>

<select id="findCustomerByName" parameterType="com.lydms.po.Customer" resultType="com.lydms.po.Customer">
    select * from t_customer where 1=1
    <if test="username! = null and username ! =''">
        and username like concat('%',#{username},'%')
    </if>
</select>

select * from t_customer where and username like concat('%',?,'%');

<select id="findCustomerByName" parameterType="com.lydms.po.Customer" resultType="com.lydms.po.Customer">
    select * from t_customer
    <where>
        <if test="username! = null and username ! =''">
            and username like concat('%',#{username},'%')
        </if>
    </where>
</select>

<select id="findCustomerByName" parameterType="com.lydms.po.Customer" resultType="com.lydms.po.Customer">
    select * from t_customer
    <trim prefix="where" prefixOverrides="and">
        <if test="username! = null and username ! =''">
            and username like concat('%',#{username},'%')
        </if>
    </trim>
</select>

<update id="updateCustomer" parameterType="com.lydms.po.customer">
    update t_customer
    <set>
        <if test="username! = null and username ! =''">
            username=#{username},
        </if>
        <if test="jobds ! = null and jobs ! =''">
            jobs=#{jobs},
        </if>
    </set>
    where id =#{id}
</update>

<select id="findCustomerByIds" parameterType="list" resultType="com.lydms.po.Cusotmer">
    select * from t_customer where id in
    <foreach item="id" index="index" collection="list"
             open="(" separator="," close=")">
        #{id}
    </foreach>
</select>

List<Integer> ids =new ArrayList<>();
  ids.add(1);
  ids.add(3);

<select id="findCustomerByIds" parameterType="list" resultType="com.lydms.po.Cusotmer">
  <!-- 也可以使用_parameter.getUsername()进行数据获取 -->
    <bind name="pattern_username" value="'%'+ username +'%'"/>
    select * from t_customer
    where
    username like #{pattern_username}
</select>

Customer customer = new Customer();
customer.setUsername("j");//进行模糊查询使用

<result column="dealer_id" property="dealerId" jdbcType="VARCHAR"/>

<!--方式一：嵌套查询-->
    <association property="card" column="card_id" javaType="com.lydms.po.Customer"
                 select="com.lydms.mapper.IdCardMapper"/>
    <!--方式二：嵌套结果-->
    <association property="card" javaType="com.lydms.po.Customer">
        <id column="card_id" property="id"/>
        <result column="code" property="code"/>
        <result column="dealer_id" property="dealerId" jdbcType="VARCHAR"/>
    </association>

<select id="findCodeById" parameterType="integer" resultType="IdCard">
  select * from tb_idcard where id=#{id}
</select>

<select id="findPersonById" parameterType="integer" resultMap="IdCardWithPersonResult">
  select * from tb_person where id =#{id}
</select>
<resultMap id="IdCardWithPersonResult" type="Person">
    <id property="id" column="id"/>
    <result property="name" column="name"/>
    <result property="age" column="age"/>
    <result property="sex" column="sex"/>
    <!--一对一：association使用select属性，引入另外一条SQL语句-->
    <association property="card" column="card_id" javaType="IdCard"
                 select="com.lydms.mapper.IdcardMapper.findCodeById"/>
</resultMap>

<select id="findPersonById" parameterType="integer" resultMap="IdCardWithPersonResult">
      select 
        pl *, idcard.code 
      from 
        tb_person p, tb_idcard idcard 
      where 
        p.card_id = idcard.id 
        and p.id =#{id}
  </select>
    <resultMap id="IdCardWithPersonResult" type="Person">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="age" column="age"/>
        <result property="sex" column="sex"/>
        <!--一对多：association 进行结果映射-->
        <association property="card" javaType="IdCard">
            <id property="id" column="id"/>
            <result property="code" column="code"/>
        </association>
    </resultMap>

<resultMap id="f" type="integer">
    <!--方式一：嵌套查询-->
    <collection property="orderList" column="id" ofType="com.lydms.po.Orders"
                select="com.lydms.mapper.OrdersMapper.selectOrders"/>
    <!--方式二：嵌套结果-->
    <collection property="orderList" ofType="com.lydms.po.Orders">
        <id property="id" column="id"/>
        <result property="number" column="number"/>
    </collection>
</resultMap>

<select id="findUserWithOrders" parameterType="integer" resultMap="UserWithOrderResult">
  select 
    u.*,o.id as orders_id, o.number 
  from 
    tb_user u, tb_orders o 
  where 
    u.id = o.user_id 
    and u.id =#{id}
</select>
<resultMap id="UserWithOrderResult" type="User">
    <id property="id" column="id"/>
    <result property="username" column="username"/>
    <result property="address" column="address"/>
    <!--一对多关联映射：collection-->
    <!--ofType表示属性集合中元素的类型，List<Orders>属性即Orders类-->
    <collection property="ordersList" ofType="Orders">
        <id property="id" column="id"/>
        <result property="number" column="number"/>
    </collection>
</resultMap>

import lombok.Data;
import java.util.List;
@Data
public class Product {
    //    商品id
    private Integer id;
    //    商品名称
    private String name;
    //    商品单价
    private Double price;
    //    与订单的关联属性
    private List<Orders> orders;
}

<select id="findOrderswithProduct" parameterType="integer" resultMap="OrdersWithProductResult">
  select * from  tb_orders where id = #{id}
</select>
<!--多对多嵌套查询：通过执行另外一条SQL映射语句来返回预期的特殊类型-->
<resultMap id="OrdersWithProductResult" type="Orders">
    <id property="id" column="id"/>
    <result property="number" column="number"/>
    <collection property="productList" column="id" ofType="Product"
                select="com.lydms.mapper.ProductMapper.findProductById">
    </collection>
</resultMap>

<select id="findProductById" parameterType="integer" resultType="Product">
  select * from tb_product where id IN(
    select product_id from tb_ordersitem where orders_id = #{id}
  )
</select>

<!--多对多嵌套结果查询：查询某订单及其关联的商品详情-->
<select id="findOrderWithProduct" parameterType="integer" resultMap="OrdersWithProductResult">
  selet 0.*, p.id AS pid, p.name, p.prices
  FROM
    tb_orders p, tb_product p, tb_ordersitem oi
  WHERE
    oi.orders_id = o.id
  AND oi.product_id = p.id
  AND o.id = #{id}
</select>
<!--自定义手动映射类型-->
<resultMap id="OrdersWithProductResult" type="Orders">
  <id property="id" column="id"/>
  <result property="number" column="number"/>
  <!--多对多关联映射：collection-->
  <collection property="productList" ofType="Product">
    <id property="name" column="name"/>
    <result property="name" column="name"/>
    <result property="price" column="price"/>
  </collection>
</resultMap>