一、使用mybatis的动态代理方式开发
需求:这里以crm系统中分页条件查询所有的客户信息的功能为例?
1、创建工程
2、引入所需的jar包
3、引入日志文件、数据库连接参数的配置文件等
4、创建mybatis的核心配置文件,其中包括加载数据参数的配置文件和mybatis的映射文件,还有配置数据源(个人比较喜欢使用阿里巴巴的druid)等。
1 <?xml version="1.0" encoding="UTF-8"?>
2 <beans xmlns="http://www.springframework.org/schema/beans"
3 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:mvc="http://www.springframework.org/schema/mvc"
4 xmlns:context="http://www.springframework.org/schema/context"
5 xmlns:aop="http://www.springframework.org/schema/aop"
6 xmlns:tx="http://www.springframework.org/schema/tx"
7 xmlns:task="http://www.springframework.org/schema/task"
8 xmlns:dubbo="http://code.alibabatech.com/schema/dubbo"
9 xsi:schemaLocation="http://www.springframework.org/schema/beans
10 http://www.springframework.org/schema/beans/spring-beans-4.0.xsd
11 http://www.springframework.org/schema/mvc
12 http://www.springframework.org/schema/mvc/spring-mvc-4.0.xsd
13 http://www.springframework.org/schema/context
14 http://www.springframework.org/schema/context/spring-context-4.0.xsd
15 http://www.springframework.org/schema/aop
16 http://www.springframework.org/schema/aop/spring-aop-4.0.xsd
17 http://www.springframework.org/schema/tx
18 http://www.springframework.org/schema/tx/spring-tx-4.0.xsd
19 http://www.springframework.org/schema/task
20 http://www.springframework.org/schema/task/spring-task-4.0.xsd
21 http://code.alibabatech.com/schema/dubbo
22 http://code.alibabatech.com/schema/dubbo/dubbo.xsd">
23
24
25 <!-- spring整合mybatis的配置文件 -->
26 <!-- 1、加载数据库连接配置文件 -->
27 <context:property-placeholder location="classpath:db.properties"/>
28 <!-- 2、数据源 -->
29 <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
30 <property name="driverClassName" value="${jdbc.driver}"/>
31 <property name="url" value="${jdbc.url}"/>
32 <property name="username" value="${jdbc.username}"/>
33 <property name="password" value="${jdbc.password}"/>
34 <property name="maxActive" value="10" />
35 <property name="maxIdle" value="5" />
36 </bean>
37
38 <!-- 3、管理mybatis的会话工厂对象 -->
39 <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
40 <!-- 注入数据源 -->
41 <property name="dataSource" ref="dataSource"/>
42 <!-- 加载mybatis的全局配置文件 -->
43 <property name="configLocation" value="classpath:SqlMapConfig.xml"/>
44 </bean>
45
46 <!-- 4、管理mybatis中所有mapper接口的代理对象 -->
47 <bean id="mapper" class="org.mybatis.spring.mapper.MapperScannerConfigurer">
48 <property name="basePackage" value="com.zxz.ssm.crm.mapper"/>
49 </bean>
50
51 </beans>
5、创建pojo类
6、创建mybatis的映射文件(配置成功后记得将该映射文件加载到mybatis的核心配置文件中)
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 <!-- 客户: -->
7 <mapper namespace="com.zxz.ssm.crm.mapper.CustomerMapper">
8
9 <!-- 提取查询条件的sql语句 -->
10 <sql id="customer_where">
11 <where>
12 <if test="custName!=null and custName!=''">
13 <!-- 【注意:这里尽量使用#{}占位符,是为了防止sql注入的问题】,但是也可以使用${}拼接符 -->
14 <!-- and cust_name like '%${custName}%' -->
15 and cust_name like "%"#{custName}"%"
16 </if>
17 <if test="custSource!=null and custSource!=''">
18 and cust_source=#{custSource}
19 </if>
20 <if test="custIndustry!=null and custIndustry!=''">
21 and cust_industry=#{custIndustry}
22 </if>
23 <if test="custLevel!=null and custLevel!=''">
24 and cust_level=#{custLevel}
25 </if>
26 </where>
27 </sql>
28
29 <!-- 带分页查询数据 -->
30 <!-- 按用户传递过来的参数条件查询客户数据的集合: -->
31 <select id="findByQueryVoList" parameterType="com.zxz.ssm.crm.pojo.QueryVo" resultType="com.zxz.ssm.crm.pojo.Customer">
32 select
33 c.cust_id,c.cust_name,b1.dict_item_name cust_source,b2.dict_item_name cust_industry,b3.dict_item_name cust_level,
34 c.cust_linkman,c.cust_phone,c.cust_mobile,c.cust_zipcode,c.cust_address
35 from customer c
36 LEFT JOIN base_dict b1 on c.cust_source=b1.dict_id
37 LEFT JOIN base_dict b2 on c.cust_industry=b2.dict_id
38 LEFT JOIN base_dict b3 on c.cust_level=b3.dict_id
39 <include refid="customer_where"/>
40 limit #{start},#{size}
41 </select>
42
43 <!-- 带分页查询数据 -->
44 <!-- 按用户传递过来的参数条件查询数据的总记录数 -->
45 <select id="findByQueryCount" parameterType="com.zxz.ssm.crm.pojo.QueryVo" resultType="java.lang.Integer">
46 select
47 count(*)
48 from customer c
49 LEFT JOIN base_dict b1 on c.cust_source=b1.dict_id
50 LEFT JOIN base_dict b2 on c.cust_industry=b2.dict_id
51 LEFT JOIN base_dict b3 on c.cust_level=b3.dict_id
52 <include refid="customer_where"/>
53 </select>
54</mapper>
7、通过service层注入mapper接口的代理对象调用查询方法,接着再controller控制层调用service成中的查询方法得到相应的数据,并存放到model对象中,最后填充在页面上即可。