官方文档，官方文档已经写的非常完备，这里对官网内容进行讲解，演练，演练的样例数据使用《MySQL必知必会》上面的。

使用方式

Maven依赖

  1. <dependency>
  2.   <groupId>mysql</groupId>
  3.   <artifactId>mysql-connector-java</artifactId>
  4.   <version>5.1.49</version>
  5. </dependency>
  6. <dependency>
  7.   <groupId>org.mybatis</groupId>
  8.   <artifactId>mybatis</artifactId>
  9.   <version>3.5.5</version>
  10. </dependency>

添加MyBatis核心配置，一般命名为mybatis-config.xml,按照官方的命名方式。

  1. <?xml version="1.0" encoding="UTF-8" ?>
  2. <!--        注意 XML 头部的声明，它用来验证 XML 文档的正确性。-->
  3. <!DOCTYPE configuration
  4.         PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
  5.         "http://mybatis.org/dtd/mybatis-3-config.dtd">
  6. <configuration>
  8.     <!-- 环境 -->
  9.     <environments default="development">
  10.         <!-- 开发环境（调试阶段） -->
  12.         <environment id="development">
  13.             <!-- 采用JDBC的事务管理方法 -->
  14.             <transactionManager type="jdbc" />
  16.             <!-- POOLED代表采取连接池的方式管理连接 -->
  17.             <dataSource type="POOLED">
  18.                 <property name="driver" value="com.mysql.jdbc.Driver"/>
  19.                 <property name="url" value="jdbc:mysql://localhost:3306/customer_test"/>
  20.                 <property name="username" value="root"/>
  21.                 <property name="password" value="root"/>
  22.             </dataSource>
  23.         </environment>
  26.         <!-- 生产环境（发布阶段） -->
  27.         <environment id="production">
  28.             <!-- 采用JDBC的事务管理方法 -->
  29.             <transactionManager type="JDBC" />
  31.             <!-- POOLED代表采取连接池的方式管理连接 -->
  32.             <dataSource type="POOLED">
  33.                 <property name="driver" value="com.mysql.jdbc.Driver"/>
  34.                 <property name="url" value="jdbc:mysql://localhost:3306/customer_test"/>
  35.                 <property name="username" value="root"/>
  36.                 <property name="password" value="root"/>
  37.             </dataSource>
  38.         </environment>
  39.     </environments>
  41. </configuration>

集成druid连接池

有很多方式集成druid，其中一个比较简单的方式只需三步

  • 第一步：添加依赖

    1. <dependency>
    2. <groupId>com.alibaba</groupId>
    3. <artifactId>druid</artifactId>
    4. <version>1.1.22</version>
    5. </dependency>

  • 第二步：新建一个类继承UnpooledDataSourceFactory 

    1. public class DruidDataSourceFactory extends UnpooledDataSourceFactory {
    2.   public DruidDataSourceFactory() {
    3.       this.dataSource =  new DruidDataSource();
    4.   }
    5. }

  • 第三步：在MyBatis核心配置文件中修改环境配置

    1. <environment id="development">
    2. <!-- 采用JDBC的事务管理方法 -->
    3. <transactionManager type="jdbc" />
    5. <!-- com.lff.thirdPart.DruidDataSourceFactory为继承UnpooledDataSourceFactory的类 -->
    6. <dataSource type="com.lff.thirdPart.DruidDataSourceFactory">
    7.   <property name="driverClass" value="com.mysql.jdbc.Driver"/>
    8.   <property name="url" value="jdbc:mysql://localhost:3306/customer_test"/>
    9.   <property name="username" value="root"/>
    10.   <property name="password" value="root"/>
    11.   <property name="initialSize" value="5"/>
    12.   <property name="maxActive" value="10"/>
    13.   <property name="maxWait" value="5000"/>
    14. </dataSource>
    15. </environment>

    映射文件

    在MyBatis里面怎么写SQL呢？比如增、删、改、查。还有在查询数据库的时候，需要把查询的结果转为Java bean对象的，应该怎么转呢？这时就需要映射文件了。
    映射文件也是一个XML文件，MyBatis也规定了其格式应该怎么写
    我们通常在项目的resources目录下创建一个mappers文件夹，在里面写映射文件。比如要查询所有的顾客数据mappers/customers.xml
    官方文档有详细的解释

    1. <?xml version="1.0" encoding="UTF-8" ?>
    2. <!DOCTYPE mapper
    3.       PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
    4.       "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
    5. <mapper namespace="customers">
    6.   <select id="allCustomers" resultType="map">
    7.       select * from customers
    8.   </select>
    9. </mapper>

    namespace为命名空间，因为命名空间下面可能会有很多个语句，起到隔离的目的。
    select的查询标签有个id，可以随便起一个，也称为语句名称。
    resultType为返回数据的类型，这里返回map类型。

