Spring MVC + oracle 实现增删改查

原创

口袋里的小龙 2021-12-09 14:59:48 博主文章分类：Spring ©著作权

文章标签 spring mvc SpringMVC+ORACLE 实现增删改查Spring+oracle spring java 文章分类 Java 后端开发

©著作权归作者所有：来自51CTO博客作者口袋里的小龙的原创作品，请联系作者获取转载授权，否则将追究法律责任

Spring MVC + oracle 实现增删改查_SpringMVC+ORACLE

jar包只要这几个就好了

数据库

---------创建表 
CREATE TABLE UserInfo (    
user_id number(5) primary key not null,    
user_name nvarchar2(50) not null,    
user_password nvarchar2(50)    ); 
commit;

insert into UserInfo values(1,'1001','111111'); insert into UserInfo values(2,'1002','111111'); insert into UserInfo values(3,'1003','111111'); insert into UserInfo values(4,'1004','111111'); insert into UserInfo values(5,'1005','111111'); commit;

SELECT * FROM UserInfo;

Spring 配置文件

<?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">
  <!-- springmvc访问页面是通过名字来获取的 对应于映射 mapping -->
  <bean name="/index.action" class="com.dragon.controller.HelloController">
    <!-- 执行成功返回的页面 -->
    <property name="successView" value="index"/>
  </bean>

  <!-- 配置视图解析器 可以大大的简化代码 -->
  <bean id="viewResolver"   class="org.springframework.web.servlet.view.InternalResourceViewResolver">
    <!-- 配置视图模式提供jstl的支持 以便于国际化 -->
    <property name="viewClass"    value="org.springframework.web.servlet.view.JstlView"/>
    <!-- 前缀 -->
    <property name="prefix" value="/"/>
    <!-- 后缀 -->
    <property name="suffix" value=".jsp"/>
  </bean>

  <!-- 配置数据库连接 -->
  <bean id="dataSource"   class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <!-- 数据库连接驱动 -->
    <property name="driverClassName" value="oracle.jdbc.driver.OracleDriver" />
    <!-- 数据库连接地址 -->
    <property name="url" value="jdbc:oracle:thin:@localhost:1521:orcl" />
    <!-- 用户名 -->
    <property name="username" value="system" />
    <!--  -->
    <property name="password" value="orcl" />
  </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="add*" propagation="REQUIRED" />
      <tx:method name="delete*" propagation="REQUIRED" />
      <tx:method name="update*" propagation="REQUIRED" />
      <tx:method name="*" read-only="true" />
    </tx:attributes>
  </tx:advice>

  <bean id="userDao" class="com.dragon.dao.impl.UserDaoImpl">
    <property name="dataSource" ref="dataSource" />
  </bean>
  <!-- 查询全部的方法 -->
  <bean id="userController" name="/selectAll.action" class="com.dragon.controller.UserController">
    <property name="userDaoImpl" ref="userDao"/>
  </bean>
  <!-- 添加的方法 -->
  <bean id="addUserController" name="/addUser.action" class="com.dragon.controller.AddController">
    <property name="userDaoImpl" ref="userDao"/>
    <!--  请求转发 -->
    <property name="successView" value="selectAll.action"/>
  </bean>
  <!-- 删除的方法 -->
  <bean id="deleteUserController" name="/deleteUser.action" class="com.dragon.controller.DeleteController">
    <property name="userDaoImpl" ref="userDao"/>
    <!--  请求转发 -->
    <property name="successView" value="selectAll.action"/>
  </bean>
  <!-- 加载修改 -->
  <bean id="loadEditController" name="/loadUser.action" class="com.dragon.controller.LoadEdit">
    <property name="userDaoImpl" ref="userDao"/>
  </bean>
  <!-- 修改 -->
  <bean id="editUserController" name="/editUser.action" class="com.dragon.controller.EditController">
    <property name="userDaoImpl" ref="userDao"/>
    <!--  请求转发 -->
    <property name="successView" value="selectAll.action"/>
  </bean>
</beans>

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">
  <display-name/>

  <!-- 配置spring支持 -->
  <!-- 配置spring的核心控制器 -->
  <servlet>
    <servlet-name>springapp</servlet-name>
    <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
    <load-on-startup>1</load-on-startup>
  </servlet>
  <!-- 配置拦截  action为后缀名的请求 -->
  <servlet-mapping>
    <servlet-name>springapp</servlet-name>
    <url-pattern>*.action</url-pattern>
  </servlet-mapping>
  <welcome-file-list>
    <welcome-file>index.jsp</welcome-file>
  </welcome-file-list>
