MyBatis开发,最让人开心的就是可以随意写SQL,这样有多好的性能的SQL都可以进行调优。
但是MyBatis的优点也是它的缺点,不论什么项目都需要编写SQL,令人头疼的要命,一般业务(例如单表操作)的简单查询、修改、删除、插入,都需要自己手工去编写SQL。
还好有第三方的软件给我解决这些事情,可以像使用Hibernate一样使用MyBatis,当需要进行特殊定制的再进行修改。
1. 本文档主要描述
1、表格; 2、视图;3、存储过程;4、自定义的MyBatis
1.1. 表格
com.epublic.cwa.dao.table.entity
com.epublic.cwa.dao.table.mapper
1.2. 视图
com.epublic.cwa.dao.view.entity
com.epublic.cwa.dao.view.mapper
1.3. 存储过程
com.epublic.cwa.dao.procedure.entity
com.epublic.cwa.dao.procedure.mapper
1.4. 自定义MyBatis
com.epublic.cwa.dao.custom.entity
com.epublic.cwa.dao.custom.mapper
2. 安装插件
为了快速进行开发,少做重复性劳动工作,必须使用工具。
经过Google、Bing等工具搜索与对比,选定工具:
在线文档,参考
http://ibatis.apache.org/docs/tools/ibator/
由于ibatis转移为mybatis,所以参考mybatis 最新文档:
http://code.google.com/p/mybatis/wiki/Downloads?tm=2
下载的MyBatis Generator Tool中有文档说明
2.1. 插件下载地址
http://yunpan.cn/cJaVdf3Fszf5a 访问密码 6479
下载获取MyBatis_Generator.7z文件,解压缩后,放入Eclipse的插件中,插件如何放,我就不啰嗦了,自己百度搞定。
3. 扩展自定义排序
3.1. 引入项目Jar包
一个是数据库,一个是我们将要扩展的jar
org.mybatis.generator.core_1.3.1.201101032122.jar
mysql-connector-java-5.1.13.jar
3.2. 编写代码
将plugin的生命周期和怎么扩展,
http://ibatis.apache.org/docs/tools/ibator/reference/pluggingIn.html
生命周期和怎么扩展都已经交代明了,接下来就是动手实践了
packagecom.epublic.xzk.db.mybatis.plugin;
import java.util.List;
importorg.mybatis.generator.api.CommentGenerator;
importorg.mybatis.generator.api.IntrospectedTable;
importorg.mybatis.generator.api.PluginAdapter;
importorg.mybatis.generator.api.ShellRunner;
importorg.mybatis.generator.api.dom.java.Field;
importorg.mybatis.generator.api.dom.java.FullyQualifiedJavaType;
importorg.mybatis.generator.api.dom.java.JavaVisibility;
importorg.mybatis.generator.api.dom.java.Method;
importorg.mybatis.generator.api.dom.java.Parameter;
importorg.mybatis.generator.api.dom.java.TopLevelClass;
importorg.mybatis.generator.api.dom.xml.Attribute;
importorg.mybatis.generator.api.dom.xml.TextElement;
importorg.mybatis.generator.api.dom.xml.XmlElement;
/**
* <pre>
* add paginationusing mysql limit.2
* This class isonly used in ibator code generator.
*[generatorConfiguration]
* [context id="context1"]
* [plugintype="com.epublic.xzk.db.mybatis.plugin.PaginationPlugin" /]
*
* </pre>
*/
public class PaginationPlugin extends PluginAdapter {
@Override
publicbooleanmodelExampleClassGenerated(TopLevelClasstopLevelClass,IntrospectedTable introspectedTable) {
// addfield, getter, setter for limit clause
addLimit(topLevelClass,introspectedTable,"limitStart");
addLimit(topLevelClass,introspectedTable,"limitEnd");
returnsuper.modelExampleClassGenerated(topLevelClass,introspectedTable);
}
@Override
publicbooleansqlMapSelectByExampleWithoutBLOBsElementGenerated(XmlElementelement,
IntrospectedTable introspectedTable) {
// LIMIT5,10; // 检索记录行 6-15
XmlElement isNotNullElement= new XmlElement("if");//$NON-NLS-1$
isNotNullElement.addAttribute(new Attribute("test","limitStart != null and limitStart >=0"));//$NON-NLS-1$ //$NON-NLS-2$
isNotNullElement.addElement(new TextElement("limit${limitStart} , ${limitEnd}"));
element.addElement(isNotNullElement);
// LIMIT 5;//检索前 5个记录行
returnsuper.sqlMapUpdateByExampleWithoutBLOBsElementGenerated(element,introspectedTable);
}
privatevoid addLimit(TopLevelClasstopLevelClass, IntrospectedTable introspectedTable, String name){
CommentGenerator commentGenerator =context.getCommentGenerator();
Field field= new Field();
field.setVisibility(JavaVisibility.PROTECTED);
field.setType(FullyQualifiedJavaType.getIntInstance());
field.setName(name);
field.setInitializationString("-1");
commentGenerator.addFieldComment(field,introspectedTable);
topLevelClass.addField(field);
charc =name.charAt(0);
String camel= Character.toUpperCase(c) +name.substring(1);
Method method= new Method();
method.setVisibility(JavaVisibility.PUBLIC);
method.setName("set" +camel);
method.addParameter(new Parameter(FullyQualifiedJavaType.getIntInstance(),name));
method.addBodyLine("this." +name+ "=" +name+ ";");
commentGenerator.addGeneralMethodComment(method,introspectedTable);
topLevelClass.addMethod(method);
method= new Method();
method.setVisibility(JavaVisibility.PUBLIC);
method.setReturnType(FullyQualifiedJavaType.getIntInstance());
method.setName("get" +camel);
method.addBodyLine("return " +name+ ";");
commentGenerator.addGeneralMethodComment(method,introspectedTable);
topLevelClass.addMethod(method);
}
/**
* This plugin is always valid -no properties are required
*/
publicboolean validate(List<String>warnings) {
returntrue;
}
publicstaticvoidgenerate() {
String config= PaginationPlugin.class.getClassLoader().getResource("mybatisConfig.xml").getFile();
String[] arg= { "-configfile", config, "-overwrite"};
ShellRunner.main(arg);
}
publicstaticvoidmain(String[] args) {
generate();
}
}
4. 生成脚本的样例
4.1. 表
建立文件“generatorConfig-java-table.xml”
<?xmlversion="1.0" encoding="UTF-8" ?>
<!DOCTYPEgeneratorConfiguration PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration1.0//EN""http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd" >
<generatorConfiguration >
<context id="context1" >
<plugin type="com.epublic.xzk.db.mybatis.plugin.PaginationPlugin" />
<commentGenerator>
<!-- 避免生成注解
<propertyname="suppressDate" value="true" />
-->
<property name="suppressAllComments" value="true" />
</commentGenerator>
<jdbcConnection driverClass="com.mysql.jdbc.Driver"
connectionURL="jdbc:mysql://192.168.0.128:3306/faceserver_xzk"
userId="root"
password="admin"
/>
<!-- Model文件保存位置 -->
<javaModelGenerator targetPackage="com.epublic.cwa.dao.table.entity" targetProject="01.cwa-web\src\main\java-dao">
<property name="trimStrings" value="true" />
</javaModelGenerator>
<!-- 生成的mapper文件-->
<sqlMapGenerator targetPackage="com.epublic.cwa.dao.table.mapper" targetProject="01.cwa-web\src\main\java-dao" >
</sqlMapGenerator>
<!-- 生成的查询条件的类 -->
<javaClientGenerator type="XMLMAPPER" targetPackage="com.epublic.cwa.dao.table.mapper" targetProject="01.cwa-web\src\main\java-dao">
</javaClientGenerator>
<!-- 该表作为字典表参考数据,不再使用
<tableschema="" tableName="CWA_SYS_ROLES" ></table>
<tableschema="" tableName="CWA_SYS_USER_ROLE" ></table>
-->
<table schema=""tableName="cwa_sys_functions" ></table>
<table schema=""tableName="CWA_SYS_ROLE_FUNCTION" ></table>
<table schema=""tableName="cwa_sys_users" ></table>
<table schema=""tableName="cwa_sys_model_view_dic" ></table>
<table schema=""tableName="cwa_dept" ></table>
<table schema=""tableName="CWA_USER" ></table>
<table schema=""tableName="cwa_sys_log" ></table>
</context>
</generatorConfiguration>
生成的代码如下:
4.2. 视图
建立文件“generatorConfig-java-view.xml”
<?xmlversion="1.0" encoding="UTF-8" ?>
<!DOCTYPEgeneratorConfiguration PUBLIC "-//mybatis.org//DTD MyBatis GeneratorConfiguration 1.0//EN""http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd" >
<generatorConfiguration >
<context id="MySQL_Context" >
<plugin type="com.epublic.xzk.db.mybatis.plugin.PaginationPlugin" />
<commentGenerator>
<!-- 避免生成注解
<propertyname="suppressDate" value="true" />
-->
<property name="suppressAllComments" value="true" />
</commentGenerator>
<jdbcConnection driverClass="com.mysql.jdbc.Driver"
connectionURL="jdbc:mysql://192.168.0.128:3306/faceserver_xzk"
userId="root"
password="admin"
/>
<javaTypeResolver >
<property name="forceBigDecimals" value="false" />
</javaTypeResolver>
<!-- Model文件保存位置 -->
<javaModelGenerator targetPackage="com.epublic.cwa.dao.view.entity" targetProject="01.cwa-web\src\main\java-dao">
<property name="trimStrings" value="true" />
</javaModelGenerator>
<!-- 生成的mapper文件-->
<sqlMapGenerator targetPackage="com.epublic.cwa.dao.view.mapper" targetProject="01.cwa-web\src\main\java-dao">
</sqlMapGenerator>
<!-- 生成的查询条件的类 -->
<javaClientGenerator type="XMLMAPPER" targetPackage="com.epublic.cwa.dao.view.mapper" targetProject="01.cwa-web\src\main\java-dao">
</javaClientGenerator>
<table schema=""tableName="v_cwa_sys_users"
enableCountByExample="true"
enableSelectByExample="true"
enableInsert="false"
enableUpdateByExample="false"
enableDeleteByExample="false"
selectByExampleQueryId="true"></table>
<table schema=""tableName="v_cwa_device"
enableCountByExample="true"
enableSelectByExample="true"
enableInsert="false"
enableUpdateByExample="false"
enableDeleteByExample="false"
selectByExampleQueryId="true"></table>
</context>
</generatorConfiguration>
生成脚本如下:
4.3. 自定义
自己随便写了,只要符合MyBatis就可以了。
4.4. 存储过程
自己随便写了,只要符合MyBatis就可以了。
5. 调用示例
5.1. 范例代码
/**
* 查询分页
*
* @param formDto
* 查询表单
* @param orderByDto
* @param pageDto
* @return
*/
public PageModel<VCwaDevice>queryByPage(MachineDeviceQueryDtoformDto, CommonQueryByOrderByDto orderByDto,
PageDtopageDto) {
VCwaDeviceExampleexample = new VCwaDeviceExample();
// Step02.查询总数
int totalRecord = vCwaDeviceMapper.countByExample(example);
// Step04.增加排序对象
String queryOrderBy = orderByDto.getQueryOrderBy();
if (StringUtils.isEmpty(queryOrderBy)==false) {
example.setOrderByClause(queryOrderBy);
} else {
example.setOrderByClause("id desc");
}
// Step03.计算分页对象
int pageSize = pageDto.getPageSize();
int curPage = pageDto.getCurPage();
PageModel<VCwaDevice>borrowList = new PageModel<VCwaDevice>(pageSize,curPage, totalRecord);
int startRow = borrowList.getStartRow();
example. setLimitStart(startRow);
example.setLimitEnd(pageSize);
// Step05.查询数据库,获取分页数据
List<VCwaDevice>dataList = vCwaDeviceMapper.selectByExample(example);
borrowList.setDataList(dataList);
return borrowList;
}
5.2. DTO对象
5.2.1. PageDto
packagecom.epublic.cwa.common.web.page;
import java.util.ArrayList;
import java.util.List;
/**
* 分页对象
*
* @author Ken.xu
* @version 2015年2月8日下午7:03:21
*/
public class PageDto {
// 每页显示的页码个数
privateintSHOW_PAGE_COUNT= 5;
privateintcurPage= 0;// 当前页码
privateintpageSize= 15;// 每页大小
// 以下两个参数不初始化设置
privateinttotalRows= 0;// 总记录数
privateintpageNum= 0;// 总页数
publicList<Integer> getShowPageNumList() {
List<Integer> showPageNumList = newArrayList<Integer>();
intpageNumBegin = 1,pageNumEnd= pageNum;
if(pageNum <=SHOW_PAGE_COUNT){//总页码小于5个
pageNumEnd= pageNum;
} else{
// 获取中间的位置
intpageCenterNum =SHOW_PAGE_COUNT/ 2;
if(curPage -pageCenterNum> 0) {//超过第三个,那么左侧-2为中间即可
pageNumBegin= curPage - pageCenterNum;
} else{
pageNumBegin= 0;
}
pageNumEnd= pageNumBegin + SHOW_PAGE_COUNT;
}
for(intpageNum= pageNumBegin;pageNum<= pageNumEnd;pageNum++){
showPageNumList.add(pageNum);
}
returnshowPageNumList;
}
publicPageDto() {
}
/**
* 默认构造器,初始化成员变量
*
* @param totalRows
* 总记录数
* @param curPage
* 当前页码
* @param pageSize
* 每页大小
* @param items
* 存放数据
*/
publicPageDto(inttotalRows,intcurPage,intpageSize){
this.setPage(totalRows,curPage,pageSize);
}
/**
* 默认构造器,初始化成员变量
*
* @param totalRows
* 总记录数
* @param curPage
* 当前页码
* @param pageSize
* 每页大小
*/
publicvoid setPage(inttotalRows,intcurPage,intpageSize){
this.pageSize =pageSize;
this.totalRows =totalRows;
this.curPage =curPage;
// 计算总页码
this.pageNum = (int)Math.ceil((double)totalRows / pageSize);
}
publicint getPageSize() {
returnpageSize;
}
publicvoid setPageSize(intpageSize){
this.pageSize =pageSize;
}
publicint getTotalRows() {
returntotalRows;
}
publicvoid setTotalRows(inttotalrows){
this.totalRows =totalrows;
}
publicint getPageNum() {
returnpageNum;
}
publicvoid setPageNum(intpageNum){
this.pageNum =pageNum;
}
publicint getCurPage() {
returncurPage;
}
publicvoid setCurPage(intcurPage){
this.curPage =curPage;
}
}
5.2.2. CommonQueryByOrderByDto
packagecom.epublic.cwa.common.web.dto;
/**
* 查询分页
*
* @author Ken.xu
* @version 2015年2月9日上午8:54:28
*/
public class CommonQueryByOrderByDto {
/**
* 排序的中文描述
*/
privateString queryOrderByChsText;
/**
* 排序的字符串(SQL脚本的OrderBy一部分)
*/
privateString queryOrderBy;
publicString getQueryOrderByChsText() {
returnqueryOrderByChsText;
}
publicvoid setQueryOrderByChsText(StringqueryOrderByChsText) {
this.queryOrderByChsText =queryOrderByChsText;
}
publicString getQueryOrderBy() {
returnqueryOrderBy;
}
publicvoid setQueryOrderBy(StringqueryOrderBy) {
this.queryOrderBy =queryOrderBy;
}
}