MyBatis怎么知道映射文件的存在呢？需要在MyBatis核心配置文件中加一下

  1.     <!-- 映射文件 -->
  2.     <mappers>
  3.         <mapper resource="mappers/customers.xml" />
  4.     </mappers>

使用方式，官方文档对如何使用有超详细的介绍

  1. import com.lff.Customers;
  2. import org.apache.ibatis.io.Resources;
  3. import org.apache.ibatis.session.SqlSession;
  4. import org.apache.ibatis.session.SqlSessionFactory;
  5. import org.apache.ibatis.session.SqlSessionFactoryBuilder;
  6. import org.junit.Test;
  8. import java.io.IOException;
  9. import java.io.Reader;
  10. import java.util.List;
  11. import java.util.Map;
  13. public class customersTest {
  14.     @Test
  15.     public void getCustomerList() throws IOException {
  17.         try (Reader reader = Resources.getResourceAsReader("mybatis-config.xml")) { //加载MyBatis核心配置文件
  18.             SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder(); //使用SqlSessionFactoryBuilder创建工厂对象
  19.             SqlSessionFactory factory = builder.build(reader);       
  20.             SqlSession session = factory.openSession(); //使用工厂对象生产SqlSession对象
  21.             //customers.allCustomers为命名空间和select标签的id
  22.             List<Map> customersList = session.selectList("customers.allCustomers");//使用SqlSession对象做增、删、改、查
  23.             System.out.println(customersList);
  24.         }
  25.     }
  26. }

查询结果

  1. [
  2.  {cust_country=USA, cust_email=sales@villagetoys.com, cust_address=200 Maple Lane, cust_name=Village Toys, cust_state=MI, cust_contact=John Smith, cust_id=1000000001, cust_city=Detroit, cust_zip=44444}, 
  3.  {cust_country=USA, cust_address=333 South Lake Drive, cust_name=Kids Place, cust_state=OH, cust_contact=Michelle Green, cust_id=1000000002, cust_city=Columbus, cust_zip=43333},
  4.  {cust_country=USA, cust_email=jjones@fun4all.com, cust_address=1 Sunny Place, cust_name=Fun4All, cust_state=IN, cust_contact=Jim Jones, cust_id=1000000003, cust_city=Muncie, cust_zip=42222}, 
  5.  {cust_country=USA, cust_email=dstephens@fun4all.com, cust_address=829 Riverside Drive, cust_name=Fun4All, cust_state=AZ, cust_contact=Denise L. Stephens, cust_id=1000000004, cust_city=Phoenix, cust_zip=88888}, 
  6.  {cust_country=USA, cust_address=4545 53rd Street, cust_name=The Toy Store, cust_state=IL, cust_contact=Kim Howard, cust_id=1000000005, cust_city=Chicago, cust_zip=54545}
  7. ]

现在结果已经查出来了，怎么把查询的结果映射到JavaBean 呢？

注意我们的表结构是这样的？
image.png
字段都是采用下划线标识的，这是数据库设计表的规范，但是我们bean对象里面的属性都是采用驼峰命名的，怎么才能对应其来呢？

方法一

MyBatis已经为我们处理了，只要在MyBatis的核心配置文件中显示的配置一下就好了

  1. <settings>
  2.       <!-- 数据库表字段：my_first_name 映射到JavaBean 属性：myFirstName -->
  3.     <setting name="mapUnderscoreToCamelCase" value="true"/>
  4. </settings>

是否开启驼峰命名自动映射，即从经典数据库列名 my_first_name 映射到经典 Java 属性名 myFirstName。默认为false
更新我们的映射文件,resultType改为我们的JavaBean对象

  1. <?xml version="1.0" encoding="UTF-8" ?>
  2. <!DOCTYPE mapper
  3.         PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  4.         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  5. <mapper namespace="customers">
  7.     <select id="allCustomers" resultType="com.lff.Customers">
  8.         select * from customers
  9.     </select>
  10. </mapper>

