目录

插入

删除

修改

查询

1. 查询全部

2. 多条件查询

3. 模糊查询

4. 一对多查询

5. 多对一查询

6. 多对多查询

动态语句的构建

插入

insert into category_ ( name ) values (#{name})

删除

delete from category_ where id= #{id}

修改

update category_ set name=#{name} where id=#{id}

查询

select * from category_ where id= #{id}

查询全部

select * from category_

多条件查询

select * from category_ where id> #{id} and name like concat('%',#{name},'%')

模糊查询

mysql: select * from category_ where name like concat('%',#{0},'%')
oracle: select * from category_ where name like '%'||#{0}||'%'

一对多查询

一 category对多 product
要求结果如下：

image.png

得到一组category 每个category包含多个product

category数据库中表

CREATE TABLE category_ (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(32) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

product数据库中表

create table product_(
id int NOT NULL AUTO_INCREMENT,
name varchar(30)  DEFAULT NULL,
price float  DEFAULT 0,
cid int ,
PRIMARY KEY (id)
)AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

Category pojo

public class Category {
    private int id;
    private String name;
    List<Product> products;

product pojo

public class Product {
    private int id;
    private String name;
    private float price;
...

CategoryMapper

public interface CategoryMapper {
    @Select(" select * from category_ ")
    @Results({ 
                @Result(property = "id", column = "id"),
                @Result(property = "products", javaType = List.class, column = "id", many = @Many(select = "com.how2java.mapper.ProductMapper.listByCategory") )
            })
    public List<Category> list();
}

ProductMapper

public interface ProductMapper {
    @Select(" select * from product_ where cid = #{cid}")
    public List<Product> listByCategory(int cid);    
}

多对一查询

多product对一category

image.png

pojo和数据库中的表与一对多相同

CategoryMapper

public interface CategoryMapper {
    @Select(" select * from category_ where id = #{id}")
    public Category get(int id);     
}

ProductMapper

public interface ProductMapper {
    @Select(" select * from product_ ")
    @Results({ @Result(property="category",column="cid",one=@One(select="com.how2java.mapper.CategoryMapper.get")) 
    })
    public List<Product> list();
}

多对多查询

这里一个订单order对应多个小项orderitem(产品，订单，产品数量),一个产品product对应多个小项orderitem 间接的 多个order对应多个产品product

image.png

public class Category {
    private int id;
    private String name;
    List<Product> products;

public class Product {
    private int id;
    private String name;
    private float price;
    private Category category;

public class OrderItem {
    private int id;
    private int number;
    private Order order;
    private Product product;

public class Order {
    private int id;
    private String code;
    List<OrderItem> orderItems;

public interface ProductMapper {
     
    @Select("select * from product_ where id = #{id}")
    public Product get(int id);
}

public interface OrderItemMapper {
     
    @Select(" select * from order_item_ where oid = #{oid}")
    @Results({ 
        @Result(property="product",column="pid",one=@One(select="com.how2java.mapper.ProductMapper.get")) 
    }) 
    public List<OrderItem> listByOrder(int oid);
}

public interface OrderMapper {
    @Select("select * from order_")
     @Results({
            @Result(property = "id", column = "id"),
            @Result(property = "orderItems", javaType = List.class, column = "id", 
                    many = @Many(select = "com.how2java.mapper.OrderItemMapper.listByOrder"))
            })      
    public List<Order> list();
     
}

动态语句构建

一个项目中的动态select例子见 https://www.jianshu.com/p/834ed62e1ec9
CategoryDynaSqlProvider

public class CategoryDynaSqlProvider {
    public String list() {
         return new SQL()
                 .SELECT("*")
                 .FROM("category_")
                 .toString();
         
    }
    public String get() {
        return new SQL()
                .SELECT("*")
                .FROM("category_")
                .WHERE("id=#{id}")
                .toString();
    }
     
    public String add(){
        return new SQL()
                .INSERT_INTO("category_")
                .VALUES("name", "#{name}")
                .toString();
    }
    public String update(){
        return new SQL()
                .UPDATE("category_")
                .SET("name=#{name}")
                .WHERE("id=#{id}")
                .toString();
    }
    public String delete(){
        return new SQL()
                .DELETE_FROM("category_")
                .WHERE("id=#{id}")
                .toString();
    }
     
}

mapper文件

public interface CategoryMapper {
  
    @InsertProvider(type=CategoryDynaSqlProvider.class,method="add") 
    public int add(Category category); 
        
    @DeleteProvider(type=CategoryDynaSqlProvider.class,method="delete")
    public void delete(int id); 
        
    @SelectProvider(type=CategoryDynaSqlProvider.class,method="get") 
    public Category get(int id); 
      
    @UpdateProvider(type=CategoryDynaSqlProvider.class,method="update") 
    public int update(Category category);  
        
    @SelectProvider(type=CategoryDynaSqlProvider.class,method="list")     
    public List<Category> list(); 
}