ibatis的应用

原本是 ibatis-2.3.4 中的一个例子 但是发现例子中有少量的错误，以及功能并不全面所以加以修改

 

首先创建一个 实体bean

Account.java

public class Account {

  private int id;
  private String firstName;
  private String lastName;
  private String emailAddress;

  public int getId() {
    return id;
  }

  public void setId(int id) {
    this.id = id;
  }

  public String getFirstName() {
    return firstName;
  }

  public void setFirstName(String firstName) {
    this.firstName = firstName;
  }

  public String getLastName() {
    return lastName;
  }

  public void setLastName(String lastName) {
    this.lastName = lastName;
  }

  public String getEmailAddress() {
    return emailAddress;
  }

  public void setEmailAddress(String emailAddress) {
    this.emailAddress = emailAddress;
  }

  @Override
	public String toString() {
		return "id:"+this.id+"\tfirstName:"+this.firstName+"\tlastName:"+lastName+"\temailAddress:"+this.emailAddress;
	}
}

 

可以直接借用一下源码例子中的这个类，我只是重写了一下他自身的 toString 方法。

 

然后编写一个 ibatis实现的CRUD 类

SimpleExample.java

import java.io.IOException;
import java.io.Reader;
import java.sql.SQLException;
import java.util.List;

import com.ibatis.common.resources.Resources;
import com.ibatis.sqlmap.client.SqlMapClient;
import com.ibatis.sqlmap.client.SqlMapClientBuilder;

/**
 * This is not a best practices class.  It's just an example
 * to give you an idea of how iBATIS works.  For a more complete
 * example, see JPetStore 5.0 at http://www.ibatis.com.
 */
public class SimpleExample {

  /**
   * SqlMapClient instances are thread safe, so you only need one.
   * In this case, we'll use a static singleton.  So sue me.  ;-)
   */
  private static SqlMapClient sqlMapper;

  /**
   * It's not a good idea to put code that can fail in a class initializer,
   * but for sake of argument, here's how you configure an SQL Map.
   */
  static {
    try {
      Reader reader = Resources.getResourceAsReader("SqlMapConfig.xml");
      sqlMapper = SqlMapClientBuilder.buildSqlMapClient(reader);
      reader.close(); 
    } catch (IOException e) {
      throw new RuntimeException("Something bad happened while building the SqlMapClient instance." + e, e);
    }
  }

  public static List<?> selectAllAccounts () throws SQLException {
    return sqlMapper.queryForList("selectAllAccounts");
  }
  
  public static List<?> selectAccountByName(String str) throws SQLException{
	  return sqlMapper.queryForList("selectAccountByName",str) ;
  }

  public static Account selectAccountById  (int id) throws SQLException {
    return (Account) sqlMapper.queryForObject("selectAccountById", id);
  }

  public static void insertAccount (Account account) throws SQLException {
    try {
		sqlMapper.startTransaction() ;
		sqlMapper.insert("insertAccount", account);
		sqlMapper.commitTransaction() ;
	} catch (SQLException e) {
		sqlMapper.getCurrentConnection().rollback();
		e.printStackTrace();
	}
  }

  public static void insertAccountBySequence (Account account) throws SQLException {
	try {
		sqlMapper.startTransaction() ;
		sqlMapper.insert("insertAccountBySequence", account);
		sqlMapper.commitTransaction() ;
	} catch (SQLException e) {
		sqlMapper.getCurrentConnection().rollback();
		e.printStackTrace();
	}
  }
  
  public static void updateAccount (Account account) throws SQLException {
	  try {
			sqlMapper.startTransaction() ;
			sqlMapper.update("updateAccount", account);
			sqlMapper.commitTransaction() ;
		} catch (SQLException e) {
			sqlMapper.getCurrentConnection().rollback();
			e.printStackTrace();
		}
  }