</web-app>

index页面

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@ taglib prefix="j" uri="<A href="
  http://java.sun.com/jsp/jstl/core"%" rel="nofollow">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 border="1">
    <tr>
      <td>编号</td>
      <td>名称</td>
      <td></td>
      <td>操作</td>
    </tr>
    <j:forEach var="user" items="${maps.userInfoList }">
      <tr>
        <td><j:out value="${user.user_id }"></j:out></td>
        <td><j:out value="${user.user_name }"></j:out></td>
        <td><j:out value="${user.user_password }"></j:out></td>
        <td>&nbsp;&nbsp;&nbsp;<a
          href="deleteUser.action?user_id=${user.user_id }">删除</a>&nbsp;&nbsp;&nbsp;<a
          href="loadUser.action?user_id=${user.user_id }">修改</a>
        </td>
      </tr>
    </j:forEach>
    <tr>
      <td><a href="add.jsp"> 添加</a></td>

    </tr>
  </table>
</body>
</html>

edit页面

<%@ 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 'edit.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="editUser.action" method="post">
    <table border="1">
      <tr>
        <td>编号</td>
        <td><input type="text" value="${userInfo.user_id }"
          name="user_id"></td>
      </tr>
      <tr>
        <td>名称</td>
        <td><input type="text" value="${userInfo.user_name }"
          name="user_name"></td>
      </tr>
      <tr>
        <td></td>
        <td><input type="text" value="${userInfo.user_password }"
          name="user_password"></td>
      </tr>
      <tr>
        <td><input type="submit" value="修改" /></td>
      </tr>
    </table>
  </form>
</body>
</html>

add页面

<%@ 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="addUser.action" method="post">
    <table border="1">
      <tr>
        <td>编号：<input value="" name="user_id" type="text" /></td>
        <td>名称：<input value="" name="user_name" type="text" /></td>
        <td>：<input value="" name="user_password" type="text" /></td>
        <td><input type="submit" value="提交" /></td>
      </tr>
    </table>
  </form>
</body>
</html>

实体类

package com.dragon.entity;

import java.io.Serializable;
/**
 * 用户类
 * @author Administrator
 *
 */
public class UserInfo implements Serializable{
 /**
  * 
  */
 private static final long serialVersionUID = 1L;
 private int user_id;
 private String user_name;
 private String user_password;
 public int getUser_id() {
  return user_id;
 }
 public void setUser_id(int user_id) {
  this.user_id = user_id;
 }
 public String getUser_name() {
  return user_name;
 }
 public void setUser_name(String user_name) {
  this.user_name = user_name;
 }
 public String getUser_password() {
  return user_password;
 }
 public void setUser_password(String user_password) {
  this.user_password = user_password;
 }
 public UserInfo(int user_id, String user_name, String user_password) {
  super();
  this.user_id = user_id;
  this.user_name = user_name;
  this.user_password = user_password;
 }
 public UserInfo() {
  super();
 }

}

UserDao 类

package com.dragon.dao;

import java.util.List;

import com.dragon.entity.UserInfo;

public interface UserDao {  

public abstract List<UserInfo> getAllUser();  
public abstract int addUserInfo(UserInfo userInfo);  
public abstract UserInfo getUserInfo(int id);  
public abstract int deleteUserInfo(UserInfo userInfo);  
public abstract int updateUserInfo(UserInfo userInfo);  

 }

userDaoImpl 类

package com.dragon.dao.impl;

import java.sql.ResultSet; import java.sql.SQLException; import java.util.List;

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

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

public class UserDaoImpl extends SimpleJdbcDaoSupport implements UserDao {  
/**   
* 查询全部   
*/  
public List<UserInfo> getAllUser() {   
// TODO Auto-generated method stub   
List<UserInfo> userInfoList = null;  
 try {   
 String sql = "select * from userInfo";    //ParameterizedBeanPropertyRowMapper.newInstance(UserInfo.class)    
userInfoList = this.getSimpleJdbcTemplate().query(sql, new UserMapper() );   
} catch (Exception e) {    
// TODO: handle exception    
e.printStackTrace();   
}     
return userInfoList;  
}

