MyBatis实战篇——实现数据的增删改查

    前面我们已经讲了，MyBatis 是支持普通 SQL 查询，存储过程和高级映射的优秀持久层框架。 MyBatis 消除了几乎所有的 JDBC 代码和参数的手工设置以及对结果集的检索。MyBatis 可以使用简单的XML 或注解用于配置和原始映射，将接口和 Java 的 POJO（Plain Old Java Objects，普通的Java对象）映射成数据库中的记录。

    纸上得来终觉浅，理论最终还是要通过实践来验证，今天我们来看下mybatis到底是怎么如何运用到实践中的？

    这里我用到的框架是SSM，所以在这里我们主要解决的是怎么从dao层读取到配置文件再到sql语句，从jsp页面——controller——service这里我们不在提，而从dao层开始。

    dao层：      

<span style="font-family:KaiTi_GB2312;font-size:18px;">    /**
	 * 导出个人用户
	 * @param paramMap
	 * @return
	 * @throws Exception 
	 */
	public List exportCustomerIndividual(Map paramMap) throws Exception{
		return (List) DataAccessor.query(getNameSpace() + ".pageQuery", paramMap, RS_TYPE.LIST);
	}
</span>

    然后我们来看看其中的query方法：

    

<span style="font-family:KaiTi_GB2312;font-size:18px;">    /**
	 * 
	 * @param sqlId
	 * @param paramMap
	 * @param rsType
	 * @return
	 * @throws Exception
	 */
	public static Object query(final String sqlId, Map paramMap, RS_TYPE rsType) throws SQLException {
		Object resultObj = null;
		try {
			switch (rsType) {
				case LIST:
					resultObj = sqlMapper.queryForList(sqlId, paramMap);
					break;

				case MAP:
					resultObj = sqlMapper.queryForObject(sqlId, paramMap);
					break;
				
				case OBJECT:
					resultObj = sqlMapper.queryForObject(sqlId, paramMap);
					break;

				case PAGED:
					DataWrap dw = new DataWrap();
					/*
					 * 增加权限控制
					 * @author 
					 * @date 2013-10-4
					 */
					// =========增加权限控制
					// 开始=============================================
					// 删除此处
					// Integer count = (Integer)sqlMapper.queryForObject(sqlId +
					// "_count", paramMap);
					// dw.recordCount = count.intValue();

					int pageSize = -1;
					try {
						pageSize = Integer.parseInt((String) paramMap.get("__pageSize"));
					} catch (Exception e) {
						// e.printStackTrace();
						pageSize = 20;
					}

					int currentPage = -1;
					try {
						currentPage = Integer.parseInt((String) paramMap.get("__currentPage"));
						if (currentPage == 0) {
							currentPage = 1;
						}
					} catch (Exception e) {
						currentPage = 1;
					}

					
					List rslist = sqlMapper.queryForList(sqlId, paramMap);

					com.brick.permission.util.ControlPermissionUtil.controlPaginatedList(rslist, paramMap, sqlMapper);

					dw.recordCount = rslist.size();

					dw.pageCount = dw.recordCount / pageSize + (dw.recordCount % pageSize == 0 ? 0 : 1);
					dw.pageSize = pageSize;
					dw.currentPage = currentPage;

					int startNum = (currentPage - 1) * pageSize;
					int endNum = currentPage * pageSize > dw.recordCount ? dw.recordCount : currentPage * pageSize;

					List list = new LinkedList();

					for (int i = startNum; i < endNum; i++) {
						if (i <= rslist.size() - 1) {
							list.add(rslist.get(i));
						}
					}

					dw.rs = list;

					// =========增加权限控制
					// 结束=============================================

					resultObj = dw;
					
			}
				
		} catch (SQLException e) {
			logger.error("执行查询失败", e);
			throw e;
		}
		return resultObj;
	}
</span>

   那么我们又是怎么获取spring中的东西呢？

