一、更多查询

1.模糊查询

修改Category.xml，提供listCategoryByName查询语句select * from category where name like concat('%',#{0},'%')。concat('%',#{0},'%') 这是mysql的写法，如果是oracle，写法是select * from category_ where name like '%'||#{0}||'%'。

1 2         select * from   category  where name like concat('%',#{0},'%')3 

1     private void queryByName() throws IOException {2         create();3         List
     
       categories = session.selectList("listCategoryByName", "i");4         for (Category category : categories) {5             System.out.println(category.getName());6         }7         close();8     }
     

2.多条件查询

结合前面的模糊查询，多一个限定id的条件。因为是多个参数，而selectList方法又只接受一个参数对象，所以需要把多个参数放在Map里，然后把这个Map对象作为参数传递进去。

1     private void queryByNameId() throws IOException { 2         create(); 3         Map
     
       params = new HashMap
      
       (); 4         params.put("id", 8); 5         params.put("name", "xiao"); 6         List
       
         categories = session.selectList("listCategoryByNameId", params); 7         for (Category category : categories) { 8             System.out.println(category.getName()); 9         }10         close();11     }
       
      
     

1     3         select * from category where id>#{id} and name like4         concat('%',#{0},'%')5     

二、Mybatis的关系映射

1.一对多（一个分类对应多个产品）

<1>创建产品表以及产品表对应的实体。

1 package mybatis.pojo; 2  3 public class Product { 4     private int id; 5     private String name; 6     private float price; 7  8     public int getId() { 9         return id;10     }11 12     public void setId(int id) {13         this.id = id;14     }15 16     public String getName() {17         return name;18     }19 20     public void setName(String name) {21         this.name = name;22     }23 24     public float getPrice() {25         return price;26     }27 28     public void setPrice(float price) {29         this.price = price;30     }31 32     public String toString() {33         return "Product [id=" + id + ", name=" + name + ", price=" + price + "]";34     }35 36 }

<2>修改Category实体类，提供product集合。

1 package mybatis.pojo; 2  3 import java.util.List; 4  5 public class Category { 6     private int id; 7     private String name; 8     private List
     
       products; 9 10     public int getId() {11         return id;12     }13 14     public void setId(int id) {15         this.id = id;16     }17 18     public String getName() {19         return name;20     }21 22     public void setName(String name) {23         this.name = name;24     }25 26     public List
      
        getProducts() {27         return products;28     }29 30     public void setProducts(List
       
         products) {31         this.products = products;32     }33 34     public String toString() {35         return "Category [id=" + id + ", name=" + name + "]";36     }37 }
       
      
     

<3>修改Category.xml

通过left join关联查询，对Category和Product表进行关联查询。与前面学习的有所区别，这里不是用的resultType, 而是resultMap，通过resultMap把数据取出来放在对应的对象属性里。

注： Category的id字段和Product的id字段同名，Mybatis不知道谁是谁的，所以需要通过取别名cid,pid来区分，name字段同理。

1 
      2  5  6 
     
       7     
      
        8         
        9         
       10 11         
       12         
       13         
       
        14             
        15             
        16             
        17         
       18     
      19     
      20     
      21         select c.*, p.*, c.id 'cid', p.id 'pid', c.name 'cname', p.name 'pname' from22         category c left join product p on c.id = p.cid23     24 
     

<4>测试

1 package mybatis.test; 2  3 import java.io.IOException; 4 import java.io.InputStream; 5 import java.util.List; 6  7 import org.apache.ibatis.session.SqlSession; 8 import org.apache.ibatis.session.SqlSessionFactory; 9 import org.apache.ibatis.session.SqlSessionFactoryBuilder;10 11 import mybatis.pojo.Category;12 import mybatis.pojo.Product;13 14 public class testOneToMany {15 16     public static void main(String[] args) throws IOException {17         // TODO Auto-generated method stub18         String resource = "mybatis-config.xml";19         InputStream inputStream = org.apache.ibatis.io.Resources.getResourceAsStream(resource);20         SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);21         SqlSession session = sqlSessionFactory.openSession();22         List
     
       categories = session.selectList("listCategory");23         for (Category c : categories) {24             System.out.println(c);25             List
      
        products = c.getProducts();26             for (Product product : products) {27                 System.out.println("\t" + product.toString());28             }29         }30         session.commit();31         session.close();32     }33 34 }
      
     

2.多对一（多个产品对应一个分类）

<1>修改Product.java，增加category属性

1 package mybatis.pojo; 2  3 import java.util.List; 4  5 public class Category { 6     private int id; 7     private String name; 8     private List
     
       products; 9 10     public int getId() {11         return id;12     }13 14     public void setId(int id) {15         this.id = id;16     }17 18     public String getName() {19         return name;20     }21 22     public void setName(String name) {23         this.name = name;24     }25 26     public List
      
        getProducts() {27         return products;28     }29 30     public void setProducts(List
       
         products) {31         this.products = products;32     }33 34     public String toString() {35         return "Category [id=" + id + ", name=" + name + "]";36     }37 }
       
      
     

<2>新增Product.xml

1 
      2  5  6 
     
       7  8  9     
      10         select *11         from product where id= #{id}12     13 14     
      
       15         update product16         set cid=#{cid} where id=#{pid}17     
      18 19     
      
       20         update product21         set cid=#{category.id} where id=#{id}22     
      23 24     
      
       25         
       26         
       27         
       28 29         
       30         
       31         
       
        32             
        33             
        34         
       35     
      36     
      37     
      38         select c.*, p.*, c.id 'cid',39         p.id 'pid', c.name 'cname', p.name 'pname' from40         category c left join41         product p on c.id = p.cid42     43 
     

通过listProduct配置关联查询的sql语句。然后通过resultMap ，进行字段和属性的对应。使用association 进行多对一关系关联，指定表字段名称与对象属性名称的一一对应关系。

注： Category的id 字段 和Product的id字段同名，Mybatis不知道谁是谁的，所以需要通过取别名cid,pid来区分。name字段同理。

<3>在mybatis-config.xml中增加对于Product.xml的映射

1 
     

<4>测试

1 package mybatis.test; 2  3 import java.io.IOException; 4 import java.io.InputStream; 5 import java.util.List; 6  7 import org.apache.ibatis.session.SqlSession; 8 import org.apache.ibatis.session.SqlSessionFactory; 9 import org.apache.ibatis.session.SqlSessionFactoryBuilder;10 11 import mybatis.pojo.Category;12 import mybatis.pojo.Product;13 14 public class testManyToOne {15 16     public static void update() throws IOException {17         String resource = "mybatis-config.xml";18         InputStream inputStream = org.apache.ibatis.io.Resources.getResourceAsStream(resource);19         SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);20         SqlSession session = sqlSessionFactory.openSession();21 22         Category category = session.selectOne("getCategory", 1);23         Product product = session.selectOne("getProduct", 6);24         product.setCategory(category);25         session.update("updateProduct2", product);26 27         // Map
     
       params = new HashMap
      
       ();28         // params.put("cid", category.getId());29         // params.put("pid", product.getId());30         //31         // session.update("updateProduct", params);32 33         session.commit();34         session.close();35     }36 37     public static void main(String[] args) throws IOException {38         // TODO Auto-generated method stub39         update();40         String resource = "mybatis-config.xml";41         InputStream inputStream = org.apache.ibatis.io.Resources.getResourceAsStream(resource);42         SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);43         SqlSession session = sqlSessionFactory.openSession();44 45         List
       
         products = session.selectList("listProduct");46         for (Product product : products) {47             System.out.println(product + "\t对应的分类是：" + product.getCategory());48 49         }50 51         session.commit();52         session.close();53 54     }55 56 }
       
      
     

练习：修改product(id=5)对应的类别为category(id=1)

两种解决思路

<1>首先查询id=5的product，再查询id=1的category，然后调用setCategory方法设置类别，然后对product进行更新操作。

<2>首先查询id=5的product，然后直接设置参数对product进行修改。

①先准备必要的sql语句。

Product.xml中

在updateProduct2中，因为product的成员变量中包含catergory对象，所以直接使用category.id获取类别的id。

1      2         select * 3         from product where id= #{id} 4      5  6     
     
       7         update product 8         set cid=#{cid} where id=#{pid} 9     
     10 11     
     
      12         update product13         set cid=#{category.id} where id=#{id}14     
     

Category.xml中

1     2         select *3         from category where id= #{id}4     

②测试

第一种思路

1     public static void update() throws IOException { 2         String resource = "mybatis-config.xml"; 3         InputStream inputStream = org.apache.ibatis.io.Resources.getResourceAsStream(resource); 4         SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); 5         SqlSession session = sqlSessionFactory.openSession(); 6  7         Category category = session.selectOne("getCategory", 1); 8         Product product = session.selectOne("getProduct", 6); 9         product.setCategory(category);10         session.update("updateProduct2", product);11 12         session.commit();13         session.close();14     }

