MyBatis 延迟加载 的 一对多,多对一查询
Mybatis 的动态语句查询真是太优秀了,,为程序开发减轻了不少工作。
同样是框架mybatis 的腰键盘怎么这么突出
准备工作:为了测试方便搭建 JavaProject 工程
环境: - jdk 1.8 - myBatis 3.2.7
开始:
db.priperties 文件
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/ssh
user=root
password=root
log4j.properties 文件 (方便测试)
# Global logging configuration
#\u5728\u5f00\u53d1\u73af\u5883\u4e0b\u65e5\u5fd7\u7ea7\u522b\u8981\u8bbe\u7f6e\u6210DEBUG\uff0c\u751f\u4ea7\u73af\u5883\u8bbe\u7f6e\u6210info\u6216error
log4j.rootLogger=DEBUG, stdout
# Console output...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n
SqlMapConfig.xml 核心配置文件
SqlMapConfig.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"></properties>
<!-- 起别名 -->
<typeAliases>
<!-- 单个实体类起别名
<typeAlias type="top.vkiss.mybatis.pojo.Customer" alias="user"/>
-->
<!-- 批量 包下所有类 别名为:首字母小写的类名 -->
<package name="top.vkiss.mybatis.pojo"/>
</typeAliases>
<!-- 将来下面的配置交给spring接管 -->
<environments default="development">
<environment id="development">
<!-- 配置的事务信息 -->
<transactionManager type="JDBC" />
<!-- 配置数据源 -->
<dataSource type="POOLED">
<property name="driver" value="${driver}" />
<property name="url" value="${url}" />
<property name="username" value="${user}" />
<property name="password" value="${password}" />
</dataSource>
</environment>
</environments>
<!-- 加载映射文件 -->
<mappers>
<!-- 加载单个映射文件
<mapper resource="top/vkiss/mybatis/dao/UserMapper.xml"/>
-->
<!-- 类名加载单个映射文件 规则:映射文件与实体类名称一致 ,且在同包中
<mapper class="top.vkiss.mybatis.dao.UserMapper"/>
-->
<!-- 批量引入映射文件 规则:映射文件名和类名一致 ,且在同包中 -->
<package name="top.vkiss.mybatis.dao"/>
</mappers>
</configuration>
Stu.java 两个实体类
package top.vkiss.mybatis.pojo;
public class User {
private int uid;
private String uname;
private String uaddress;
public int getUid() {
return uid;
}
public void setUid(int uid) {
this.uid = uid;
}
public String getUname() {
return uname;
}
public void setUname(String uname) {
this.uname = uname;
}
public String getUaddress() {
return uaddress;
}
public void setUaddress(String uaddress) {
this.uaddress = uaddress;
}
@Override
public String toString() {
return "User [uid=" + uid + ", uname=" + uname + ", uaddress=" + uaddress + "]";
}
}
Customer.java 实体类
package top.vkiss.mybatis.pojo;
import java.io.Serializable;
public class Customer implements Serializable{
private Integer id;
private String name;
private String pwd;
private String address;
private String phone;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPwd() {
return pwd;
}
public void setPwd(String pwd) {
this.pwd = pwd;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
@Override
public String toString() {
return "Customer [id=" + id + ", name=" + name + ", pwd=" + pwd + ", address=" + address + ", phone=" + phone
+ "]";
}
public Customer(Integer id, String name, String pwd, String address, String phone) {
super();
this.id = id;
this.name = name;
this.pwd = pwd;
this.address = address;
this.phone = phone;
}
public Customer() {
super();
// TODO Auto-generated constructor stub
}
}
CAndU.java 实体类
package top.vkiss.mybatis.pojo;
public class CAndU {
private Customer cu;
private User user;
public Customer getCu() {
return cu;
}
public void setCu(Customer cu) {
this.cu = cu;
}
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
@Override
public String toString() {
return "CAndU [cu=" + cu + ", user=" + user + "]";
}
}
UserMapper.java dao层接口 (MyBatis 动态接口 接口 不屑接口实现类)
package top.vkiss.mybatis.dao;
import java.util.List;
import java.util.Map;
import top.vkiss.mybatis.pojo.CAndU;
import top.vkiss.mybatis.pojo.User;
public interface UserMapper {
public void addUser(User user);
public int queryAllConut();
public List<User> queryAllByMap(Map map);
public List<User> queryAllByNameAndAddress(User uesr);
public List<User> queryAllCAndU(CAndU cu);
public List<CAndU> queryAllByCAndUMap(CAndU cu);
public List<User> queryUserByEXAM(User user);
public List<User> getUserAllByIdArray(int[] ids);
public void updateUserByEXAM(User user);
}
UserMapper.xml dao层接口映射文件 (所有 sql 语句 都在这)
<?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="top.vkiss.mybatis.dao.UserMapper">
<!-- 添加 -->
<insert id="addUser" parameterType="user">
<!-- 添加之后可以获取到主键 MYSQL中order:AFTER ORACLE中:BEFORE 必须有返回值 -->
<selectKey keyProperty="uid" order="AFTER" resultType="int">
select LAST_INSERT_ID();
</selectKey>
insert into t_user (uname,uaddress) values(#{uname},#{uaddress})
</insert>
<!-- 查询 -->
<select id="queryAllByNameAndAddress" parameterType="user" resultType="user">
select uid,uname,uaddress from t_user where uname=#{uname} and uaddress=#{uaddress}
</select>
<!-- 查询两张表 -->
<select id="queryAllCAndU" parameterType="candU" resultType="user">
select u.uid uid,u.uname uname,u.uaddress uaddress from t_user u,t_customer c where u.uname=#{user.uname} and c.name=#{cu.name}
</select>
<!-- 查询两张表 返回值用 resultMap -->
<resultMap type="candU" id="CAndUMap">
<!-- 表示的是 结果集唯一 约束 - column :表示的 查询结果的 列明 -property :表示的是 实体类中的属性名
<id column="uid" property="id"/> -->
<!-- 查询出来的普通属性 -->
<result column="uid" property="user.uid"/>
<result column="uname" property="user.uname"/>
<result column="uaddress" property="user.uaddress"/>
<result column="cid" property="cu.id"/>
<result column="cname" property="cu.name"/>
</resultMap>
<select id="queryAllByCAndUMap" parameterType="candU" resultMap="CAndUMap">
select u.uid uid,u.uname uname,u.uaddress uaddress,c.id cid,c.name cname,c.address caddress,c.id cid,c.phone cphone,c.pwd cpwd from t_user u,t_customer c where u.uname='张三' and c.name='888'
</select>
<!-- 删除 -->
<select id="delUser" parameterType="int">
delete from t_user where uid=#{1}
</select>
<!-- 修改 By EXAM -->
<update id="updateUserByEXAM" parameterType="user">
update t_user
<set>
<include refid="userSetEXAM"></include>
</set>
<where>
uid=#{uid}
</where>
</update>
<!-- 查询所有 -->
<select id="queryUser" resultType="user">
select uid,uname,upwd from t_user
</select>
<!-- 查询所有 By EXAM 多条件,不唯一查询
wher标签可以去除第一个 and
-->
<select id="queryUserByEXAM" parameterType="user" resultType="user">
select uid,uname,uaddress from t_user
<where>
<include refid="userEXAM"></include>
</where>
</select>
<!-- 查询条数 -->
<select id="queryAllConut" resultType="int">
select count(*) from t_user
</select>
<!-- 模糊查 -->
<select id="getUserAllByLike" resultType="user">
select id,name,pwd from t_user where name like "%"#{value}"%"
</select>
<!-- 关键字 in 查询 -->
<select id="getUserAllByIdArray" resultType="user">
select uid,uname,uaddress from t_user
<where>
uid in
<foreach collection="array" item="uid" open="(" close=")" separator=",">#{uid}</foreach>
</where>
</select>
<!-- 代码片段 提高重用性 id唯一
使用方 法:<include refid="指向代码片段ID"></include> -->
<sql id="userEXAM">
<if test="uid!=null and uid!=''">and uid = #{uid}</if>
<if test="uname!=null and uname!=''">and uname = #{uname}</if>
<if test="uaddress!=null and uaddress!=''">and uaddress = #{uaddress}</if>
</sql>
<sql id="userSetEXAM">
<if test="uid!=null and uid!=''">uid = #{uid},</if>
<if test="uname!=null and uname!=''">uname = #{uname},</if>
<if test="uaddress!=null and uaddress!=''">uaddress = #{uaddress},</if>
</sql>
</mapper>
MyBatiesUtils.java (方便测试,获取SqlSessionFactory 的工具类)
package top.vkiss.mybatis.util;
import java.io.IOException;
import java.io.InputStream;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
public class MyBatiesUtils {
private static SqlSessionFactory sqlSessionFactory;
static{
InputStream is=null;
try {
is = Resources.getResourceAsStream("SqlMapConfig.xml");
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
sqlSessionFactory=new SqlSessionFactoryBuilder().build(is);
}
public static SqlSessionFactory getFactory(){
return sqlSessionFactory;
}
}
testMybatis.java (测试类)
package top.vkiss.mybatis.test;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.junit.Test;
import top.vkiss.mybatis.dao.UserMapper;
import top.vkiss.mybatis.pojo.CAndU;
import top.vkiss.mybatis.pojo.Customer;
import top.vkiss.mybatis.pojo.User;
import top.vkiss.mybatis.util.MyBatiesUtils;
public class testMybatis {
SqlSessionFactory sf = MyBatiesUtils.getFactory();
@Test
public void addUser() throws Exception {
SqlSession se = sf.openSession();
UserMapper uM = se.getMapper(UserMapper.class);
User user = new User();
user.setUname("张三");
user.setUaddress("深圳");
uM.addUser(user);
se.commit();
}
@Test
public void queryAllByNameAndAddress() throws Exception {
SqlSession se = sf.openSession();
UserMapper uM = se.getMapper(UserMapper.class);
User user = new User();
user.setUname("张三");
user.setUaddress("深圳");
List<User> list = uM.queryAllByNameAndAddress(user);
System.out.println(list);
se.commit();
}
//查询两张表
@Test
public void queryAllCAndU() throws Exception {
SqlSession se = sf.openSession();
UserMapper uM = se.getMapper(UserMapper.class);
User user = new User();
user.setUname("111");
Customer customer = new Customer();
customer.setName("111");
CAndU cu = new CAndU();
cu.setCu(customer);
cu.setUser(user);
List<User> list = uM.queryAllCAndU(cu);
for (User cAndU : list) {
System.out.println(cAndU);
}
se.commit();
}
//查询两张表 返回值用 resultMap
@Test
public void queryAllByCAndUMap() throws Exception {
SqlSession se = sf.openSession();
UserMapper uM = se.getMapper(UserMapper.class);
User user = new User();
user.setUname("张三");
Customer customer = new Customer();
customer.setName("888");
CAndU cu = new CAndU();
cu.setCu(customer);
cu.setUser(user);
List<CAndU> list = uM.queryAllByCAndUMap(cu);
for (CAndU cAndU : list) {
System.out.println(cAndU);
}
System.out.println(list);
se.commit();
}
//修改 By EXAM
@Test
public void updateUserByEXAM() throws Exception {
SqlSession se = sf.openSession();
UserMapper uM = se.getMapper(UserMapper.class);
User user = new User();
user.setUid(10);
user.setUname("李四");
user.setUaddress("北京");
uM.updateUserByEXAM(user);
se.commit();
}
//查询条数
@Test
public void queryAllConut() throws Exception {
SqlSession se = sf.openSession();
UserMapper uM = se.getMapper(UserMapper.class);
int n = uM.queryAllConut();
System.out.println(n);
se.commit();
}
//查询 By EXAM
@Test
public void queryUserByEXAM() throws Exception {
SqlSession se = sf.openSession();
UserMapper uM = se.getMapper(UserMapper.class);
User user = new User();
user.setUid(10);
//user.setUname("123");
//user.setUaddress("456");
List<User> list = uM.queryUserByEXAM(user);
System.out.println(list);
se.commit();
}
//查询 By ID IN Array
@Test
public void getUserAllByIdArray() throws Exception {
SqlSession se = sf.openSession();
UserMapper uM = se.getMapper(UserMapper.class);
List<User> list = uM.getUserAllByIdArray(new int[]{1,3,45,6});
System.out.println(list);
se.commit();
}
@Test
public void abc(){
System.out.println("fafadfafwefiopujewfioeujhfkalfjoiawjefoiajoilefjilaew");
}
}
测试类中不同方法 对应不同 测试结果,这里不一一列出
所用 jar 包 : (mybatis 3.2.7 所有jar ,及 连接数据库的驱动包)asm-3.3.1.jar
cglib-2.2.2.jar
commons-logging-1.1.1.jar
javassist-3.17.1-GA.jar
log4j-1.2.17.jar
log4j-api-2.0-rc1.jar
log4j-core-2.0-rc1.jar
mybatis-3.2.7.jar
mysql-connector-java-5.1.7-bin.jar
slf4j-api-1.7.5.jar
slf4j-log4j12-1.7.5.jar