  public static void deleteAccount (int id) throws SQLException {
	  try {
			sqlMapper.startTransaction() ;
			sqlMapper.delete("deleteAccount", id);
			sqlMapper.commitTransaction() ;
		} catch (SQLException e) {
			sqlMapper.getCurrentConnection().rollback();
			e.printStackTrace();
		}
  }

}

  

我在里面添加了模糊查询及其主键自动增长的添加方式

 

现在将源码例子中的两个 XML 文件复制到 src 目录

SqlMapConfig.xml 文件

<?xml version="1.0" encoding="UTF-8" ?>

<!DOCTYPE sqlMapConfig      
    PUBLIC "-//ibatis.apache.org//DTD SQL Map Config 2.0//EN"      
    "http://ibatis.apache.org/dtd/sql-map-config-2.dtd">

<sqlMapConfig>
	<properties resource="SqlMap.properties"/>
  <!-- Configure a built-in transaction manager.  If you're using an 
       app server, you probably want to use its transaction manager 
       and a managed datasource -->
  <transactionManager type="JDBC" commitRequired="false">
    <dataSource type="SIMPLE">
      <property name="JDBC.Driver" value="${driver}"/>
      <property name="JDBC.ConnectionURL" value="${url}"/>
      <property name="JDBC.Username" value="${username}"/>
      <property name="JDBC.Password" value="${password}"/>
    </dataSource>
  </transactionManager>

  <!-- List the SQL Map XML files. They can be loaded from the 
       classpath, as they are here (com.domain.data...) -->
  <sqlMap resource="Account.xml"/>

</sqlMapConfig>

 

修改为以上的内容

 

Account.xml 做同样的操作

<?xml version="1.0" encoding="UTF-8" ?>

<!DOCTYPE sqlMap      
    PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"      
    "http://ibatis.apache.org/dtd/sql-map-2.dtd">

