公司要用ibatis,所以学习学习,初学ibatis,版本号ibatis-2.3.4.726.jar,
所需jar包:
log4j-1.2.15.jar,ojdbc14.jar。
代码已上传,项目不包含ojdbc14.jar,自己加上哈。
使用数据库oracle10,首先对单个实体的各种操作进行测试:
脚本代码:
/*
Navicat Oracle Data Transfer
Oracle Client Version : 10.2.0.1.0
Source Server : chenjun
Source Server Version : 100200
Source Host : localhost:1521
Source Database :
Source Schema : SCOTT
Target Server Type : ORACLE
Target Server Version : 100200
File Encoding : 65001
Date: 2010-04-04 00:09:32
*/
-- ----------------------------
-- Table structure for "STUDENT_"
-- ----------------------------
DROP TABLE "STUDENT_";
CREATE TABLE "SCOTT"."STUDENT_"
( "ID_" NUMBER NOT NULL ENABLE,
"NAME_" VARCHAR2(50),
"SEX_" VARCHAR2(50),
"AGE_" VARCHAR2(50),
"GRADE_" VARCHAR2(50),
"CLASS_" VARCHAR2(50),
PRIMARY KEY ("ID_")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
;
-- ----------------------------
-- Records of STUDENT_
-- ----------------------------
INSERT INTO "STUDENT_" VALUES ('1', 'student1', '男', '11', '一年级', '二班');
INSERT INTO "STUDENT_" VALUES ('2', 'student2', '男', '12', '一年级', '二班');
INSERT INTO "STUDENT_" VALUES ('3', 'student3', '男', '13', '一年级', '二班');
INSERT INTO "STUDENT_" VALUES ('4', 'student4', '女', '14', '一年级', '二班');
INSERT INTO "STUDENT_" VALUES ('5', 'student5', '女', '15', '一年级', '二班');
INSERT INTO "STUDENT_" VALUES ('6', 'student6', '男', '16', '二年级', '一班');
INSERT INTO "STUDENT_" VALUES ('7', 'student7', '男', '17', '二年级', '一班');
INSERT INTO "STUDENT_" VALUES ('8', 'student8', '女', '18', '一年级', '一班');
INSERT INTO "STUDENT_" VALUES ('9', 'story1', '男', '19', '一年级', '二班');
INSERT INTO "STUDENT_" VALUES ('10', 'story2', '女', '11', '一年级', '一班');
INSERT INTO "STUDENT_" VALUES ('11', 'story3', '男', '12', '二年级', '一班');
INSERT INTO "STUDENT_" VALUES ('12', 'story4', '女', '13', '一年级', '二班');
INSERT INTO "STUDENT_" VALUES ('13', 'story5', '男', '14', '一年级', '一班');
INSERT INTO "STUDENT_" VALUES ('14', 'story6', '男', '15', '一年级', '一班');
INSERT INTO "STUDENT_" VALUES ('15', 'story7', '女', '16', '一年级', '二班');
INSERT INTO "STUDENT_" VALUES ('16', 'story8', '男', '17', '一年级', '二班');
INSERT INTO "STUDENT_" VALUES ('17', 'story9', '男', '18', '一年级', '二班');
INSERT INTO "STUDENT_" VALUES ('18', 'story8', '男', '15', '一年级', '二班');
INSERT INTO "STUDENT_" VALUES ('19', 'story8', '男', '16', '一年级', '二班');
INSERT INTO "STUDENT_" VALUES ('20', 'story8', '保密', '16', '一年级', '二班');
-- ----------------------------
-- Checks structure for table "STUDENT_"
-- ----------------------------
ALTER TABLE "STUDENT_" ADD CHECK ("ID_" IS NOT NULL);
-- ----------------------------
-- Primary Key structure for table "STUDENT_"
-- ----------------------------
ALTER TABLE "STUDENT_" ADD PRIMARY KEY ("ID_");
实体类:
package org.forever.xxxx.domain;
import java.io.Serializable;
import org.forever.xxxx.pagination.PageInfo;
/**
* 学生类
*/
public class Student extends PageInfo implements Serializable {
private static final long serialVersionUID = -2786021709397070449L;
private int id;// 主键
private String name;// 姓名
private String sex;// 性别
private int age;// 年龄
private String grade;// 年级
private String clazz;// 班级
public Student() {
}
public Student(String name, String sex, Integer age, String grade,
String clazz) {
this.name = name;
this.sex = sex;
this.age = age;
this.grade = grade;
this.clazz = clazz;
}
public Student(int id, String name, String sex, int age, String grade,
String clazz) {
this.id = id;
this.name = name;
this.sex = sex;
this.age = age;
this.grade = grade;
this.clazz = clazz;
}
//get和set省略
public String toString() {
return new StringBuffer().append("学生编号:"+this.id)
.append("\t学生姓名:"+this.name)
.append("\t学生年龄:"+this.age)
.append("\t学生性别:"+this.sex)
.append("\t学生年级:"+this.grade)
.append("\t学生班级:"+this.clazz)
.toString();
}
}
实体的配置文件:
<?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="Student"> <typeAlias alias="Student" type="org.forever.xxxx.domain.Student" /> <!-- 可以配置多个结果映射信息描述 --> <resultMap id="StudentResult" class="Student"> <result property="id" column="ID_" /> <result property="name" column="NAME_" /> <result property="sex" column="SEX_" /> <result property="age" column="AGE_" /> <result property="grade" column="GRADE_" /> <result property="clazz" column="CLASS_" /> </resultMap> <!-- 将参数封装成map进行传递 --> <select id="findByMap" parameterClass="java.util.Map" resultMap="StudentResult"> SELECT * FROM STUDENT_ WHERE STUDENT_.NAME_=#name# </select> <!-- 多条件查询 --> <select id="find" parameterClass="java.util.Map" resultMap="StudentResult"> <!-- 这种写法的缺陷显而易见,不推荐--> SELECT * FROM STUDENT_ WHERE STUDENT_.GRADE_=#grade# AND STUDENT_.CLASS_=#class# AND STUDENT_.NAME_ LIKE '%$name$%' AND STUDENT_.AGE_ BETWEEN #start_age# AND #end_age# AND STUDENT_.SEX_ IN ('男','女') ORDER BY STUDENT_.NAME_ ASC,STUDENT_.AGE_ ASC </select> <!-- 附上mysql的分页语句模板 照着语句模板套上就行了 @TABLE_NAME--表名 @CONDITIONS--条件 @ORDERS--排序 @FIRST_INDEX--起始索引 @PAGE_SIZE--每页大小 获取集合语句 SELECT * FROM @TABLE_NAME WHERE 1=1 @CONDITIONS @ORDERS LIMIT @FIRST_INDEX,@PAGE_SIZE 获取统计语句 SELECT COUNT(*) FROM @TABLE_NAME WHERE 1=1 @CONDITIONS --> <!-- 处理条件的模板sql语句 --> <sql id="condition_sql"> <!-- 条件集合不为null时说明有附加条件 --> <isNotNull property="conditions"> <!-- 迭代条件集合,还有其他条件的解析,自己补充吧 ,这种条件可以写成模板sql,让统计语句重复使用--> <iterate property="conditions" prepend=" AND " conjunction=" AND "> <!-- 等于条件解析 --> <isEqual property="${conditions[].operation}" compareValue="EQ"> ($conditions[].propertyName$ = #conditions[].propertyValue#) </isEqual> <!-- 模糊条件解析 --> <isEqual property="${conditions[].operation}" compareValue="LIKE"> ($conditions[].propertyName$ LIKE '%'||#conditions[].propertyValue#||'%') </isEqual> <!-- 范围条件解析 --> <isEqual property="${conditions[].operation}" compareValue="BETWEEN"> ($conditions[].propertyName$ BETWEEN #conditions[].propertyValue[0]# AND #conditions[].propertyValue[1]#) </isEqual> <!-- in条件解析 --> <isEqual property="${conditions[].operation}" compareValue="IN"> ($conditions[].propertyName$ IN <iterate open="(" close="))" conjunction="," property="${conditions[].propertyValue}"> #conditions[].propertyValue[]# </iterate> </isEqual> </iterate> </isNotNull> </sql> <!-- 分页查询单个对象的信息2(推荐) --> <select id="queryByStudent" parameterClass="Student" resultMap="StudentResult"> <![CDATA[ SELECT * FROM ( SELECT ROWNUM NUM,STUDENT_.* FROM STUDENT_ WHERE 1=1 AND (ROWNUM<=#currentPage#*#pageSize#) ]]> <include refid="condition_sql"/> <!-- 排序条件处理 --> $orderSql$ <![CDATA[ ) WHERE NUM >(#currentPage#-1)*#pageSize# ]]> </select> <!-- 分页统计查询2(推荐) --> <select id="queryPageCount" parameterClass="Student" resultClass="int"> SELECT COUNT(*) FROM STUDENT_ WHERE 1=1 <include refid="condition_sql"/> </select> <!-- 分页统计查询1(不推荐) --> <select id="findPageCount" parameterClass="Student" resultClass="int"> SELECT COUNT(*) FROM STUDENT_ WHERE 1=1 $conditionSql$ </select> <!-- 分页结果集查询1(不推荐) --> <select id="findByStudent" parameterClass="Student" resultMap="StudentResult"> <![CDATA[ SELECT * FROM ( SELECT ROWNUM NUM,STUDENT_.* FROM STUDENT_ WHERE 1=1 AND (ROWNUM<=#currentPage#*#pageSize#) $conditionSql$ $orderSql$ ) WHERE NUM >(#currentPage#-1)*#pageSize# ]]> </select> <!-- 单个参数配置 --> <select id="findByNameStudent" parameterClass="string" resultMap="StudentResult"> SELECT * FROM STUDENT_ WHERE STUDENT_.NAME_=#name# </select> <!-- 根据学生id查询 --> <select id="findByIdStudent" parameterClass="int" resultMap="StudentResult"> SELECT * FROM STUDENT_ WHERE STUDENT_.ID_=#id# </select> <!-- 更新一条记录 --> <update id="updateStudent" parameterClass="Student"> UPDATE STUDENT_ SET STUDENT_.AGE_=#age#,STUDENT_.NAME_=#name#,STUDENT_.CLASS_=#clazz#,STUDENT_.GRADE_=#grade#,STUDENT_.SEX_=#sex# WHERE STUDENT_.ID_ = #id# </update> <!-- 删除一条记录 --> <delete id="deleteStudent" parameterClass="int"> DELETE STUDENT_ WHERE STUDENT_.ID_ = #id# </delete> <!-- 批量删除 --> <delete id="batchDelete" parameterClass="java.util.List"> DELETE STUDENT_ WHERE STUDENT_.ID_ IN <iterate conjunction="," open="(" close=")"> #value[]# </iterate> </delete> <!-- 添加一条记录,参数类型为Student --> <insert id="saveStudent" parameterClass="Student"> <!-- 获取序列的下一个值 keyProperty为实体的属性--> <selectKey keyProperty="id" resultClass="int"> SELECT SEQ_STUDENT_ID.NEXTVAL AS SID FROM DUAL </selectKey> <!-- #属性#字段 --> <![CDATA[ INSERT INTO STUDENT_( ID_, NAME_, SEX_, AGE_, GRADE_, CLASS_ ) VALUES( #id#, #name#, #sex#, #age#, #grade#, #clazz# ) ]]> </insert> <!-- 查询所有信息 --> <select id="findAllStudent" resultMap="StudentResult"> SELECT * FROM STUDENT_ </select> </sqlMap>
实体的接口:
package org.forever.xxxx.dao;
import java.util.List;
import java.util.Map;
import org.forever.xxxx.domain.Student;
public interface StudentDao {
public List<?> find(Map<?,?> map);
/**
* 将查询参数封装成map进行查询
* @param map
* @return
*/
public Student findByMap(Map<?,?> map);
/**
* 根据名字进行查询
* @param name
* @return
*/
public Student findByNameStudent(String name);
/**
* 根据id查询
* @return
*/
public Student findByIdStudent(int id);
/**
* 更新一条数据库信息
* @param student
*/
public void updateStudent(Student student);
/**
* 批量删除
*/
public void batchDelete(List<Integer> ids);
/**
* 删除单条数据
* @param id
*/
public void deleteStudent(int id);
/**
* 查找所有的学生信息
* @return
*/
public List<?> findAllStudent();
/**
* 保存或者修改学生信息
* @param student
*/
public void saveStudent(Student student);
/**
* 分页组合查询
* @param student
*/
public List<?> findByStudent(Student student);
/**
* 升级版的分页组合查询,解析sql将在配置文件完成
* @param student
* @return
*/
public List<?> queryByStudent(Student student);
}
实体dao接口的实现:
package org.forever.xxxx.dao;
import java.io.IOException;
import java.io.Reader;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import org.forever.xxxx.domain.Student;
import org.forever.xxxx.pagination.Condition;
import org.forever.xxxx.pagination.Operation;
import org.forever.xxxx.pagination.Order;
import org.forever.xxxx.pagination.OrderType;
import org.forever.xxxx.pagination.PageInfo;
import com.ibatis.common.resources.Resources;
import com.ibatis.sqlmap.client.SqlMapClient;
import com.ibatis.sqlmap.client.SqlMapClientBuilder;
public class StudentDaoImpl implements StudentDao {
private static SqlMapClient sqlMapper;
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 void batchDelete(List<Integer> ids) {
try {
sqlMapper.delete("batchDelete", ids);
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
public void deleteStudent(int id) {
try {
sqlMapper.delete("deleteStudent", id);
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
public Student findByIdStudent(int id) {
try {
return (Student) sqlMapper.queryForObject("findByIdStudent", id);
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
public void updateStudent(Student student) {
try {
sqlMapper.update("updateStudent", student);
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
public List<?> find(Map<?, ?> map) {
try {
return sqlMapper.queryForList("find", map);
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
public Student findByNameStudent(String name) {
try {
return (Student) sqlMapper
.queryForObject("findByNameStudent", name);
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
public Student findByMap(Map<?, ?> map) {
try {
return (Student) sqlMapper.queryForObject("findByMap", map);
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
public List<?> findAllStudent() {
try {
return sqlMapper.queryForList("findAllStudent");
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
public void saveStudent(Student student) {
try {
sqlMapper.insert("saveStudent", student);
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
/**
* 不推荐(使用)
*/
public List<?> findByStudent(Student student) {
try {
processCondition(student);
processOrder(student);
int totalItems = Integer.parseInt(sqlMapper.queryForObject(
"findPageCount", student).toString());
// 设置总条数
student.setTotalItems(totalItems);
// 设置总页数
student
.setTotalPage(totalItems % student.getPageSize() == 0 ? totalItems
/ student.getPageSize()
: totalItems / student.getPageSize() + 1);
return sqlMapper.queryForList("findByStudent", student);
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
/**
* 处理排序条件sql
*
* @param pageInfo
*/
public void processOrder(PageInfo pageInfo) {
StringBuffer order = new StringBuffer();
List<Order> orders = pageInfo.getOrders();
if (orders != null && orders.size() > 0) {
order.append(" ORDER BY ");
for (Order item : orders) {
String propertyName = item.getPropertyName();
switch (item.getOrderType()) {
case ASC:
order.append(propertyName + " ASC,");
break;
case DESC:
order.append(propertyName + " DESC,");
break;
default:
break;
}
}
order.replace(order.length() - 1, order.length(), "");
}
pageInfo.setOrderSql(order.toString());
}
/**
* 处理条件(拼接语句真烦,不安全,不推荐)
*/
public void processCondition(PageInfo pageInfo) {
StringBuffer condition = new StringBuffer();
List<Condition> conditions = pageInfo.getConditions();
if (conditions != null && conditions.size() > 0) {
for (Condition item : conditions) {
Object value = item.getPropertyValue();
String propertyName = item.getPropertyName();
switch (item.getOperation()) {
case EQ:
if (value instanceof String) {
condition.append(" AND (" + propertyName + " ='"
+ value + "') ");
} else {
condition.append(" AND (" + propertyName + " =" + value
+ ") ");
}
break;
case LIKE:
condition.append(" AND (" + propertyName + " LIKE '%"
+ value + "%') ");
break;
case IN:
Object[] obj = (Object[]) value;
if (obj[0] instanceof String) {
condition.append(" AND (" + propertyName + " IN (");
for (Object object : obj) {
condition.append("'" + object + "',");
}
condition.replace(condition.length() - 1, condition
.length(), "");
condition.append("))");
} else {
for (Object object : obj) {
condition.append(object + ",");
}
condition.replace(condition.length() - 1, condition
.length(), "");
condition.append("))");
}
break;
case BETWEEN:
condition.append(" AND ( " + propertyName + " BETWEEN "
+ ((Object[]) value)[0] + " AND "
+ ((Object[]) value)[1] + " )");
break;
default:
break;
}
}
}
pageInfo.setConditionSql(condition.toString());
}
/**
* 强烈推荐这种方式的写法
*/
public List<?> queryByStudent(Student student) {
try {
// //注意当使用ibatis的dao接口包时,这些dao接口的实现类就不会让我们自己手写
// /可以将以下代码提取出来就可以了
int totalItems = Integer.parseInt(sqlMapper.queryForObject(
"queryPageCount", student).toString());
// 设置总条数
student.setTotalItems(totalItems);
// 设置总页数
student
.setTotalPage(totalItems % student.getPageSize() == 0 ? totalItems
/ student.getPageSize()
: totalItems / student.getPageSize() + 1);
// //
return sqlMapper.queryForList("queryByStudent", student);
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
public static void main(String[] args) {
StudentDao studentDao = new StudentDaoImpl();
Student student = new Student();
student.setPageSize(4);
student.setCurrentPage(1);
/*
* 例子:
* 查询一年级二班,
* 名字中包含stor的,
* 年龄在14到18的,
* 性别是男和女的学生
* 并且按照姓名升序排序,
* 名字相同的按照年龄的降序排序。
*/
student.setConditions(Arrays.asList(new Condition("GRADE_", "一年级",
Operation.EQ), new Condition("CLASS_", "二班", Operation.EQ),
new Condition("NAME_", "stor", Operation.LIKE), new Condition(
"AGE_", new Object[] { 14, 18 }, Operation.BETWEEN),
new Condition("SEX_", new Object[] { "男", "女", "保密" },
Operation.IN)));
student.setOrders(
Arrays.asList(
new Order("NAME_", OrderType.ASC),
new Order("AGE_", OrderType.DESC)
)
);
List<?> list = studentDao.queryByStudent(student);
System.out.println("共有条数:" + student.getTotalItems());
System.out.println("共有页数:" + student.getTotalPage());
for (Object object : list) {
System.out.println(object);
}
}
}
分页实体:
package org.forever.xxxx.pagination;
import java.io.Serializable;
import java.util.List;
//分页信息
public class PageInfo implements Serializable {
private static final long serialVersionUID = -2013522911148457717L;
private int currentPage = 1;// 当前页
private int totalPage;// 总页数
private int totalItems;// 总条数
private int pageSize = 10;// 每页显示多少条
private List<Condition> conditions;// 条件集合
private List<Order> orders;// 排序集合
private String conditionSql="";// 条件的sql拼接语句(不推荐)
private String orderSql="";// 排序拼接语句
public PageInfo() {
}
public int getCurrentPage() {
return currentPage;
}
public void setCurrentPage(int currentPage) {
this.currentPage = currentPage;
}
public int getTotalPage() {
return totalPage;
}
public void setTotalPage(int totalPage) {
this.totalPage = totalPage;
}
public int getTotalItems() {
return totalItems;
}
public void setTotalItems(int totalItems) {
this.totalItems = totalItems;
}
public int getPageSize() {
return pageSize;
}
public void setPageSize(int pageSize) {
this.pageSize = pageSize;
}
public List<Condition> getConditions() {
return conditions;
}
public void setConditions(List<Condition> conditions) {
this.conditions = conditions;
}
public List<Order> getOrders() {
return orders;
}
public void setOrders(List<Order> orders) {
this.orders = orders;
//排序的处理放在任何一个地方处理都可以,暂时放在这里
//这里处理的话就必须这样赋予所有的排序条件,如下:
/* student.setOrders(
Arrays.asList(
new Order("NAME_", OrderType.ASC),
new Order("AGE_", OrderType.DESC)
)
);*/
StringBuffer order = new StringBuffer();
if (orders != null && orders.size() > 0) {
order.append(" ORDER BY ");
for (Order item : orders) {
String propertyName = item.getPropertyName();
switch (item.getOrderType()) {
case ASC:
order.append(propertyName + " ASC,");
break;
case DESC:
order.append(propertyName + " DESC,");
break;
default:
break;
}
}
//去掉多余的逗号
order.replace(order.length() - 1, order.length(), "");
}
setOrderSql(order.toString());
}
public String getConditionSql() {
return conditionSql;
}
public void setConditionSql(String conditionSql) {
this.conditionSql = conditionSql;
}
public String getOrderSql() {
return orderSql;
}
public void setOrderSql(String orderSql) {
this.orderSql = orderSql;
}
}
sqlmapconfig配置:
<?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="database.properties"/>
<!-- 简单配置数据库链接信息 ,不提交事务-->
<transactionManager type="JDBC">
<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 resource="org/forever/xxxx/domain/Student.xml"/>
</sqlMapConfig>
省略了pageInfo的其他实体信息,完整代码提供下载。学习参考使用。