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

<?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="String" resultType="com.bean.Product">

        select * from Product where name like "%"#{haha}"%"

    </select>

    <select id="getProductListByQueryVo" parameterType="com.bean.QueryVo" resultType="com.bean.Product">

        select * from Product where name like "%"#{product.name}"%"

    </select>

</mapper>

　　编写测试方法

@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">

        <!--property主键在pojo中属性名

         column 数据库中字段名-->

        <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>

            <!--colletion:遍历的集合

             item:遍历集合的变量

             open:在前面添加sql片段

             close:结束添加sql片段

             separator:使用的分隔符-->

            <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>

    <!-- 配置SqlSessionFactory -->

    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">

        <!-- 配置mybatis核心配置文件 -->

        <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

<?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>

    <mappers>

        <mapper resource="com/mapper/ProductMapper.xml"></mapper>

    </mappers>

</configuration>

配置mapper代理-在applicationContext.xml中

<!-- Mapper代理的方式开发方式一，配置Mapper代理对象 -->

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

        <!-- 配置Mapper接口 -->

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

        <!-- 配置sqlSessionFactory -->

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

    </bean>

或者扫描所有mapper文件夹

    <!-- Mapper代理的方式开发方式二，扫描包方式配置代理 -->

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

        <!-- 配置Mapper接口 -->

        <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);

    }

}

巴特西

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

最新文章

热门文章

巴特西

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

最新文章

热门文章

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