因為新專案不想用Hibernate那麼複雜的功能,所以試用了一下MyBatis,感覺蠻好上手的,對於剛開始想接觸DAO寫法的人應該是很好理解,也不用處理表格關聯性。
先連到產品清單頁,再連到產生工具的下載頁面,下載mybatis-generator-1.3.2 release
其實也有提供eclipse的外掛,但是我不喜歡裝太多外掛作罷。
我會將要用的檔案通通放在同一個資料夾
看一下批次檔
REM set JAVA_HOME=D:\IDE\Java\jdk1.7.0_51
java -jar mybatis-generator-core-1.3.2.jar -configfile generatorConfig.xml -overwrite
很簡單就是主程式、設定檔、要複寫之前的檔案
重點都在設定檔
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE generatorConfiguration
PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
"http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">
<generatorConfiguration>
<!-- 資料庫驅動jar -->
<classPathEntry location="./postgresql-9.3-1101.jdbc41.jar" />
<context id="DB2Tables" targetRuntime="MyBatis3">
<!-- 資料庫連接 -->
<jdbcConnection driverClass="org.postgresql.Driver"
connectionURL="jdbc:postgresql://192.168.1.1:5432/secomms"
userId="postgres" password="123456">
</jdbcConnection>
<!-- false:JDBC DECIMAL、NUMERIC類型解析為Integer,默認方式 -->
<!-- true:JDBC DECIMAL、NUMERIC類型解析為java.math.BigDecimal -->
<javaTypeResolver>
<property name="forceBigDecimals" value="false" />
</javaTypeResolver>
<!-- 生成模型的包名和位置 -->
<!-- (可以自訂位址,但是路徑不存在不會自動創建使用Maven生成在target目錄下,會自動創建) -->
<javaModelGenerator targetPackage="com.secom.mobile.modules.entity"
targetProject="src">
<property name="enableSubPackages" value="false" />
<!-- 從數據庫返回的值被清理前後的空格 -->
<property name="trimStrings" value="true" />
</javaModelGenerator>
<!-- 生成的映射文件包名和位置 -->
<sqlMapGenerator targetPackage="com.secom.mobile.modules.mappings"
targetProject="src">
<property name="enableSubPackages" value="false" />
</sqlMapGenerator>
<!-- 生成DAO的包名和位置 -->
<javaClientGenerator targetPackage="com.secom.mobile.modules.dao"
targetProject="src" type="XMLMAPPER">
<property name="enableSubPackages" value="false" />
</javaClientGenerator>
<!-- tableName:用於自動生成代碼的資料庫表;domainObjectName:對應於資料庫表的javaBean類名 -->
<table tableName="userinfo" schema="public" delimitIdentifiers="true" delimitAllColumns="true">
<!-- 此欄位使用自動增加的型態,不需要加入到SQL語法 -->
<generatedKey column="serno" sqlStatement="JDBC"/>
</table>
<table tableName="usercloud" schema="public" delimitIdentifiers="true" delimitAllColumns="true"/>
</context>
</generatorConfiguration>
該調整的大概都已經調整好了,要使用的話就改一下驅動程式位置、資料庫連結、targetPackage跟列出你的Table
Table比較要注意的是,如果你欄位像是自動增加流水號的方式,請要加上
接下來就可透過命令列執行產生程式碼的動作,再把產生結果放到eclipse
dao內的Mapper就是提供我們操作資料庫表格的介面
entity則是跟資料庫對應的類別,Example則是提供我們查詢時可適用的操作物件
mappings則是實際上組合SQL的定義檔xml
在專案內需要
'org.mybatis:mybatis:3.2.8'
如果是要整合spring還要加上
'org.mybatis:mybatis-spring:1.2.2'
再來設定資料庫連線
<?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:tx="http://www.springframework.org/schema/tx"
xmlns:aop="http://www.springframework.org/schema/aop"
xsi:schemaLocation="
http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx-3.0.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop-3.0.xsd">
<!-- JNDI方式配置數據源 -->
<!-- <bean id="dataSource" class="org.springframework.jndi.JndiObjectFactoryBean">
<property name="jndiName" value="${jndiName}"></property> </bean> -->
<!-- 配置數據源 -->
<bean name="dataSource" class="com.alibaba.druid.pool.DruidDataSource"
init-method="init" destroy-method="close">
<property name="url" value="${jdbc_url}" />
<property name="username" value="${jdbc_username}" />
<property name="password" value="${jdbc_password}" />
<!-- 初始化連接大小 -->
<property name="initialSize" value="0" />
<!-- 連接池最大使用連接數量 -->
<property name="maxActive" value="20" />
<!-- 連接池最大空閒 -->
<property name="maxIdle" value="20" />
<!-- 連接池最小空閒 -->
<property name="minIdle" value="0" />
<!-- 獲取連接最大等待時間 -->
<property name="maxWait" value="60000" />
<!-- <property name="poolPreparedStatements" value="true" /> <property
name="maxPoolPreparedStatementPerConnectionSize" value="33" /> -->
<property name="validationQuery" value="${validationQuery}" />
<property name="testOnBorrow" value="false" />
<property name="testOnReturn" value="false" />
<property name="testWhileIdle" value="true" />
<!-- 配置間隔多久才進行一次檢測,檢測需要關閉的空閒連接,單位是毫秒 -->
<property name="timeBetweenEvictionRunsMillis" value="60000" />
<!-- 配置一個連接在池中最小生存的時間,單位是毫秒 -->
<property name="minEvictableIdleTimeMillis" value="25200000" />
<!-- 打開removeAbandoned功能 -->
<property name="removeAbandoned" value="true" />
<!-- 1800秒,也就是30分鐘 -->
<property name="removeAbandonedTimeout" value="1800" />
<!-- 關閉abanded連接時輸出錯誤日誌 -->
<property name="logAbandoned" value="true" />
<!-- 監控數據庫 -->
<!-- <property name="filters" value="stat" /> -->
<property name="filters" value="mergeStat" />
</bean>
<!-- myBatis文件 -->
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dataSource" />
<!-- 自動掃描entity目錄, 省掉Configuration.xml裡的手工配置 -->
<property name="mapperLocations"
value="classpath:com/secom/mobile/modules/mappings/*.xml" />
</bean>
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<property name="basePackage" value="com.secom.mobile.modules.dao" />
<property name="sqlSessionFactoryBeanName" value="sqlSessionFactory" />
</bean>
<!-- 配置事務管理器 -->
<bean id="transactionManager"
class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource" />
</bean>
<!-- 注解方式配置事物 -->
<!-- <tx:annotation-driven transaction-manager="transactionManager" /> -->
<!-- 攔截器方式配置事物 -->
<tx:advice id="transactionAdvice" transaction-manager="transactionManager">
<tx:attributes>
<tx:method name="add*" propagation="REQUIRED" />
<tx:method name="append*" propagation="REQUIRED" />
<tx:method name="insert*" propagation="REQUIRED" />
<tx:method name="save*" propagation="REQUIRED" />
<tx:method name="update*" propagation="REQUIRED" />
<tx:method name="modify*" propagation="REQUIRED" />
<tx:method name="edit*" propagation="REQUIRED" />
<tx:method name="delete*" propagation="REQUIRED" />
<tx:method name="remove*" propagation="REQUIRED" />
<tx:method name="repair" propagation="REQUIRED" />
<tx:method name="delAndRepair" propagation="REQUIRED" />
<tx:method name="get*" propagation="SUPPORTS" />
<tx:method name="find*" propagation="SUPPORTS" />
<tx:method name="load*" propagation="SUPPORTS" />
<tx:method name="search*" propagation="SUPPORTS" />
<tx:method name="datagrid*" propagation="SUPPORTS" />
<tx:method name="*" propagation="SUPPORTS" />
</tx:attributes>
</tx:advice>
<aop:config>
<aop:pointcut id="transactionPointcut"
expression="execution(* com.secom.mobile.modules.service..*(..))" />
<aop:advisor pointcut-ref="transactionPointcut"
advice-ref="transactionAdvice" />
</aop:config>
<!-- 配置druid監控spring jdbc -->
<bean id="druid-stat-interceptor"
class="com.alibaba.druid.support.spring.stat.DruidStatInterceptor">
</bean>
<bean id="druid-stat-pointcut" class="org.springframework.aop.support.JdkRegexpMethodPointcut"
scope="prototype">
<property name="patterns">
<list>
<value>com.secom.mobile.modules.service.*</value>
</list>
</property>
</bean>
<aop:config>
<aop:advisor advice-ref="druid-stat-interceptor"
pointcut-ref="druid-stat-pointcut" />
</aop:config>
</beans>
此設定檔使用了聲明方式來管理Transaction,並使用alibaba連線池,不過不是重點剛好一併放上來而已,主要你需要注意myBatis相關配置。
Service部分
package com.secom.mobile.modules.service;
import java.io.Serializable;
/**
* @author samzhu
* @param <T> 實體物件
* @param <ID> ID類型 ex:String,Long,Integer
*/
public abstract class BaseService<T,ID extends Serializable> {
/**
* 儲存
* @param record
* @return
*/
public abstract int save(T record);
/**
* 更新有值的部分
* @param record
* @return
*/
public abstract int updateSelective(T record);
/**
* 更新所有值
* @param record
* @return
*/
public abstract int updateByReplace(T record);
/**
* 取出主鍵物件
* @param id
* @return
*/
public abstract T get(ID id);
/**
* 依照主鍵刪除
* @param id
* @return
*/
public abstract int delete(ID id);
}
package com.secom.mobile.modules.service;
import java.util.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.secom.mobile.modules.dao.*;
import com.secom.mobile.modules.entity.*;
/**
* 雲端用戶管理
* @author samzhu
*
*/
@Service
public class UsercloudService extends BaseService<Usercloud,Long>{
@Autowired
private UserinfoMapper userinfoMapper;
@Autowired
private UsercloudMapper usercloudMapper;
@Override
public int save(Usercloud record) {
return usercloudMapper.insert(record);
}
@Override
public int updateSelective(Usercloud record) {
return usercloudMapper.updateByPrimaryKeySelective(record);
}
@Override
public int updateByReplace(Usercloud record) {
return usercloudMapper.updateByPrimaryKey(record);
}
@Override
public Usercloud get(Long id) {
return usercloudMapper.selectByPrimaryKey(id);
}
/**
* 取得應該唯一帳號資料
* @param subcontractid
* @param loginid
* @return
*/
public Usercloud getBySubcontractidLoginid(String subcontractid, String loginid) {
Usercloud usercloud = null;
UsercloudExample example = new UsercloudExample();
example.createCriteria().andSubcontractidEqualTo(subcontractid).andLoginidEqualTo(loginid);
List<Usercloud> list = usercloudMapper.selectByExample(example);
if(list == null || list.size() ==0){
usercloud = null;
}else{
usercloud = list.get(0);
}
return usercloud;
}
/**
* 連動存檔
* @param userinfo
* @param usercloud
* @return
*/
public int save(Userinfo userinfo, Usercloud usercloud) {
int sqlresult = 0;
userinfo.setTimecreate(new Date(System.currentTimeMillis()));
userinfoMapper.insert(userinfo);
usercloud.setSerno(userinfo.getSerno());
usercloud.setTimecreate(new Date(System.currentTimeMillis()));
usercloudMapper.insert(usercloud);
sqlresult = 1;
return sqlresult;
}
/**
* 刪除特定使用者
*/
@Override
public int delete(Long serno) {
usercloudMapper.deleteByPrimaryKey(serno);
userinfoMapper.deleteByPrimaryKey(serno);
return 1;
}
}
大部分都是透過主鍵操作,不過如果要客製搜尋的部分可參考getBySubcontractidLoginid使用Example
Mapper有提供兩種更新方式,使用時要分清楚
updateByPrimaryKey => 使用替換方式將傳入物件的所有值更新至資料表
updateByPrimaryKeySelective => 只將傳入物件有值的部分拿來更新資料表,null則不會出現在組合的sql中
再來是你如果需要記錄所有的SQL可以修改log4j設定檔來達成
log4j.additivity.SQL=false
# MyBatis logging configuration...
log4j.logger.com.secom.mobile.modules.dao=DEBUG,SQL
log4j.appender.SQL=org.apache.log4j.DailyRollingFileAppender
log4j.appender.SQL.encoding=UTF-8
log4j.appender.SQL.File=${secomms.root}/WEB-INF/logs/SQL.log
log4j.appender.SQL.DatePattern='.'yyyy-MM-dd
log4j.appender.SQL.layout=org.apache.log4j.PatternLayout
log4j.appender.SQL.layout.ConversionPattern=%d{yyyy/MM/dd HH\:mm\:ss,SSS} %X{userid} %t [%-5.5p] %c{1} - %m\n
一定要開到DEBUG才會有完整log喔
那你可以記錄到以下log
2014/11/20 15:41:56,949 http-bio-8080-exec-5 [DEBUG] selectByPrimaryKey - ==> Preparing: select "tokenid", "userid", "tokencontent", "ststus", "timecreate", "timeupdate", "timeaccess", "usertype", "custid", "contractid", "desip" from "public"."tokenmana" where "tokenid" = ?
2014/11/20 15:41:56,981 http-bio-8080-exec-5 [DEBUG] selectByPrimaryKey - ==> Parameters: 804c392a768166c329ed75b9ef361288(String)
2014/11/20 15:41:56,996 http-bio-8080-exec-5 [DEBUG] selectByPrimaryKey - <== Total: 1
2014/11/20 15:41:57,661 http-bio-8080-exec-5 [DEBUG] insert - ==> Preparing: insert into "public"."tokenmana" ("tokenid", "userid", "tokencontent", "ststus", "timecreate", "timeupdate", "timeaccess", "usertype", "custid", "contractid", "desip") values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
2014/11/20 15:41:57,677 http-bio-8080-exec-5 [DEBUG] insert - ==> Parameters: 1d73496744287f0c10173bc57b446f0f(String), mitake(String), null, 1(Integer), 2014-11-20 15:41:57.661(Timestamp), 2014-11-20 15:41:57.661(Timestamp), 2014-11-20 15:41:57.661(Timestamp), 1(Integer), 202.39.240.74(String), 00A000002(String), 202.39.240.74(String)
2014/11/20 15:41:57,677 http-bio-8080-exec-5 [DEBUG] insert - <== Updates: 1
大部分情況這樣的log已經相當夠用了