MyBatis动态条件、一对多、整合spring（二）

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

输入映射和输出映射

Mapper.xml映射文件定义了操作数据库的sql，每一个sql是一个statement，映射文件是mybatis的核心。

parameterType输入类型

1.传递简单类型

Integer、String 使用#{}占位符 | ${}拼接字符

parameterType="Integer"

parameterType="String"

2.传递pojo对象

Product类

parameterType="com.bean.Product"

3.传递pojo包装对象

开发过程中，我们可以通过传递pojo当做查询条件，而查询用户的userid时又要查询订单表的order信息，这时定义成一个包装类作为参数传递。

新建QueryVo类

package com.bean;import com.sun.tools.internal.xjc.reader.xmlschema.bindinfo.BIConversion;public class QueryVo {
private BIConversion.User user; public BIConversion.User getUser() {
return user;
} public void setUser(BIConversion.User user) {
this.user = user;
}
}

配置mapper.xml

编写测试方法

@Test
public void getProductListByQueryVo(){
String resource = "SqlMapConfig.xml";
InputStream is = Main.class.getClassLoader().getResourceAsStream(resource);
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(is); SqlSession session = sessionFactory.openSession();
ProductMapper mapper = session.getMapper(ProductMapper.class);
QueryVo vo = new QueryVo();
com.bean.Product p = new com.bean.Product();
p.setName("桃");
vo.setProduct(p);
List<Product> list = mapper.getProductListByQueryVo(vo);
for(Product l : list){
System.out.print(l);
}
}

resultType输出类型

1.输出简单类型

<select id="getProductCount" resultType="Integer">
select count(*) from Product
</select>

public interface ProductMapper {
Product getProduct(Integer id);
List<Product> getProductListByQueryVo(QueryVo queryVo);
int getProductCount();
}

@Test
public void getProductCount(){
String resource = "SqlMapConfig.xml";
InputStream is = Main.class.getClassLoader().getResourceAsStream(resource);
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(is); SqlSession session = sessionFactory.openSession();
ProductMapper mapper = session.getMapper(ProductMapper.class); int count = mapper.getProductCount();
System.out.print(count);
}

2.输出pojo对象和pojo集合（参考上一篇）

<select id="getProduct" parameterType="Integer" resultType="com.bean.Product">
select * from Product where Id = #{id}
</select>
<select id="getProductListByName" parameterType="String" resultType="com.bean.Product">
select * from Product where name like "%"#{haha}"%"
</select>

3.resultMap

resultType可以指定将查询结果映射为pojo，但需要pojo的属性名和sql查询的列一致方可映射成功。

如果sql查询字段名和pojo属性名不一致，可以通过resultMap将字段名和属性做一个对应关系，resultMap是指上还需要将查询结果映射到pojo对象中。

resultMap可以实现将查询结果映射为复杂类型的pojo，比如在查询结果映射对象中包括pojo和list实现一对多查询和一对一查询。

新建实体类，mapper.xml，接口，和测试方法如下：

package com.bean;public class BigProduct {
private Integer bigPId;
private String bigPname;
private String bigPprice;
...
}

<?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="com.mapper.BigProductMapper">
<select id="getProductList" resultMap="bigProductMap">
select id,name,price from Product
</select> <resultMap id="bigProductMap" type="com.bean.BigProduct">

<id property="bigPId" column="id"></id>

<result property="bigPname" column="name"></result>
<result property="bigPprice" column="price"></result>
</resultMap>
</mapper>

package com.mapper;import com.bean.BigProduct;import java.util.List;public interface BigProductMapper {
List<BigProduct> getProductList();
}

@Test
public void getBigProductList(){
String resource = "SqlMapConfig.xml";
InputStream is = Main.class.getClassLoader().getResourceAsStream(resource);
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(is); SqlSession session = sessionFactory.openSession();
BigProductMapper mapper = session.getMapper(BigProductMapper.class);
List<BigProduct> productList = mapper.getProductList();
for(BigProduct bp : productList){
System.out.print(bp);
}
}

动态sql

while if 标签-不用写where 1==1 去考虑后面的and

