Mybatis 实现手机管理系统的持久化数据访问层

　　　　　最近公司需要对客户手机进行管理并提供二维码存储手机串号的加密字符．供其他接入系统通过扫面二维码解析使用．系统提供手机信息管理，客户管理，用户管理功能．

１．使用到的POJO类

１．１　User

package com.green.phonemanage.model;

/**
 * @author maybo 用户实体类
 */
public class User {
	private int id;
	private String loginName;// 登录名
	private String passwd;// 密码
	private String name;// 用户名字
	private String idCard;// 用户身份证
	private String sex;// 性别
	private int age;// 年龄
	private String phone;// 电话
	private String department;// 部门
	private int status;// 状态
	private int role;// 角色

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getLoginName() {
		return loginName;
	}

	public void setLoginName(String loginName) {
		this.loginName = loginName;
	}

	public String getPasswd() {
		return passwd;
	}

	public void setPasswd(String passwd) {
		this.passwd = passwd;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getIdCard() {
		return idCard;
	}

	public void setIdCard(String idCard) {
		this.idCard = idCard;
	}

	public String getSex() {
		return sex;
	}

	public void setSex(String sex) {
		this.sex = sex;
	}

	public int getAge() {
		return age;
	}

	public void setAge(int age) {
		this.age = age;
	}

	public String getPhone() {
		return phone;
	}

	public void setPhone(String phone) {
		this.phone = phone;
	}

	public String getDepartment() {
		return department;
	}

	public void setDepartment(String department) {
		this.department = department;
	}

	public int getStatus() {
		return status;
	}

	public void setStatus(int status) {
		this.status = status;
	}

	public int getRole() {
		return role;
	}

	public void setRole(int role) {
		this.role = role;
	}

	@Override
	public String toString() {
		return "User [id=" + id + ", loginName=" + loginName + ", passwd="
				+ passwd + ", name=" + name + ", idCard=" + idCard + ", sex="
				+ sex + ", age=" + age + ", phone=" + phone + ", department="
				+ department + ", status=" + status + ", role=" + role + "]";
	}

}

　　１．２Client

package com.green.phonemanage.model;

/**
 * @author maybo
 *
 */
public class Client {
	private int id;
	private String code;// 客户编码
	private String address;// 地址
	private String name;// 客户名字
	private String idCard;// 客户身份证
	private String phone;// 客户电话
	private String province;// 省
	private String city;// 市
	private String area;// 区

	public void setArea(String area) {
		this.area = area;
	}

	public void setCity(String city) {
		this.city = city;
	}

	public void setProvince(String province) {
		this.province = province;
	}

	public String getArea() {
		return area;
	}

	public String getCity() {
		return city;
	}

	public String getProvince() {
		return province;
	}

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getCode() {
		return code;
	}

	public void setCode(String code) {
		this.code = code;
	}

	public String getAddress() {
		return address;
	}

	public void setAddress(String address) {
		this.address = address;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getIdCard() {
		return idCard;
	}

	public void setIdCard(String idCard) {
		this.idCard = idCard;
	}

	public String getPhone() {
		return phone;
	}

	public void setPhone(String phone) {
		this.phone = phone;
	}

	@Override
	public String toString() {
		return "Client [id=" + id + ", code=" + code + ", address=" + address
				+ ", name=" + name + ", idCard=" + idCard + ", phone=" + phone
				+ "]";
	}

}

　　1．３CellPhone

package com.green.phonemanage.model;

import java.util.Arrays;
import java.util.Date;

/**
 * @author maybo
 *
 */
public class CellPhone {
	private int id;
	private String phoneBrand;// 手机品牌
	private String phoneModel;// 手机型号
	private String phoneColor;// 手机颜色
	private Client client;// 客户
	private Client receiver;// 签收人
	private String createDate;// 创建时间
	private String serviceLife;// 寿命
	private byte[] qrCode;// 二维码
	private int status;// 状态
	private String imei;// 手机串号

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getPhoneBrand() {
		return phoneBrand;
	}

	public void setPhoneBrand(String phoneBrand) {
		this.phoneBrand = phoneBrand;
	}

	public String getPhoneModel() {
		return phoneModel;
	}

	public void setPhoneModel(String phoneModel) {
		this.phoneModel = phoneModel;
	}

	public String getPhoneColor() {
		return phoneColor;
	}

	public void setPhoneColor(String phoneColor) {
		this.phoneColor = phoneColor;
	}

	public Client getClient() {
		return client;
	}

	public void setClient(Client client) {
		this.client = client;
	}

	public Client getReceiver() {
		return receiver;
	}

	public void setReceiver(Client receiver) {
		this.receiver = receiver;
	}

	public String getCreateDate() {
		return createDate;
	}