外界使用

  1. import com.lff.Customers;
  2. import org.apache.ibatis.io.Resources;
  3. import org.apache.ibatis.session.SqlSession;
  4. import org.apache.ibatis.session.SqlSessionFactory;
  5. import org.apache.ibatis.session.SqlSessionFactoryBuilder;
  6. import org.junit.Test;
  8. import java.io.IOException;
  9. import java.io.Reader;
  10. import java.util.List;
  12. public class customersTest {
  13.     @Test
  14.     public void getCustomerList() throws IOException {
  16.         try (Reader reader = Resources.getResourceAsReader("mybatis-config.xml")) { //加载MyBatis核心配置文件
  17.             SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder(); //使用SqlSessionFactoryBuilder创建工厂对象
  18.             SqlSessionFactory factory = builder.build(reader);       
  19.             SqlSession session = factory.openSession(); //使用工厂对象生产SqlSession对象
  20.             List<Customers> customersList = session.selectList("customers.allCustomers");//使用SqlSession对象做增、删、改、查
  22.             for (Customers customers : customersList) {
  23.                  System.out.println(customers.getCustName());
  24.             }
  25.         }
  27.     }
  28. }

打印结果

  1. Village Toys
  2. Kids Place
  3. Fun4All
  4. Fun4All
  5. The Toy Store

方法二

如果不想在核心配置文件里面配置，我们也可以在映射文件里面配置

  1. <?xml version="1.0" encoding="UTF-8" ?>
  2. <!DOCTYPE mapper
  3.         PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  4.         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  8. <mapper namespace="customers">
  10. <!--    resultMap的值是下面resultMap标签的id-->
  11.     <select id="allCustomers" resultMap="customers">
  12.         SELECT * FROM customers
  13.     </select>
  14. <!--    type为我们要转化的Bean类-->
  15.     <resultMap id="customers" type="com.lff.Customers">
  16. <!--        property为Bean类中的属性名，column为数据库表里面对应的字段-->
  17.         <result property="custId" column="cust_id" />
  18. <!--            如果是主键也可以使用id标签来指定-->
  19. <!--        <id property="custId" column="cust_id"/>-->
  20.         <result property="custName" column="cust_name" />
  21.         <result property="custAddress" column="cust_address" />
  22.         <result property="custCity" column="cust_city" />
  23.         <result property="custState" column="cust_state" />
  24.         <result property="custZip" column="cust_zip" />
  25.         <result property="custCountry" column="cust_country" />
  26.         <result property="custContact" column="cust_contact" />
  27.         <result property="custEmail" column="cust_email" />
  28.     </resultMap>
  31. </mapper>

外界使用的问题

在测试代码中，我们是这样写的

  1. public class customersTest {
  2.     @Test
  3.     public void getCustomerList() throws IOException {
  5.         try (Reader reader = Resources.getResourceAsReader("mybatis-config.xml")) { //加载MyBatis核心配置文件
  6.             SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder(); //使用SqlSessionFactoryBuilder创建工厂对象
  7.             SqlSessionFactory factory = builder.build(reader);       
  8.             SqlSession session = factory.openSession(); //使用工厂对象生产SqlSession对象
  9.             //customers.allCustomers为mapper的namespace命名空间和select标签的id来执行语句
  10.             List<Customers> customersList = session.selectList("customers.allCustomers");//使用SqlSession对象做增、删、改、查
  12.             for (Customers customers : customersList) {
  13.                  System.out.println(customers.getCustName());
  14.             }
  15.         }
  17.     }
  18. }

官方文档明确强调SqlSessionFactoryBuilder被创建完应该被丢弃、SqlSessionFactory的实例应该一直保存在内存里面、SqlSession每个线程都应该有它自己的 SqlSession 实例。我们根据官方文档上来操作，官方文档就是最佳实践。我们搞个工具类来直接获取SqlSession对象就可以了，拿到SqlSession对象来执行具体的sql语句。

  1. package utils;
  3. import org.apache.ibatis.io.Resources;
  4. import org.apache.ibatis.session.SqlSession;
  5. import org.apache.ibatis.session.SqlSessionFactory;
  6. import org.apache.ibatis.session.SqlSessionFactoryBuilder;
  8. import java.io.Reader;
  10. public class MyBatisUtils {
  11.     private static SqlSessionFactory factory;
  12.     static {
  13.         try (Reader reader  = Resources.getResourceAsReader("mybatis-config.xml")) {
  14.             // 创建一个工厂构建器
  15.             SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
  16.             // 创建一个工厂
  17.             factory = builder.build(reader);
  18.         } catch (Exception e) {
  19.             e.printStackTrace();
  20.         }
  21.     }
  24.     public static SqlSession openSession() {
  25.         return factory.openSession();
  26.     }
  28. }