第二种思路

1     public static void update() throws IOException { 2         String resource = "mybatis-config.xml"; 3         InputStream inputStream = org.apache.ibatis.io.Resources.getResourceAsStream(resource); 4         SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); 5         SqlSession session = sqlSessionFactory.openSession(); 6  7         Product product = session.selectOne("getProduct", 6); 8          Map
     
       params = new HashMap
      
       (); 9          params.put("cid", 1);10          params.put("pid", product.getId());11         12          session.update("updateProduct", params);13         session.commit();14         session.close();15     }
      
     

3.多对多（一张订单可以包含多种产品，一种产品可以出现在多张订单中）

为了维系多对多关系，必须要一个中间表。 在这里我们使用订单项(OrderItem)表来作为中间表。

<1>建立order表，order_item表

<2>添加数据

1 INSERT INTO order_ VALUES (1,'code000A');2 INSERT INTO order_ VALUES (2,'code000B');3  4 INSERT INTO order_item VALUES (null, 1, 1, 100);5 INSERT INTO order_item VALUES (null, 1, 2, 100);6 INSERT INTO order_item VALUES (null, 1, 3, 100);7 INSERT INTO order_item VALUES (null, 2, 2, 100);8 INSERT INTO order_item VALUES (null, 2, 3, 100);9 INSERT INTO order_item VALUES (null, 2, 4, 100); 

