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

  实体类

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;