为了更加清晰的看到执行SQL的过程，我们需要查看打印信息。可以在Setting中开启打印日志功能。

  1. <settings>
  2.   <!-- 数据库表字段：my_first_name 映射到JavaBean 属性：myFirstName -->
  3.   <setting name="mapUnderscoreToCamelCase" value="true"/>
  4.   <!-- 打印日志信息 -->
  5.   <setting name="logImpl" value="STDOUT_LOGGING" />
  6. </settings>

你也可以直接使用第三方的日志功能，只要在Maven中依赖即可，MyBatis会自动识别到。

  1. <dependency>
  2.   <groupId>ch.qos.logback</groupId>
  3.   <artifactId>logback-classic</artifactId>
  4.   <version>1.2.3</version>
  5. </dependency>

官方对日志的实现

参数传递

传递一个参数的情况

mapper文件,使用#{}的方式接受参数

  1. <?xml version="1.0" encoding="UTF-8" ?>
  2. <!DOCTYPE mapper
  3.         PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  4.         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  5. <mapper namespace="customers">
  7.     <select id="getOneCustomer" resultType="com.lff.Customers">
  8.         SELECT * FROM customers where cust_id = #{id}
  9.     </select>
  11. </mapper>

{id}为参数占位符，名称任意。

外界使用

  1. Customers customer =  session.selectOne("customers.getOneCustomer","1000000001");

customers.getOneCustomer为mapper命名空间.select标签id
1000000001为传递的参数

传递多个参数

mapper文件

  1. <?xml version="1.0" encoding="UTF-8" ?>
  2. <!DOCTYPE mapper
  3.         PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  4.         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  5. <mapper namespace="customers">
  7.     <select id="getOneCustomer" resultType="com.lff.Customers">
  8.         SELECT * FROM customers where cust_name = #{custName} AND cust_city like #{custCity}
  9.     </select>
  11. </mapper>

参数的含义
#{name}、#{city}要与外界传入的参数名称相同。

外界使用

  1. public class customersTest {
  2.     @Test
  3.     public void getCustomerList() throws IOException {
  4.         SqlSession session = MyBatisUtils.openSession();
  5.         Map<String,String> params = new HashMap<>();
  6.         params.put("custName","Fun4All");
  7.         params.put("custCity","%ie%");
  8.         List<Customers> customers = session.selectList("customers.getOneCustomer",params);
  9.         for (Customers customer : customers) {
  10.             System.out.println(customer.getCustCity());
  11.         }
  12.      }
  13. }

Map里面的name、city 要与映射文件里面的接受参数相同。

当然外界使用的时候也可以传入JavaBean对象。

  1. public class customersTest {
  2.     @Test
  3.     public void getCustomerList() throws IOException {
  4.         SqlSession session = MyBatisUtils.openSession();
  5.         Customers params = new Customers();
  6.         params.setCustName("Fun4All");
  7.         params.setCustCity("%ie%");
  9.         List<Customers> customers = session.selectList("customers.getOneCustomer",params);
  10.         for (Customers customer : customers) {
  11.             System.out.println(customer.getCustCity());
  12.         }
  13.         }
  14. }

${}、#{}

{}为预编译传值，可以防止SQL注入。点击查看官方解释
${}直接文本替换。

多表查询

在《SQL必知必会》的表结构中，有一个产品表和供应商表，每一个产品都有一个供应商。
产品表
image.png
供应商表
image.png
对应的JavaBean

  1. package com.lff;
  3. public class Products {
  4.     private String prodId;
  5.     private String vendId;
  6.     private String prodName;
  7.     private String prodPrice;
  8.     private String prodDesc;
  9.     private Vendors vendors;
  11.     public String getProdId() {
  12.         return prodId;
  13.     }
  15.     public void setProdId(String prodId) {
  16.         this.prodId = prodId;
  17.     }
  19.     public String getVendId() {
  20.         return vendId;
  21.     }
  23.     public void setVendId(String vendId) {
  24.         this.vendId = vendId;
  25.     }
  27.     public String getProdName() {
  28.         return prodName;
  29.     }
  31.     public void setProdName(String prodName) {
  32.         this.prodName = prodName;
  33.     }
  35.     public String getProdPrice() {
  36.         return prodPrice;
  37.     }
  39.     public void setProdPrice(String prodPrice) {
  40.         this.prodPrice = prodPrice;
  41.     }
  43.     public String getProdDesc() {
  44.         return prodDesc;
  45.     }
  47.     public void setProdDesc(String prodDesc) {
  48.         this.prodDesc = prodDesc;
  49.     }
  51.     public Vendors getVendors() {
  52.         return vendors;
  53.     }
  55.     public void setVendors(Vendors vendors) {
  56.         this.vendors = vendors;
  57.     }
  58. }

