mybatis环境搭建、模糊查询、分页、增删改、事务处理

最近研究了下mybatis，它和hibernate创建和操作类似

1. 从配置文件(通常是XML配置文件中)得到 sessionfactory.
2. 由sessionfactory 产生 session
3. 在session 中完成对数据的增删改查和事务提交等.
4. 在用完之后关闭session 。

 

废话不多说我就直接上图写代码。

先在scott数据库中建一个表

create table INFO_LOG
(
  operator   VARCHAR2(20) not null,
  createdate NUMBER(8) default TO_CHAR(SYSDATE,'YYYYMMDD'),
  createtime NUMBER(6) default TO_CHAR(SYSDATE,'HH24MISS'),
  mac        VARCHAR2(100),
  annex      VARCHAR2(2000)
)

建一个web项目工程ssm 引入mybatis-3.2.7.jar、ojdbc14.jar

先配置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>
<span style="white-space:pre">	</span><!-- 引用资源文件 -->
<span style="white-space:pre">	</span><properties resource="config.properties"  />
<span style="white-space:pre">	</span><!-- 类型别名是为 Java 类型命名一个短的名字 -->
<span style="white-space:pre">	</span><typeAliases>
<span style="white-space:pre">		</span><typeAlias alias="InfoLog" type="com.iweye.ssm.pojo.InfoLog"/>
<span style="white-space:pre">	</span></typeAliases>
<span style="white-space:pre">	</span>
<span style="white-space:pre">	</span><environments default="development">
<span style="white-space:pre">		</span><environment id="development">
<span style="white-space:pre">			</span><transactionManager type="JDBC" />
<span style="white-space:pre">			</span><dataSource type="POOLED">
<span style="white-space:pre">				</span><property name="driver" value="${driver}" />
<span style="white-space:pre">				</span><property name="url" value="${url}" />
<span style="white-space:pre">				</span><property name="username" value="${username}" />
<span style="white-space:pre">				</span><property name="password" value="${password}" />
<span style="white-space:pre">			</span></dataSource>
<span style="white-space:pre">		</span></environment>
<span style="white-space:pre">	</span></environments>
<span style="white-space:pre">	</span>
<span style="white-space:pre">	</span><mappers>
<span style="white-space:pre">		</span><mapper resource="com/iweye/ssm/dao/InfoLog.xml" />
<span style="white-space:pre">	</span></mappers>
</configuration>

config.properties

driver=oracle.jdbc.driver.OracleDriver
url=jdbc:oracle:thin:@localhost:1521:orcl
username=scott
password=tiger