<select id="getProductListByName" parameterType="com.bean.Product" resultType="com.bean.Product">
select * from Product
<where>
<if test="name != null or name != ''">
and name like "%"#{name}"%"
</if>
</where>
</select>

sql片段-可以将重复的sql提取出来，使用时用include引用即可。

<sql id="queryProduct">
select * from Product
</sql>
<select id="getProductListByName" parameterType="com.bean.Product" resultType="com.bean.Product">
<include refid="queryProduct"></include>
<where>
<if test="name != null or name != ''">
and name like "%"#{name}"%"
</if>
</where>
</select>

foreach标签-像sql传递数组或list，mybatis使用foreach解析如 id in (1,2,3,4)

修改QueryVo 使其支持list ids 存储多个id，并添加getter/setter方法

package com.bean;import java.util.List;public class QueryVo {
private Product product;
private List<Integer> ids; public Product getProduct() {
return product;
} public void setProduct(Product product) {
this.product = product;
} public List<Integer> getIds() {
return ids;
} public void setIds(List<Integer> ids) {
this.ids = ids;
}
}

mapper.xml

<select id="getProductByIds" parameterType="com.bean.QueryVo" resultType="com.bean.Product">
select * from product
<where>

<foreach collection="ids" item="item" open="id in (" close=")" separator=",">
#{item}
</foreach>
</where>
</select>

接口