供应商Bean

  1. package com.lff;
  3. public class Vendors {
  4.     private String vendId;
  5.     private String vendName;
  6.     private String vendAddress;
  7.     private String vendCity;
  8.     private String vendState;
  9.     private String vendZip;
  10.     private String vendCountry;
  12.     public String getVendId() {
  13.         return vendId;
  14.     }
  16.     public void setVendId(String vendId) {
  17.         this.vendId = vendId;
  18.     }
  20.     public String getVendName() {
  21.         return vendName;
  22.     }
  24.     public void setVendName(String vendName) {
  25.         this.vendName = vendName;
  26.     }
  28.     public String getVendAddress() {
  29.         return vendAddress;
  30.     }
  32.     public void setVendAddress(String vendAddress) {
  33.         this.vendAddress = vendAddress;
  34.     }
  36.     public String getVendCity() {
  37.         return vendCity;
  38.     }
  40.     public void setVendCity(String vendCity) {
  41.         this.vendCity = vendCity;
  42.     }
  44.     public String getVendState() {
  45.         return vendState;
  46.     }
  48.     public void setVendState(String vendState) {
  49.         this.vendState = vendState;
  50.     }
  52.     public String getVendZip() {
  53.         return vendZip;
  54.     }
  56.     public void setVendZip(String vendZip) {
  57.         this.vendZip = vendZip;
  58.     }
  60.     public String getVendCountry() {
  61.         return vendCountry;
  62.     }
  64.     public void setVendCountry(String vendCountry) {
  65.         this.vendCountry = vendCountry;
  66.     }
  67. }

我们根据以上条件查询一下产品表的数据。我们期望Products的vendors属性也能够有值。
mapper文件

  1. <?xml version="1.0" encoding="UTF-8" ?>
  2. <!DOCTYPE mapper
  3.         PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  4.         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  5. <mapper namespace="productNameSpace">
  7.     <select id="getProducts" resultType="com.lff.Products">
  8.         SELECT Products.* ,Vendors.* FROM Products JOIN Vendors ON Vendors.vend_id=Products.vend_id;
  9.     </select>
  11. </mapper>

由于是新增的mapper，所以要在核心配置文件里面加上

  1. <!-- 映射文件 -->
  2. <mappers>
  3.   <mapper resource="mappers/customers.xml" />
  4.   <mapper resource="mappers/products.xml" />
  5. </mappers>

外界使用

  1. public class Products {
  2.     @Test
  3.     public void getProductsList() throws IOException {
  4.         SqlSession session = MyBatisUtils.openSession();
  6.         List<com.lff.Products> products = session.selectList("productNameSpace.getProducts");
  7.         for (com.lff.Products product : products) {
  8.             System.out.println(product.getProdDesc());
  9.         }
  10.     }
  11. }

image.png
默认情况下查询的vendors为空。

解决方案：

方法一:使用resultMap显示的声明

  1. <?xml version="1.0" encoding="UTF-8" ?>
  2. <!DOCTYPE mapper
  3.         PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  4.         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  5. <mapper namespace="productNameSpace">
  7.     <select id="getProducts" resultMap="product_vendors">
  8.         SELECT Products.* ,Vendors.* FROM Products JOIN Vendors ON Vendors.vend_id=Products.vend_id;
  9.     </select>
  11. <!--    type为想要映射的JavaBean-->
  12.     <resultMap id="product_vendors" type="com.lff.Products">
  13. <!--        Products中有个属性叫vendors，vendors中有个属性叫vendId-->
  14.         <result property="vendors.vendId" column="vend_id" />
  15.         <result property="vendors.vendName" column="vend_name" />
  16.         <result property="vendors.vendAddress" column="vend_address" />
  17.         <result property="vendors.vendCity" column="vend_city" />
  18.         <result property="vendors.vendState" column="vend_state" />
  19.         <result property="vendors.vendZip" column="vend_zip" />
  20.         <result property="vendors.vendCountry" column="vend_country" />
  21.     </resultMap>
  22. </mapper>