<3>添加实体类Order和OrderItem

①Order.java

1 package mybatis.pojo; 2  3 import java.util.List; 4  5 public class Order { 6     private int id; 7     private String code; 8     private List
     
       orderItems; 9 10     public List
      
        getOrderItems() {11         return orderItems;12     }13 14     public void setOrderItems(List
       
         orderItems) {15         this.orderItems = orderItems;16     }17 18     public int getId() {19         return id;20     }21 22     public void setId(int id) {23         this.id = id;24     }25 26     public String getCode() {27         return code;28     }29 30     public void setCode(String code) {31         this.code = code;32     }33 34 }
       
      
     

②OrderItem.java

1 package mybatis.pojo; 2  3 public class OrderItem { 4     private int id; 5     private int number; 6     private Order order; 7     private Product product; 8  9     public int getId() {10         return id;11     }12 13     public void setId(int id) {14         this.id = id;15     }16 17     public int getNumber() {18         return number;19     }20 21     public void setNumber(int number) {22         this.number = number;23     }24 25     public Order getOrder() {26         return order;27     }28 29     public void setOrder(Order order) {30         this.order = order;31     }32 33     public Product getProduct() {34         return product;35     }36 37     public void setProduct(Product product) {38         this.product = product;39     }40 41 }

<4>映射文件

①Order.xml

1 
      2  5  6 
     
       7     
      
        8         
        9         
       10 11         
       
        12             
        13             
        14             
        
         15                 
         16                 
         17                 
         18             
        19         
       20     
      21 22 23     
      24         select o.*,p.*,oi.*, o.id25         'oid', p.id 'pid', oi.id 'oiid', p.name 'pname'26         from order_ o27         left join28         order_item oi on o.id =oi.oid29         left join product p on p.id = oi.pid30     31     
      32         select o.*,p.*,oi.*, o.id 'oid', p.id 'pid', oi.id 'oiid', p.name 'pname'33         from order_ o34         left join order_item oi on o.id =oi.oid35         left join product p on p.id = oi.pid36         where o.id = #{id}37     38 
     

②Product.xml

