在很多Java EE项目中,Spring+MyBatis框架经常被用到,项目搭建在这里不再赘述,现在要将的是如何在项目中书写,增删改查的语句,如何操作数据库,以及后台如何获取数据,如何进行关联查询,以及MyBatis的分页问题。
首先先看看项目的架构,方便后边叙述。
这个项目中是一个Sping+MyBatis的完整demo(这边将页面没有展示。)这次的主题主要是后台数据处理逻辑。接下来为大家逐一介绍各个文件,
org.config Spring配置包括数据库的链接信息
org.controller 逻辑控制,也就是MVC中的C
org.dao 接口基类
org.entity 实体以及MyBatis语句
org.util 工具类
在数据库中存在两张表,分别为Customer_info,order_info。用这两张表格最后实现多表格的关联查询。
第一步骤:建立与数据库表格字段相一致的实体类:
customerInfo.java
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
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
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
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>#{begin} and rn<#{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
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>#{begin} and rn<#{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
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中的这段代码;
在controller中的代码如下:
从图中可以看到,在这边使用了一个增强的for循环来进行处理,这样就实现了多表之间的关联查询,接下来将数据显示到前台页面即可。
主要代码:
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