1.mybatis02:
mybatis-config.xml:
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration> <!-- 加载属性文件 --> <properties resource="db.properties"/> <environments default="development"> <environment id="development"> <!-- 使用jdbc中的事务 --> <transactionManager type="JDBC"></transactionManager> <!-- 配置数据源 --> <dataSource type="POOLED"> <property name="driver" value="${driver}"/> <property name="url" value="${url}"/> <property name="username" value="${username}"/> <property name="password" value="${password}"/> </dataSource> </environment> </environments> <!-- 引入映射文件 --> <mappers> <!-- <mapper resource="cn/zzsxt/mapper/TbUserMapper.xml"/> --> <!-- 注意:此种方法要求mapper接口和mapper映射文件要名称相同,且放到同一个目录下; --> <!-- <mapper class="cn.zzsxt.mapper.TbUserMapper"/> --> <!-- 注意:此种方法要求mapper接口和mapper映射文件要名称相同,且放到同一个目录下; --> <package name="cn.zzsxt.mapper"/> </mappers> </configuration>
log4j.properties:
#Console
log4j.appender.Console=org.apache.log4j.ConsoleAppender
log4j.appender.Console.layout=org.apache.log4j.PatternLayout
log4j.appender.Console.layout.ConversionPattern=%d [%t] %-5p [%c] - %m%n
log4j.logger.org.apache.ibatis=DEBUG
#log4j.rootLogger=warn,appender1
#log4j.appender.appender1=org.apache.log4j.ConsoleAppender
#log4j.appender.appender1.layout=org.apache.log4j.SimpleLayout
db.properties:
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test
username=root
password=root
TbUser.java:
package cn.zzsxt.entity; import java.io.Serializable; import java.util.Date; public class TbUser implements Serializable{ private long id; private String username; private String sex; private Date birthday; private String address; public long getId() { return id; } public void setId(long id) { this.id = id; } public String getUsername() { return username; } public void setUsername(String username) { this.username = username; } public String getSex() { return sex; } public void setSex(String sex) { this.sex = sex; } public Date getBirthday() { return birthday; } public void setBirthday(Date birthday) { this.birthday = birthday; } public String getAddress() { return address; } public void setAddress(String address) { this.address = address; } @Override public String toString() { return "TbUser [id=" + id + ", username=" + username + ", sex=" + sex + ", birthday=" + birthday + ", address=" + address + "]"; } }
TbUserMapper.xml:
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <!-- 1、 mapper接口的全限定名要和mapper映射文件的namespace值一致。 2、 mapper接口的方法名称要和mapper映射文件的statement的id一致。 3、 mapper接口的方法参数类型要和mapper映射文件的statement的parameterType的值一致,而且它的参数是一个。 4、 mapper接口的方法返回值类型要和mapper映射文件的statement的resultType的值一致。 --> <mapper namespace="cn.zzsxt.mapper.TbUserMapper"> <!-- 1.需求根据用户名称模糊查询 2.思路:模糊查询返回结果可能是多条纪录 3.resultType:指定就是单条记录所映射成java对象类型 4.${}:表示拼接sql串,将接收到参数的内容不加任何修饰拼接在sql中 5.使用${}容易引起sql注入 6.${value}:接受输入参数的内容,如果传入类型是简单类型,${}中只能用value --> <select id="findUserByName" parameterType="java.lang.String" resultType="cn.zzsxt.entity.TbUser"> select * from tb_user where username like '%${value}%' </select> <select id="findUserById" parameterType="java.lang.Long" resultType="cn.zzsxt.entity.TbUser"> select * from tb_user where id=#{id} </select> <insert id="addUser" parameterType="cn.zzsxt.entity.TbUser"> insert into tb_user(username,sex,birthday,address) values(#{username},#{sex},#{birthday},#{address}) </insert> </mapper>
TbUserMapper.java:
package cn.zzsxt.mapper; import java.util.List; import java.util.Map; import cn.zzsxt.entity.TbUser; import cn.zzsxt.vo.TbUserQuery; public interface TbUserMapper { List<TbUser> findUserByName(String username); TbUser findUserById(long id); int addUser(TbUser user); /** * 查询用户信息,要求对用户名进行模糊查找,对性别进行精确查找 * @return */ List<TbUser> findUserByNameAndSexVo(TbUserQuery userQuery); /** * 查询用户信息,要求对用户名进行模糊查找,对性别进行精确查找 * @param map * @return */ List<TbUser> findUserByNameAndSexMap(Map<String,Object> map); /** * 查询用户信息,要求对用户名进行模糊查找,对性别进行精确查找 * @param user * @return */ List<TbUser> findUserByAddressAndSex(TbUser user); /** * 查询用户表中总记录数 * @return */ int getCount(); /** * 使用resultMap封装结果,根据用户编号查询用户信息, * @param id * @return */ TbUser findUserByResultMap(long id); /** * 查询部分列 * @param id * @return */ TbUser findUserByResultType(long id); List<TbUser> findUserList(TbUserQuery userQuery); }
TbUserMapper.xml:
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <!-- 1、 mapper接口的全限定名要和mapper映射文件的namespace值一致。 2、 mapper接口的方法名称要和mapper映射文件的statement的id一致。 3、 mapper接口的方法参数类型要和mapper映射文件的statement的parameterType的值一致,而且它的参数是一个。 4、 mapper接口的方法返回值类型要和mapper映射文件的statement的resultType的值一致。 --> <mapper namespace="cn.zzsxt.mapper.TbUserMapper"> <!-- 1.需求根据用户名称模糊查询 2.思路:模糊查询返回结果可能是多条纪录 3.resultType:指定就是单条记录所映射成java对象类型 4.${}:表示拼接sql串,将接收到参数的内容不加任何修饰拼接在sql中 5.使用${}容易引起sql注入 6.${value}:接受输入参数的内容,如果传入类型是简单类型,${}中只能用value --> <select id="findUserByName" parameterType="java.lang.String" resultType="cn.zzsxt.entity.TbUser"> select * from tb_user where username like '%${value}%' </select> <select id="findUserById" parameterType="java.lang.Long" resultType="cn.zzsxt.entity.TbUser"> select * from tb_user where id=#{id} </select> <insert id="addUser" parameterType="cn.zzsxt.entity.TbUser"> insert into tb_user(username,sex,birthday,address) values(#{username},#{sex},#{birthday},#{address}) </insert> <!-- 根据用户名和性别查询 --> <select id="findUserByNameAndSexVo" parameterType="cn.zzsxt.vo.TbUserQuery" resultType="cn.zzsxt.entity.TbUser"> select * from tb_user where username like '%${user.username}%' and sex=#{user.sex} </select> <!-- 根据用户名和性别查询,参数为map --> <select id="findUserByNameAndSexMap" parameterType="java.util.HashMap" resultType="cn.zzsxt.entity.TbUser"> select * from tb_user where username like '%${uname}%' and sex=#{sex} </select> <!-- 根据用户名和性别查询 --> <select id="findUserByAddressAndSex" parameterType="cn.zzsxt.entity.TbUser" resultType="cn.zzsxt.entity.TbUser"> select * from tb_user where address like '%${address}%' and sex=#{sex} </select> <!-- 查询总记录数 --> <select id="getCount" resultType="java.lang.Integer"> select count(*) from tb_user </select> <!-- 定义resultMap:解决字段名称与属性名称不一致的问题。 将SELECT id id_,username username_, sex sex_ FROM tb_user WHERE id = 1 和 User类中的属性作一个映射关系 type:resultMap最终映射的java对象类型,可以使用别名 id:对resultMap的唯一标识 --> <resultMap id="userResultMap" type="cn.zzsxt.entity.TbUser"> <id column="id_" property="id"/> <result column="username_" property="username"/> <result column="sex_" property="sex"/> <result column="birthday" property="birthday"/> <result column="address" property="address"/> </resultMap> <select id="findUserByResultMap" parameterType="java.lang.Long" resultMap="userResultMap"> select id id_,username username_,sex sex_,birthday,address from tb_user where id=#{id} </select> <!-- 查询部分列 --> <select id="findUserByResultType" parameterType="java.lang.Long" resultType="cn.zzsxt.entity.TbUser"> select id,username,sex from tb_user where id=#{id} </select> <!-- 动态SQL --> <select id="findUserList" parameterType="cn.zzsxt.vo.TbUserQuery" resultType="cn.zzsxt.entity.TbUser"> select * from tb_user <where> <!-- <if test="user.address!=null and user.address!=''"> --> <!-- and address like'%${user.address}%' --> <!-- </if> --> <!-- <if test="user.sex!=null and user.sex!=''"> --> <!-- and sex=#{user.sex} --> <!-- </if> --> <!-- 引入SQL片段 --> <include refid="where_address_sex"></include> </where> </select> <!-- SQL片段:提高sql语句的复用性 --> <sql id="where_address_sex"> <if test="user.address!=null and user.address!=''"> and address like'%${user.address}%' </if> <if test="user.sex!=null and user.sex!=''"> and sex=#{user.sex} </if> <if test="ids!=null"> <!-- 使用foreach遍历传入ids collection:指定输入对象中集合属性 item:每次遍历生成对象 open:开始遍历时拼接的串 close:结束遍历时拼接的串 separator分隔符:遍历的两个对象中需要拼接的串 --> <foreach collection="ids" item="user_id" open="and id in (" close=")" separator=","> #{user_id} </foreach> </if> </sql> </mapper>
TbUserQuery.java:
package cn.zzsxt.vo; import java.util.List; import cn.zzsxt.entity.TbUser; public class TbUserQuery { private TbUser user; //用户编号 private List<Long> ids; public TbUser getUser() { return user; } public void setUser(TbUser user) { this.user = user; } public List<Long> getIds() { return ids; } public void setIds(List<Long> ids) { this.ids = ids; } }
TestTbUserMapper.java:
package cn.zzsxt.test; import java.io.InputStream; import java.util.Date; import java.util.HashMap; import java.util.List; import java.util.Map; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; import org.junit.Test; import cn.zzsxt.entity.TbUser; import cn.zzsxt.mapper.TbUserMapper; import cn.zzsxt.vo.TbUserQuery; public class TestTbUserMapper { @Test public void testFindUserByName(){ InputStream ips = this.getClass().getResourceAsStream("/mybatis-config.xml"); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(ips); SqlSession sqlSession=sqlSessionFactory.openSession(); //获取TbUserMapper生成的代理(通过jdk的动态代理实现的) TbUserMapper tbUserMapper = sqlSession.getMapper(TbUserMapper.class); List<TbUser> list = tbUserMapper.findUserByName("张"); for (TbUser tbUser : list) { System.out.println(tbUser); } sqlSession.close(); } @Test public void testFindUserById(){ InputStream ips = this.getClass().getResourceAsStream("/mybatis-config.xml"); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(ips); SqlSession sqlSession=sqlSessionFactory.openSession(); //获取TbUserMapper生成的代理(通过jdk的动态代理实现的) TbUserMapper tbUserMapper = sqlSession.getMapper(TbUserMapper.class); TbUser user = tbUserMapper.findUserById(1); System.out.println(user); sqlSession.close(); } @Test public void testAddUser(){ InputStream ips = this.getClass().getResourceAsStream("/mybatis-config.xml"); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(ips); SqlSession sqlSession=sqlSessionFactory.openSession(); //获取TbUserMapper生成的代理(通过jdk的动态代理实现的) TbUserMapper tbUserMapper = sqlSession.getMapper(TbUserMapper.class); TbUser user = new TbUser(); user.setUsername("王五"); user.setSex("女"); user.setBirthday(new Date()); user.setAddress("郑州"); int count = tbUserMapper.addUser(user); sqlSession.commit(); System.out.println(count); sqlSession.close(); } @Test public void testFindUserByNameAndSexVo(){ InputStream ips = this.getClass().getResourceAsStream("/mybatis-config.xml"); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(ips); SqlSession sqlSession=sqlSessionFactory.openSession(); //获取TbUserMapper生成的代理(通过jdk的动态代理实现的) TbUserMapper tbUserMapper = sqlSession.getMapper(TbUserMapper.class); TbUserQuery userQuery = new TbUserQuery(); TbUser user = new TbUser(); user.setUsername("五"); user.setSex("男"); userQuery.setUser(user); List<TbUser> list= tbUserMapper.findUserByNameAndSexVo(userQuery); for (TbUser tbUser : list) { System.out.println(tbUser); } sqlSession.close(); } @Test public void testFindUserByNameAndSexMap(){ InputStream ips = this.getClass().getResourceAsStream("/mybatis-config.xml"); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(ips); SqlSession sqlSession=sqlSessionFactory.openSession(); //获取TbUserMapper生成的代理(通过jdk的动态代理实现的) TbUserMapper tbUserMapper = sqlSession.getMapper(TbUserMapper.class); Map<String,Object> map = new HashMap<String,Object>(); map.put("uname", "五"); map.put("sex", "男"); List<TbUser> list= tbUserMapper.findUserByNameAndSexMap(map); for (TbUser tbUser : list) { System.out.println(tbUser); } sqlSession.close(); } @Test public void testFindUserByAddressAndSex(){ InputStream ips = this.getClass().getResourceAsStream("/mybatis-config.xml"); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(ips); SqlSession sqlSession=sqlSessionFactory.openSession(); //获取TbUserMapper生成的代理(通过jdk的动态代理实现的) TbUserMapper tbUserMapper = sqlSession.getMapper(TbUserMapper.class); TbUser user = new TbUser(); user.setAddress("安阳"); user.setSex("男"); List<TbUser> list= tbUserMapper.findUserByAddressAndSex(user); for (TbUser tbUser : list) { System.out.println(tbUser); } sqlSession.close(); } }
TestTbUserMapper2.java:
package cn.zzsxt.test; import java.io.InputStream; import java.util.ArrayList; import java.util.List; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; import org.junit.Test; import cn.zzsxt.entity.TbUser; import cn.zzsxt.mapper.TbUserMapper; import cn.zzsxt.vo.TbUserQuery; public class TestTbUserMapper2 { @Test public void testGetCount(){ InputStream ips = this.getClass().getResourceAsStream("/mybatis-config.xml"); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(ips); SqlSession sqlSession=sqlSessionFactory.openSession(); //获取TbUserMapper生成的代理(通过jdk的动态代理实现的) TbUserMapper tbUserMapper = sqlSession.getMapper(TbUserMapper.class); int count = tbUserMapper.getCount(); System.out.println("总记录数为:"+count); sqlSession.close(); } @Test public void testFindUserByResultMap(){ InputStream ips = this.getClass().getResourceAsStream("/mybatis-config.xml"); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(ips); SqlSession sqlSession=sqlSessionFactory.openSession(); //获取TbUserMapper生成的代理(通过jdk的动态代理实现的) TbUserMapper tbUserMapper = sqlSession.getMapper(TbUserMapper.class); TbUser user = tbUserMapper.findUserByResultMap(1L); System.out.println(user); sqlSession.close(); } @Test public void testFindUserByResultType(){ InputStream ips = this.getClass().getResourceAsStream("/mybatis-config.xml"); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(ips); SqlSession sqlSession=sqlSessionFactory.openSession(); //获取TbUserMapper生成的代理(通过jdk的动态代理实现的) TbUserMapper tbUserMapper = sqlSession.getMapper(TbUserMapper.class); TbUser user = tbUserMapper.findUserByResultType(1L); System.out.println(user); sqlSession.close(); } /** * 动态SQL */ @Test public void testFindUserList(){ InputStream ips = this.getClass().getResourceAsStream("/mybatis-config.xml"); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(ips); SqlSession sqlSession=sqlSessionFactory.openSession(); //获取TbUserMapper生成的代理(通过jdk的动态代理实现的) TbUserMapper tbUserMapper = sqlSession.getMapper(TbUserMapper.class); TbUserQuery userQuery = new TbUserQuery(); TbUser user = new TbUser(); //user.setAddress("安阳"); //user.setSex("男"); userQuery.setUser(user); //此处若不传值,报错!!!!!! //foreach测试 List<Long> ids = new ArrayList<Long>(); ids.add(1L); ids.add(2L); ids.add(3L); userQuery.setIds(ids); List<TbUser> list = tbUserMapper.findUserList(userQuery); for (TbUser tbUser : list) { System.out.println(tbUser); } sqlSession.close(); } }
2.mybatis03:
mybatis-config.xml:
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration> <!-- 加载属性文件 --> <properties resource="db.properties"/> <environments default="development"> <environment id="development"> <!-- 使用jdbc中的事务 --> <transactionManager type="JDBC"></transactionManager> <!-- 配置数据源 --> <dataSource type="POOLED"> <property name="driver" value="${driver}"/> <property name="url" value="${url}"/> <property name="username" value="${username}"/> <property name="password" value="${password}"/> </dataSource> </environment> </environments> <!-- 引入映射文件 --> <mappers> <!-- <mapper resource="cn/zzsxt/mapper/TbUserMapper.xml"/> --> <!-- 注意:此种方法要求mapper接口和mapper映射文件要名称相同,且放到同一个目录下; --> <!-- <mapper class="cn.zzsxt.mapper.TbUserMapper"/> --> <!-- 注意:此种方法要求mapper接口和mapper映射文件要名称相同,且放到同一个目录下; --> <package name="cn.zzsxt.mapper"/> </mappers> </configuration>
db.properties:
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mybatis
username=root
password=root
log4j.properties:
#Console
log4j.appender.Console=org.apache.log4j.ConsoleAppender
log4j.appender.Console.layout=org.apache.log4j.PatternLayout
log4j.appender.Console.layout.ConversionPattern=%d [%t] %-5p [%c] - %m%n
log4j.logger.org.apache.ibatis=DEBUG
#log4j.rootLogger=warn,appender1
#log4j.appender.appender1=org.apache.log4j.ConsoleAppender
#log4j.appender.appender1.layout=org.apache.log4j.SimpleLayout
TbGoods.java:
package cn.zzsxt.entity; import java.util.Date; public class TbGoods { private long id; private String name; private double price; private Date create_time; private String detail; public long getId() { return id; } public void setId(long id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public double getPrice() { return price; } public void setPrice(double price) { this.price = price; } public Date getCreate_time() { return create_time; } public void setCreate_time(Date create_time) { this.create_time = create_time; } public String getDetail() { return detail; } public void setDetail(String detail) { this.detail = detail; } @Override public String toString() { return "TbGoods [id=" + id + ", name=" + name + ", price=" + price + ", create_time=" + create_time + ", detail=" + detail + "]"; } }
TbOrder.java:
package cn.zzsxt.entity; import java.util.Date; import java.util.List; public class TbOrder { private long id; private long user_id; private String order_number; private Date create_time; private String detail; //订单与订单明细:一对多 private List<TbOrderDetail> orderDetailList; public long getId() { return id; } public void setId(long id) { this.id = id; } public long getUser_id() { return user_id; } public void setUser_id(long user_id) { this.user_id = user_id; } public String getOrder_number() { return order_number; } public void setOrder_number(String order_number) { this.order_number = order_number; } public Date getCreate_time() { return create_time; } public void setCreate_time(Date create_time) { this.create_time = create_time; } public String getDetail() { return detail; } public void setDetail(String detail) { this.detail = detail; } public List<TbOrderDetail> getOrderDetailList() { return orderDetailList; } public void setOrderDetailList(List<TbOrderDetail> orderDetailList) { this.orderDetailList = orderDetailList; } @Override public String toString() { return "TbOrder [id=" + id + ", user_id=" + user_id + ", order_number=" + order_number + ", create_time=" + create_time + ", detail=" + detail + "]"; } }
TbOrderDetail.java:
package cn.zzsxt.entity; public class TbOrderDetail { private long id; private long order_id; private long goods_id; private long goods_num; //订单明细与商品:一对一 private TbGoods goods; public long getId() { return id; } public void setId(long id) { this.id = id; } public long getOrder_id() { return order_id; } public void setOrder_id(long order_id) { this.order_id = order_id; } public long getGoods_id() { return goods_id; } public void setGoods_id(long goods_id) { this.goods_id = goods_id; } public long getGoods_num() { return goods_num; } public void setGoods_num(long goods_num) { this.goods_num = goods_num; } public TbGoods getGoods() { return goods; } public void setGoods(TbGoods goods) { this.goods = goods; } @Override public String toString() { return "TbOrderDetail [id=" + id + ", order_id=" + order_id + ", goods_id=" + goods_id + ", goods_num=" + goods_num + "]"; } }
TbUser.java:
package cn.zzsxt.entity; import java.io.Serializable; import java.util.Date; import java.util.List; public class TbUser implements Serializable{ private long id; private String username; private String sex; private Date birthday; private String address; //用户与订单:一对多 private List<TbOrder> orderList; public long getId() { return id; } public void setId(long id) { this.id = id; } public String getUsername() { return username; } public void setUsername(String username) { this.username = username; } public String getSex() { return sex; } public void setSex(String sex) { this.sex = sex; } public Date getBirthday() { return birthday; } public void setBirthday(Date birthday) { this.birthday = birthday; } public String getAddress() { return address; } public void setAddress(String address) { this.address = address; } public List<TbOrder> getOrderList() { return orderList; } public void setOrderList(List<TbOrder> orderList) { this.orderList = orderList; } @Override public String toString() { return "TbUser [id=" + id + ", username=" + username + ", sex=" + sex + ", birthday=" + birthday + ", address=" + address + "]"; } }
TbOrderQuery01.java:
package cn.zzsxt.vo; import cn.zzsxt.entity.TbOrder; public class TbOrderQuery01 extends TbOrder { private String username; private String sex; public String getUsername() { return username; } public void setUsername(String username) { this.username = username; } public String getSex() { return sex; } public void setSex(String sex) { this.sex = sex; } @Override public String toString() { return "TbOrderQuery01 [username=" + username + ", sex=" + sex + "]"+super.toString(); } }
TbOrderQuery02.java:
package cn.zzsxt.vo; import cn.zzsxt.entity.TbOrder; import cn.zzsxt.entity.TbUser; public class TbOrderQuery02 extends TbOrder { private TbUser user; public TbUser getUser() { return user; } public void setUser(TbUser user) { this.user = user; } @Override public String toString() { return "TbOrderQuery02:"+super.toString()+" [user=" + user + "]"; } }
TbOrderQuery03.java:
package cn.zzsxt.vo; import java.util.List; import cn.zzsxt.entity.TbOrder; import cn.zzsxt.entity.TbOrderDetail; import cn.zzsxt.entity.TbUser; public class TbOrderQuery03 extends TbOrder { //订单与用户:一对一 private TbUser user; //订单与订单明细:一对多 private List<TbOrderDetail> orderDetailList; public TbUser getUser() { return user; } public void setUser(TbUser user) { this.user = user; } public List<TbOrderDetail> getOrderDetailList() { return orderDetailList; } public void setOrderDetailList(List<TbOrderDetail> orderDetailList) { this.orderDetailList = orderDetailList; } @Override public String toString() { return "TbOrderQuery03 [user=" + user + ", orderDetailList=" + orderDetailList + ", toString()=" + super.toString() + "]"; } }
TbOrderMapper.java:
package cn.zzsxt.mapper; import java.util.List; import cn.zzsxt.entity.TbUser; import cn.zzsxt.vo.TbOrderQuery01; import cn.zzsxt.vo.TbOrderQuery02; import cn.zzsxt.vo.TbOrderQuery03; public interface TbOrderMapper { /** * 查询订单信息,关联用户信息 * @return */ List<TbOrderQuery01> findOrderAndUser(); /** * 查询订单信息,关联用户信息,将查询结果封装成自定义的resultMap中 * @return */ List<TbOrderQuery02> findOrderAndUserResultMap(); /** * 查询订单信息,关联用户信息和订单明细 * @return */ List<TbOrderQuery03> findOrderAndDetailAndUser(); /** * 查询用户信息,关联订单信息,订单明细和商品信息 * @return */ List<TbUser> findUserAndGoods(); }
TbOrderMapper.xml
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="cn.zzsxt.mapper.TbOrderMapper"> <!-- 查询订单信息关联用户信息 一对一 --> <select id="findOrderAndUser" resultType="cn.zzsxt.vo.TbOrderQuery01"> SELECT tb_order.id, tb_order.user_id, tb_order.order_number, tb_user.username, tb_user.sex FROM tb_order, tb_user WHERE tb_order.user_id = tb_user.id </select> <!-- 自定义resultMap --> <resultMap type="cn.zzsxt.vo.TbOrderQuery02" id="orderAndUserResultMap"> <id column="order_id" property="id"/> <result column="user_id" property="user_id"/> <result column="order_number" property="order_number"/> <result column="detail" property="detail"/> <!-- 一对一关联 --> <association property="user" javaType="cn.zzsxt.entity.TbUser"> <id column="id" property="id"/> <result column="username" property="username"/> <result column="sex" property="sex"/> <result column="birthday" property="birthday"/> <result column="address" property="address"/> </association> </resultMap> <select id="findOrderAndUserResultMap" resultMap="orderAndUserResultMap"> SELECT tb_order.id order_id, tb_order.user_id, tb_order.order_number, tb_order.detail, tb_user.username, tb_user.sex, tb_user.birthday, tb_user.address FROM tb_order, tb_user WHERE tb_order.user_id = tb_user.id </select> <!-- 自定义resultMap --> <resultMap type="cn.zzsxt.vo.TbOrderQuery03" id="orderAndDetailAndUserResultMap"> <id column="order_id" property="id"/> <result column="user_id" property="user_id"/> <result column="order_number" property="order_number"/> <result column="create_time" property="create_time"/> <result column="detail" property="detail"/> <!-- 一对一关联 --> <association property="user" javaType="cn.zzsxt.entity.TbUser"> <id column="id" property="id"/> <result column="username" property="username"/> <result column="sex" property="sex"/> <result column="birthday" property="birthday"/> <result column="address" property="address"/> </association> <!-- 一对多关联 --> <collection property="orderDetailList" ofType="cn.zzsxt.entity.TbOrderDetail"> <id column="detail_id" property="id"/> <result column="goods_id" property="goods_id"/> <result column="goods_num" property="goods_num"/> </collection> </resultMap> <select id="findOrderAndDetailAndUser" resultMap="orderAndDetailAndUserResultMap"> SELECT tb_order.id order_id, tb_order.user_id, tb_order.order_number, tb_order.create_time, tb_order.detail, tb_user.username, tb_user.sex, tb_user.birthday, tb_user.address, tb_order_detail.id detail_id, tb_order_detail.goods_id, tb_order_detail.goods_num FROM tb_order, tb_user, tb_order_detail WHERE tb_order.user_id = tb_user.id AND tb_order.id = tb_order_detail.order_id </select> <!-- 多对多 --> <resultMap type="cn.zzsxt.entity.TbUser" id="userAndGoodsResultMap"> <id column="user_id" property="id"/> <result column="username" property="username"/> <result column="sex" property="sex"/> <result column="birthday" property="birthday"/> <result column="address" property="address"/> <!-- 一对多 --> <collection property="orderList" ofType="cn.zzsxt.entity.TbOrder"> <id column="order_id" property="id"/> <result column="order_number" property="order_number"/> <result column="create_time" property="create_time"/> <result column="detail" property="detail"/> <!-- 一对多 --> <collection property="orderDetailList" ofType="cn.zzsxt.entity.TbOrderDetail"> <id column="detail_id" property="id"/> <result column="goods_id" property="goods_id"/> <result column="goods_num" property="goods_num"/> <!-- 一对一 --> <association property="goods" javaType="cn.zzsxt.entity.TbGoods"> <result column="name" property="name"/> <result column="price" property="price"/> <result column="goods_create_time" property="create_time"/> <result column="goods_detail" property="detail"/> </association> </collection> </collection> </resultMap> <select id="findUserAndGoods" resultMap="userAndGoodsResultMap"> SELECT tb_user.id user_id, tb_user.username, tb_user.sex, tb_user.birthday, tb_user.address, tb_order.id order_id, tb_order.order_number, tb_order.create_time, tb_order.detail, tb_order_detail.id detail_id, tb_order_detail.goods_id, tb_order_detail.goods_num, tb_goods.name, tb_goods.price, tb_goods.create_time goods_create_time, tb_goods.detail goods_detail FROM tb_order, tb_user, tb_order_detail, tb_goods WHERE tb_order.user_id = tb_user.id AND tb_order.id = tb_order_detail.order_id AND tb_order_detail.goods_id = tb_goods.id </select> </mapper>
TestOrderMapper.java:
package cn.zzsxt.test; import java.io.InputStream; import java.util.List; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; import org.junit.Test; import cn.zzsxt.entity.TbGoods; import cn.zzsxt.entity.TbOrder; import cn.zzsxt.entity.TbOrderDetail; import cn.zzsxt.entity.TbUser; import cn.zzsxt.mapper.TbOrderMapper; import cn.zzsxt.vo.TbOrderQuery01; import cn.zzsxt.vo.TbOrderQuery02; import cn.zzsxt.vo.TbOrderQuery03; public class TestOrderMapper { @Test public void testFindOrderAndUser(){ InputStream ips = this.getClass().getResourceAsStream("/mybatis-config.xml"); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(ips); SqlSession sqlSession = sqlSessionFactory.openSession(); TbOrderMapper orderMapper = sqlSession.getMapper(TbOrderMapper.class); List<TbOrderQuery01> list = orderMapper.findOrderAndUser(); for (TbOrderQuery01 tbOrderQuery01 : list) { System.out.println(tbOrderQuery01); } } @Test public void testFindOrderAndUserResultMap(){ InputStream ips = this.getClass().getResourceAsStream("/mybatis-config.xml"); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(ips); SqlSession sqlSession = sqlSessionFactory.openSession(); TbOrderMapper orderMapper = sqlSession.getMapper(TbOrderMapper.class); List<TbOrderQuery02> list = orderMapper.findOrderAndUserResultMap(); for (TbOrderQuery02 tbOrderQuery02 : list) { System.out.println(tbOrderQuery02); } } @Test public void testFindOrderAndDetailAndUser(){ InputStream ips = this.getClass().getResourceAsStream("/mybatis-config.xml"); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(ips); SqlSession sqlSession = sqlSessionFactory.openSession(); TbOrderMapper orderMapper = sqlSession.getMapper(TbOrderMapper.class); List<TbOrderQuery03> list = orderMapper.findOrderAndDetailAndUser(); for (TbOrderQuery03 tbOrderQuery03 : list) { System.out.println(tbOrderQuery03); } } @Test public void testFindUserAndGoods(){ InputStream ips = this.getClass().getResourceAsStream("/mybatis-config.xml"); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(ips); SqlSession sqlSession = sqlSessionFactory.openSession(); TbOrderMapper orderMapper = sqlSession.getMapper(TbOrderMapper.class); List<TbUser> list = orderMapper.findUserAndGoods(); for (TbUser tbUser : list) { System.out.println("用户信息:"+tbUser); List<TbOrder> orderList = tbUser.getOrderList();//获取订单信息 System.out.println("订单信息:"); for (TbOrder tbOrder : orderList) { System.out.println(tbOrder); System.out.println("订单明细:"); List<TbOrderDetail> orderDetailList = tbOrder.getOrderDetailList();//获取订单明细 for (TbOrderDetail tbOrderDetail : orderDetailList) { System.out.println(tbOrderDetail); //获取商品信息 TbGoods tbGoods = tbOrderDetail.getGoods(); System.out.println(tbGoods); } } } } }