<span style="font-family:KaiTi_GB2312;font-size:18px;">public class DataAccessor {
	
	public static enum RS_TYPE {LIST, MAP, OBJECT, PAGED};
	public static enum OPERATION_TYPE {QUERY, INSERT, UPDATE, DELETE};
	
	private static Logger logger = Logger.getLogger(DataAccessor.class);	
	private static SqlMapClient sqlMapper;
	
	static{
			
	    try {
		      Reader reader = Resources.getResourceAsReader("config/sql/sqlmap_cfg.xml");
		      sqlMapper = SqlMapClientBuilder.buildSqlMapClient(reader);
		      reader.close(); 
		    } catch (Exception e) {
		    	e.printStackTrace();
		      // Fail fast.
		      throw new RuntimeException("Something bad happened while building the SqlMapClient instance." + e, e);
		    }
	}	
</span>

     首先获得能够连接数据库的配置文件，并且能够告诉我想要实现sql语句在什么地方：

<span style="font-family:KaiTi_GB2312;font-size:18px;"><transactionManager type="JDBC" commitRequired="true">
		<dataSource type="SIMPLE">
			<property value="oracle.jdbc.driver.OracleDriver" name="JDBC.Driver" />
			<!--<property value="jdbc:oracle:thin:@10.172.244.77:1521:oracle" name="JDBC.ConnectionURL" />
			<property name="JDBC.Username" value="factoring0002" />
			<property name="JDBC.Password" value="HopeDate2014" />-->

			<property value="jdbc:oracle:thin:@192.168.0.6:1521:orcl" name="JDBC.ConnectionURL" />
			<property value="factoring0813" name="JDBC.Username" />
			<property value="1" name="JDBC.Password" />


			<property name="Pool.MaximumActiveConnections" value="10" />
			<property name="Pool.MaximumIdleConnections" value="5" />
			<property name="Pool.MaximumWait" value="500" />
			<property name="Pool.TimeToWait" value="500" />

		</dataSource>
	</transactionManager>

<sqlMap resource="config/sql/map/puhuilicai/customer/CustomerIndividual.xml" />
</span>

     最后获得实现的sql语句：

<span style="font-family:KaiTi_GB2312;font-size:18px;"><select id="pageQuery" parameterClass="map" resultClass="java.util.HashMap">
    <![CDATA[
 			SELECT 
 				T1.ID,
		        T1.CUST_CODE,
		        NVL(T1.CUST_NAME, T1.USER_NAME) CUST_NAME,
		        T1.CUST_STATUS,
		        T1.CUST_TYPE,
		        T1.USER_NAME,
		        T1.USER_ID,
		        T1.MOBILE,
		        T1.EMAIL,
		        T1.RESIDENCE_ADDR,
		        T1.BIRTH,
		        T7.P_,
		        FLOOR(TO_NUMBER(SYSDATE-T1.BIRTH)/365) AGE,
		        T1.SEX,
		        T1.DOUCUMENT_NUM CUST_NUMBER,
		        TO_CHAR(T1.CREATE_TIME, 'YYYY-MM-DD HH24:MI:SS') CREATE_TIME,
		        TO_CHAR(T5.LAST_LOGIN_TIME, 'YYYY-MM-DD HH24:MI:SS') LAST_LOGIN_TIME,
		        NVL(T2.CERTIFIED_STATUS, 0) PHONE_STATUS,
		        NVL(T3.CERTIFIED_STATUS, 0) REAL_NAME_STATUS,
		        NVL(T6.BANK_CARD_STATUS, 1) BANK_CARD_STATUS,
		        NVL(T4.CERTIFIED_STATUS, 0) EMAIL_STATUS
		   FROM T_CUST_INDIVIDUAL T1
		   LEFT JOIN T_AUTH_PHONE T2
		    ON T1.USER_ID = T2.USER_ID AND T2.CERTIFIED_STATUS = 1
		   LEFT JOIN T_AUTH_REAL_NAME T3
		    ON T1.USER_ID = T3.USER_ID AND T3.CERTIFIED_STATUS = 1
		   LEFT JOIN T_AUTH_EMAIL T4
		    ON T1.USER_ID = T4.USER_ID AND T4.CERTIFIED_STATUS = 1
		   LEFT JOIN T_PHONE_ATTRIBUTION T7
				ON substr(T1.MOBILE,1,7)=T7.PHONE
		   LEFT JOIN (SELECT USER_ID, MAX(LOGIN_TIME) LAST_LOGIN_TIME
		                FROM T_CUST_USER_LOGIN
		               GROUP BY USER_ID) T5
		     ON T1.USER_ID = T5.USER_ID
		   LEFT JOIN T_CUST_BANK T6 ON T1.USER_ID = T6.USER_ID AND T6.STATUS = 0 AND T6.BANK_CARD_STATUS = 2
		  WHERE T1.STATUS = 0
    ]]>
    
    <isNotEmpty prepend=" " property="emailStatus" >  AND T4.CERTIFIED_STATUS=#emailStatus# </isNotEmpty>
    <isNotEmpty prepend=" " property="phoneStatus" >  AND T2.CERTIFIED_STATUS=#phoneStatus# </isNotEmpty>
    <isNotEmpty prepend=" " property="realNameStatus" >  AND T3.CERTIFIED_STATUS=#realNameStatus# </isNotEmpty>
    <isNotEmpty prepend=" " property="bankStatus" >  AND T6.BANK_CARD_STATUS=#bankStatus# </isNotEmpty>
    <isNotEmpty prepend=" " property="start_time" >  AND T1.CREATE_TIME  >= #start_time# </isNotEmpty>
    <isNotEmpty prepend=" " property="end_time" >  AND T1.CREATE_TIME <= #end_time# </isNotEmpty>
      <isNotEmpty prepend=" " property="selectedPlace" >AND T7.P_ =  <![CDATA[$selectedPlace$ ]]> </isNotEmpty>
    <isNotEmpty prepend=" " property="searchContent">
			<![CDATA[
					AND ( T1.CUST_NAME LIKE '%$searchContent$%'
					OR T1.USER_NAME LIKE '%$searchContent$%'
					OR T1.USER_ID LIKE '%$searchContent$%'
					OR T1.EMAIL LIKE '%$searchContent$%'
					OR T1.MOBILE LIKE '%$searchContent$%'
					OR T1.DOUCUMENT_NUM LIKE '%$searchContent$%')
			]]>
		</isNotEmpty>
		
		<isEmpty property="ORDERTYPE" >
			<![CDATA[ORDER BY T1.CREATE_TIME DESC]]>
		</isEmpty>
		
		<isNotEmpty property="ORDERTYPE">
			<![CDATA[ORDER BY T1.$ORDERTYPE$]]>
		</isNotEmpty>
		
  </select>
</span>

    在这里我们可以发现mybatis的灵活之处，我们可以看到里面的isNotEmpty，也就是说如果参数传进来，有这个参数我就执行，没有的话，我就不执行，这样一个sql语句，可以解决很多功能上的问题，用起来很是方便，更大的体现了抽象这一思想。
     很多东西我们只学习理论还是远远不够的，我们还要实践，这样我们才能真正地体会到好处，更好地解决实际问题。