iBatis示范

iBatis示例

使用的是ibatis-2.3.4.726.jar

数据库驱动是mysql-connector-java-5.1.7-bin.jar、ojdbc14.jar

 

实体类

package org.monday.domain;

import java.util.Date;

/**
 * 实体类
 */
public class User {

	private int id;
	private String name;
	private String phone;
	private Date birthday;
	
	//getter and setter

	
}

 

 其映射文件User.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="User">

	<!-- 设置缓存 -->
	<cacheModel id="userCache" type="MEMORY" readOnly="true" serialize="false">
		<flushInterval hours="12" />
		<flushOnExecute statement="insert" />
		<flushOnExecute statement="update" />
		<flushOnExecute statement="delete" />
		<property name="reference-type" value="WEAK" />
	</cacheModel>

	<!-- 设置结果类型 -->
	<resultMap id="User" class="org.monday.domain.User">
		<result property="id" column="id" javaType="int" jdbcType="INTEGER" />
		<result property="name" column="name" javaType="string" jdbcType="VARCHAR" />
		<result property="phone" column="phone" javaType="string" jdbcType="VARCHAR" />
		<result property="birthday" column="birthday" javaType="date" jdbcType="DATE" />
	</resultMap>

	<!-- 设置别名 -->
	<typeAlias alias="User" type="org.monday.domain.User" />

	<!-- 添加 -->
	<insert id="insert" parameterClass="User">
		<!-- MySQL -->
		<!-- 
			这个可以不写
			<selectKey resultClass="int" keyProperty="id">  
        		select last_insert_id() as id 
			</selectKey>
		 -->
		insert into user_table(name,phone,birthday)
		values(#name#,#phone#,#birthday#)
		
		<!-- Oracle 实现自增使用序列,ibatis_seq要创建建好-->
		<!-- 
			<selectKey resultClass="int" keyProperty="id" type="pre">       
	            select ibatis_seq.nextval as id from dual
			</selectKey>
			insert into user_table(id,name,phone,birthday)
			values(#id#,#name#,#phone#,#birthday#)
		 -->
	</insert>

	<!-- 修改 -->
	<update id="update" parameterClass="User">
		update user_table
		set
		name=#name#,phone=#phone#,birthday=#birthday#
		where id=#id#
	</update>

	<!-- 删除 -->
	<delete id="delete" parameterClass="int">
		delete from user_table
		where id=#id#
	</delete>

	<!-- 根据Id查询 -->
	<select id="findById" parameterClass="int" resultClass="User">
		select *
		from
		user_table
		where id=#id#
	</select>

	<!-- 查询 全部 -->
	<select id="findAll" resultMap="User" cacheModel="userCache">
		select *
		from user_table
	</select>

	<!-- 查询记录数 -->
	<select id="findCount" resultClass="int">
		select count(*)
		from user_table
	</select>

	<!-- 模糊查询 -->
	<select id="findByLike" parameterClass="string" resultClass="User">
		select *
		from user_table
		where
		name
		like '%$name$%' 
	</select>

	<!-- 日期查询 -->
	<select id="findByDate" parameterClass="java.util.HashMap" resultClass="User">
		select *
		from user_table
		<!-- 有 > >= < <= 要使用<![CDATA[]]> where <![CDATA[birthday >= #start# and birthday <= #end#]]> -->
		where birthday between #start# and #end#
	</select>

	<!-- 存储过程 -->
	<procedure id="showData" resultClass="User">
		{call showData()}
	</procedure>

	<!-- 存储过程 in -->
	<parameterMap id="in_proc" class="int"> <!-- java.lang.Integer -->
		<parameter property="param" mode="IN" javaType="int" jdbcType="INTEGER" />
	</parameterMap>
	<procedure id="in_proc" parameterMap="in_proc" resultClass="User">
		{call in_proc (?)}
	</procedure>

</sqlMap>    

 

iBatis工具类

package org.monday.util;

import java.io.Reader;

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

/**
 * iBatis工具类
 * @author Monday
 */
public class IbatisUtil {

	private static SqlMapClient sqlMapClient;

	private IbatisUtil() {
	}

	static {
		String resource = "sqlMapConfig.xml";
		Reader reader = null;
		try {
			reader = Resources.getResourceAsReader(resource);
		} catch (Exception e) {
			throw new ExceptionInInitializerError("初始化Ibatis出现错误");
		}
		sqlMapClient = SqlMapClientBuilder.buildSqlMapClient(reader);
	}

	public static SqlMapClient getSqlMapClient() {
		return sqlMapClient;
	}
}

 

 

分页类

package org.monday.util;

import java.util.ArrayList;
import java.util.List;

/**
 * 分页类
 * @author Monday
 */
public class Page<T> {

	public static final int DEFAULT_PAGE_SIZE = 10; // 每页默认显示10条记录

	private int pageNo; // 当前页数
	private int pageSize; // 每页显示条数
	private int totalCount; // 总记录数
	private List<T> list; // 结果集

	public Page() {
		this(1, DEFAULT_PAGE_SIZE, 0, new ArrayList<T>());
	}

	public Page(int pageNo, int pageSize, int totalCount, List<T> list) {
		this.pageNo = pageNo;
		this.pageSize = pageSize;
		this.totalCount = totalCount;
		this.list = list;
	}

	public int getPageNo() {
		return pageNo;
	}

	public int getPageSize() {
		return pageSize;
	}

	public int getTotalCount() {
		return totalCount;
	}

	public List<T> getList() {
		return list;
	}

	/**
	 * 总页数
	 */
	public int getTotalPage() {
		if (totalCount % pageSize == 0) {
			return totalCount / pageSize;
		} else {
			return totalCount / pageSize + 1;
		}
		// return (totalCount + pageSize - 1) / pageSize;
	}

	/**
	 * 首页
	 */
	public int getFirstPage() {
		return 1;
	}

	/**
	 * 尾页
	 */
	public int getLastPage() {
		return getTotalPage();
	}

	/**
	 * 上一页
	 */
	public int getPreviousPage() {
		// 若当前页<=首页，则返回首页
		if (pageNo <= getFirstPage()) {
			return getFirstPage();
		}
		return pageNo - 1;
	}

	/**
	 * 下一页
	 */
	public int getNextPage() {
		// 若当前页>=尾页，则返回尾页
		if (pageNo >= getLastPage()) {
			return getLastPage();
		}
		return pageNo + 1;
	}

}

 

 

Dao实现类

package org.monday.dao.impl;

import java.sql.SQLException;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.monday.dao.UserDao;
import org.monday.domain.User;
import org.monday.util.IbatisUtil;
import org.monday.util.Page;

import com.ibatis.sqlmap.client.SqlMapClient;

public class UserDaoImpl implements UserDao {

	/**
	 * 添加
	 */
	@Override
	public void insert(User user) {
		SqlMapClient sqlMapClient = IbatisUtil.getSqlMapClient();
		try {
			sqlMapClient.insert("insert", user);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	/**
	 * 修改
	 */
	@Override
	public void update(User user) {
		SqlMapClient sqlMapClient = IbatisUtil.getSqlMapClient();
		try {
			sqlMapClient.update("update", user);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	/**
	 * 根据对象删除
	 */
	@Override
	public void delete(User user) {
		SqlMapClient sqlMapClient = IbatisUtil.getSqlMapClient();
		try {
			sqlMapClient.delete("delete", user.getId());
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	/**
	 * 根据id删除（推荐）
	 */
	@Override
	public void delete(int id) {
		SqlMapClient sqlMapClient = IbatisUtil.getSqlMapClient();
		try {
			sqlMapClient.delete("delete", id);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	/**
	 * 批量添加
	 */
	@Override
	public void insertBatch(List<User> list) {
		SqlMapClient sqlMapClient = IbatisUtil.getSqlMapClient();
		try {
			sqlMapClient.startTransaction(); // 开启事务
			sqlMapClient.startBatch(); // 开启批量
			for (User user : list) {
				sqlMapClient.insert("insert", user);
			}
			sqlMapClient.executeBatch(); // 执行批量操作
			sqlMapClient.commitTransaction(); // 提交事务
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			try {
				sqlMapClient.endTransaction(); // 结束事务
			} catch (SQLException e) {
				throw new RuntimeException(e);
			}
		}
	}

	/**
	 * 批量更新
	 */
	@Override
	public void updateBatch(List<User> list) {
		SqlMapClient sqlMapClient = IbatisUtil.getSqlMapClient();
		try {
			sqlMapClient.startTransaction(); // 开启事务
			sqlMapClient.startBatch(); // 开启批量
			for (User user : list) {
				sqlMapClient.update("update", user);
			}
			sqlMapClient.executeBatch(); // 执行批量操作
			sqlMapClient.commitTransaction(); // 提交事务
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			try {
				sqlMapClient.endTransaction(); // 结束事务
			} catch (SQLException e) {
				throw new RuntimeException(e);
			}
		}
	}

	/**
	 * 根据对象批量删除
	 */
	@Override
	public void deleteBatch(List<User> list) {
		SqlMapClient sqlMapClient = IbatisUtil.getSqlMapClient();
		try {
			sqlMapClient.startTransaction(); // 开启事务
			sqlMapClient.startBatch(); // 开启批量
			for (User user : list) {
				sqlMapClient.delete("delete", user.getId());
			}
			sqlMapClient.executeBatch(); // 执行批量操作
			sqlMapClient.commitTransaction(); // 提交事务
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			try {
				sqlMapClient.endTransaction(); // 结束事务
			} catch (SQLException e) {
				throw new RuntimeException(e);
			}
		}
	}

	/**
	 * 根据id批量删除（推荐）
	 */
	@Override
	public void deleteBatch(int... ids) {
		SqlMapClient sqlMapClient = IbatisUtil.getSqlMapClient();
		try {
			sqlMapClient.startTransaction(); // 开启事务
			sqlMapClient.startBatch(); // 开启批量
			for (int i = 0; i < ids.length; i++) {
				sqlMapClient.delete("delete", ids[i]);
			}
			sqlMapClient.executeBatch(); // 执行批量操作
			sqlMapClient.commitTransaction();// 提交事务
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			try {
				sqlMapClient.endTransaction(); // 结束事务
			} catch (SQLException e) {
				throw new RuntimeException(e);
			}
		}
	}

	/**
	 * 根据id查询
	 */
	@Override
	public User findById(int id) {
		SqlMapClient sqlMapClient = IbatisUtil.getSqlMapClient();
		try {
			User user = (User) sqlMapClient.queryForObject("findById", id);
			return user;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	/**
	 * 查询全部
	 */
	@Override
	public List<User> findAll() {
		SqlMapClient sqlMapClient = IbatisUtil.getSqlMapClient();
		try {
			List<User> list = sqlMapClient.queryForList("findAll");
			return list;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	/**
	 * 查询记录数
	 */
	@Override
	public int findCount() {
		SqlMapClient sqlMapClient = IbatisUtil.getSqlMapClient();
		try {
			int count = (Integer) sqlMapClient.queryForObject("findCount");
			return count;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	/**
	 * 模糊查询
	 */
	@Override
	public List<User> findByLike(String name) {
		SqlMapClient sqlMapClient = IbatisUtil.getSqlMapClient();
		try {
			List<User> list = sqlMapClient.queryForList("findByLike", name);
			return list;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	/**
	 * 日期查询
	 */
	@Override
	public List<User> findByDate(Date start, Date end) {
		SqlMapClient sqlMapClient = IbatisUtil.getSqlMapClient();
		try {
			Map<String, Date> param = new HashMap<String, Date>();
			param.put("start", start);
			param.put("end", end);
			List<User> list = sqlMapClient.queryForList("findByDate", param);
			return list;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	/**
	 * 分页查询
	 */
	@Override
	public Page<User> findByPage(int pageNo, int pageSize) {
		SqlMapClient sqlMapClient = IbatisUtil.getSqlMapClient();
		try {
			int skip = (pageNo - 1) * pageSize;
			int max = pageSize;
			List<User> list = sqlMapClient.queryForList("findAll", skip, max);
			int totalCount = findCount();
			return new Page<User>(pageNo, pageSize, totalCount, list);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

}

 

存储过程的测试

 

--调用存储过程

create procedure showData()
begin
	select * from user_table;
end ;


create  procedure in_proc (in param integer)
begin
	if param=0 then
		select * from user_table order by id asc;
	else
		select * from user_table order by id desc;
	end if;	
end;

 

 

package org.monday.other;

import java.util.List;

import org.monday.domain.User;
import org.monday.util.IbatisUtil;

import com.ibatis.sqlmap.client.SqlMapClient;

/**
 * 存储过程操作
 * @author Monday
 */
public class ProcMain {

	public static void main(String[] args) throws Exception {
		proc();
		// in_proc();
	}

	/**
	 * 普通的存储过程
	 */
	private static void proc() throws Exception {
		SqlMapClient sqlMapClient = IbatisUtil.getSqlMapClient();
		List<User> list = sqlMapClient.queryForList("showData");
		show(list);
	}

	/**
	 * 有输入参数的存储过程
	 */
	private static void in_proc() throws Exception {
		SqlMapClient sqlMapClient = IbatisUtil.getSqlMapClient();
		List<User> list = sqlMapClient.queryForList("in_proc", 10);
		show(list);
	}

	// 遍历集合
	private static void show(List<User> list) {
		for (User u : list) {
			System.out.println(u);
		}
	}
}

 

iBatis配置文件

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="jdbc.properties"/>
	
	<!-- 设置全局属性 -->
	<settings 
		useStatementNamespaces="false" 
		cacheModelsEnabled="true" 
		enhancementEnabled="true"
		lazyLoadingEnabled="true"
		maxRequests="32"
		maxSessions="10"
		maxTransactions="5" 
	/>

	<!-- 设置别名 -->
	<typeAlias alias="User" type="org.monday.domain.User"/>
	
	<!-- 设置事务管理 -->
	<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>
	
	<!-- 引入SqlMap映射文件 -->
	<sqlMap resource="org/monday/domain/User.xml"/>
	
</sqlMapConfig>

 

 

测试：

package junit.test;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.junit.BeforeClass;
import org.junit.Test;
import org.monday.dao.UserDao;
import org.monday.dao.impl.UserDaoImpl;
import org.monday.domain.User;
import org.monday.util.Page;

/**
 * 单元测试
 * @author Monday
 */
public class IbatisTest {

	private static UserDao userDao;

	@BeforeClass
	public static void init() {
		userDao = new UserDaoImpl();
	}

	@Test
	public void test_insert() {
		userDao.insert(new User("test", "9898777", new Date()));
	}

	@Test
	public void test_update() {
		userDao.update(new User(4, "test_update", "9898777999", new Date()));
	}

	@Test
	public void test_delete1() {
		userDao.delete(new User(4));
	}

	@Test
	public void test_delete2() {
		userDao.delete(5);
	}

	@Test
	public void test_insertBatch() {
		List<User> list = new ArrayList<User>();
		list.add(new User("test1", "98987771", new Date()));
		list.add(new User("test2", "98987772", new Date()));
		list.add(new User("test3", "98987773", new Date()));
		list.add(new User("test4", "98987774", new Date()));
		list.add(new User("test5", "98987775", new Date()));
		userDao.insertBatch(list);
	}

	@Test
	public void test_updateBatch() {
		List<User> list = new ArrayList<User>();
		list.add(new User(6, "test11", "989877711", new Date()));
		list.add(new User(7, "test22", "989877722", new Date()));
		list.add(new User(8, "test33", "989877733", new Date()));
		list.add(new User(9, "test44", "989877744", new Date()));
		list.add(new User(10, "test55", "989877755", new Date()));
		userDao.updateBatch(list);
	}

	@Test
	public void test_deleteBatch1() {
		List<User> list = new ArrayList<User>();
		list.add(new User(6));
		list.add(new User(7));
		list.add(new User(8));
		list.add(new User(9));
		list.add(new User(10));
		userDao.deleteBatch(list);
	}

	@Test
	public void test_deleteBatch2() {
		int[] ids = { 11, 12, 13, 14, 15 };
		userDao.deleteBatch(ids);
	}

	@Test
	public void test_findById() {
		System.out.println(userDao.findById(1));
	}

	@Test
	public void test_findAll() {
		List<User> list = userDao.findAll();
		for (User u : list) {
			System.out.println(u);
		}
	}

	@Test
	public void test_findCount() {
		System.out.println(userDao.findCount());
	}

	@Test
	public void test_findByLike() {
		List<User> list = userDao.findByLike("app");
		for (User u : list) {
			System.out.println(u);
		}
	}

	@Test
	public void test_findByDate() throws Exception {
		Date start = new SimpleDateFormat("yyyy-MM-dd").parse("2012-06-01");
		Date end = new SimpleDateFormat("yyyy-MM-dd").parse("2012-07-01");
		List<User> list = userDao.findByDate(start, end);
		for (User u : list) {
			System.out.println(u);
		}
	}

	@Test
	public void test_findByPage() {
		int pageNo = 2;
		int pageSize = 5;
		Page<User> page = userDao.findByPage(pageNo, pageSize);
		System.out.println("-----结果集-----");
		for (User user : page.getList()) {
			System.out.println(user);
		}
		System.out.println("总记录数：" + page.getTotalCount());
		System.out.println("总页数：" + page.getTotalPage());
		System.out.println("首页：" + page.getFirstPage());
		System.out.println("尾页：" + page.getLastPage());
		System.out.println("上一页：" + page.getPreviousPage());
		System.out.println("下一页：" + page.getNextPage());
	}
}