MyBatisSpring+MyBatis框架中sql语句之写,数据集的传递及多表关联查询

  在多Java
EE项目被,Spring+MyBatis框架经常让用到,项目搭建在此间不再赘述,现在一经拿的凡什么样以档次遭到修,增删改查的语,如何操作数据库,以及后台如何获取数据,如何开展关联查询,以及MyBatis的分页问题。

  首先先看看项目的架,方便后边叙述。

MyBatis 1

   这个类型蒙凡是一个Sping+MyBatis的完全demo(这边将页面没有显示。)这次的主题主要是后台数据处理逻辑。接下来为大家逐一介绍各个文件,

  org.config   Spring配置包括数据库的链接信息

  org.controller  逻辑控制,也即是MVC中的C

  org.dao      接口基类

  org.entity      实体和MyBatis语句

  org.util       工具类

  以数据库中存在个别摆表,分别吗Customer_info,order_info。用当下半布置表最后实现多表格的涉嫌查询。

  第一步骤:建立与数据库表格字段相平等的实体类:

  customerInfo.java

MyBatis 2MyBatis 3

package org.entity;
//实现该接口--序列化,将对象写入文件
import java.io.Serializable;
import java.util.List;

public class CustomerInfo implements Serializable {
    private Integer customer_id;//客户信息的id
    private String  customer_name;//客户姓名
    private String identity_no;//身份证号码
    private String job_add;//工作单位
    private String tel;//座机号码
    private String cellphone;//移动电话
    private String adds;//联系地址
    private Integer post;//邮编
    private String mail;//电子邮箱
    //关联查询属性orderInfo
    private List<OrderInfo> orderInfos;


    public List<OrderInfo> getOrderInfos() {
        return orderInfos;
    }
    public void setOrderInfos(List<OrderInfo> orderInfos) {
        this.orderInfos = orderInfos;
    }

    public Integer getCustomer_id() {
        return customer_id;
    }
    public void setCustomer_id(Integer customer_id) {
        this.customer_id = customer_id;
    }
    public String getCustomer_name() {
        return customer_name;
    }
    public void setCustomer_name(String customer_name) {
        this.customer_name = customer_name;
    }
    public String getIdentity_no() {
        return identity_no;
    }
    public void setIdentity_no(String identity_no) {
        this.identity_no = identity_no;
    }
    public String getJob_add() {
        return job_add;
    }
    public void setJob_add(String job_add) {
        this.job_add = job_add;
    }
    public String getTel() {
        return tel;
    }
    public void setTel(String tel) {
        this.tel = tel;
    }
    public String getCellphone() {
        return cellphone;
    }
    public void setCellphone(String cellphone) {
        this.cellphone = cellphone;
    }
    public String getAdds() {
        return adds;
    }
    public void setAdds(String adds) {
        this.adds = adds;
    }
    public Integer getPost() {
        return post;
    }
    public void setPost(Integer post) {
        this.post = post;
    }
    public String getMail() {
        return mail;
    }
    public void setMail(String mail) {
        this.mail = mail;
    }



}

View Code

  OrderInfo.java

MyBatis 4MyBatis 5

 1 package org.entity;
 2 
 3 import java.io.Serializable;
 4 import java.sql.Date;
 5 
 6 //订单退货单
 7 public class OrderInfo implements Serializable{
 8     private Integer order_id;//订单(退货单)信息的id
 9     private String order_type;//订单类型
10     private String order_status;//订单状态
11     private Integer product_id;//商品id
12     private Integer product_account;//商品数量
13     private Double pay_money;//总额
14     private Integer customer_id;//客户id
15     private Date start_date;//开始日期
16     private Date deadline;//要求完成日期
17     private String return_reason;//退货原因
18     public Integer getOrder_id() {
19         return order_id;
20     }
21     public void setOrder_id(Integer order_id) {
22         this.order_id = order_id;
23     }
24     
25     public String getOrder_type() {
26         return order_type;
27     }
28     public void setOrder_type(String order_type) {
29         this.order_type = order_type;
30     }
31     public String getOrder_status() {
32         return order_status;
33     }
34     public void setOrder_status(String order_status) {
35         this.order_status = order_status;
36     }
37     public Integer getProduct_id() {
38         return product_id;
39     }
40     public void setProduct_id(Integer product_id) {
41         this.product_id = product_id;
42     }
43     public Integer getProduct_account() {
44         return product_account;
45     }
46     public void setProduct_account(Integer product_account) {
47         this.product_account = product_account;
48     }
49     public Double getPay_money() {
50         return pay_money;
51     }
52     public void setPay_money(Double pay_money) {
53         this.pay_money = pay_money;
54     }
55     public Integer getCustomer_id() {
56         return customer_id;
57     }
58     public void setCustomer_id(Integer customer_id) {
59         this.customer_id = customer_id;
60     }
61     public Date getStart_date() {
62         return start_date;
63     }
64     public void setStart_date(Date start_date) {
65         this.start_date = start_date;
66     }
67     public Date getDeadline() {
68         return deadline;
69     }
70     public void setDeadline(Date deadline) {
71         this.deadline = deadline;
72     }
73     public String getReturn_reason() {
74         return return_reason;
75     }
76     public void setReturn_reason(String return_reason) {
77         this.return_reason = return_reason;
78     }
79     
80 
81 }

