Mybatis 實現手機管理系統的持久化數據訪問層
來自： http://www.cnblogs.com/maybo/p/5183735.html
最近公司需要對客戶手機進行管理并提供二維碼存儲手機串號的加密字符．供其他接入系統通過掃面二維碼解析使用．系統提供手機信息管理，客戶管理，用戶管理功能．
１．使用到的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安裝將環境配置加入配置文件．
本文由用戶 JarrodGalbr 自行上傳分享，僅供網友學習交流。所有權歸原作者，若您的權利被侵害，請聯系管理員。
轉載本站原創文章，請注明出處，并保留原始鏈接、圖片水印。
本站是一個以用戶分享為主的開源技術平臺，歡迎各類分享！