基于SSM的CRM系统开发实现（二）

主要讲述dao层注意的点
前端页面展示：

下面是CustomerMapper.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.itheima.dao.CustomerMapper">

	<sql id="customer_where">
		<where>
		    <if test="custName != null and custName != ''">
				and a.cust_name like '%${custName}%'
			</if>
			<if test="custSource != null and custSource != ''">
				and a.cust_source=#{custSource}
			</if>
			<if test="custIndustry != null and custIndustry != ''">
				and a.cust_industry=#{custIndustry}
			</if>
			<if test="custLevel != null and custLevel != ''">
				and a.cust_level=#{custLevel}
			</if>
		</where>
	</sql>

	<select id="findCustomerByVo" parameterType="cn.itheima.pojo.QueryVo" resultType="cn.itheima.pojo.Customer">
		select a.cust_id,a.cust_name, b.dict_item_name cust_source, c.dict_item_name cust_industry, 
			d.dict_item_name cust_level,a.cust_phone,a.cust_mobile, a.cust_linkman, a.cust_zipcode, 
			a.cust_address, a.cust_createtime
		from customer a
		left join base_dict b on a.cust_source = b.dict_id
		left join base_dict c on a.cust_industry = c.dict_id
		left join base_dict d on a.cust_level = d.dict_id
		
		<include refid="customer_where"></include>
		limit #{start}, #{size}
	</select>
	
	<select id="findCustomerByVoCount" parameterType="cn.itheima.pojo.QueryVo" resultType="int">
		select count(*)
		from customer a
		left join base_dict b on a.cust_source = b.dict_id
		left join base_dict c on a.cust_industry = c.dict_id
		left join base_dict d on a.cust_level = d.dict_id
		
		<include refid="customer_where"></include>
	</select>
	
	<select id="findCustomerById" parameterType="long" resultType="cn.itheima.pojo.Customer">
		select * from customer where cust_id=#{id}
	</select>
	
	<update id="updateCustomerById" parameterType="cn.itheima.pojo.Customer">
		update customer 
		
		<!-- set标签作用:
					第一可以自动添加set关键字, 
					第二可以去掉最后一个更新的逗号 -->
		<set>
			<if test="cust_name != null and  cust_name != ''">
				cust_name=#{cust_name} ,
			</if>
			<if test="cust_source != null and cust_source  != ''">
				cust_source=#{cust_source},
			</if>
			<if test="cust_industry != null and  cust_industry != ''">
				cust_industry=#{cust_industry},
			</if>
			<if test="cust_level != null and  cust_level != ''">
				cust_level=#{cust_level},
			</if>
			<if test="cust_linkman != null and  cust_linkman != ''">
				cust_linkman=#{cust_linkman},
			</if>
			<if test=" cust_phone != null and  cust_phone != ''">
				cust_phone=#{cust_phone},
			</if>
			<if test="cust_mobile != null and cust_mobile  != ''">
				cust_mobile=#{cust_mobile},
			</if>
			<if test="cust_zipcode != null and  cust_zipcode != ''">
				cust_zipcode=#{cust_zipcode},
			</if>
			<if test="cust_address != null and   cust_address!= ''">
				cust_address=#{cust_address},
			</if>
		</set>
		where cust_id=#{cust_id}
	</update>
	
	<delete id="delCustomerById" parameterType="long">
		delete from customer where cust_id=#{id}
	</delete>
</mapper>

对于公用的部分可用<sql>标签把它拿出来，避免重复编写，就是where处的条件。
因为页面中有多个（4个）搜索条件，即可按照客户名称查询、也可按照客户来源查询、也可按照所属行业查询、也可按照客户级别查询，这就是所谓的高级查询。我们在查询时不一定要把全部的查询条件都用到，可能只用到其中一部分，所以要使用标签并且使用<if>标签来进行判空，如果<if>标签判断为空，说明我们没有用到这个搜索条件，所以sql语句中的where条件就不拼接这个搜索条件；只有当<if>标签判断不为空时我们才在sql语句中的where条件中拼接这个搜索条件。

select语句使用limit进行分页查询，得到每页显示的记录数，分页可以避免一次性显示太多记录的问题，一次性显示太多记录会让人看不过来。

分页的时候总是两个select成对使用，一个是拿出记录，一个是得到记录总数，即如下：

<select id="findCustomerByVo" parameterType="cn.itheima.pojo.QueryVo" resultType="cn.itheima.pojo.Customer">
		select a.cust_id,a.cust_name, b.dict_item_name cust_source, c.dict_item_name cust_industry, 
			d.dict_item_name cust_level,a.cust_phone,a.cust_mobile, a.cust_linkman, a.cust_zipcode, 
			a.cust_address, a.cust_createtime
		from customer a
		left join base_dict b on a.cust_source = b.dict_id
		left join base_dict c on a.cust_industry = c.dict_id
		left join base_dict d on a.cust_level = d.dict_id
		
		<include refid="customer_where"></include>
		limit #{start}, #{size}
	</select>
	
	<select id="findCustomerByVoCount" parameterType="cn.itheima.pojo.QueryVo" resultType="int">
		select count(*)
		from customer a
		left join base_dict b on a.cust_source = b.dict_id
		left join base_dict c on a.cust_industry = c.dict_id
		left join base_dict d on a.cust_level = d.dict_id
		
		<include refid="customer_where"></include>
	</select>

进行更新记录操作时首先从当前页面把该记录的id传到后台，后台根据这个id从数据库中把这个记录找出来，然后在编辑页面（或编辑对话框）中把这个记录的数据回显出来，之后才是进行编辑操作。

同理，进行更新操作时也要使用<set>和<if>进行判空，只有<if>不为空时我们才set这个字段

删除就是通过id删除即可。