Ibatis是apache的一个开源项目http://ibatis.apache.org/,不过现在3.0版本以后改名为mybatis了,而且移到google开源项目那边去了http://code.google.com/p/mybatis/,我也不知道作者是出于什么原因,不过目前还是使用ibatis较多吧,毕竟积累了8年的用户,ibatis是数据持久化层的框架之一,其他的著名的还有hibernate等等,这个以后再介绍,本文将简要说明使用ibatis的增删改查(使用sql,存储过程下篇介绍)的操作。
数据库:oracle10g
模拟场景:系统账号的增删改查操作
项目:上篇文章创建的spring mvc(spring+ibatis+velocity)项目onlinemarket
第一步、新建数据库表TB_USERS
CREATETABLE "MPOS"."TB_USERS"
( "USERID" NUMBERNOTNULLENABLE,
"NAME" VARCHAR2(20),
"PASSWORD" VARCHAR2(50),
"ADDRESS" VARCHAR2(200),
"POSTCODE" VARCHAR2(20),
"TELEPHONE"VARCHAR2(20),
"MOBILE" VARCHAR2(20),
"EMAIL" VARCHAR2(50),
"COMPANY" VARCHAR2(50),
"GRADUATE_SCHOOL"VARCHAR2(50),
"REGISTER_TIME"TIMESTAMP (6),
CONSTRAINT "KEY_USERID"PRIMARYKEY ("USERID")
USINGINDEXPCTFREE10INITRANS2MAXTRANS255
STORAGE(INITIAL65536NEXT1048576MINEXTENTS1MAXEXTENTS2147483645
PCTINCREASE0FREELISTS1FREELISTGROUPS1BUFFER_POOLDEFAULT)
TABLESPACE "USERS" ENABLE
) PCTFREE10PCTUSED40INITRANS1MAXTRANS255NOCOMPRESSLOGGING
STORAGE(INITIAL65536NEXT1048576MINEXTENTS1MAXEXTENTS2147483645
PCTINCREASE0FREELISTS1FREELISTGROUPS1BUFFER_POOLDEFAULT)
TABLESPACE "USERS" ;
CREATEUNIQUEINDEX "MPOS"."KEY_USERID" ON "MPOS"."TB_USERS" ("USERID")
PCTFREE10INITRANS2MAXTRANS255
STORAGE(INITIAL65536NEXT1048576MINEXTENTS1MAXEXTENTS2147483645
PCTINCREASE0FREELISTS1FREELISTGROUPS1BUFFER_POOLDEFAULT)
TABLESPACE "USERS" ;
ALTERTABLE "MPOS"."TB_USERS"ADDCONSTRAINT "KEY_USERID"PRIMARYKEY ("USERID")
USINGINDEXPCTFREE10INITRANS2MAXTRANS255
STORAGE(INITIAL65536NEXT1048576MINEXTENTS1MAXEXTENTS2147483645
PCTINCREASE0FREELISTS1FREELISTGROUPS1BUFFER_POOLDEFAULT)
TABLESPACE "USERS" ENABLE;
ALTERTABLE "MPOS"."TB_USERS"MODIFY ("USERID"NOTNULLENABLE);
第二步、onlinemarket-dataaccess数据访问层pom.xml添加相关的jar依赖
<projectxmlns="http://maven.apache.org/POM/4.0.0"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<artifactId>onlinemarket-parent</artifactId>
<groupId>com.figo.onlinemarket</groupId>
<version>0.0.1-SNAPSHOT</version>
</parent>
<groupId>com.figo.onlinemarket</groupId>
<artifactId>onlinemarket-dataaccess</artifactId>
<version>0.0.1-SNAPSHOT</version>
<dependencies>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
</dependency>
<dependency>
<groupId>commons-beanutils</groupId>
<artifactId>commons-beanutils</artifactId>
<version>1.7.0</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-orm</artifactId>
<version>3.1.1.RELEASE</version>
</dependency>
<dependency>
<groupId>com.ibatis</groupId>
<artifactId>ibatis2-dao</artifactId>
<version>2.1.7.597</version>
</dependency>
<dependency>
<groupId>org.apache.ibatis</groupId>
<artifactId>ibatis-sqlmap</artifactId>
<version>2.3.4.726</version>
</dependency>
</dependencies>
</project>
第三步、创建数据访问层接口UserDAO.java
package com.figo.onlinemarket.dataaccess.daointerface;
import java.util.List;
import com.figo.onlinemarket.dataaccess.model.User;
/**
* @author zhuzhifei
*
*/
public interface UserDAO {
/**
* 增加用户
* @param user
* @return
*/
public Long insertUser(User user);
/**
* 删除用户
* @param userId
* @return
*/
public int deleteUser(String name);
/**
* 修改用户
* @param user
* @return
*/
public int updateUser(User user);
/**
* 查询所有用户
* @param
* @return
*/
public List<User> selectUsers();
/**
* 查询用户
* @param userId
* @return
*/
public User selectUser(User user);
}
第四步、创建数据访问层实现类UserDAOImpl.java
/**
*
*/
package com.figo.onlinemarket.dataaccess.daoimplement;
import java.util.List;
import org.springframework.dao.DataAccessException;
import org.springframework.orm.ibatis.support.SqlMapClientDaoSupport;
import com.figo.onlinemarket.dataaccess.daointerface.UserDAO;
import com.figo.onlinemarket.dataaccess.model.User;
/**
* @author zhuzhifei
*
*/
public class UserDAOImpl extends SqlMapClientDaoSupport implements UserDAO {
@Override
public Long insertUser(User user) throws DataAccessException{
// TODO Auto-generated method stub
if (user == null) {
throw new IllegalArgumentException("Can't insert a null data object into db.");
}
getSqlMapClientTemplate().insert("insertUser", user);
return user.getUserId();
}
@Override
public int deleteUser(String name)throws DataAccessException {
// TODO Auto-generated method stub
return getSqlMapClientTemplate().delete("deleteUser", name);
}
@Override
public int updateUser(User user) throws DataAccessException{
// TODO Auto-generated method stub
return getSqlMapClientTemplate().update("updateUser", user);
}
@Override
public List<User> selectUsers() throws DataAccessException{
// TODO Auto-generated method stub
return (List<User>)getSqlMapClientTemplate().queryForList("selectAllUsers");
}
@Override
public User selectUser(User user) throws DataAccessException{
// TODO Auto-generated method stub
return (User)getSqlMapClientTemplate().queryForObject("selectUser",user );
}
}
第五步、配置相关的文件
1、 onlinemarket-dataaccess\src\main\resources\META-INF\spring\onlinemarket-dao.xml配置数据访问类
<?xmlversion="1.0"encoding="UTF-8"?>
<beansxmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:jaxws="http://cxf.apache.org/jaxws"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:task="http://www.springframework.org/schema/task"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://cxf.apache.org/jaxws
http://cxf.apache.org/schemas/jaxws.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-3.0.xsd
http://www.springframework.org/schema/task
http://www.springframework.org/schema/task/spring-task-3.0.xsd"
default-autowire="byName">
<beanid="userDAO"class="com.figo.onlinemarket.dataaccess.daoimplement.UserDAOImpl"parent="onlineMarketBaseSqlMapClientDAO"/>
</beans>
2、 onlinemarket-dataaccess\src\main\resources\META-INF\spring\onlinemarket-datasource.xml配置数据源,数据源配置在tomcat的上下文中
F:\Tomcat6\conf\context.xml:
<Resource name="onlinemarketDataSource"
auth="Container"
type="javax.sql.DataSource"
driverClassName="oracle.jdbc.driver.OracleDriver"
url="jdbc:oracle:thin:@192.168.123.123:1521:onlinemarket"
username="onlinemarket"
password="onlinemarket"
maxActive="5"
maxIdle="1"
maxWait="10000" />
onlinemarket-datasource.xml:
<?xmlversion="1.0"encoding="UTF-8"?>
<beansxmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:osgi="http://www.springframework.org/schema/osgi"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/osgi http://www.springframework.org/schema/osgi/spring-osgi.xsd"
default-autowire="byName">
<!-- mposControl(system for mpos) -->
<!-- data source -->
<beanid="onlineMarketDataSource" class="org.springframework.jndi.JndiObjectFactoryBean">
<propertyname="jndiName">
<value>java:comp/env/onlinemarketDataSource</value>
</property>
</bean>
<beanid="onlineMarketSqlMapClient"class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
<propertyname="configLocation">
<value>classpath:META-INF/spring/sqlmap-onlinemarket.xml</value>
</property>
</bean>
<!-- DAO -->
<beanid="onlineMarketBaseSqlMapClientDAO"abstract="true">
<propertyname="sqlMapClient"ref="onlineMarketSqlMapClient"></property>
<propertyname="dataSource"ref="onlineMarketDataSource"/>
</bean>
<!-- transcation -->
<beanid="onlineMarketTransactionManager"class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<propertyname="dataSource"ref="onlineMarketDataSource"/>
</bean>
<beanname="mposTransactionTemplate"class="org.springframework.transaction.support.TransactionTemplate">
<propertyname="transactionManager"ref="onlineMarketTransactionManager"/>
</bean>
</beans>
3、 onlinemarket-dataaccess\src\main\resources\META-INF\spring\sqlmap-onlinemarket.xml对象和数据表的映射关系配置
<?xmlversion="1.0"encoding="UTF-8"?>
<!DOCTYPEsqlMapConfig PUBLIC"-//iBATIS.com//DTD SQL Map Config 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-config-2.dtd">
<sqlMapConfig>
<settingscacheModelsEnabled="true"enhancementEnabled="false"
lazyLoadingEnabled="false"maxRequests="3000"maxSessions="3000"
maxTransactions="3000"useStatementNamespaces="false"/>
<sqlMapresource="sqlmap/User-sqlmap-mapping.xml"/><!—这里可以添加多张表-->
</sqlMapConfig>
4、 onlinemarket-dataaccess\src\main\resources\sqlmap\User-sqlmap-mapping.xml tb_users表增删改查操作的配置
<?xmlversion="1.0"encoding="UTF-8"?>
<!DOCTYPEsqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd">
<sqlMapnamespace="onlinemarket">
<resultMapid="USER"class="com.figo.onlinemarket.dataaccess.model.User">
<resultcolumn="USERID"javaType="long"jdbcType="NUMBER"
nullValue="0"property="userId"/>
<resultcolumn="NAME"javaType="java.lang.String"jdbcType="VARCHAR2"
property="name"/>
<resultcolumn="PASSWORD"javaType="java.lang.String"jdbcType="VARCHAR2"
property="password"/>
<resultcolumn="ADDRESS"javaType="java.lang.String"jdbcType="VARCHAR2"
property="address"/>
<resultcolumn="POSTCODE"javaType="java.lang.String"jdbcType="VARCHAR2"
property="postcode"/>
<resultcolumn="TELEPHONE"javaType="java.lang.String"
jdbcType="VARCHAR2"property="telephone"/>
<resultcolumn="MOBILE"javaType="java.lang.String"jdbcType="VARCHAR2"
property="mobile"/>
<resultcolumn="EMAIL"javaType="java.lang.String"jdbcType="VARCHAR2"
property="email"/>
<resultcolumn="COMPANY"javaType="java.lang.String"jdbcType="VARCHAR2"
property="company"/>
<resultcolumn="GRADUATE_SCHOOL"javaType="java.lang.String"
jdbcType="VARCHAR2"property="graduateSchool"/>
<resultcolumn="REGISTER_TIME"javaType="java.util.Date"
jdbcType="TIMESTAMP(6)"property="registerTime"/>
</resultMap>
<insertid="insertUser">
<selectKeykeyProperty="userId"resultClass="long">
<![CDATA[
select SEQ_TB_USERS.nextval from dual
]]>
</selectKey>
<![CDATA[
insert into TB_USERS(USERID,NAME, PASSWORD,ADDRESS,POSTCODE,TELEPHONE,MOBILE,EMAIL,COMPANY,GRADUATE_SCHOOL,REGISTER_TIME) values (#userId#,#name#,#password#, #address#, #postcode#,#telephone#,#mobile#, #email#, #company#, #graduateSchool#,systimestamp)
]]>
</insert>
<!-- mapped statement for IbatisLoginLogDAO.selectByLoginTime -->
<deleteid="deleteUser">
<![CDATA[
delete from TB_USERS where (name=#name#)
]]>
</delete>
<!-- mapped statement for IbatisLoginLogDAO.selectByLoginTime -->
<updateid="updateUser">
update TB_USERS
<dynamicprepend=" set ">
<isNotEmptyprepend=","property="address">
address = #address#
</isNotEmpty>
<isNotEmptyprepend=","property="telephone">
telephone = #telephone#
</isNotEmpty>
<isNotEmptyprepend=","property="mobile">
mobile = #mobile#
</isNotEmpty>
<isNotEmptyprepend=","property="email">
email = #email#
</isNotEmpty>
<isNotEmptyprepend=","property="company">
company = #company#
</isNotEmpty>
<isNotEmptyprepend=","property="graduateSchool">
graduateSchool = #graduateSchool#
</isNotEmpty>
</dynamic>
where name=#name#
</update>
<!-- mapped statement for IbatisLoginLogDAO.selectAll -->
<selectid="selectAllUsers"resultMap="USER">
<![CDATA[
select * from TB_USERS
]]>
</select>
<!-- mapped statement for IbatisLoginLogDAO.selectByAccount -->
<selectid="selectUser"resultMap="USER">
<![CDATA[
select * from TB_USERS where ((name = #name#) and (password=#password#))
]]>
</select>
</sqlMap>
5、 以上配置文件需要配置到onlinemarket-web\src\main\resources\META-INF\spring\applicationContext.xml
<?xmlversion="1.0"encoding="UTF-8"?>
<beansxmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:context="http://www.springframework.org/schema/context"
xmlns="http://www.springframework.org/schema/beans"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-3.0.xsd">
<importresource="classpath:META-INF/spring/onlinemarket-datasource.xml"/>
<importresource="classpath:META-INF/spring/onlinemarket-dao.xml"/>
<importresource="classpath:META-INF/spring/onlinemarket-business.xml"/>
</beans>
第六步、业务逻辑层和控制器层的编写,这里就简单写一下了t\onlinemarket-web\src\main\java\com\figo\onlinemarket\web\action\UserAction.java
package com.figo.onlinemarket.web.action;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.RequestMapping;
import com.alibaba.fastjson.JSONObject;
import com.figo.onlinemarket.business.LoginBusiness;
import com.figo.onlinemarket.business.UserBusiness;
import com.figo.onlinemarket.dataaccess.model.User;
import com.figo.onlinemarket.web.common.ResponseResult;
/**
* @author zhuzhifei
*
*/
@Controller
public class UserAction {
protected static final String VIEW_EMPTY = "layout/empty";
/** 默认的页面信息键值 **/
protected static final String DEFAULT_SCREEN_CONTENT = "screen_content";
@Autowired
private UserBusiness userBusiness;
/**
* 增
* @param user
* @param model
* @return
*/
@RequestMapping(value = "/user/insertUser.htm")
public String insertUser(User user, ModelMap model) {
Long rowId = userBusiness.insertUser(user);
ResponseResult result = new ResponseResult();
result.setResult(rowId);
if (rowId > 0) {
result.setSuccess(true);
}
JSONObject jsonresult = (JSONObject) JSONObject.toJSON(result);
model.put(DEFAULT_SCREEN_CONTENT, jsonresult);
return VIEW_EMPTY;
}
/**
* 删
* @param name
* @param model
* @return
*/
@RequestMapping(value = "/user/deleteUser.htm")
public String deleteUser(String name, ModelMap model) {
int rowId = userBusiness.deleteUser(name);
ResponseResult result = new ResponseResult();
result.setResult(rowId);
if (rowId > 0) {
result.setSuccess(true);
}
JSONObject jsonresult = (JSONObject) JSONObject.toJSON(result);
model.put(DEFAULT_SCREEN_CONTENT, jsonresult);
return VIEW_EMPTY;
}
/**
* 改
* @param user
* @param model
* @return
*/
@RequestMapping(value = "/user/updateUser.htm")
public String updateUser(User user, ModelMap model) {
int rowId = userBusiness.updateUser(user);
ResponseResult result = new ResponseResult();
result.setResult(rowId);
if (rowId > 0) {
result.setSuccess(true);
}
JSONObject jsonresult = (JSONObject) JSONObject.toJSON(result);
model.put(DEFAULT_SCREEN_CONTENT, jsonresult);
return VIEW_EMPTY;
}
/**
* 查所有
* @param user
* @param model
* @return
*/
@RequestMapping(value = "/user/selectUsers.htm")
public String selectUsers(User user, ModelMap model) {
List<User> users= userBusiness.selectUsers();
ResponseResult result = new ResponseResult();
result.setResult(users);
result.setSuccess(true);
JSONObject jsonresult = (JSONObject) JSONObject.toJSON(result);
model.put(DEFAULT_SCREEN_CONTENT, jsonresult);
return VIEW_EMPTY;
}
/**
* 查某个用户
* @param user
* @param model
* @return
*/
@RequestMapping(value = "/user/selectUser.htm")
public String selectUser(User user, ModelMap model) {
User userNew= userBusiness.selectUser(user);
ResponseResult result = new ResponseResult();
result.setResult(userNew);
result.setSuccess(true);
JSONObject jsonresult = (JSONObject) JSONObject.toJSON(result);
model.put(DEFAULT_SCREEN_CONTENT, jsonresult);
return VIEW_EMPTY;
}
}