	public void setCreateDate(String createDate) {
		this.createDate = createDate;
	}

	public void setServiceLife(String serviceLife) {
		this.serviceLife = serviceLife;
	}

	public String getServiceLife() {
		return serviceLife;
	}

	public byte[] getQrCode() {
		return qrCode;
	}

	public void setQrCode(byte[] qrCode) {
		this.qrCode = qrCode;
	}

	public int getStatus() {
		return status;
	}

	public void setStatus(int status) {
		this.status = status;
	}

	public void setImei(String imei) {
		this.imei = imei;
	}

	public String getImei() {
		return imei;
	}

	@Override
	public String toString() {
		return "CellPhone [id=" + id + ", phoneBrand=" + phoneBrand
				+ ", phoneModel=" + phoneModel + ", phoneColor=" + phoneColor
				+ ", client=" + client + ", receiver=" + receiver
				+ ", createDate=" + createDate + ", serviceLife=" + serviceLife
				+ ", qrCode=" + Arrays.toString(qrCode) + ", status=" + status
				+ ", IMEI=" + imei + "]";
	}

}

　　2．定义Dao层接口

２．１　CellPhoneDao

package com.green.phonemanage.dao;


import java.sql.Blob;
import java.util.List;

import com.green.phonemanage.model.CellPhone;
import com.green.phonemanage.model.SearchForm;

/**
 * @author maybo
 *
 */
public interface CellPhoneDao {
	public void add(CellPhone cellPhone);// 添加手机

	public void rmove(int id);// 删除手机

	public void update(CellPhone cellPhone);// 修改手机

	public List<CellPhone> finds(SearchForm searchForm);// 查询手机

	public CellPhone findById(int id);// 查询手机通过id

	public Integer findByIMEI(String IMEI);// 通过手机串号查询手机是否已经存在
	
	public CellPhone findQr(int id);// 查询手机二维码通过id
	
	public Integer findByClient(int id);//查询通过用户id
	
	public Integer findTotalByStatus(int status);//查询总数通过状态

	public Integer findTotalByBrand(String brand);//查询总数通过品牌
	
	public Integer findTotalByAddress(String address);//查询总数通过地址
	
	public List<String> findBrands();//查询品牌
	
	public List<Integer> findStatus();//查询所状态
	
	public List<String> findCitys();//查询所有城市
	
	public Integer totals();//查询总数
	
}

　　2．２　ClientDao

package com.green.phonemanage.dao;

import java.util.List;

import com.green.phonemanage.model.Client;
import com.green.phonemanage.model.SearchForm;

/**
 * @author maybo
 *
 */
public interface ClientDao {
	public void add(Client client);// 添加客户

	public void rmove(int id);// 删除客户

	public void update(Client client);// 修改客户

	public List<Client> finds(SearchForm searchForm);// 查询客户

	public Client findById(int id);// 查询客户通过id
	
	public Integer findByIdCard(String idCard);//通过身份证查询客户是否存在
	
	public Integer totals();//查询总数
}

　　2．３　UserDao

package com.green.phonemanage.dao;

import java.util.List;

import com.green.phonemanage.model.SearchForm;
import com.green.phonemanage.model.User;

/**
 * @author maybo
 *
 */
public interface UserDao {
	public Integer login(User user);// 用户登录

	public void register(User user);// 用户注册

	public void rmove(int id);// 删除用户

	public void update(User user);// 修改用户

	public List<User> finds(SearchForm searchForm);// 查找用户

	public User findById(int id);// 查询用户通过id

	public Integer findByLoginName(String loginName);// 查询登录名是否存在
	
	public void freeze(int id);//冻结用户
	
	public void unfreeze(int id);//用户解冻
	