View Code

  Page.java

MyBatis 6MyBatis 7

 1 package org.entity;
 2 
 3 public class Page {
 4     //显示第几页数据,默认第一页
 5     private Integer page=1;
 6     //一页显示几条,默认5条
 7     private Integer pageSize = 3;
 8     //最大页数
 9     private Integer totalPage=1;
10     
11     public Integer getTotalPage() {
12         return totalPage;
13     }
14     public void setTotalPage(Integer totalPage) {
15         this.totalPage = totalPage;
16     }
17     //利用page和pageSize计算begin起点
18     public Integer getBegin(){
19         return (page-1)*pageSize;
20     }
21     //利用page和pageSize计算end结束点
22     public Integer getEnd(){
23         return page*pageSize+1;
24     }
25     
26     public Integer getPage() {
27         return page;
28     }
29     public void setPage(Integer page) {
30         this.page = page;
31     }
32     public Integer getPageSize() {
33         return pageSize;
34     }
35     public void setPageSize(Integer pageSize) {
36         this.pageSize = pageSize;
37     }
38 }

View Code

  以连片下去的立刻点儿只文本中,里边详细些了争进行增删改查,以及多表之间的查询操作等。其中sql语句被的各个一个id对诺在dao方法被的办法名称。dao文件的情节如下;

  customer.xml

