SpringMVC(四) 连接oracle数据库操作

首先看一下项目结构

实体类

package com.dragon.entity;

public class UserInfo {
private int user_id;
private String user_name;
private String user_password;
public int getUser_id() {
return user_id;
}
public void setUser_id(int userId) {
user_id = userId;
}
public String getUser_name() {
return user_name;
}
public void setUser_name(String userName) {
user_name = userName;
}
public String getUser_password() {
return user_password;
}
public void setUser_password(String userPassword) {
user_password = userPassword;
}

}

Dao层

package com.dragon.dao;

import java.util.List;

import com.dragon.entity.UserInfo;

public interface UserInfoDao {
public abstract List<UserInfo> getAll();
public abstract int insertUserInfo(UserInfo userInfo);
}

实现层

package com.dragon.dao.impl;

import java.util.ArrayList;
import java.util.List;

import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.simple.ParameterizedBeanPropertyRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcDaoSupport;

import com.dragon.dao.UserInfoDao;
import com.dragon.entity.UserInfo;

 

public class UserInfoDaoImpl extends SimpleJdbcDaoSupport implements
UserInfoDao {
/**
* 获得所有
*/
public List<UserInfo> getAll() {
// TODO Auto-generated method stub
List<UserInfo> userInfoList = new ArrayList<UserInfo>();
String sql = "select * from UserInfo";
userInfoList = super.getSimpleJdbcTemplate().query(sql,ParameterizedBeanPropertyRowMapper.newInstance(UserInfo.class));
return userInfoList;
}
/**
* 增加
*/
public int insertUserInfo(UserInfo userInfo) {
// TODO Auto-generated method stub
String sql="INSERT INTO UserInfo values(:user_id,:user_name,:user_password)";
//返回受影响的行数
int count = super.getSimpleJdbcTemplate().update(sql, new MapSqlParameterSource().addValue("user_id", userInfo.getUser_id()).addValue("user_name", userInfo.getUser_name()).addValue("user_password", userInfo.getUser_password()));
return count;
}

}

Controller类

package com.dragon.controller;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.springframework.web.servlet.ModelAndView;
import org.springframework.web.servlet.mvc.AbstractController;

import com.dragon.dao.UserInfoDao;
import com.dragon.entity.UserInfo;

/**

*查询所有

*/

public class UserInfoController extends AbstractController {

private UserInfoDao userInfoDao;

public UserInfoDao getUserInfoDao() {
return userInfoDao;
}

public void setUserInfoDao(UserInfoDao userInfoDao) {
this.userInfoDao = userInfoDao;
}
@Override
protected ModelAndView handleRequestInternal(HttpServletRequest arg0,
HttpServletResponse arg1) throws Exception {
// TODO Auto-generated method stub
//定义用户集合
List<UserInfo> userInfoList = new ArrayList<UserInfo>();
//调用获得所有的方法
userInfoList = this.userInfoDao.getAll();
//创建集合
Map<String,Object> models = new HashMap<String, Object>();
//保存对象
models.put("userInfoList", userInfoList);
return new ModelAndView("index.jsp",models);
}

}

添加

package com.dragon.controller;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.springframework.web.servlet.ModelAndView;
import org.springframework.web.servlet.mvc.AbstractController;

import com.dragon.dao.UserInfoDao;
import com.dragon.entity.UserInfo;

public class AddUserInfoController extends AbstractController {
private UserInfoDao userInfoDao;

public UserInfoDao getUserInfoDao() {
return userInfoDao;
}

public void setUserInfoDao(UserInfoDao userInfoDao) {
this.userInfoDao = userInfoDao;
}
@Override
protected ModelAndView handleRequestInternal(HttpServletRequest request,
HttpServletResponse response) throws Exception {
// TODO Auto-generated method stub

UserInfo userInfo = new UserInfo();

//获得用户的输入
userInfo.setUser_id(Integer.valueOf(request.getParameter("user_id")));
userInfo.setUser_name(request.getParameter("user_name"));
userInfo.setUser_password(request.getParameter("user_password"));

System.out.println(userInfo.getUser_id());

int i = this.userInfoDao.insertUserInfo(userInfo);
System.out.println(i);
//定义用户集合
List<UserInfo> userInfoList = new ArrayList<UserInfo>();
//调用获得所有的方法
userInfoList = this.userInfoDao.getAll();
//创建集合
Map<String,Object> models = new HashMap<String, Object>();
//保存对象
models.put("userInfoList", userInfoList);
return new ModelAndView("index.jsp",models);
}

}

jdbc.properties配置文件

