1 测试数据库搭建
本文是《.NET/JAVA/PHP/Python 企业级Web开发框架整合系列》之中的一个框架,使用的数据库与其他框架相同。
这里再提供一下本文使用到的SYS_USER表的建表SQL:
-- Create table
create table SYS_USER
(
user_code VARCHAR2(36) PRIMARY KEY,
login_name VARCHAR2(50) not null,
login_pwd VARCHAR2(50) not null,
user_level NUMBER(2) not null,
user_type NUMBER(2) not null,
telephone VARCHAR2(50),
position VARCHAR2(50),
user_name VARCHAR2(50) not null,
org_code VARCHAR2(50) not null,
head_portrait VARCHAR2(200),
user_sex NUMBER(2) not null,
register_time DATE not null,
last_logon_type NUMBER(2),
last_logon_time DATE,
last_logon_ip VARCHAR2(50),
logon_count NUMBER(8) not null,
logon_error_count NUMBER(8) not null,
user_state NUMBER(2) not null,
expire_policy NUMBER(2) not null,
allow_logined_count NUMBER(8) not null,
expire_date DATE not null
);
-- Add comments to the columns
comment on column SYS_USER.user_code is '用户编码,可以是手机号码等';
comment on column SYS_USER.login_name is '登录名称';
comment on column SYS_USER.login_pwd is '登录密码';
comment on column SYS_USER.user_level is '用户级别,1:总经理,2:部门经理,3:部门主管,4:客户经理';
comment on column SYS_USER.user_type is '用户类型,1:普通用户,2:管理员';
comment on column SYS_USER.telephone is '联系电话';
comment on column SYS_USER.position is '职位';
comment on column SYS_USER.user_name is '用户姓名';
comment on column SYS_USER.org_code is '机构编码,指向sys_org表';
comment on column SYS_USER.head_portrait is '用户头像';
comment on column SYS_USER.user_sex is '用户性别,1:男,2:女';
comment on column SYS_USER.register_time is '注册时间';
comment on column SYS_USER.last_logon_type is '最后登录方式,1:android,2:web,3:IOS';
comment on column SYS_USER.last_logon_time is '最后登录时间';
comment on column SYS_USER.last_logon_ip is '最后登录IP';
comment on column SYS_USER.logon_count is '登录总数';
comment on column SYS_USER.logon_error_count is '登录错误总数,同一天错误指定次数后锁定';
comment on column SYS_USER.user_state is '1:新注册未审核,2:正常,3:锁定';
comment on column SYS_USER.expire_policy is '密码过期策略,1:永不过期,2:登陆次数,3:指定日期,4:下次登陆修改密码';
comment on column SYS_USER.allow_logined_count is '允许登陆次数,在EXPIRE_POLICY为2时有效';
comment on column SYS_USER.expire_date is '账户过期日期,在EXPIRE_POLICY为3时有效';
2 jar包导入
需要导入的包如下:
c3p0-0.9.2.1.jar
mybatis-3.2.5.jar
mybatis-generator-core-1.3.1.jar
mybatis-spring-1.2.1.jar
ojdbc14.jar
本文结束将附完整的项目代码,包含所有jar。
3 编写java类
A.创建javabean
创建包:com.whowii.core.bean,并在其中创建类:User,代码如下:
package com.whowii.core.bean;
import java.util.Date;
public class User implements java.io.Serializable {
private static final long serialVersionUID = 7442514800601880744L;
private String userCode;
private String loginName;
private String loginPwd;
private Byte userLevel;
private Byte userType;
private String telephone;
private String position;
private String userName;
private String orgCode;
private String headPortrait;
private Byte userSex;
private Date registerTime;
private Byte lastLogonType;
private Date lastLogonTime;
private String lastLogonIp;
private Integer logonCount;
private Integer logonErrorCount;
private Byte userState;
private Byte expirePolicy;
private Integer allowLoginedCount;
private Date expireDate;
public String getUserCode() {
return userCode;
}
public void setUserCode(String userCode) {
this.userCode = userCode;
}
public String getLoginName() {
return loginName;
}
public void setLoginName(String loginName) {
this.loginName = loginName;
}
public String getLoginPwd() {
return loginPwd;
}
public void setLoginPwd(String loginPwd) {
this.loginPwd = loginPwd;
}
public Byte getUserLevel() {
return userLevel;
}
public void setUserLevel(Byte userLevel) {
this.userLevel = userLevel;
}
public Byte getUserType() {
return userType;
}
public void setUserType(Byte userType) {
this.userType = userType;
}
public String getTelephone() {
return telephone;
}
public void setTelephone(String telephone) {
this.telephone = telephone;
}
public String getPosition() {
return position;
}
public void setPosition(String position) {
this.position = position;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public String getOrgCode() {
return orgCode;
}
public void setOrgCode(String orgCode) {
this.orgCode = orgCode;
}
public String getHeadPortrait() {
return headPortrait;
}
public void setHeadPortrait(String headPortrait) {
this.headPortrait = headPortrait;
}
public Byte getUserSex() {
return userSex;
}
public void setUserSex(Byte userSex) {
this.userSex = userSex;
}
public Date getRegisterTime() {
return registerTime;
}
public void setRegisterTime(Date registerTime) {
this.registerTime = registerTime;
}
public Byte getLastLogonType() {
return lastLogonType;
}
public void setLastLogonType(Byte lastLogonType) {
this.lastLogonType = lastLogonType;
}
public Date getLastLogonTime() {
return lastLogonTime;
}
public void setLastLogonTime(Date lastLogonTime) {
this.lastLogonTime = lastLogonTime;
}
public String getLastLogonIp() {
return lastLogonIp;
}
public void setLastLogonIp(String lastLogonIp) {
this.lastLogonIp = lastLogonIp;
}
public Integer getLogonCount() {
return logonCount;
}
public void setLogonCount(Integer logonCount) {
this.logonCount = logonCount;
}
public Integer getLogonErrorCount() {
return logonErrorCount;
}
public void setLogonErrorCount(Integer logonErrorCount) {
this.logonErrorCount = logonErrorCount;
}
public Byte getUserState() {
return userState;
}
public void setUserState(Byte userState) {
this.userState = userState;
}
public Byte getExpirePolicy() {
return expirePolicy;
}
public void setExpirePolicy(Byte expirePolicy) {
this.expirePolicy = expirePolicy;
}
public Integer getAllowLoginedCount() {
return allowLoginedCount;
}
public void setAllowLoginedCount(Integer allowLoginedCount) {
this.allowLoginedCount = allowLoginedCount;
}
public Date getExpireDate() {
return expireDate;
}
public void setExpireDate(Date expireDate) {
this.expireDate = expireDate;
}
}
该javabean与sys_user表相对应。
B.创建mapper
新建包:com.whowii.core.mapper,并在其中新建类“UserMapper”和配置文件“user-mapper.xml”,内容分别下:
package com.whowii.core.mapper;
import org.springframework.dao.DataAccessException;
import com.whowii.core.bean.User;
public interface UserMapper {
// 插入一个新用户
public int insert(User user) throws DataAccessException;
}
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//ibatis.apache.org//DTD Mapper 3.0//EN"
"http://ibatis.apache.org/dtd/ibatis-3-mapper.dtd">
<mapper namespace="com.whowii.core.mapper.UserMapper">
<!-- 插入一个新用户 -->
<insert id="insert" parameterType="user" >
INSERT INTO sys_user(USER_CODE,LOGIN_NAME,LOGIN_PWD,USER_LEVEL,USER_TYPE,
TELEPHONE,POSITION,USER_NAME,ORG_CODE,USER_SEX,
REGISTER_TIME,LOGON_COUNT,LOGON_ERROR_COUNT,USER_STATE,EXPIRE_POLICY,
ALLOW_LOGINED_COUNT,EXPIRE_DATE)
VALUES(#{userCode},#{loginName},#{loginPwd},#{userLevel},#{userType},
#{telephone},#{position},#{userName},#{orgCode},#{userSex},
#{registerTime},#{logonCount},#{logonErrorCount},#{userState},#{expirePolicy},
#{allowLoginedCount},#{expireDate})
</insert>
</mapper>
该mapper仅实现的数据插入操作,本文仅演示insert功能,其它功能后文继续完善。
先修改UserService接口:
package com.whowii.core.service;
import org.springframework.dao.DataAccessException;
import com.whowii.core.bean.User;
public interface UserService {
public void insertUser(User user) throws DataAccessException;
}
然后修改UserServiceImpl实现类:
package com.whowii.core.service.impl;
import javax.annotation.Resource;
import org.springframework.dao.DataAccessException;
import org.springframework.stereotype.Service;
import com.whowii.core.bean.User;
import com.whowii.core.mapper.UserMapper;
import com.whowii.core.service.UserService;
@Service("userService")
public class UserServiceImpl implements UserService {
private UserMapper userMapper;
@Resource(name = "userMapper")
public void setUserMapper(UserMapper userMapper) {
this.userMapper = userMapper;
}
public void insertUser(User user) throws DataAccessException {
this.userMapper.insert(user);
}
}
实现类通过Resource注解方式注入UserMapper实例,并在数据插入时调用Mapper的insert方法。
D.修改Action代码,最终完成的代码如下:
package com.whowii.core.action;
import java.util.Date;
import java.util.UUID;
import javax.annotation.Resource;
import org.apache.log4j.Logger;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import com.whowii.core.bean.User;
import com.whowii.core.service.UserService;
@Controller
@RequestMapping(value = "/manage/enter")
public class EnterController {
private Logger logger = Logger.getLogger(getClass());
private UserService userService;
@Resource(name = "userService")
public void setUserService(UserService userService) {
this.userService = userService;
}
// 访问URL:website_java2/enter/index.do
@RequestMapping(value = "/index", method = RequestMethod.GET)
public String index(String id, Model model) {
logger.info("访问了index方法,id=" + id);
model.addAttribute("title", "系统登录");
model.addAttribute("date", "20150316");
User entity = new User();
entity.setUserCode(UUID.randomUUID().toString());
entity.setLoginName("A-NAME");
entity.setLoginPwd("A-PWD");
entity.setUserLevel((byte) 2);
entity.setUserType((byte) 1);
entity.setTelephone("A-TELEPHONE");
entity.setPosition("A-POSITION");
entity.setUserName("A-UN");
entity.setOrgCode("10");
entity.setHeadPortrait("A-PHOTO");
entity.setUserSex((byte) 1);
entity.setRegisterTime(new Date());
entity.setLastLogonType((byte) 1);
entity.setLastLogonTime(new Date());
entity.setLastLogonIp("A-192.168");
entity.setLogonCount(101);
entity.setLogonErrorCount(11);
entity.setUserState((byte) 1);
entity.setExpirePolicy((byte) 1);
entity.setAllowLoginedCount(999);
entity.setExpireDate(new Date());
this.userService.insertUser(entity);
return "manage/enter/index";
}
}
由于sys_user表的很多都是非空字段,所以这演示代码需要写入的属性比较多,可以根据实际情况修改。
4 修改配置文件
A.创建数据库配置文件
在src下增加properties文件:database.properties,内容如下:
# --------------------ORACL 数据库配置----------------------
# 基本连接信息
# 驱动类
oracle.driverClass=oracle.jdbc.driver.OracleDriver
# jdbc url
oracle.jdbcUrl=jdbc:oracle:thin:@localhost:1521:ORCL
# 数据库用户名
oracle.user=xx
# 数据库密码
oracle.password=xx
# 连接池信息
# 当连接池中的连接用完时,C3P0一次性创建新连接的数目,默认为 3
oracle.acquireIncrement=5
# 初始化时创建的连接数,应在minPoolSize与maxPoolSize之间取值。默认为3;
oracle.initialPoolSize=30
# 最大空闲时间,超过空闲时间的连接将被丢弃。为0或负数则永不丢弃。默认为0;
oracle.maxIdleTime=10
# 连接池中保留的最小连接数。默认为15
oracle.minPoolSize=20
# 连接池中保留的最大连接数。默认为15;
oracle.maxPoolSize=100
# JDBC的标准参数,用以控制数据源内加载的PreparedStatement数量。
# 但由于预缓存的Statement属 于单个Connection而不是整个连接池。
# 所以设置这个参数需要考虑到多方面的因素,如果maxStatements与 maxStatementsPerConnection均为0,则缓存被关闭。默认为0;
oracle.maxStatements=0
# 连接池内单个连接所拥有的最大缓存Statement数。默认为0;
oracle.maxStatementsPerConnection=5
这里需要根据你的实际情况,修改其中的数据库IP、端口、实例名、用户名和密码等信息。
B.创建mybatis配置文件
在src下增加xml文件:mybatis-config.xml,内容如下:
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//ibatis.apache.org//DTD Config 3.0//EN" "http://ibatis.apache.org/dtd/ibatis-3-config.dtd">
<configuration>
<typeAliases>
<!-- 别名定义 -->
<typeAlias type="com.whowii.core.bean.User" alias="user" />
</typeAliases>
<mappers>
<mapper resource="com/whowii/core/mapper/user-mapper.xml" />
</mappers>
</configuration>
需要注意的是别名(
这里是User)定义,后面的spring配置文件中将使用到。
C.修改spring配置文件
修改src/applicationContext.xml,加入database、mybatis、service等配置,最终的代码如下:
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:aop="http://www.springframework.org/schema/aop"
xmlns:tx="http://www.springframework.org/schema/tx" xmlns:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="
http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx-3.0.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-3.0.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop-3.0.xsd">
<!-- 加载 properties 配置文件 -->
<bean id="propertyConfigurer"
class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
<property name="location">
<value>classpath:database.properties</value>
</property>
<property name="fileEncoding" value="utf-8" />
</bean>
<!-- 数据源配置 -->
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"
destroy-method="close">
<!-- 基本连接信息 -->
<property name="driverClass" value="${oracle.driverClass}" />
<property name="jdbcUrl" value="${oracle.jdbcUrl}" />
<!-- 数据库用户名 -->
<property name="user" value="${oracle.user}" />
<!-- 数据库密码 -->
<property name="password" value="${oracle.password}" />
<!-- 连接池信息 -->
<!-- 当连接池中的连接用完时,C3P0一次性创建新连接的数目 -->
<property name="acquireIncrement" value="${oracle.acquireIncrement}" />
<!-- 初始化时创建的连接数,应在minPoolSize与maxPoolSize之间取值。默认为3; -->
<property name="initialPoolSize" value="${oracle.initialPoolSize}" />
<!-- 最大空闲时间,超过空闲时间的连接将被丢弃。为0或负数则永不丢弃。默认为0; -->
<property name="maxIdleTime" value="${oracle.maxIdleTime}" />
<!-- 连接池中保留的最小连接数。默认为15 -->
<property name="minPoolSize" value="${oracle.minPoolSize}" />
<!-- 连接池中保留的最大连接数。默认为15; -->
<property name="maxPoolSize" value="${oracle.maxPoolSize}" />
<!-- JDBC的标准参数,用以控制数据源内加载的PreparedStatement数量。但由于预缓存的Statement属 于单个Connection而不是整个连接池。所以设置这个参数需要考虑到多方面的因素,如果maxStatements与
maxStatementsPerConnection均为0,则缓存被关闭。默认为0; -->
<property name="maxStatements" value="${oracle.maxStatements}" />
<!-- 连接池内单个连接所拥有的最大缓存Statement数。默认为0; -->
<property name="maxStatementsPerConnection" value="${oracle.maxStatementsPerConnection}" />
</bean>
<!-- MyBatis配置 -->
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="configLocation" value="classpath:mybatis-config.xml" />
<property name="dataSource" ref="dataSource" />
</bean>
<!-- dao 层由 MapperScannerConfigurer自动生成mapper bean -->
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<property name="basePackage" value="com.whowii.core.mapper" />
</bean>
<!-- 自动扫描业务包 -->
<context:component-scan base-package="com.whowii.core.service.impl" />
</beans>
5 测试访问
启动tomcat,打开浏览器并访问:http://localhost:8180/website_java2/manage/enter/index.do
使用pl/sql或toad等工具查看数据库中的sys_user表就发现多了一条数据:
项目完整代码:
http://download.csdn.net/detail/xz2001/8509715