mybatis 一二事（三）

1 jdbc.driver=com.mysql.jdbc.Driver2 jdbc.url=jdbc:mysql://localhost:3306/order3 jdbc.username=root4 jdbc.password=root

 1 <?xml version="1.0" encoding="UTF-8" ?> 2 <!DOCTYPE configuration 3 PUBLIC "-//mybatis.org//DTD Config 3.0//EN" 4 "http://mybatis.org/dtd/mybatis-3-config.dtd"> 5 <configuration> 6     <!-- 加载数据库连接参数配置文件 --> 7     <properties resource="db.properties" /> 8      9     <!-- 10         全局配置参数11         比如 二级缓存 延迟加载...等12         此全局参数会影响mybatis运行的性能，要谨慎配置    13      -->14 <!--     <settings> -->15 <!--     <setting name="" value=""/> -->16 <!--     </settings> -->17     18     <!-- 定义别名 -->19     <typeAliases>20         <!-- 单个别名定义21         type：pojo的路径 22         alias：别名的名称23         -->24         <!-- <typeAlias type="cn.itcast.mybatis.po.User" alias="user"/> -->25         <!-- 批量别名定义26         name：指定包名，将包下边的所有pojo定义别名 ，别名为类名（首字母大写或小写都行）27          -->28         <package name="com.mybatis.bean"/>29     </typeAliases>30     31     <!-- 和spring整合后 environments配置将废除 -->32     <environments default="development">33         <environment id="development">34             <transactionManager type="JDBC" />35             <dataSource type="POOLED">36                 <property name="driver" value="${jdbc.driver}"/>37                 <property name="url" value="${jdbc.url}"/>38                 <property name="username" value="${jdbc.username}"/>39                 <property name="password" value="${jdbc.password}"/>40             </dataSource>41         </environment>42     </environments>43 44     <!-- 配置mapper映射文件 -->45     <mappers>46         <!-- resource方式47         在UserMapper.xml，定义namespace为mapper接口的地址，映射文件通过namespace找到对应的mapper接口文件48          -->49         <!-- <mapper resource="sqlmap/UserMapper.xml" /> -->50         <!-- class方式51         class：指定 mapper接口的地址52         遵循规则：将mapper.xml和mapper.java文件放在一个目录 且文件名相同53          -->54         <!-- <mapper class="cn.itcast.mybatis.mapper.UserMapper"/> -->55         56         <!--57             批量mapper扫描58             遵循规则：将mapper.xml和mapper.java文件放在一个目录 且文件名相同59             主要以这样的方式为主来加载mapper60           -->61         <package name="com.mybatis.mapper"/>62         63         64     </mappers>65 </configuration>

  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   6 <mapper namespace="com.mybatis.mapper.OrderMapper" >  7       8     <!--   9         ******** 10         1 对 1 查询建议使用 resultType 11         ******** 12      --> 13      14     <!-- type 为主类，即A类包含了B类作为属性，则type就写A类 --> 15     <resultMap type="Orders" id="orderUserResultMap"> 16         <!--  17             id 和 result 这两个标签是映射到 Orders 中去的  18             如果有多个字段决定id唯一，则写多个id标签 19         --> 20         <id column="id" property="id"/> 21         <result column="order_number" property="order_number"/> 22         <result column="user_id" property="user_id"/> 23  24         <!--  25             用于映射单个关联对象 26             property: 将关联信息映射到哪个属性 27             javaType: 属性映射的类型 28          --> 29         <association property="user" javaType="com.mybatis.bean.User"> 30             <id column="user_id" property="id"/> 31             <result column="username" property="username"/> 32             <result column="birthday" property="birthday"/> 33         </association> 34     </resultMap>     35  36  37     <!--  38         extends： 继承上一个resultMap, 公用相同属性 39      --> 40     <resultMap type="Orders" id="orderUserDetailResultMap" extends="orderUserResultMap"> 41  42         <!--  43             collection： 用于映射集合对象 44             property: 将集合信息映射到哪个属性 45             ofType: 这个集合对象的类型，也就是<>泛型 46          --> 47         <collection property="orderdetails" ofType="com.mybatis.bean.Orderdetail"> 48             <id column="order_detail_id" property="id"/> 49             <result column="item_id" property="item_id"/> 50             <result column="item_num" property="item_num"/> 51             <result column="item_price" property="item_price"/> 52         </collection> 53  54     </resultMap>     55  56     <!--  57         1 对 1 查询 58      --> 59     <select id="findOrderUserList" resultType="OrderUserCustom"> 60         select  61             o.id, 62             o.order_number, 63             o.user_id, 64             u.username, 65             u.birthday 66         from  67             orders o  68         inner join  69             user u  70         on  71             o.user_id = u.id 72     </select> 73  74     <select id="findOrderUserListByResultMap" resultMap="orderUserResultMap"> 75         select  76             o.id, 77             o.order_number, 78             o.user_id, 79             u.username, 80             u.birthday 81         from  82             orders o  83         inner join  84             user u  85         on  86             o.user_id = u.id 87     </select> 88      89     <!--  90         1 对 多 查询 91      --> 92      <select id="findOrderItemsList" resultMap="orderUserDetailResultMap"> 93          select  94                 o.id, 95                 o.order_number, 96                 o.user_id, 97                 u.username, 98                 u.birthday, 99                 od.item_id,100                 od.item_num,101                 od.item_price,102                 od.id as order_detail_id103             from 104                 orders o 105             inner join 106                 user u 107             on 108                 o.user_id = u.id 109             left join 110                 orderdetail od 111             on 112                 o.id = od.orders_id113     </select>114 </mapper>

 1 package com.mybatis.mapper; 2  3 import java.util.List; 4  5 import com.mybatis.bean.OrderUserCustom; 6 import com.mybatis.bean.Orders; 7  8 public interface OrderMapper { 9     10     public List<OrderUserCustom> findOrderUserList() throws Exception;11     12     public List<Orders> findOrderUserListByResultMap() throws Exception;13     14     public List<Orders> findOrderItemsList() throws Exception;15     16 }

 1 package com.mybatis.bean; 2  3 /** 4  * 商品信息 5  *  6  * @author Thinkpad 7  * 8  */ 9 public class Items {10     private int id;// 商品id11     private String item_name;// 商品名称12     private Float item_price;// 商品价格13     private String item_detail;// 商品明细14 15     public int getId() {16         return id;17     }18 19     public void setId(int id) {20         this.id = id;21     }22 23     public String getItem_name() {24         return item_name;25     }26 27     public void setItem_name(String item_name) {28         this.item_name = item_name;29     }30 31     public Float getItem_price() {32         return item_price;33     }34 35     public void setItem_price(Float item_price) {36         this.item_price = item_price;37     }38 39     public String getItem_detail() {40         return item_detail;41     }42 43     public void setItem_detail(String item_detail) {44         this.item_detail = item_detail;45     }46 47 }

 1 package com.mybatis.bean; 2  3 /** 4  * 订单明细 5  *  6  * @author Thinkpad 7  * 8  */ 9 public class Orderdetail {10     private int id;// 主键11     private int orders_id;// 订单id12     private int item_id;// 商品id13     private int item_num;// 商品数量14     private Float item_price;// 商品价格15 16     // 商品信息17     private Items items;// 明细对应的商品信息18 19     public int getId() {20         return id;21     }22 23     public void setId(int id) {24         this.id = id;25     }26 27     public int getOrders_id() {28         return orders_id;29     }30 31     public void setOrders_id(int orders_id) {32         this.orders_id = orders_id;33     }34 35     public int getItem_id() {36         return item_id;37     }38 39     public void setItem_id(int item_id) {40         this.item_id = item_id;41     }42 43     public int getItem_num() {44         return item_num;45     }46 47     public void setItem_num(int item_num) {48         this.item_num = item_num;49     }50 51     public Float getItem_price() {52         return item_price;53     }54 55     public void setItem_price(Float item_price) {56         this.item_price = item_price;57     }58 59     public Items getItems() {60         return items;61     }62 63     public void setItems(Items items) {64         this.items = items;65     }66 67 }

 1 package com.mybatis.bean; 2  3 import java.util.List; 4  5 /** 6  * 订单信息 7  *  8  * @author Thinkpad 9  *10  */11 public class Orders {12     private int id;// 订单id13     private int user_id;// 用户id14     private String order_number;// 订单号15 16     private User user;// 用户信息17 18     private List<Orderdetail> orderdetails;// 订单明细信息19 20     public int getId() {21         return id;22     }23 24     public void setId(int id) {25         this.id = id;26     }27 28     public int getUser_id() {29         return user_id;30     }31 32     public void setUser_id(int user_id) {33         this.user_id = user_id;34     }35 36     public String getOrder_number() {37         return order_number;38     }39 40     public void setOrder_number(String order_number) {41         this.order_number = order_number;42     }43 44     public User getUser() {45         return user;46     }47 48     public void setUser(User user) {49         this.user = user;50     }51 52     public List<Orderdetail> getOrderdetails() {53         return orderdetails;54     }55 56     public void setOrderdetails(List<Orderdetail> orderdetails) {57         this.orderdetails = orderdetails;58     }59 60 }

 1 package com.mybatis.bean; 2  3 public class OrdersCustom extends User { 4  5     private String user_id;// 用户id 6     private String order_number;// 订单号 7  8     public String getUser_id() { 9         return user_id;10     }11 12     public void setUser_id(String user_id) {13         this.user_id = user_id;14     }15 16     public String getOrder_number() {17         return order_number;18     }19 20     public void setOrder_number(String order_number) {21         this.order_number = order_number;22     }23 24 }

 1 package com.mybatis.bean; 2  3 import java.util.Date; 4  5 public class OrderUserCustom extends Orders { 6  7     private String username; 8     private Date birthday; 9     public String getUsername() {10         return username;11     }12     public void setUsername(String username) {13         this.username = username;14     }15     public Date getBirthday() {16         return birthday;17     }18     public void setBirthday(Date birthday) {19         this.birthday = birthday;20     }21 22 }

 1 package com.mybatis.bean; 2  3 import java.util.Date; 4 import java.util.List; 5  6 public class User implements java.io.Serializable { 7     private int id; 8     private int[] ids;// 存储多个id 9     private String username;// 用户姓名10     private String sex;// 性别11     private Date birthday;// 出生日期12     private String address;// 地址13     private String detail;// 详细信息14     private Float score;// 成绩15 16     // 订单信息17     private List<Orders> orders;18 19     public int getId() {20         return id;21     }22 23     public void setId(int id) {24         this.id = id;25     }26 27     public String getUsername() {28         return username;29     }30 31     public void setUsername(String username) {32         this.username = username;33     }34 35     public String getSex() {36         return sex;37     }38 39     public void setSex(String sex) {40         this.sex = sex;41     }42 43     public Date getBirthday() {44         return birthday;45     }46 47     public void setBirthday(Date birthday) {48         this.birthday = birthday;49     }50 51     public String getAddress() {52         return address;53     }54 55     public void setAddress(String address) {56         this.address = address;57     }58 59     public String getDetail() {60         return detail;61     }62 63     public void setDetail(String detail) {64         this.detail = detail;65     }66 67     public Float getScore() {68         return score;69     }70 71     public void setScore(Float score) {72         this.score = score;73     }74 75     @Override76     public String toString() {77         return "User [id=" + id + ", username=" + username + ", sex=" + sex78                 + ", birthday=" + birthday + ", address=" + address79                 + ", detail=" + detail + ", score=" + score + "]";80     }81 82     public List<Orders> getOrders() {83         return orders;84     }85 86     public void setOrders(List<Orders> orders) {87         this.orders = orders;88     }89 90     public int[] getIds() {91         return ids;92     }93 94     public void setIds(int[] ids) {95         this.ids = ids;96     }97 98 }