 @SuppressWarnings("unused")  
private static class UserMapper implements ParameterizedRowMapper<UserInfo>{   
/**    
* 从数据库中检索到数据的时候转换为实体类的类型    
*/  
 public UserInfo mapRow(ResultSet resultSet, int rowNum) throws SQLException {   
// TODO Auto-generated method stub    
UserInfo userInfo = new UserInfo();    userInfo.setUser_id(resultSet.getInt("user_id"));    userInfo.setUser_name(resultSet.getString("user_name"));    userInfo.setUser_password(resultSet.getString("user_password"));

   return userInfo;   
}

 }  
/**   
* 添加用户对象的方法   
*/  
public int addUserInfo(UserInfo userInfo) {   
// TODO Auto-generated method stub   
//定义sql语句   
String sql = "insert into UserInfo values(:user_id,:user_name,:user_password )";   int result = 0;   
try {    
result = this.getSimpleJdbcTemplate().update(sql, new MapSqlParameterSource()    .addValue("user_id", userInfo.getUser_id())    
.addValue("user_name", userInfo.getUser_name())    
.addValue("user_password", userInfo.getUser_password()));   
} catch (Exception e) {   
 // TODO: handle exception
    e.printStackTrace();   }

  return result;  
}  
/**\   
* 根据编号查询对象信息   
*/  
public UserInfo getUserInfo(int id) {   
// TODO Auto-generated method stub   UserInfo userInfo =  null;  
 try {    
String sql = "SELECT * FROM UserInfo WHERE user_id = :user_id";    
//根据编号查询对象   List<UserInfo> userInfoList =  this.getSimpleJdbcTemplate().query(sql, new UserMapper(), new MapSqlParameterSource().addValue("user_id", id));   
if(userInfoList.size()>0){   
 userInfo = userInfoList.get(0);   
}   
} catch (Exception e) {    
// TODO: handle exception    
e.printStackTrace();   
}   return userInfo;  
}  
/**
   * 删除的方法   */
  public int deleteUserInfo(UserInfo userInfo) { 
  // TODO Auto-generated method stub  
 int result = 0;   try {    
String sql  = "DELETE FROM UserInfo WHERE user_id=:user_id";    result = this.getSimpleJdbcTemplate().update(sql, new MapSqlParameterSource().addValue("user_id", userInfo.getUser_id()));   } catch (Exception e) {    
// TODO: handle exception    
e.printStackTrace();  
}   return result;  
} 
 /**   * 修改的方法   
*/  public int updateUserInfo(UserInfo userInfo) {
   // TODO Auto-generated method stub   
int result = 0;   
try {  
  String sql = "UPDATE UserInfo SET user_name =:user_name,user_password=:user_password WHERE user_id=:user_id";   
result = this.getSimpleJdbcTemplate().update(sql, new MapSqlParameterSource().addValue("user_name", userInfo.getUser_name()).addValue("user_password", userInfo.getUser_password()).addValue("user_id", userInfo.getUser_id()));  
 } catch (Exception e) {    
// TODO: handle exception 
   e.printStackTrace();  
 }   return result;  
}

}

查询全部的Controller

package com.dragon.controller;

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.UserDao; 
import com.dragon.entity.UserInfo;

public class UserController extends AbstractController {
  private UserDao userDaoImpl;  public UserDao getUserDaoImpl() {
   return userDaoImpl;  
}  
public void setUserDaoImpl(UserDao userDaoImpl) {   
this.userDaoImpl = userDaoImpl;  
}
  private String successView;
    public String getSuccessView() { 
  return successView;  
}
  public void setSuccessView(String successView) { 
  this.successView = successView;  
}
  @Override  protected ModelAndView handleRequestInternal(HttpServletRequest request,HttpServletResponse response) throws Exception { 
  // TODO Auto-generated method stub    
List<UserInfo> userInfoList = this.userDaoImpl.getAllUser();
 //   for (int i = 0; i < userInfoList.size(); i++) { 
//    System.out.println(userInfoList.get(i).getUser_name()); 
//   }    
//创建集合对象保存参数结果    
Map<String, Object> maps = new HashMap<String, Object>();    
//保存用户信息集合    
maps.put("userInfoList", userInfoList);   
return new ModelAndView("index","maps",maps);  }

}

加载修改的Controller

package com.dragon.controller;

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.UserDao; import com.dragon.entity.UserInfo;

public class LoadEdit extends AbstractController {
  private UserDao userDaoImpl;  
public UserDao getUserDaoImpl() {
   return userDaoImpl;  
}  
public void setUserDaoImpl(UserDao userDaoImpl) {

   this.userDaoImpl = userDaoImpl;  
}  
private String successView;    
public String getSuccessView() {
   return successView;  
}  
public void setSuccessView(String successView) {
   this.successView = successView;  
}  
@Override  
protected ModelAndView handleRequestInternal(HttpServletRequest request,    HttpServletResponse response) throws Exception {
   // TODO Auto-generated method stub   
String user_id = request.getParameter("user_id");   
UserInfo userInfo  = this.userDaoImpl.getUserInfo(Integer.valueOf(user_id));      
return new ModelAndView("edit", "userInfo" ,userInfo);  
}

}

