声明:本文不是本人原创,大概是com.wysm.netstar所有,现在也无法考证。希望大家学习交流。
本文将以一个实际例子来讲解整合iBATIS和Spring在WEB开发中的应用。在例子中,将应用DAO,FACADE这些常用的设计模式。对于ORACL中的sequence处理方式,事务处理都有所涉及;当执行SQL语句时,Spring与iBATIS一起为你管理资源,并按要求来创建和关闭连接。Spring会把映射的SQL语句传给iBATIS, 由iBATIS来运行映射的语句,且若需要,iBATIS会把结果集传给给你映射SQL语句时所指定的Bean。如果你已有了任何参数,那就可将那些结果集放入hash map中并把结果集传给带有映射的SQL语句的模板。
1、需求说明
假定我们要实现一个用户注册和登录的需求。注册时将用户信息保存到数据库,登录时使用注册的信息进行校验。
2、数据库设计
我们建2个表,一个存放基本的用户信息(T_USERS),一个放附加的信息(T_USER_INFO)。这2个表是1对1的关系,其实可以只建一个表,但在实际的开发中,我们常常会把它们拆分,把常用的信息放到主表中,不常用的放到辅表中,这样可以提升性能。本文使用的数据库是ORACLE,表结构如下:
2.1 T_USERS 用户信息主表
字段名 | 字段说明 | 字段类型 |
userid | 用户在系统中的流水号,为PRIMARY KEY | NUMBER(6,0) NOT NULL |
ualias | 用户别名,在登录时输入的名字 | VARCHAR2(20) NOT NULL |
password | 密码 | VARCHAR2(30) NULL |
realname | 真实姓名 | VARCHAR2(20) NULL |
| | VARCHAR2(30) NOT NULL |
usertypeid | 用户类型 | CHAR(2) NOT NULL |
CREATE TABLE T_USERS (
userid NUMBER(6,0) NOT NULL,
usertypeid CHAR(2) NOT NULL,
ualias VARCHAR2(20) NOT NULL,
password VARCHAR2(30) NULL,
email VARCHAR2(30) NOT NULL,
realname VARCHAR2(20) NULL
);
CREATE UNIQUE INDEX XAK1T_USERS ON T_USERS (ualias ASC );
CREATE UNIQUE INDEX XAK2T_USERS ON T_USERS (email ASC );
ALTER TABLE T_USERS ADD (PRIMARY KEY(userid));
(建表,索引和关键字)
2.2 T_USER_INFO 用户信息辅表
字段名 | 字段说明 | 字段类型 |
userid | 用户在系统中的流水号为KEY | NUMBER(6,0) NOT NULL |
regdate | 注册日期 | DATE NULL |
lastlogdate | 最近一次登录日期 | DATE NULL |
logtimes | 登录次数 | NUMBER(6) NULL |
CREATE TABLE T_USER_INFO (
userid NUMBER(6,0) NOT NULL,
regdate DATE NULL,
lastlogdate DATE NULL,
logtimes NUMBER(6) NULL
);
ALTER TABLE T_USER_INFO ADD ( PRIMARY KEY (userid) ) ;
ALTER TABLE T_USER_INFO ADD ( FOREIGN KEY (userid) REFERENCES T_USERS ) ;
(建表,主键和外键)
2.3 SEQUENCE Sequence_userid 用户流水号产生
CREATE SEQUENCE Sequence_userid
INCREMENT BY 1
START WITH 1
MAXVALUE 999999
MINVALUE 1
NOCYCLE
NOORDER;
3. 构建源代码包目录
4. iBATIS sqlmap定义
com.wysm.netstar.persistence.sqlmapdao.sql/sql-map-config.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="com/wysm/netstar/properties/database.properties"/>
<transactionManager type="JDBC">
<dataSource type="SIMPLE">
<property value="${driver}" name="JDBC.Driver"/>
<property value="${url}" name="JDBC.ConnectionURL"/>
<property value="${username}" name="JDBC.Username"/>
<property value="${password}" name="JDBC.Password"/>
</dataSource>
</transactionManager>
<sqlMap resource="com/wysm/netstar/persistence/sqlmapdao/sql/User.xml"/>
<sqlMap resource="com/wysm/netstar/persistence/sqlmapdao/sql/Sequence.xml"/>
<sqlMap resource="com/wysm/netstar/persistence/sqlmapdao/sql/UserInfo.xml"/>
</sqlMapConfig>
(采用JDNI的方式时配置文件如下:不过database.properties文件的相应内容有所区别对于安全性和效率来说建议采用JDNI的连接方式)
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMapConfig PUBLIC "-//iBATIS.com//DTD SQL Map Config 2.0//EN" "http://www.ibatis.com/dtd/sql-map-config-2.dtd">
<sqlMapConfig>
<properties resource=" com/wysm/netstar/properties/database.properties " />
<settings
参数优化,在采用JDBC的SIMPLE连接方式叶可以加上左边的内容。 |
enhancementEnabled="true"
lazyLoadingEnabled="true"
errorTracingEnabled="true"
maxRequests="32"
maxSessions="10"
maxTransactions="5"
useStatementNamespaces="false" />
<transactionManager type="JDBC">
<dataSource type="JNDI">
<property name="DataSource" value="${userDataSource}" />
</dataSource>
</transactionManager>
<sqlMap
<sqlMap resource="com/wysm/netstar/persistence/sqlmapdao/sql/User.xml"/>
<sqlMap resource="com/wysm/netstar/persistence/sqlmapdao/sql/Sequence.xml"/>
<sqlMap resource="com/wysm/netstar/persistence/sqlmapdao/sql/UserInfo.xml"/>
</sqlMapConfig>
数据库连接属性文件
com.wysm.netstar.properties.database.properties
####################################
# Database Connectivity Properties
####################################
# JDBC
driver=oracle.jdbc.driver.OracleDriver
url=jdbc:oracle:thin:@localhost:1521:ORA92SER
username=netstar
password=netstar
# JNDI
userDataSource=user/jndi
com.wysm.netstar.persistence.sqlmapdao.sql/Sequence.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="Sequence">
<select id="getSequenceUserId" resultClass="int">
select sequence_userid.nextval from dual
</select>
</sqlMap>
com.wysm.netstar.persistence.sqlmapdao.sql/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">
<typeAlias alias="user" type="com.wysm.netstar.domain.User"/>
<select id="getUserByUserAlias" resultClass="user" parameterClass="string">
SELECT
USERID as userId,
UALIAS as userAlias,
PASSWORD as password,
REALNAME as realName,
EMAIL as email,
USERTYPEID as userTypeId
FROM T_USERS
WHERE UALIAS = #userAlias#
</select>
<select id="getUserByUserAliasAndPassword" resultClass="user" parameterClass="user">
SELECT
USERID as userId,
UALIAS as userAlias,
PASSWORD as password,
REALNAME as realName,
EMAIL as email,
USERTYPEID as userTypeId
FROM T_USERS
WHERE UALIAS = #userAlias# AND
PASSWORD = #password#
</select>
<select id="getUserByEmail" resultClass="user" parameterClass="string">
SELECT
USERID as userId,
UALIAS as userAlias,
PASSWORD as password,
REALNAME as realName,
EMAIL as email,
USERTYPEID as userTypeId
FROM T_USERS
WHERE EMAIL=#email#
</select>
<update id="updateUser" parameterClass="user">
UPDATE T_USERS SET
UALIAS= #userAlias#,
PASSWORD=#password#,
REALNAME=#realName#,
EMAIL = #email#,
USERTYPEID = #userTypeId#
WHERE USERID = #userId#
</update>
<insert id="insertUser" parameterClass="user">
INSERT INTO T_USERS
(USERID,
UALIAS,
PASSWORD,
REALNAME,
EMAIL,
USERTYPEID)
VALUES
(#userId#, #userAlias#,#password#,#realName#, #email#, #userTypeId#)
</insert>
<delete id="deleteUser" parameterClass="int">
DELETE FROM T_USERS
WHERE USERID=#userId#
</delete>
<select id="existUserId" parameterClass="int">
select count(*) FROM T_USERS
WHERE USERID=#userId#
</select>
</sqlMap>
com.wysm.netstar.persistence.sqlmapdao.sql/UserInfo.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">
<typeAlias alias="userInfo" type="com.wysm.netstar.domain.UserInfo"/>
<select id="getUserInfoByUserId" resultClass="userInfo" parameterClass="int">
SELECT
USERID,
REGDATE,
LASTLOGDATE,
LOGTIMES
FROM T_USER_INFO
WHERE USERID = #userId#
</select>
<update id="updateUserInfo" parameterClass="userInfo">
UPDATE T_USER_INFO SET
REGDATE= #regDate#,
LASTLOGDATE=#lastLogDate#,
LOGTIMES=#logTimes#
WHERE USERID = #userId#
</update>
<insert id="insertUserInfo" parameterClass="userInfo">
INSERT INTO T_USER_INFO
(USERID,
REGDATE,
LASTLOGDATE,
LOGTIMES)
VALUES
(#userId#, #regDate#,#lastLogDate#,#logTimes#)
</insert>
<delete id="deleteUserInfo" parameterClass="int">
DELETE FROM T_USER_INFO
WHERE USERID=#userId#
</delete>
</sqlMap>