Mybatis系列之实战篇(上)

来源:转载

Mybatis系列之实战篇(上)

引言

经过前面的学习(本文最后有前面文章的链接,小伙伴们可以根据需要自行阅读),我们需要一个实际的项目来练练手,对掌握的知识做进一步的巩固和沉淀。

术语介绍

本文以一个实际电商项目上中的客户管理模块作为讲解案例。在正文开始前,先约定一些术语,这些术语在后面的讲解中会频繁出现,所以希望小伙伴发用两分钟的时间看一下,这对后面的阅读会有很大帮助。

Party

我们将一个集团客户的主要信息抽象提取出来,称之为Party。每个Party下允许有多个Code。

Code

客户代码,每个集团客户允许有一个或多个Code。客户在系统中制作采购订单时,是以Code为实际操作者,也就是说每个采购订单必须有且仅有一个Code。每个Code对应着我们一个生产工厂。

Plant

生产工厂,每个生产工厂提供的产品不同,客户要购买某个工厂的商品,必须有一个与之对应的Code。

Location

客户地点,由Address和Purpose组合而成。每个Code下允许有多个用途不同的地点,限制如下:有且仅有一个用途为Legal的地点(营业执照上的地点)
有一个或多个用途为Ship To的地点(收货地点)
有一个或多个用途为Bill To的地点(发票寄送地点)

Address

地址。实际的物理地址,有省、市、区、街道等信息组成。一个Address可以有搭配不同的用途(Purpose)组成不同的客房地点Location。

Purpose

地址用途。这是一个附加的信息,用于标识出地点的用途,比如注册地、收货地、发票寄送地等。

数据库实体关系图



数据库DDL