也可以这样写更加清晰

  1. <?xml version="1.0" encoding="UTF-8" ?>
  2. <!DOCTYPE mapper
  3.         PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  4.         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  5. <mapper namespace="productNameSpace">
  6.     <select id="getProducts" resultMap="product_vendors">
  7.         SELECT Products.* ,Vendors.* FROM Products JOIN Vendors ON Vendors.vend_id=Products.vend_id;
  8.     </select>
  10.     <!--    type为想要映射的JavaBean-->
  11.     <resultMap id="product_vendors" type="com.lff.Products">
  12.         <id property="prodId" column="prod_id"></id>
  13.         <result property="vendId" column="vend_id"></result>
  14.         <result property="prodName" column="prod_name"></result>
  15.         <result property="prodPrice" column="prod_price"></result>
  16.         <result property="prodDesc" column="prod_desc"></result>
  17. <!--        关联的属性vendors为Products的一个属性，类型为com.lff.Vendors。javaType表示关联的具体类型-->
  18.         <association property="vendors" javaType="com.lff.Vendors">
  19.             <id property="vendId" column="vend_id" />
  20.             <result property="vendName" column="vend_name" />
  21.             <result property="vendAddress" column="vend_address" />
  22.             <result property="vendCity" column="vend_city" />
  23.             <result property="vendState" column="vend_state" />
  24.             <result property="vendZip" column="vend_zip" />
  25.             <result property="vendCountry" column="vend_country" />
  26.         </association>
  27.     </resultMap>
  28. </mapper>

只要看到column就是数据库查询出来的字段名称，property是指JavaBean中的属性名称。

外界使用

  1. public class Products {
  2.     @Test
  3.     public void getProductsList() throws IOException {
  4.         SqlSession session = MyBatisUtils.openSession();
  6.         List<com.lff.Products> products = session.selectList("productNameSpace.getProducts");
  7.         for (com.lff.Products product : products) {
  8.             System.out.println(product.getProdDesc());
  9.         }
  10.     }
  11. }

Snipaste_2021-10-26_10-48-40.png

第二种方式

  1. <select id="getProducts" resultType="com.lff.Products">
  2.   SELECT Products.* ,
  3.     Vendors.vend_id  `vendors.vendId`,
  4.     Vendors.vend_name  `vendors.vendName`,
  5.     Vendors.vend_address  `vendors.vendAddress`,
  6.     Vendors.vend_city  `vendors.vendCity`,
  7.     Vendors.vend_state  `vendors.vendState`,
  8.     Vendors.vend_zip  `vendors.vendZip`,
  9.     Vendors.vend_country  `vendors.vendCountry`
  10.   FROM Products JOIN Vendors ON Vendors.vend_id=Products.vend_id;
  11. </select>

在查询的时候直接将结果取别名，别名和JavaBean中的属性名相同。
image.png
这种方式简单直接。

插入操作

