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>