CREATE TABLE `addr_province` ( `province_id` int(4) NOT NULL AUTO_INCREMENT, `province_name` varchar(64) NOT NULL, `is_valid` tinyint(4) NOT NULL DEFAULT '1', `created_time` datetime NOT NULL, `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`province_id`), KEY `province_name` (`province_name`)) ENGINE=InnoDB AUTO_INCREMENT=35 DEFAULT CHARSET=utf8 COMMENT='省份表';CREATE TABLE `addr_city` ( `city_id` int(4) NOT NULL AUTO_INCREMENT, `city_name` varchar(64) NOT NULL, `province_id` int(4) NOT NULL, `is_valid` tinyint(4) NOT NULL DEFAULT '1', `created_time` datetime NOT NULL, `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`city_id`), KEY `province_id` (`province_id`), KEY `city_name` (`city_name`), CONSTRAINT `sys_city_ibfk_1` FOREIGN KEY (`province_id`) REFERENCES `addr_province` (`province_id`)) ENGINE=InnoDB AUTO_INCREMENT=387 DEFAULT CHARSET=utf8 COMMENT='城市表';CREATE TABLE `addr_area` ( `area_id` int(4) NOT NULL AUTO_INCREMENT, `area_name` varchar(64) NOT NULL, `city_id` int(4) NOT NULL, `is_valid` tinyint(4) NOT NULL DEFAULT '1', `created_time` datetime NOT NULL, `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`area_id`), KEY `city_id` (`city_id`), KEY `area_name` (`area_name`), CONSTRAINT `sys_area_ibfk_1` FOREIGN KEY (`city_id`) REFERENCES `addr_city` (`city_id`)) ENGINE=InnoDB AUTO_INCREMENT=3124 DEFAULT CHARSET=utf8 COMMENT='地区表';CREATE TABLE `address` ( `address_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `province_id` int(10) unsigned NOT NULL, `city_id` int(10) unsigned NOT NULL, `area_id` int(10) unsigned NOT NULL, `street` varchar(128) NOT NULL, `zip_code` varchar(16) DEFAULT NULL, `contact_person` varchar(32) DEFAULT NULL, `tel` varchar(32) DEFAULT NULL, `fax` varchar(32) DEFAULT NULL, `cell_phone` varchar(32) DEFAULT NULL, `email` varchar(128) DEFAULT NULL, `is_valid` tinyint(4) NOT NULL DEFAULT '1', `created_time` datetime NOT NULL, `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`address_id`)) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COMMENT='地址表';CREATE TABLE `addr_purpose` ( `purpose_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `purpose_name` varchar(255) NOT NULL, `is_valid` tinyint(4) NOT NULL DEFAULT '1', `created_time` datetime NOT NULL, `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`purpose_id`), UNIQUE KEY `purpose_name` (`purpose_name`)) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COMMENT='地址用途表';CREATE TABLE `customer_party` ( `party_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `party_name` varchar(128) DEFAULT NULL, `country` varchar(32) DEFAULT NULL, `area` varchar(32) DEFAULT NULL, `is_valid` tinyint(4) DEFAULT '1', `created_time` datetime DEFAULT NULL, `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`party_id`), UNIQUE KEY `party_name` (`party_name`) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='客户Party主表';CREATE TABLE `customer_code` ( `code_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `customer_code` varchar(8) DEFAULT NULL COMMENT '客户代码', `plant_id` int(10) unsigned NOT NULL, `party_id` int(10) unsigned DEFAULT NULL, `is_valid` tinyint(4) DEFAULT '1', `created_time` datetime DEFAULT NULL, `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`code_id`), KEY `plant_id` (`plant_id`), KEY `party_id` (`party_id`), CONSTRAINT `customer_code_ibfk_1` FOREIGN KEY (`plant_id`) REFERENCES `plant` (`plant_id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `customer_code_ibfk_2` FOREIGN KEY (`party_id`) REFERENCES `customer_party` (`party_id`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 COMMENT='客户代码表';CREATE TABLE `customer_location` ( `location_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `code_id` int(10) unsigned DEFAULT NULL, `address_id` int(10) unsigned DEFAULT NULL, `purpose_id` int(10) unsigned DEFAULT NULL, `is_valid` tinyint(4) DEFAULT '1', `created_time` datetime DEFAULT NULL, `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`location_id`), UNIQUE KEY `code_id` (`code_id`,`address_id`,`purpose_id`), KEY `address_id` (`address_id`), KEY `code_id_2` (`code_id`), KEY `purpose_id` (`purpose_id`), CONSTRAINT `customer_location_ibfk_1` FOREIGN KEY (`code_id`) REFERENCES `customer_code` (`code_id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `customer_location_ibfk_2` FOREIGN KEY (`address_id`) REFERENCES `address` (`address_id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `customer_location_ibfk_3` FOREIGN KEY (`purpose_id`) REFERENCES `addr_purpose` (`purpose_id`) ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COMMENT='客户地址表';
鉴于这是一个实际的项目,有些设计字段不方便公开,所以这里仅仅使用最基础的字段。

项目目录结构


包说明:

com.emerson.etao.controller: Spring MVC控制器包com.emerson.etao.dao:数据访问层包,Mybatis映射文件对应的Dao接口存放在这个包中com.emerson.etao.entity:数据实体类包,com.emerson.etao.service:服务层包,服务层接口定义存放在这个包中com.emerson.etao.service.impl:服务层实现包,服务层接口实现代码存放在这个包中com.emerson.etao.security:项目安全包,用户认证、授权、许可等Shiro相关代码com.emerson.etao.mapping:Mybatis映射文件存放在此包中

依赖Jar包

使用Maven对项目进行管理,只需要POM.xml文件中加入依赖的Jar包即可。
<dependencies><!-- Spring --><dependency><groupId>org.springframework</groupId><artifactId>spring-context</artifactId><version>${org.springframework-version}</version><exclusions><!-- Exclude Commons Logging in favor of SLF4j --><exclusion><groupId>commons-logging</groupId><artifactId>commons-logging</artifactId></exclusion></exclusions></dependency><!-- Spring MVC --><dependency><groupId>org.springframework</groupId><artifactId>spring-webmvc</artifactId><version>${org.springframework-version}</version></dependency><!-- Mysql & JDBC --><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>5.1.36</version></dependency><dependency><groupId>org.springframework</groupId><artifactId>spring-jdbc</artifactId><version>4.2.1.RELEASE</version><scope>compile</scope></dependency><!-- mybaits-spring --><dependency><groupId>org.mybatis</groupId><artifactId>mybatis-spring</artifactId><version>1.2.3</version></dependency><!-- mybatis --><dependency><groupId>org.mybatis</groupId><artifactId>mybatis</artifactId><version>3.3.0</version></dependency><!-- AspectJ --><dependency><groupId>org.aspectj</groupId><artifactId>aspectjrt</artifactId><version>${org.aspectj-version}</version></dependency><!-- Logging --><dependency><groupId>org.slf4j</groupId><artifactId>slf4j-api</artifactId><version>${org.slf4j-version}</version></dependency><dependency><groupId>org.slf4j</groupId><artifactId>jcl-over-slf4j</artifactId><version>${org.slf4j-version}</version><scope>runtime</scope></dependency><dependency><groupId>org.slf4j</groupId><artifactId>slf4j-log4j12</artifactId><version>${org.slf4j-version}</version><scope>runtime</scope></dependency><dependency><groupId>log4j</groupId><artifactId>log4j</artifactId><version>1.2.15</version><exclusions><exclusion><groupId>javax.mail</groupId><artifactId>mail</artifactId></exclusion><exclusion><groupId>javax.jms</groupId><artifactId>jms</artifactId></exclusion><exclusion><groupId>com.sun.jdmk</groupId><artifactId>jmxtools</artifactId></exclusion><exclusion><groupId>com.sun.jmx</groupId><artifactId>jmxri</artifactId></exclusion></exclusions><scope>runtime</scope></dependency><!-- @Inject --><dependency><groupId>javax.inject</groupId><artifactId>javax.inject</artifactId><version>1</version></dependency><!-- Servlet --><dependency><groupId>javax.servlet</groupId><artifactId>servlet-api</artifactId><version>2.5</version><scope>provided</scope></dependency><dependency><groupId>javax.servlet.jsp</groupId><artifactId>jsp-api</artifactId><version>2.1</version><scope>provided</scope></dependency><dependency><groupId>javax.servlet</groupId><artifactId>jstl</artifactId><version>1.2</version></dependency><!-- Test --><dependency><groupId>junit</groupId><artifactId>junit</artifactId><version>4.7</version><scope>test</scope></dependency><!-- Shiro --><dependency><groupId>org.apache.shiro</groupId><artifactId>shiro-core</artifactId><version>1.2.3</version></dependency><!-- JSON --><dependency><groupId>org.codehaus.jackson</groupId><artifactId>jackson-mapper-asl</artifactId><version>1.9.13</version></dependency></dependencies>

Web.xml & Spring & Mybatis配置

以下是web文件,Spring和Mybatis的配置文件,以将所有与本文无关的配置删除掉了。

Web.xml

<?xml version="1.0" encoding="UTF-8"?><web-app version="2.5" xmlns="http://java.sun.com/xml/ns/javaee"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"> <!-- The definition of the Root Spring Container shared by all Servlets and Filters --><context-param><param-name>contextConfigLocation</param-name><param-value>/WEB-INF/spring/root-context.xml</param-value></context-param><!-- Creates the Spring Container shared by all Servlets and Filters --><listener><listener-class>org.springframework.web.context.ContextLoaderListener</listener-class></listener><!-- Processes application requests --><servlet><servlet-name>appServlet</servlet-name><servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class><init-param><param-name>contextConfigLocation</param-name><param-value>/WEB-INF/spring/appServlet/servlet-context.xml</param-value></init-param><load-on-startup>1</load-on-startup></servlet><servlet-mapping><servlet-name>appServlet</servlet-name><url-pattern>/</url-pattern></servlet-mapping> <!-- 支持UTF-8编码 --> <filter><filter-name>encodingFilter</filter-name><filter-class>org.springframework.web.filter.CharacterEncodingFilter</filter-class><init-param> <param-name>encoding</param-name> <param-value>UTF-8</param-value></init-param> </filter> <filter-mapping> <filter-name>encodingFilter</filter-name> <url-pattern>/*</url-pattern> </filter-mapping></web-app>

Spring配置文件

<?xml version="1.0" encoding="UTF-8"?><beans xmlns="http://www.springframework.org/schema/beans"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd"><!-- Root Context: defines shared resources visible to all other web components --><context:component-scan base-package="com.emerson.etao" /><!-- dataSource --><bean id="dataSource"class="org.springframework.jdbc.datasource.DriverManagerDataSource"><property name="driverClassName" value="com.mysql.jdbc.Driver" /><property name="url" value="jdbc:mysql://127.0.0.1:3306/DATABASE_NAME" /><property name="username" value="DB_USERNAME" /><property name="password" value="DB_PASSWORD" /></bean><!-- Mybatis SqlSessionFactory --><bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean"><property name="dataSource" ref="dataSource" /><!-- 自动扫描mapping.xml文件 --><property name="mapperLocations" value="classpath:com/emerson/etao/mapping/**/*.xml"></property><property name="configLocation" value="classpath:mybatis-config.xml"></property></bean><!-- DAO接口所在包名,Spring会自动查找其下的类 --><bean id="mapperScanner" class="org.mybatis.spring.mapper.MapperScannerConfigurer"><property name="basePackage" value="com.emerson.etao.dao" /><property name="sqlSessionFactoryBeanName" value="sqlSessionFactory" /></bean></beans>

Mybatis配置

<?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> <settings><!-- 将数据库字段命名规则A_COLUMN转换为Java使用的驼峰式命名规则aCloumn --><setting name="mapUnderscoreToCamelCase" value="true" /><setting name="logImpl" value="STDOUT_LOGGING" /> </settings></configuration>

Mybatis接口及映射文件模板

这里使用了接口式编程方式,为每一个映射文件指定一个映射接口。考虑到每种基础数据都会对应着CRUD的操作,所以这里对映射文件中的一些元素的命名规则进行统一设计,且提供了映射接口的基接口。如果其他DAO接口需要增加额外的方法,只需要继承此接口并定义额外的方法签名即可。

基类接口

package com.emerson.etao.dao;import java.util.List;/** * DAO基类接口 * 使用此接口规范Mybatis映射文件中相应元素的id命名,所有映射文件接口需继承此接口 * * @author Chris Mao(Zibing) * * @param <T> */public interface IBaseDao<T> {/** * 规则Id值查询相应的数据表记录 * * @param id * @return T */public T getById(int id);/** * 获取数据表中所有记录 * * @return List */public List<T> getAll();/** * 将数据插入到数据表中 * * @param entity * @return 写入成功的记录条数 */public int insert(T entity);/** * 将数据批量插入到数据表中 * * @param entities * @return 写入成功的记录条数 */public int batchInsert(List<T> entities);/** * 更新数据表中记录 * * @param entity * @return 更新成功的记录条数 */public int update(T entity);/** * 删除指定id的数据表记录 * * @param id * @return 删除的记录条数 */public int delete(int id);/** * 批量删除数据表记录 * @param ids * @return 删除的记录条数 */public int batchDelete(List<Integer> ids);/** * 查找符合条件的记录 * * @param entity * @return List */public List<T> find(T entity);}
接口中的每一个方法,分别对应着Mybatis映射文件中不同的元素的id值。这里对find方法简单说明一下,因为Mybatis映射文件中只允许指定一个传入参数,而查询往往会用到多条件的查询的,所以使用了一个小技巧,只需使用set方法将查询条件赋值到相应的实体类对象属性中,然后将这个对象作为一个查询参数传入即可,在映射文件中根据传入实体类对象的属性值动态的生成SQL语句。

映射文件模板

需要在留空的属性处填写实际的内容。
<?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="Java接口全限定名"> <sql id="selectColumns">FIELD LIST</sql> <sql id="insertColumns">FIELD LIST</sql> <select id="getById" parameterType="int" resultType="">SELECT<include refid="selectColumns"></include>FROM TABLENAME WHERE KEY_FIELD = #{id} </select> <select id="getAll" resultType="">SELECT<include refid="selectColumns"></include>FROM TABLENAME ORDER BY KEY_FIELD </select> <!-- 插入数据并返回主键值,注意,这里的KeyProperty应该是Java类里的属性名称,而非数据表中的字段名 --> <insert id="insert" parameterType="" useGeneratedKeys="true" keyProperty="">INSERT INTOTABLENAME(<include refid="insertColumns"></include>)VALUES() </insert> <!-- 批量新增 --> <insert id="batchInsert" parameterType="java.util.List">INSERT INTO TABLENAME(<include refid="insertColumns"></include>) VALUES<foreach collection="list" item="c" separator=","> ()</foreach> </insert> <!-- 更新数据 --> <update id="update" parameterType="">UPDATE TABLENAME<set></set>WHERE KEY_FIELD = #{} </update> <!-- 根据传入的Id值,删除单条记录 --> <delete id="delete" parameterType="int">DELETE FROM TABLENAME WHERE KEY_FIELD = #{id} </delete> <!-- 根据传入的Id值列表,删除多条记录 --> <delete id="batchDelete" parameterType="java.util.List">DELETE FROM TABLENAME WHERE KEY_FIELD in<foreach collection="list" item="item" index="index" open="(" close=")" separator=","> #{item}</foreach> </delete></mapper>

Service接口设计及实现

我们对映射文件及Dao接口都设计了一些规范,那么Service接口自然也不会例外了。这里将通用的方法提取出来,编写了Service的基础接口及实现,后面所有的接口都需继承自此接口和实现。
package com.emerson.etao.service;import java.util.List;/** * 服务层基础接口,所有服务接口需继承此接口 * * @author Chris Mao(Zibing) * */public interface IBaseService<T> {/** * 规则Id值查询相应的数据表记录 * * @param id * @return T */public T getById(int id);/** * 获取数据表中所有记录,如果参数pageIndex不为0,则返回指定页面数据,否则返回所有数据 * * @param pageIndex 页面索引,0表示不分页 * @return */public List<T> getAll(int pageIndex);/** * 将数据插入到数据表中 * * @param entity * @return 写入成功的记录条数 */public int insert(T entity);/** * 将数据批量插入到数据表中 * * @param entities * @return 写入成功的记录条数 */public int batchInsert(List<T> entities);/** * 更新数据表中记录 * * @param entity * @return 更新成功的记录条数 */public int update(T entity);/** * 删除指定id的数据表记录 * * @param id * @return 删除的记录条数 */public int delete(int id);/** * 批量删除数据表记录 * @param ids * @return 删除的记录条数 */public int batchDelete(List<Integer> ids);/** * 查找符合条件的记录 * * @param entity * @return List */public List<T> find(T entity);}

接口实现
package com.emerson.etao.service.impl;import java.util.List;import com.emerson.etao.dao.IBaseDao;import com.emerson.etao.service.IBaseService;import com.github.pagehelper.PageHelper;/** * 服务层实现类基类 * * 所有务服层接口实现类须继承此类,且必须覆盖抽象方法getBaseDao(),以便在基础方法中获取与数据库交互的dao对象 * * 例子: * * @Service("communicatorService") public class CommunicatorServiceImpl extends * IBaseServiceImpl<Communicator> implements ICommunicatorService { * * @Resource private ICommunicatorDao dao; * * @Override protected IBaseDao<Communicator> getBaseDao() { return this.dao; } * } * * @author Chris Mao(Zibing) * * @param <T> */public abstract class BaseServiceImpl<T> implements IBaseService<T> {protected abstract IBaseDao<T> getBaseDao();@Overridepublic T getById(int id) {return this.getBaseDao().getById(id);}@Overridepublic List<T> getAll(int pageIndex) {if (pageIndex > 0) {int pageSize = (pageIndex == 0) ? 0 : 10;PageHelper.startPage(pageIndex, pageSize);}return this.getBaseDao().getAll();}@Overridepublic int insert(T entity) {return this.getBaseDao().insert(entity);}@Overridepublic int batchInsert(List<T> entities) {return this.getBaseDao().batchInsert(entities);}@Overridepublic int update(T entity) {return this.getBaseDao().update(entity);}@Overridepublic int delete(int id) {return this.getBaseDao().delete(id);}@Overridepublic int batchDelete(List<Integer> ids) {return this.getBaseDao().batchDelete(ids);}@Overridepublic List<T> find(T entity) {return this.getBaseDao().find(entity);}}


附录

《Mybatis系列(一)入门》《Mybatis系列(二)配置》《Mybatis系列(三)简单示例》《Mybatis系列(四)映射文件》《Mybatis系列(五)动态SQL》《Mybatis系列(六)接口式编程》《Mybatis系列(七)关联映射》《Mybatis系列(八)集合映射》《Mybatis系列(九)Spring & Mybatis整合》



分享给朋友:
您可能感兴趣的文章:
随机阅读: