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" 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" parameterkeyProperty="id"> select last_insert_id() as id </selectKey> -->insert into user_table(name,phone,birthday)values(#name#,#phone#,#birthday#)<!-- Oracle 实现自增使用序列,ibatis_seq要创建建好--><!-- <selectKey resultkeyProperty="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" parameterparameterparameterresultresultMap="User" cacheModel="userCache">select *from user_table</select><!-- 查询记录数 --><select id="findCount" resultparameterresultparameterresultresultmode="IN" javaType="int" jdbcType="INTEGER" /></parameterMap><procedure id="in_proc" parameterMap="in_proc" resultname="code">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 {/** * 添加 */@Overridepublic void insert(User user) {SqlMapClient sqlMapClient = IbatisUtil.getSqlMapClient();try {sqlMapClient.insert("insert", user);} catch (SQLException e) {throw new RuntimeException(e);}}/** * 修改 */@Overridepublic void update(User user) {SqlMapClient sqlMapClient = IbatisUtil.getSqlMapClient();try {sqlMapClient.update("update", user);} catch (SQLException e) {throw new RuntimeException(e);}}/** * 根据对象删除 */@Overridepublic void delete(User user) {SqlMapClient sqlMapClient = IbatisUtil.getSqlMapClient();try {sqlMapClient.delete("delete", user.getId());} catch (SQLException e) {throw new RuntimeException(e);}}/** * 根据id删除（推荐） */@Overridepublic void delete(int id) {SqlMapClient sqlMapClient = IbatisUtil.getSqlMapClient();try {sqlMapClient.delete("delete", id);} catch (SQLException e) {throw new RuntimeException(e);}}/** * 批量添加 */@Overridepublic 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);}}}/** * 批量更新 */@Overridepublic 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);}}}/** * 根据对象批量删除 */@Overridepublic 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批量删除（推荐） */@Overridepublic 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查询 */@Overridepublic 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);}}/** * 查询全部 */@Overridepublic List<User> findAll() {SqlMapClient sqlMapClient = IbatisUtil.getSqlMapClient();try {List<User> list = sqlMapClient.queryForList("findAll");return list;} catch (SQLException e) {throw new RuntimeException(e);}}/** * 查询记录数 */@Overridepublic int findCount() {SqlMapClient sqlMapClient = IbatisUtil.getSqlMapClient();try {int count = (Integer) sqlMapClient.queryForObject("findCount");return count;} catch (SQLException e) {throw new RuntimeException(e);}}/** * 模糊查询 */@Overridepublic 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);}}/** * 日期查询 */@Overridepublic 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);}}/** * 分页查询 */@Overridepublic 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()beginselect * from user_table;end ;create procedure in_proc (in param integer)beginif param=0 thenselect * from user_table order by id asc;elseselect * 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;@BeforeClasspublic static void init() {userDao = new UserDaoImpl();}@Testpublic void test_insert() {userDao.insert(new User("test", "9898777", new Date()));}@Testpublic void test_update() {userDao.update(new User(4, "test_update", "9898777999", new Date()));}@Testpublic void test_delete1() {userDao.delete(new User(4));}@Testpublic void test_delete2() {userDao.delete(5);}@Testpublic 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);}@Testpublic 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);}@Testpublic 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);}@Testpublic void test_deleteBatch2() {int[] ids = { 11, 12, 13, 14, 15 };userDao.deleteBatch(ids);}@Testpublic void test_findById() {System.out.println(userDao.findById(1));}@Testpublic void test_findAll() {List<User> list = userDao.findAll();for (User u : list) {System.out.println(u);}}@Testpublic void test_findCount() {System.out.println(userDao.findCount());}@Testpublic void test_findByLike() {List<User> list = userDao.findByLike("app");for (User u : list) {System.out.println(u);}}@Testpublic 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);}}@Testpublic 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());}}?
?
?
?