mapper文件

  1.     <insert id="insertCustomer" parameterType="com.lff.Customers">
  2.         INSERT INTO Customers
  3.         (cust_id,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_contact,cust_email)
  4.         VALUES
  5.             (#{custId},#{custName},#{custAddress},#{custCity},#{custState},#{custZip},#{custContact},#{custEmail})
  6.     </insert>

外界使用

  1. public class customersTest {
  2.     @Test
  3.      public void insertCustomer(){
  4.          SqlSession session = MyBatisUtils.openSession();
  5.          //传入JavaBean
  6.          Customers customers = new Customers();
  7.          customers.setCustId(1002);
  8.          customers.setCustName("lff");
  9.          customers.setCustCity("beijing");
  10.          customers.setCustContact("Beijing");
  11.          customers.setCustEmail("190000000@163.com");
  12.          customers.setCustZip("10001");
  13.          customers.setCustState("CN");
  14.          customers.setCustAddress("Beijing");
  16.          session.insert("customers.insertCustomer",customers);
  17.          //对数据插入、更新、删除一定要提交事务。
  18.          session.commit();
  20.      }
  21. }

session对象的openSession方法的默认参数是false，默认不提交事务。
所以一定要手动的提交事务，否则MyBatis默认会回滚。

批量插入

  1. <?xml version="1.0" encoding="UTF-8" ?>
  2. <!DOCTYPE mapper
  3.         PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  4.         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  5. <mapper namespace="customers">
  7.     <insert id="batchInsert" parameterType="List">
  8.         INSERT INTO Customers
  9.         (cust_id,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_contact,cust_email)
  10.         VALUES
  11.         <foreach collection="list" item="cust"  separator=",">
  12.             (#{cust.custId},#{cust.custName},#{cust.custAddress},#{cust.custCity},#{cust.custState},#{cust.custZip},#{cust.custContact},#{cust.custEmail})
  13.         </foreach>
  15.     </insert>
  17. </mapper>

foreach中的collection=”list”，如果外界传入的参数是List 就写collection的值就是list，如果外界传入的是数组，collection的值写array

外界使用

  1.      public void batchInsertCustomer(){
  2.          SqlSession session = MyBatisUtils.openSession();
  3.          List<Customers> customersList = new ArrayList<>();
  5.          Customers customers = new Customers();
  6.          customers.setCustId(1011);
  7.          customers.setCustName("lff");
  8.          customers.setCustCity("beijing");
  10.          Customers customers1 = new Customers();
  11.          customers1.setCustId(1012);
  12.          customers1.setCustName("Mr Li");
  13.          customers1.setCustCity("beijing");
  15.          Customers customers2 = new Customers();
  16.          customers2.setCustId(1013);
  17.          customers2.setCustName("Mr Li");
  18.          customers2.setCustCity("beijing");
  20.          customersList.add(customers);
  21.          customersList.add(customers1);
  22.          customersList.add(customers2);
  23.          session.insert("customers.batchInsert",customersList);
  24.          session.commit();
  25.      }

设置新插入的主键到参数中

更新

mapper文件

  1. <?xml version="1.0" encoding="UTF-8" ?>
  2. <!DOCTYPE mapper
  3.         PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  4.         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  5. <mapper namespace="customers">
  7.     <insert id="insertCustomer" parameterType="com.lff.Customers">
  8.         INSERT INTO Customers
  9.         (cust_id,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_contact,cust_email)
  10.         VALUES
  11.             (#{custId},#{custName},#{custAddress},#{custCity},#{custState},#{custZip},#{custContact},#{custEmail})
  12.     </insert>
  14.     <update id="updateCustomer" parameterType="com.lff.Customers">
  15.         UPDATE Customers SET cust_name=#{custName}, cust_address=#{custAddress} WHERE cust_id=#{custId}
  16.     </update>
  17. </mapper>

外界使用

  1. public class customersTest {
  2.     @Test
  3.      public void insertCustomer(){
  4.          SqlSession session = MyBatisUtils.openSession();
  5.          //传入JavaBean
  6.          Customers customers = new Customers();
  7.          customers.setCustId(1005);
  8.          customers.setCustName("Mr Li");
  9.          customers.setCustCity("beijing");
  10.          customers.setCustContact("Beijing");
  11.          customers.setCustEmail("190000000@163.com");
  12.          customers.setCustZip("10001");
  13.          customers.setCustState("CN");
  14.          customers.setCustAddress("TianJing");
  16.          session.insert("customers.updateCustomer",customers);
  17.          //对数据插入、更新、删除一定要提交事务。
  18.          session.commit();
  20.      }
  21. }

删除

mapper文件

  1. <delete id="deleteCustomer" parameterType="int">
  2.   DELETE FROM Customers WHERE cust_id = #{custId}
  3. </delete>

外界使用

  1. public void deleteCustomer(){
  2.     SqlSession session = MyBatisUtils.openSession();
  3.     session.delete("customers.deleteCustomer",100);
  4.     session.commit();
  5. }

批量删除

  1. <delete id="batchDelete" parameterType="List">
  2.   DELETE FROM Customers WHERE cust_id IN
  3.   <foreach collection="list" open="(" close=")" separator="," item="item">
  4.     #{item}
  5.   </foreach>
  6. </delete>

外界使用

  1. public void batchDelete(){
  2.     SqlSession session = MyBatisUtils.openSession();
  3.     List<Integer> params = new ArrayList<>();
  4.     params.add(1008);
  5.     params.add(1009);
  6.     params.add(1010);
  7.     session.delete("customers.batchDelete",params);
  8.     session.commit();
  10. }