public interface ProductMapper {
List<Product> getProductByIds(QueryVo queryVo);

测试

@Test
public void getProductByIds(){
String resource = "SqlMapConfig.xml";
InputStream is = Main.class.getClassLoader().getResourceAsStream(resource);
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(is);
SqlSession session = sessionFactory.openSession();
ProductMapper mapper = session.getMapper(ProductMapper.class); QueryVo queryVo = new QueryVo();
List<Integer> ids = new ArrayList<Integer>();
ids.add(1);
ids.add(3);
ids.add(9);
queryVo.setIds(ids); List<Product> list = mapper.getProductByIds(queryVo);
System.out.print(list);
}

关联查询

一对一查询：

商品分类和商品表是一对多关系反之是一对一关系读取商品获取其对应分类

在Product类中加入Category类属性

public class Product {
private Integer Id;
private String Name;
private String price;
private Category category;
...

mapper.xml

<mapper namespace="com.mapper.ProductMapper">
<resultMap id="productCategoryResultMap" type="com.bean.Product">
<id property="id" column="id"></id>
<result property="name" column="name"></result>
<result property="price" column="price"></result>

<association property="category" javaType="com.bean.Category">
<id property="id" column="categoryId"></id>
<result property="name" column="name"></result>
</association>
</resultMap>
<select id="getProductCategory" resultMap="productCategoryResultMap">
select p.id,p.name,p.price,p.categoryId,c.name from Product p
left join category c on p.categoryId = c.id;
</select>

测试

@Test
public void getProductCategory(){
String resource = "SqlMapConfig.xml";
InputStream is = Main.class.getClassLoader().getResourceAsStream(resource);
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(is);
SqlSession session = sessionFactory.openSession();
ProductMapper mapper = session.getMapper(ProductMapper.class);
List<Product> pcs = mapper.getProductCategory();
for(Product pc : pcs){
System.out.println(pc);
}
}

一对多查询：

在Category类中增加List<product> products属性并生成getset方法

public class Category {
private Integer id;
private String name;
private List<Product> products;

mapper.xml

<mapper namespace="com.mapper.ProductMapper"> <resultMap id="categoryProducts" type="com.bean.Category">
<id property="id" column="categoryId"></id>
<result property="name" column="name"></result>

<collection property="products" javaType="list" ofType="com.bean.Product">
<id property="id" column="id"></id>
<result property="name" column="name"></result>
<result property="price" column="price"></result>
</collection>
</resultMap>
<select id="categoryProducts" resultMap="categoryProducts">
select c.name,p.id,p.name,p.price,p.categoryId from category c
left join Product p on c.id = p.categoryId;
</select>

测试

@Test
public void categoryProducts(){
String resource = "SqlMapConfig.xml";
InputStream is = Main.class.getClassLoader().getResourceAsStream(resource);
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(is);
SqlSession session = sessionFactory.openSession();
ProductMapper mapper = session.getMapper(ProductMapper.class);
List<Category> list = mapper.categoryProducts();
for(Category c : list){
System.out.print(c);
for(Product p : c.getProducts()){
System.out.print(p);
}
}
System.out.print(list); }

Mybatis整合spring

将sessionFactory对象放到spring容器中，由spring帮我们创建session对象，及mapper对象。

1.导包 spring的jar包，mybatis的jar包，spring+mybatis的整合包，mysql数据库驱动包，数据库连接池的jar包。

2.添加SqlMapConfig.xml ，applicationContext.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>

<typeAliases>
<package name="com.bean" />
</typeAliases></configuration>

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:context="http://www.springframework.org/schema/context" xmlns:p="http://www.springframework.org/schema/p"
xmlns:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.0.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.0.xsd
http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.0.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.0.xsd
http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-4.0.xsd"> 
<context:property-placeholder location="classpath:db.properties" /> 
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
destroy-method="close">
<property name="driverClassName" value="${jdbc.driver}" />
<property name="url" value="${jdbc.url}" />
<property name="username" value="${jdbc.username}" />
<property name="password" value="${jdbc.password}" />
<property name="maxActive" value="10" />
<property name="maxIdle" value="5" />
</bean> 
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">

<property name="configLocation" value="classpath:SqlMapConfig.xml" />

<property name="dataSource" ref="dataSource" />
</bean>
</beans>

db.properties

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/david2018_db?characterEncoding=utf-8
jdbc.username=root
jdbc.password=1234

log4j.properties

# Global logging configuration
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

传统dao的开发方式，创建接口，创建实现类，实现类继承SqlsessionDaoSupport类来获得session对象操作。不做演示了。

Mapp代理形式开发dao

编写ProductMapper.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="com.mapper.ProductMapper">
<select id="getProduct" parameterType="Integer" resultType="com.bean.Product">
select * from Product where Id = #{id}
</select>
<select id="getProductListByName" parameterType="com.bean.Product" resultType="com.bean.Product">
<include refid="queryProduct"></include>
<where>
<if test="name != null or name != ''">
and name like "%"#{name}"%"
</if>
</where>
</select>
<insert id="insertProduct" parameterType="com.bean.Product">
<selectKey keyProperty="id" resultType="Integer" order="AFTER">
select last_insert_id()
</selectKey>
insert into Product (name,price) values (#{name},#{price})
</insert>
<update id="updateById" parameterType="com.bean.Product">
update Product set name = #{name} , price=#{price} where id = #{id}
</update>
<delete id="deleteById" parameterType="Integer">
delete from Product where id = #{id}
</delete>
</mapper>

实现ProductMapper接口

package com.mapper;import com.bean.*;import java.util.List;public interface ProductMapper {
Product getProduct(Integer id);
List<Product> getProductListByName(Product p);
void insertProduct(Product p);
void updateById(Product p);
void deleteById(Integer id);
}

加载mapper.xml文件到核心配置SqlMapConfig.xml

配置mapper代理-在applicationContext.xml中

<bean id="userMapper" class="org.mybatis.spring.mapper.MapperFactoryBean">

<property name="mapperInterface" value="com.mapper.ProductMapper" />

<property name="sqlSessionFactory" ref="sqlSessionFactory" />
</bean>

或者扫描所有mapper文件夹

<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">

<property name="basePackage" value="com.mapper" />
</bean>

测试方法：

package com.company;import com.bean.Product;
import com.mapper.ProductMapper;
import org.junit.Before;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;import java.util.List;public class Main {
private ApplicationContext context; @Before
public void setUp(){
this.context = new ClassPathXmlApplicationContext("classpath:applicationContext.xml");
} @Test
public void getProduct(){
ProductMapper mapper = this.context.getBean(ProductMapper.class);
Product p = mapper.getProduct(1);
System.out.print(p);
} @Test
public void getProductListByName(){
ProductMapper mapper = this.context.getBean(ProductMapper.class);
Product pro = new Product();
pro.setName("桃");
List<Product> p = mapper.getProductListByName(pro);
System.out.print(p);
}}