jdbc.driverClassName=oracle.jdbc.driver.OracleDriver
jdbc.url=jdbc:oracle:thin:@localhost:1521:orcl
jdbc.username=system
jdbc.password=orcl

Web。xml中的配置

<?xml version="1.0" encoding="UTF-8"?>
<web-app version="2.5"
xmlns="http://java.sun.com/xml/ns/javaee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/javaee
http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"
>


<!-- 配置spring mvc 支持-->
<servlet>
<servlet-name>springapp</servlet-name>
<servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
<load-on-startup>1</load-on-startup>
</servlet>
<!-- 配置spring映射 -->
<servlet-mapping>
<servlet-name>springapp</servlet-name>
<url-pattern>*.do</url-pattern>
</servlet-mapping>

<welcome-file-list>
<welcome-file>index.jsp</welcome-file>
</welcome-file-list>
</web-app>

springapp-servlet.xml

<?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"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-2.5.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop-2.5.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx-2.5.xsd" >

<bean id="userController" name="/userController.do" class="com.dragon.controller.UserInfoController">
<property name="userInfoDao" ref="userInfoDao"></property>
</bean>
<bean id="addUserInfoController" name="/add.do" class="com.dragon.controller.AddUserInfoController">
<property name="userInfoDao" ref="userInfoDao"></property>
</bean>
<!-- -->
<bean id="userInfoDao" class="com.dragon.dao.impl.UserInfoDaoImpl">
<property name="dataSource" ref="dataSource"></property>
</bean>

<!-- 配置事务管理器 -->
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
<property name="driverClassName" value="${jdbc.driverClassName}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</bean>
<!-- 加载文件的路径 -->
<bean id="propertyConfigurer"
class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
<property name="locations">
<list>
<value>classpath:jdbc.properties</value>
</list>
</property>
</bean>
<!-- -->
<bean id="transactionManager"
class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource"/>
</bean>

<aop:config>

<aop:advisor pointcut="execution(* *com.dragon.dao.*(..))" advice-ref="txAdvice"/>
</aop:config>

<tx:advice id="txAdvice">
<tx:attributes>

<tx:method name="get*" read-only="true"></tx:method>
<tx:method name="insert*" propagation="REQUIRED"></tx:method>
<tx:method name="update*" propagation="REQUIRED"></tx:method>
<tx:method name="delete*" propagation="REQUIRED"></tx:method>

<tx:method name="*" read-only="true"/>
</tx:attributes>
</tx:advice>

</beans>

index.jsp

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@ taglib prefix="j" uri="http://java.sun.com/jsp/jstl/core" %>

<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<base href="<%=basePath%>">

<title>My JSP 'index.jsp' starting page</title>
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page">
<!--
<link rel="stylesheet" type="text/css" href="styles.css">
-->
</head>

<body>
<table>
<tr>
<td>编号</td>
<td>名称</td>
<td>密码</td>
<td>操作</td>
</tr>

<j:forEach var="userInfo" items="${userInfoList }" >
<tr>
<td> <j:out value="${userInfo.user_id}"></j:out> </td>
<td> <j:out value="${userInfo.user_name}"></j:out></td>
<td> <j:out value="${userInfo.user_password}"></j:out></td>
<td><a href="" >删除</a></td>
</tr>

</j:forEach>
<tr >
<td colspan="4"><a href="add.jsp">添加</a></td>
</tr>
</table>
</body>
</html>

add.jsp

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<base href="<%=basePath%>">

<title>My JSP 'add.jsp' starting page</title>

<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page">
<!--
<link rel="stylesheet" type="text/css" href="styles.css">
-->

</head>

<body>
<form action="add.do" method="post">
编号：<input type="text" value="" name="user_id"/><br/>
名称：<input type="text" value="" name="user_name"/><br/>
密码：<input type="text" value="" name="user_password"/><br/>
<input value="提交" type="submit" />
</form>
</body>
</html>

访问的路径

http://localhost:8080/SpringMVC_JDBC/userController.do

慢慢的理解 其实这些官方的文档中都是有的  这种方法不是太好 一个操作就需要创建一个controller 类

数据库脚本

CREATE TABLE UserInfo(
user_id NUMBER(5) PRIMARY KEY NOT NULL,
user_name NVARCHAR2(50) ,
user_password nvarchar2(50)
);
insert into UserInfo values(1,'1001','000000');
insert into UserInfo values(2,'1002','000000');
insert into UserInfo values(3,'1003','000000');
insert into UserInfo values(4,'1004','000000');
insert into UserInfo values(5,'1005','000000');
commit;

<wbr></wbr>