MyBatis 8MyBatis 9

  1 <?xml version="1.0" encoding="UTF-8" ?>  
  2 <!DOCTYPE mapper PUBLIC "-//ibatis.apache.org//DTD Mapper 3.0//EN"      
  3  "http://ibatis.apache.org/dtd/ibatis-3-mapper.dtd">
  4 <mapper namespace="org.dao.CustomerInfoMapperDao">
  5 
  6     <select id="findAll" 
  7     resultType="org.entity.CustomerInfo">
  8         select * from customer_info
  9     </select>
 10     <delete id="deleteCustomer" parameterType="int">
 11         delete from customer_info where customer_id=#{id}
 12     </delete>
 13     
 14     <insert id="saveCustomer" parameterType="org.entity.CustomerInfo">
 15         insert into Customer_info(customer_id,customer_name,identity_no,
 16         job_add,tel,cellphone,Adds,post,mail)
 17         values(customer_seq.nextval,#{customer_name,jdbcType=VARCHAR},#{identity_no,jdbcType=VARCHAR},
 18         #{job_add,jdbcType=VARCHAR},#{tel,jdbcType=VARCHAR},#{cellphone,jdbcType=VARCHAR},
 19         #{adds,jdbcType=VARCHAR},#{post,jdbcType=NUMERIC},#{mail,jdbcType=VARCHAR})
 20     </insert>
 21     
 22     <select id="findByCustomerName" 
 23         parameterType="java.lang.String"
 24         resultType="org.entity.CustomerInfo">
 25         select * from customer_info where customer_name=#{customer_name}
 26     </select>
 27     <select id="findByCustomerId" parameterType="int" resultType="org.entity.CustomerInfo">
 28         select * from customer_info where customer_id=#{customer_id,jdbcType=NUMERIC}
 29     </select>
 30     
 31     <update id="updateCustomerInfo" parameterType="org.entity.CustomerInfo">
 32         update customer_info set customer_name=#{customer_name,jdbcType=VARCHAR},
 33         identity_no=#{identity_no,jdbcType=VARCHAR},
 34         job_add=#{job_add,jdbcType=VARCHAR},tel=#{tel,jdbcType=VARCHAR},
 35         cellphone=#{cellphone,jdbcType=VARCHAR},adds=#{adds,jdbcType=VARCHAR},
 36         post=#{post,jdbcType=NUMERIC},mail=#{mail,jdbcType=VARCHAR} 
 37         where customer_id=#{customer_id,jdbcType=NUMERIC}
 38     </update>
 39     <select id="findPage" parameterType="org.entity.Page" resultType="org.entity.CustomerInfo" >
 40         select * 
 41         FROM (select c1.*,rownum rn 
 42         FROM (select * FROM customer_info order by customer_id)c1)
 43         where rn&gt;#{begin} and rn&lt;#{end}
 44         
 45     </select>
 46     
 47     <select id="findRows" resultType="int" >
 48         select count(*) from customer_info
 49     </select>
 50     
 51     <select id="somethingNotIn" resultType="返回所对应的实体">
 52             SELECT * FROM user  WHERE username NOT IN ('zhang','wang')
 53     </select>
 54 
 55     <select id="findByCuId" parameterType="java.lang.Integer" resultMap="cuAndOrderResult">
 56     select  o.customer_id,o.order_id,o.order_type,o.order_status,o.product_id,o.product_account,o.start_date,
 57     c.customer_name,c.cellphone,c.adds
 58     from order_info o left join customer_info c on(o.customer_id=c.customer_id)
 59     where o.customer_id=#{customer_id}
 60     </select>
 61     <resultMap id="cuAndOrderResult" type="org.entity.CustomerInfo" >
 62     <id property="customer_id" column="customer_id"/>
 63     <result property="customer_name" column="customer_name"/>
 64     <result property="cellphone" column="cellphone"/>
 65     <result property="adds" column="adds"/>
 66     <collection ofType="org.entity.OrderInfo"
 67             property="orderInfos"  column="customer_id" javaType="java.util.List">
 68     <id property="order_id" column="order_id"/>
 69     <result property="order_type" column="order_type"/>
 70     <result property="order_status" column="order_status"/>
 71     <result property="product_id" column="product_id"/>
 72     <result property="product_account" column="product_account"/>
 73     <result property="start_date" column="start_date"/>
 74     </collection>
 75     </resultMap>
 76     
 77      
 78          <!-- 
 79      <select id="findByCuId" 
 80         parameterType="java.lang.Integer" 
 81         resultMap="cuAndOrderResult">
 82              select  o.customer_id,o.order_id,o.order_type,o.order_status,o.product_id,o.product_account,
 83     c.customer_name,c.cellphone,c.adds
 84     from order_info o join customer_info c on(o.customer_id=c.customer_id)
 85     where o.customer_id=#{customer_id}
 86     select * from customer_info where customer_id=#{customer_id}
 87                 
 88     </select>
 89     <select id="selectOrderInfo" 
 90         parameterType="int" 
 91         resultType="org.entity.OrderInfo">
 92         select * from order_info where customer_id=#{customer_id}
 93     </select>
 94     <resultMap id="cuAndOrderResult" type="org.entity.CustomerInfo">
 95         <id column="customer_id" property="customer_id" />
 96         <collection ofType="org.entity.OrderInfo"
 97             property="orderInfos" javaType="java.util.ArrayList"
 98             column="customer_id" select="selectOrderInfo">
 99         </collection>
100     </resultMap>
101      
102       -->
103      
104      
105      
106     
107     
108     
109 
110 </mapper>

View Code

 

  OrderInfo.xml

MyBatis 10MyBatis 11

 1 <?xml version="1.0" encoding="UTF-8" ?>  
 2 <!DOCTYPE mapper PUBLIC "-//ibatis.apache.org//DTD Mapper 3.0//EN"      
 3  "http://ibatis.apache.org/dtd/ibatis-3-mapper.dtd">
 4 <mapper namespace="org.dao.OrderInfoMapperDao">
 5 
 6     <select id="findAll" 
 7     resultType="org.entity.OrderInfo">
 8         select * from Order_Info
 9     </select>
10     
11     <select id="findPage" parameterType="org.entity.Page" resultType="org.entity.OrderInfo" >
12         select * 
13         FROM (select c1.*,rownum rn 
14         FROM (select * FROM Order_Info order by order_id)c1)
15         where rn&gt;#{begin} and rn&lt;#{end}
16         
17     </select>
18     
19     <select id="findRows" resultType="int" >
20         select count(*) from Order_Info
21     </select>
22 
23 </mapper>

View Code

  CustomerInfoMapperDao.java

MyBatis 12MyBatis 13

 1 package org.dao;
 2 
 3 import java.util.List;
 4 
 5 import org.entity.CustomerInfo;
 6 import org.entity.Page;
 7 import org.util.MyBatisDao;
 8 
 9 
10 
11 //xml文件中的方法,然后在Controller中调用该方法
12 @MyBatisDao
13 public interface CustomerInfoMapperDao {
14     public List<CustomerInfo> findAll();
15     public void deleteCustomer(int id);
16     public void saveCustomer(CustomerInfo customer);
17     public CustomerInfo findByCustomerName(String customer_name);
18     public CustomerInfo findByCustomerId(int id);
19     public List<CustomerInfo> findByCuId(int id);//根据cuid查询客户的订单信息
20     public void updateCustomerInfo(CustomerInfo customer);
21     //分页操作
22     public  List<CustomerInfo> findPage(Page page);
23     public int findRows();
24 
25 }

View Code

  接下要来说一下,MyBatis中的多表关联问题。多表关联主要针对应customer.xml中的马上段代码;

MyBatis 14

在controller中的代码如下:

MyBatis 15

自图备受可见到,在即时边使用了一个加强的for循环来开展处理,这样就是实现了多表之间的关联查询,接下将数据显示到前台页面即可。

要代码:

MyBatis 16MyBatis 17

1 List<CustomerInfo> list = dao.findByCuId(id);
2         String ls = "";
3         List<OrderInfo> cu = null;
4         for(CustomerInfo cus:list){
5             cu=cus.getOrderInfos();
6         }
7         model.addAttribute("customers", list);
8         model2.addAttribute("order",cu);

View Code

 

 

  

网站地图xml地图