dao包下面的InfoLog.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.iweye.ssm.dao.InfoLogMapper">
    <select id="selectAllInfoLogs" resultType="InfoLog">
        select * from info_log
    </select>
    <select id="selectInfoLog"  parameterType="InfoLog"   resultType="InfoLog">
        SELECT * from info_log  
        WHERE operator = #{operator}
        and createdate=#{createdate} and createtime=#{createtime}
    </select>
    
    <insert id="addInfoLog" parameterType="InfoLog"  >
      insert into info_log(operator,createdate,createtime,mac,annex)
        values(#{operator},#{createdate},#{createtime},#{mac},#{annex})
    </insert>
    <update id="updateInfoLog" parameterType="InfoLog"  >
      update  info_log 
   set createdate=#{createdate},createtime=#{createtime},mac=#{mac},annex=#{annex}
      where operator=#{operator} and createdate=#{createdate} and createtime=#{createtime}
    </update>
    <select id="selectAllInfoLogForPage"  parameterType="java.util.Map"   resultType="InfoLog">
        SELECT * FROM ( SELECT A.*, ROWNUM RN  FROM (
            select * from info_Log  where 
                   <![CDATA[createdate>=#{startDate} and createdate<=#{endDate}]]>
                   and operator like  '%'||#{operator}||'%'
        ) A WHERE <![CDATA[ ROWNUM <= #{end} ) WHERE RN >= #{start} ]]>
    </select>
    
</mapper>

之后运行DBUtil的main函数，如果不报异常说明环境搭建好了

package com.iweye.ssm.util;
 
import java.io.InputStream;
 
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
 
public class DBUtil {   
 
    public DBUtil() {
    }
    // 配置文件的所在位置和名称
    private static String resource = "mybatis-config.xml";
    // 存放连接池
     private static final ThreadLocal<SqlSession> threadLocal = new ThreadLocal<SqlSession>();
    // 用来建立连接的，该类就是连接池，使用单例设计模式
    private static SqlSessionFactory sqlSessionFactory;
    // 由于SessionFactory是重量级的，所以只实例化一次
    static {
        try {
            // 加载配置文件到内存中
            InputStream inputStream = Resources.getResourceAsStream(resource);
            // 建立连接池以及里面的连接
             sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    /**
     * 单例模式 取得数据库连接对象
     * @return
     */
    public static SqlSession getSession() {       
        // 如果没有连接，则取得一个新的连接       
        SqlSession session = threadLocal.get();
        if (session == null) {             
            session = sqlSessionFactory.openSession();          
            // 把取得出的连接记录到ThreadLocal中，以便下次使用。
            threadLocal.set(session);
        }
        return session; 
    }
 
    
    /**
     * 关闭连接
     */
     public static void closeSession() {
        SqlSession session = (SqlSession) threadLocal.get();
        threadLocal.set(null);
        if (session != null) {
            session.close();
        }
    }
     
    public static void main(String[] args) {
        System.err.println(getSession());
    }
}

 

TestDemo.java这个是我用junit测试的，可以按照顺序来试一试，只是简单的利用事务来

增查改删，还有一个模拟的分页功能

package com.iweye.ssm.test;
 
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
 
import org.apache.ibatis.transaction.Transaction;
import org.apache.ibatis.transaction.TransactionFactory;
import org.apache.ibatis.transaction.jdbc.JdbcTransactionFactory;
import org.junit.Test;
 
import com.iweye.ssm.dao.InfoLogDao;
import com.iweye.ssm.pojo.InfoLog;
import com.iweye.ssm.util.DBUtil;
 
/**
 * 从 XML 中构建 SqlSessionFactory
 * 
 * @auto <a href="mailto:1808857902@qq.com">wei_xing</a>
 * @time 2014年8月7日下午4:21:55
 * @version 2014
 */
public class TestDemo {
    @Test//增加
    public void addInfoLog() throws SQLException{
        TransactionFactory transactionFactory = new JdbcTransactionFactory(); 
        Transaction  newTransaction=transactionFactory.newTransaction(DBUtil.getSession().getConnection()); 
        try {
            InfoLogDao ild = new InfoLogDao();
            int cout = 808;
            for (int i = 0; i < 40; i++) {
                InfoLog infoLog = new InfoLog();
                infoLog.setOperator("" + (cout + i));
                infoLog.setCreatedate(20140804);
                infoLog.setCreatetime(183521);
                infoLog.setMac("999:3:" + i + "");
                infoLog.setAnnex("时间" + i + "");
                ild.addInfoLog(infoLog);
            }
            //#############打印总数###################//
            System.err.println("总数："+ild.selectAllInfoLogsCount());
            //#############打印###################//
            for(InfoLog log : ild.getAll()){
                System.err.println(log.toString());
            }
        } catch (Exception e) {
             newTransaction.rollback();
        }finally{
            newTransaction.commit();
             newTransaction.close(); 
        }
    }
    @Test//查询所有的
    public void selectAllInfoLogs(){
         InfoLogDao ild=new InfoLogDao();
        for(InfoLog log : ild.getAll()){
            System.err.println(log.toString());
        }
    }
    @Test//查询单个信息
    public void selectInfoLog(){
        InfoLog infoLog = new InfoLog();
        infoLog.setOperator("811");
        infoLog.setCreatedate(20140804);
        infoLog.setCreatetime(183521);
        System.err.println(new InfoLogDao().selectInfoLog(infoLog));
    }
    @Test//模拟分页查询模糊查询
    public void selectAllInfoLogForPage(){
        Map<String,String> map = new HashMap<String,String>();
        map.put("startDate", "20140702");
        map.put("endDate", "20140809");
        map.put("operator", "5");
        map.put("start", "1");
        map.put("end", "20");
        for(InfoLog log : new InfoLogDao().selectAllInfoLogForPage(map)){
            System.err.println(log.toString());
        }
    }
    @Test //修改
    public void update() throws SQLException{
        TransactionFactory transactionFactory = new JdbcTransactionFactory(); 
        Transaction  newTransaction=transactionFactory.newTransaction(DBUtil.getSession().getConnection()); 
        try {
            InfoLog infolog = new InfoLog();
            infolog.setAnnex("时间71111");
            infolog.setOperator("815");
            infolog.setCreatedate(20140804);
            infolog.setCreatetime(183521);
            infolog.setMac("999:3:7111");
            System.err.println(new InfoLogDao().updateInfoLog(infolog));
            
            System.err.println(new InfoLogDao().selectInfoLog(infolog));
        } catch (Exception e) {
             newTransaction.rollback();
        }finally{
            newTransaction.commit();
             newTransaction.close(); 
        }
    }
    
    @Test //删除
    public void delete() throws SQLException{
        TransactionFactory transactionFactory = new JdbcTransactionFactory(); 
        Transaction  newTransaction=transactionFactory.newTransaction(DBUtil.getSession().getConnection()); 
        try {
            InfoLog infolog = new InfoLog();
            infolog.setAnnex("时间71111");
            infolog.setOperator("815");
            infolog.setCreatedate(20140804);
            infolog.setCreatetime(183521);
            infolog.setMac("999:3:7111");
            System.err.println(new InfoLogDao().deleteInfoLog(infolog));
            
            System.err.println(new InfoLogDao().selectInfoLog(infolog));
        } catch (Exception e) {
             newTransaction.rollback();
        }finally{
            newTransaction.commit();
             newTransaction.close(); 
        }
    }
}