执行修改的Controller

package com.dragon.controller;

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

import org.springframework.web.servlet.ModelAndView; import org.springframework.web.servlet.mvc.Controller; import org.springframework.web.servlet.view.RedirectView;

import com.dragon.dao.UserDao;
 import com.dragon.entity.UserInfo;
 /** 
 * 修改的方法  
* @author Administrator
  * 
 */ 
public class EditController implements Controller {  
private UserDao userDaoImpl;  
public UserDao getUserDaoImpl() {   
return userDaoImpl;  
}  
public void setUserDaoImpl(UserDao userDaoImpl) {   
this.userDaoImpl = userDaoImpl;  
}  
private String successView;    
public String getSuccessView() {   
return successView;  
}  
public void setSuccessView(String successView) {   
this.successView = successView;  
}  
public ModelAndView handleRequest(HttpServletRequest request,    HttpServletResponse response) throws Exception {     
//获得用户的输入   
String user_id = request.getParameter("user_id");   
String user_name = request.getParameter("user_name");   
String user_password = request.getParameter("user_password");      
UserInfo userInfo = this.userDaoImpl.getUserInfo(Integer.valueOf(user_id));   userInfo.setUser_id(Integer.valueOf(user_id));   
userInfo.setUser_name(user_name);   
userInfo.setUser_password(user_password);   
int result = this.userDaoImpl.updateUserInfo(userInfo);      
if(result>0){    
return new ModelAndView(new RedirectView(this.getSuccessView()));   
}      
return new ModelAndView("edit");  
}

}

删除的Controller

package com.dragon.controller;

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

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

import com.dragon.dao.UserDao; 
import com.dragon.entity.UserInfo; 
/**  
* 删除的方法  
* @author Administrator  
*  
*/ 
public class DeleteController extends AbstractController {  
private UserDao userDaoImpl;  
public UserDao getUserDaoImpl() {
   return userDaoImpl;  
}  
public void setUserDaoImpl(UserDao userDaoImpl) {
   this.userDaoImpl = userDaoImpl;  
}  
private String successView;    
public String getSuccessView() {
   return successView;  
}  
public void setSuccessView(String successView) {
   this.successView = successView;  
}  
@Override  
protected ModelAndView handleRequestInternal(HttpServletRequest request,    HttpServletResponse response) throws Exception {
   // TODO Auto-generated method stub   
//获得用户的输入   
String user_id = request.getParameter("user_id");   
//查询当前的对象   
UserInfo userInfo  = this.userDaoImpl.getUserInfo(Integer.valueOf(user_id));   
int result = 0;   
if(userInfo!=null){    
//调用删除的方法    
result = this.userDaoImpl.deleteUserInfo(userInfo);   
}   //判断是否删除成功   
if(result>0){    
return new ModelAndView(new RedirectView(this.getSuccessView()));   
}   
return new ModelAndView("error");  
}

}

添加的Controller

package com.dragon.controller;

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

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

import com.dragon.dao.UserDao; 
import com.dragon.entity.UserInfo; 
/**  
* 添加的方法  
* @author Administrator  
*  
*/ 
public class AddController extends AbstractController {   
 private UserDao userDaoImpl;  
public UserDao getUserDaoImpl() {   
return userDaoImpl;  
}  
public void setUserDaoImpl(UserDao userDaoImpl) {  
 this.userDaoImpl = userDaoImpl;  
}  
private String successView;    
public String getSuccessView() {   
return successView;  
}  
public void setSuccessView(String successView) {   
this.successView = successView;  
}  
@Override  
protected ModelAndView handleRequestInternal(HttpServletRequest request,    HttpServletResponse response) throws Exception {
   // TODO Auto-generated method stub      
//获得用户的输入   
String user_id = request.getParameter("user_id");   
String user_name = request.getParameter("user_name");   
String user_password = request.getParameter("user_password");   
UserInfo userInfo = new UserInfo();   
userInfo.setUser_id(Integer.valueOf(user_id));   
userInfo.setUser_name(user_name);   
userInfo.setUser_password(user_password);   
//调用插入的方法   
int result = this.userDaoImpl.addUserInfo(userInfo);   
// 判断用户是否插入成功   
if(result>0){    
//如果插入成功请求重定向    
return new ModelAndView(new RedirectView(this.getSuccessView()));   
}   
return new ModelAndView("add");  
}