ibatis使用sql进行增删改查

 

         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;

    }

}