<sqlMap namespace="Account">

  <!-- Use type aliases to avoid typing the full classname every time. -->
  <typeAlias alias="Account" type="Account"/>

  <!-- Result maps describe the mapping between the columns returned
       from a query, and the class properties.  A result map isn't
       necessary if the columns (or aliases) match to the properties 
       exactly. -->
  <resultMap id="AccountResult" class="Account">
    <result property="id" column="ID"/>
    <result property="firstName" column="FIRSTNAME"/>
    <result property="lastName" column="LASTNAME"/>
    <result property="emailAddress" column="EMAILADDRESS"/>
  </resultMap>

  <!-- Select with no parameters using the result map for Account class. -->
  <select id="selectAllAccounts" resultMap="AccountResult">
    select * from ACCOUNT
  </select>

  <select id="selectAccountByName" parameterClass="String" resultClass="Account">
  	select
      ID as id,
      FIRSTNAME as firstName,
      LASTNAME as lastName,
      EMAILADDRESS as emailAddress
    from ACCOUNT
    where FIRSTNAME like '%$firstName$%'
  </select>
  
  <!-- A simpler select example without the result map.  Note the 
       aliases to match the properties of the target result class. -->
  <select id="selectAccountById" parameterClass="int" resultClass="Account">
    select
      ID as id,
      FIRSTNAME as firstName,
      LASTNAME as lastName,
      EMAILADDRESS as emailAddress
    from ACCOUNT
    where ID = #id#
  </select>
   
  <!-- Insert example, using the Account parameter class -->
  <insert id="insertAccount" parameterClass="Account">
    insert into ACCOUNT ( ID, FIRSTNAME, LASTNAME, EMAILADDRESS)
    values ( #id#, #firstName#, #lastName#, #emailAddress# )
  </insert>
  
  <insert id="insertAccountBySequence" parameterClass="Account" >
  	<selectKey keyProperty="id" resultClass="int">
  		select accountPK.nextVal as id from dual
  	</selectKey>
    insert into ACCOUNT ( ID, FIRSTNAME, LASTNAME, EMAILADDRESS)
    values ( #id#, #firstName#, #lastName#, #emailAddress# )
  </insert>

  <!-- Update example, using the Account parameter class -->
  <update id="updateAccount" parameterClass="Account">
    update ACCOUNT set
      FIRSTNAME = #firstName#,
      LASTNAME = #lastName#,
      EMAILADDRESS = #emailAddress#
    where
      ID = #id#
  </update>

  <!-- Delete example, using an integer as the parameter class -->
  <delete id="deleteAccount" parameterClass="int">
    delete from ACCOUNT where ID = #id#
  </delete>

</sqlMap>

 

 

以上操作完毕之后，需要创建一个 属性文件

SqlMap.properties 文件

driver = oracle.jdbc.driver.OracleDriver
url = jdbc:oracle:thin:@localhost:1521:DBLEE
username = jlee
password = jlee

 

 

然后将 操作Oracle数据库的 驱动包与 ibatis的jar 全部加载到 项目的 calsspath 目录下

为了可以清晰的看到 ibatis 执行的SQL语句 加入log4j的支持 所以将log4j 的jar 同样添加

到classpath 目录下 并且创建 log4j.properties 文件

log4j.properties

log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%d%p[%c]-%m%n
log4j.logger.com.ibatis=debug
log4j.logger.com.ibatis.sqlmap.engine.impl.SqlMapClientDelegate=debug
log4j.logger.java.sql.Connection=debug
log4j.logger.java.sql.Statement=debug
log4j.logger.java.sql.PreparedStatement=debug,stdout

 

 

创建一个 测试类

IbatisTest.java

import java.util.List;

import org.apache.log4j.Logger;

class IbatisTest {
	static Logger logger = Logger.getLogger(IbatisTest.class.getName());//为了在控制台可以看到打印出来的SQL语句添加log4j


	/**
	 * @param args
	 */
	public static void main(String[] args) throws Exception{
		
		Account account = new Account() ;
		account.setId(1) ;
		account.setLastName("java") ;
		account.setFirstName("JLee") ;
		account.setEmailAddress("44444444") ;
		
		account.setId(2) ;
		account.setLastName("java") ;
		account.setFirstName("JLee") ;
		account.setEmailAddress("[e-mail]") ;
		/**
		 * 执行添加记录
		 */
		SimpleExample.insertAccount(account) ;
		
		/**
		 * 以主键自动增长的方式进行添加数据
		 * 首先在  Oracle 对应的库中创建一个 Sequence
		 * create sequence accountPK start with 100 increment by 1 ;
		 */
		SimpleExample.insertAccountBySequence(account);
		
		/**
		 * 执行查询指定的记录
		 */
		account = (Account)SimpleExample.selectAccountById(1) ;
		System.out.println(account.toString());
		
		/**
		 * 执行查询所有记录
		 */
		List<Account> listAccount = (List<Account>)SimpleExample.selectAllAccounts();
		for(Account a : listAccount){
			System.out.println(a.toString());
		}
		
		/**
		 * 执行模糊查询
		 */
		List<Account> list = (List<Account>)SimpleExample.selectAccountByName("a");
		for(Account a : list){
			System.out.println("模糊查询："+a.toString());
		}
		
		/**
		 * 修改一条记录
		 */
		account.setId(1) ;
		account.setFirstName("Java") ;
		account.setLastName("JLee") ;
		account.setEmailAddress("JavaEE") ;
		SimpleExample.updateAccount(account) ;
		//修改之后查看
		account = (Account)SimpleExample.selectAccountById(1) ;
		System.out.println(account.toString());
		
		/**
		 * 删除一条记录
		 */
		SimpleExample.deleteAccount(2) ;
		//删除之后进行查看
		for(Account a : (List<Account>)SimpleExample.selectAllAccounts()){
			System.out.println(a.toString());
		}
		
	}

}

  

好了 ，项目创建完成！

项目的源文件已经上传上来。