1 
      2  5  6 
     
       7  8     
      13 14     
      
       15         update product16         set17         cid=#{cid} where id=#{pid}18     
      19 20     
      
       21         update product22         set23         cid=#{category.id} where id=#{id}24     
      25 26     
      
       27         
       28         
       29         
       30 31         
       32         
       33         
       
        34             
        35             
        36         
       37     
      38     
      39     
      40         select c.*, p.*, c.id 'cid',41         p.id 'pid', c.name 'cname', p.name 'pname' from42         category c left join43         product p on c.id = p.cid44     45     
      46         select c.*, p.*, c.id 'cid', p.id 'pid', c.name 'cname', p.name 'pname'47         from category c48         left join product p on c.id = p.cid49         where p.id = #{id}50     51 
     

③OrderItem.xml

1 
      2  5  6 
     
       7     
      
        8         insert into order_item 9         values(null,#{order.id},#{product.id},#{number})10     
      11     
      
       12         delete from order_item13         where oid = #{order.id} and pid = #{product.id}14     
      15 
     

<5>修改mybatis-config.xml

1 
     2 
     

<6>查询操作

查询出所有的订单，然后遍历每个订单下的多条订单项，以及订单项对应的产品名称，价格，购买数量。通过Order.xml的listOrder对应的sql语句进行查询：

1     2         select o.*,p.*,oi.*, o.id3         'oid', p.id 'pid', oi.id 'oiid', p.name 'pname'4         from order_ o5         left join6         order_item oi on o.id =oi.oid7         left join product p on p.id = oi.pid8     

联合order_, order_item_, product_ 三张表进行查询

1     
     
       2         
       3         
       4  5         
      
        6             
        7             
        8             
       
         9                 
        10                 
        11                 
        12             
       13         
      14     
     

 

1     private static void listOrder(SqlSession session) { 2         List
     
       orders = session.selectList("listOrder"); 3         for (Order order : orders) { 4             System.out.println(order.getCode()); 5             List
      
        orderItems = order.getOrderItems(); 6             for (OrderItem orderItem : orderItems) { 7                 System.out.format("\t%s\t%f\t%d\n", orderItem.getProduct().getName(), orderItem.getProduct().getPrice(), 8                         orderItem.getNumber()); 9             }10         }11     }
      
     

<7>建立关系

　　建立了让订单000A和产品z建立了关系。首先通过id分别获取Ordre对象和Product对象，然后创建一个新的OrderItem对象，接着设置Order，设置Product，设置数量，最后调用"addOrderItem" 对应的sql语句插入数据。

 

1     private static void addOrderItem(SqlSession session) { 2         Order order = session.selectOne("getOrder", 1); 3         Product product = session.selectOne("getProduct", 6); 4         OrderItem orderItem = new OrderItem(); 5         orderItem.setProduct(product); 6         orderItem.setOrder(order); 7         orderItem.setNumber(200); 8  9         session.insert("addOrderItem", orderItem);10     }

addOrderItem调用insert into 语句插入一条OrderItem记录：

1     
     
      2         insert into order_item3         values(null,#{order.id},#{product.id},#{number})4     
      

<8>删除关系

删除了订单00A和产品z的关系，再次查询，就看不到产品z了。删除关系的时候，通过订单id(1)和产品id(6)进行删除。其实所谓的删除关系，就是删除掉OrderItem记录。

1     private static void deleteOrderItem(SqlSession session) {2         Order order = session.selectOne("getOrder", 1);3         Product product = session.selectOne("getProduct", 6);4         OrderItem orderItem = new OrderItem();5         orderItem.setProduct(product);6         orderItem.setOrder(order);7         session.delete("deleteOrderItem", orderItem);8     }

练习：删除订单A，那么就应该删除订单A在订单项里所对应的数据。

通过Mybatis执行多条sql语句需要增加一个参数：allowMultiQueries，多条语句之间用分号隔开。

1 
     

1     
     
      2         delete from3         order_ where code = #{code};4         delete from5         order_item6         where oid = #{id}7     
     

1     private static void deleteOrder(SqlSession session) {2         Order order = session.selectOne("getOrder", 1);3         session.delete("deleteOrder", order);4     }