	public Integer totals();//查询总数
}

　　3．Mapper文件

3．１　user.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.green.phonemanage.dao.UserDao">
	<resultMap type="User" id="userMap">
		<id column="id" property="id" />
		<result column="login_name" property="loginName" />
		<result column="age" property="age" />
		<result column="passwd" property="passwd" />
		<result column="name" property="name" />
		<result column="id_card" property="idCard" />
		<result column="sex" property="sex" />
		<result column="phone" property="phone" />
		<result column="department" property="department" />
		<result column="status" property="status" />
		<result column="role" property="role" />
	</resultMap>
	<sql id="sql_select">
		select *
	</sql>
	<sql id="select_where">
		from user
		<if test="key!=null">
			where
			login_name like "%"#{key}"%" or phone like
			"%"#{key}"%" or name like
			"%"#{key}"%"
		</if>
		<if test="key==null">
			order by id desc
			limit #{pageIndex},#{pageSize}
		</if>
	</sql>
	<select id="findById" parameterType="int" resultMap="userMap">
		select *
		from user u where u.id=#{id}
	</select>
	<insert id="register" parameterType="User">
		insert into
		user(login_name,age,passwd,name,id_card,sex,phone,department,status,role)
		values(#{loginName},#{age},#{passwd},#{name},#{idCard},#{sex},#{phone},#{department},#{status},#{role})
	</insert>
	<update id="update" parameterType="User">
		update user set
		login_name=#{loginName},age=#{age},passwd=#{passwd},name=#{name},id_card=#{idCard},sex=#{sex},phone=#{phone},department=#{department},status=#{status},role=#{role}
		where id=#{id}
	</update>
	<update id="unfreeze" parameterType="int">
		update user set status=1
		where id=#{id} and role=1
	</update>
	<update id="freeze" parameterType="int">
		update user set status=0 where
		id=#{id} and role=1
	</update>
	<select id="login" parameterType="User" resultType="integer">
		select u.id
		from user u where u.login_name=#{loginName} and u.passwd=#{passwd} and u.role=#{role} and u.status=1 limit 0,1
	</select>
	<select id="findByLoginName" parameterType="string" resultType="integer">
		select u.id from user u where u.login_name=#{loginName} and u.role=1
	</select>
	<delete id="rmove" parameterType="int">
		delete from user where id=#{id}
	</delete>
	<select id="finds" parameterType="SearchForm" resultMap="userMap">
		<include refid="sql_select"></include>
		<include refid="select_where"></include>
	</select>
	<select id="totals" resultType="Integer">
	    select count(*) from user 
	</select>
</mapper>

　　3．２　cellphone.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.green.phonemanage.dao.CellPhoneDao">
	<resultMap type="CellPhone" id="cellPhoneMap">
		<id column="id" property="id" />
		<result column="phone_brand" property="phoneBrand" />
		<result column="phone_model" property="phoneModel" />
		<result column="phone_color" property="phoneColor" />
		<result column="create_date" property="createDate" />
		<result column="service_life" property="serviceLife" />
		<result column="Qr_code" property="qrCode" />
		<result column="status" property="status" />
		<result column="IMEI" property="imei" />
		<association property="client" javaType="Client">
			<id column="cl_id" property="id" />
			<result column="cl_code" property="code" />
			<result column="cl_address" property="address" />
			<result column="cl_name" property="name" />
			<result column="cl_id_card" property="idCard" />
			<result column="cl_phone" property="phone" />
		</association>
		<association property="receiver" javaType="Client">
			<id column="cli_id" property="id" />
			<result column="cli_code" property="code" />
			<result column="cli_address" property="address" />
			<result column="cli_name" property="name" />
			<result column="cli_id_card" property="idCard" />
			<result column="cli_phone" property="phone" />
		</association>
	</resultMap>
	<sql id="sql_select">
		select c.id,c.phone_brand,c.phone_model,c.phone_color,c.create_date,c.service_life,c.status,c.IMEI,cl.id as cl_id,cl.code as cl_code,cl.address as
		cl_address,cl.name as cl_name,cl.id_card as cl_id_card,cl.phone as
		cl_phone,cli.id as cli_id,cli.code as cli_code,cli.address as
		cli_address,cli.name as cli_name,cli.id_card as cli_id_card,cli.phone
		as cli_phone
	</sql>
	<sql id="select_where">
		from cellphone c left join client cl on c.client=cl.id left join
		client cli on c.receiver=cli.id
		<if test="key!=null">
			where phone_brand like "%"#{key}"%" or phone_model like
			"%"#{key}"%" or IMEI like
			"%"#{key}"%"
		</if>
		order by id desc limit #{pageIndex},#{pageSize}
	</sql>
	<select id="findById" parameterType="int" resultMap="cellPhoneMap">
		select
		c.id,c.phone_brand,c.phone_model,c.phone_color,c.create_date,c.service_life,c.status,c.IMEI,cl.id as cl_id,cl.code as cl_code,cl.address as cl_address,cl.name
		as cl_name,cl.id_card as cl_id_card,cl.phone as cl_phone,cli.id as
		cli_id,cli.code as cli_code,cli.address as cli_address,cli.name as
		cli_name,cli.id_card as cli_id_card,cli.phone as cli_phone from
		cellphone c left join client cl on c.client=cl.id left join client cli
		on c.receiver=cli.id where c.id=#{id}
	</select>
	<select id="findByIMEI" parameterType="string" resultType="integer">
		select c.id from cellphone c where c.IMEI=#{IMEI}
	</select>
	<insert id="add" parameterType="CellPhone">
		insert into
		cellphone(phone_brand,phone_model,phone_color,create_date,service_life,Qr_code,status,IMEI,client,receiver)
		values(#{phoneBrand},#{phoneModel},#{phoneColor},#{createDate},#{serviceLife},#{qrCode},#{status},#{imei},#{client.id},#{receiver.id})
	</insert>
	<update id="update" parameterType="CellPhone">
		update cellphone set
		phone_brand=#{phoneBrand},phone_model=#{phoneModel},phone_color=#{phoneColor},create_date=#{createDate},service_life=#{serviceLife},Qr_code=#{qrCode},status=#{status},IMEI=#{imei},client=#{client.id},receiver=#{receiver.id}
		where id=#{id}
	</update>
	<delete id="rmove" parameterType="int">
		delete from cellphone where
		id=#{id}
	</delete>
	<select id="finds" parameterType="SearchForm" resultMap="cellPhoneMap">
		<include refid="sql_select"></include>
		<include refid="select_where"></include>
	</select>
	<select id="findQr" parameterType="int" resultMap="cellPhoneMap">
	    select Qr_code from cellphone where id=#{id}
	</select>
	<select id="findByClient" parameterType="int" resultType="integer">
	    select c.id from cellphone c where c.client=#{id} or c.receiver=#{id}
	</select>
	<select id="findTotalByStatus" parameterType="integer" resultType="integer">
	    select count(*) from cellphone  where status=#{status}
	</select>
	<select id="findTotalByBrand" parameterType="string" resultType="integer">
	     select count(*) from cellphone c where c.phone_brand=#{brand}
	</select>
	<select id="findTotalByAddress" parameterType="string" resultType="integer">
	    select count(*) from cellphone c left join client cl on c.client=cl.id where cl.city=#{address}
	</select>
	<select id="findStatus" resultType="Integer">
	    select distinct status from cellphone 
	</select>
	<select id="findBrands" resultType="string">
	    select distinct phone_brand from cellphone
	</select>
		<select id="findCitys" resultType="string">
	    select distinct cl.city from cellphone c left join client cl on c.client=cl.id 
	</select>
		<select id="totals" resultType="Integer">
	    select count(*) from cellphone 
	</select>
</mapper>

　　3．３　client.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.green.phonemanage.dao.ClientDao">
	<resultMap type="Client" id="clientMap">
		<id column="id" property="id" />
		<result column="code" property="code" />
		<result column="address" property="address" />
		<result column="name" property="name" />
		<result column="id_card" property="idCard" />
		<result column="phone" property="phone" />
		<result column="province" property="province" />
		<result column="city" property="city" />
		<result column="area" property="area" />
	</resultMap>
	<sql id="sql_select">
		select *
	</sql>
	<sql id="select_where">
		from client
		<if test="key!=null">
			where
			code like "%"#{key}"%" or name like "%"#{key}"%" or
			phone like
			"%"#{key}"%"
		</if>
		<if test="key==null">
			order by id desc
			limit #{pageIndex},#{pageSize}
		</if>
	</sql>
	<select id="findById" parameterType="int" resultMap="clientMap">
		select *
		from client c where c.id=#{id}
	</select>
	<select id="findByIdCard" parameterType="string" resultType="integer">
		select c.id from client c where c.id_card=#{idCard} limit 0,1
	</select>
	<insert id="add" parameterType="Client">
		insert into
		client(code,address,name,id_card,phone,province,city,area)
		values(#{code},#{address},#{name},#{idCard},#{phone},#{province},#{city},#{area})
	</insert>
	<update id="update" parameterType="Client">
		update client set
		code=#{code},address=#{address},name=#{name},id_card=#{idCard},phone=#{phone},province=#{province},city=#{city},area=#{area}
		where id=#{id}
	</update>
	<delete id="rmove" parameterType="int">
		delete from client where
		id=#{id}
	</delete>
	<select id="finds" parameterType="SearchForm" resultMap="clientMap">
		<include refid="sql_select"></include>
		<include refid="select_where"></include>
	</select>
	<select id="totals" resultType="Integer">
	    select count(*) from client 
	</select>
</mapper>

　　4．mybatis-config.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>
		<typeAlias type="com.green.phonemanage.model.CellPhone"
			alias="CellPhone" />
		<typeAlias type="com.green.phonemanage.model.Client" alias="Client" />
		<typeAlias type="com.green.phonemanage.model.User" alias="User" />
		<typeAlias type="com.green.phonemanage.model.SearchForm"
			alias="SearchForm" />
		<typeAlias type="com.green.phonemanage.dao.CellPhoneDao"
			alias="CellPhoneDao" />
		<typeAlias type="com.green.phonemanage.dao.ClientDao" alias="ClientDao" />
		<typeAlias type="com.green.phonemanage.dao.UserDao" alias="UserDao" />
	</typeAliases>
	<mappers>
	</mappers>
</configuration>

　　其中数据库管理交给spring来管理如果只需启动mybaits那么可以参考mybaits安装将环境配置加入配置文件．