Java web学习——Spring MVC项目实例，三层架构通过JDBC链接SQLServer2012

雨点打透心脏的1/2处 2022-06-12 01:10 185阅读 0赞

**Spring MVC架构原理？**[原理篇][Link 1]  
![这里写图片描述][SouthEast]

**1、新建项目**  
File-New-Other,选择Dynamic web project

![这里写图片描述][SouthEast 1]

项目建好后的目录结构如下：  
![这里写图片描述][SouthEast 2]

**2、导入jar包**  
导入spring mvc框架需要的jar包  
[spring mvc常用jar包官方下载地址][spring mvc_jar]  
commons-logging-1.1.1.jar[下载地址][Link 2]  
sqljdbc jar包（此为链接sql server的jdbc jar包）  
将下载好的jar包copy到lib文件夹中  
![这里写图片描述][SouthEast 3]

**3、在WEB-INF下新建web.xml（前端控制器）、springmvc.xml(处理器映射器、视图解析器)配置文件**

*  web.xml

<?xml version="1.0" encoding="UTF-8"?>
    <web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" id="WebApp_ID" version="2.5">
    
        
        <servlet>
            <servlet-name>springDispatcherServlet</servlet-name>
            <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
            <init-param>
                <param-name>contextConfigLocation</param-name>
                <param-value>/WEB-INF/springmvc.xml</param-value>
            </init-param>
            <load-on-startup>1</load-on-startup>
        </servlet>
    
        <servlet-mapping>
            <servlet-name>springDispatcherServlet</servlet-name>
            <url-pattern>/</url-pattern>
        </servlet-mapping>
    </web-app>

*  springmvc.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:context="http://www.springframework.org/schema/context" xmlns:mvc="http://www.springframework.org/schema/mvc" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.0.xsd http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-4.0.xsd">
    
    
            
            <context:component-scan base-package="com.controller"></context:component-scan>
            <context:component-scan base-package="com.utility"></context:component-scan>
    
            
            <bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">
                <property name = "prefix" value="/WEB-INF/views/"></property>
                <property name = "suffix" value = ".jsp"></property>
            </bean>
    </beans>

**4、新建Handler（controller）、service、dao**  
![这里写图片描述][SouthEast 4]  
此处可能有人会说，咦，我在com包下新建了一个包为啥没上下层级结构显示，表急再在com下新建一个包时就可以了

按照目录结构建好相关的包和类

*  BookingController.java

package com.controller;
    
    import java.util.ArrayList;
    import java.util.List;
    import java.util.Map;
    
    
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Controller;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.servlet.ModelAndView;
    
    import com.utility.DBHelper;
    
    @Controller
    @RequestMapping("/Booking")
    public class BookingController {
    
        @Autowired
        private DBHelper dbhelper ;
    
        @RequestMapping("/getBooking")
        public ModelAndView getBooking() {
    
            List<Map<String,Object>> list=new ArrayList<Map<String,Object>>();
            try {
                list = dbhelper.ExecuteResultSet("select * from [dbo].[Users]");
            } catch (Exception e) {
                e.printStackTrace();
                StackTraceElement[] messages = e.getStackTrace();
                int length = messages.length;
                for (int i = 0; i < length; i++) { 
                    System.out.println("ClassName:" + messages[i].getClassName());
                    System.out.println("getFileName:" + messages[i].getFileName());
                    System.out.println("getLineNumber:"
                            + messages[i].getLineNumber());
                    System.out.println("getMethodName:"
                            + messages[i].getMethodName());
                    System.out.println("toString:" + messages[i].toString());
                }
            }
            return new ModelAndView("booking","list",list);
        }
    }

*  BookingService.java

package com.service;
    
    import java.util.ArrayList;
    import java.util.List;
    import java.util.Map;
    
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Service;
    
    import com.utility.DBHelper;
    
    @Service
    public class BookingService {
    
        @Autowired
        private DBHelper dbhelper;
    
        public  List<Map<String,Object>>  getStockList()
        {
            List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
            try {
                list = dbhelper.ExecuteResultSet("SELECT * FROM dbo.Stock;");
            } catch (Exception e) {
                e.printStackTrace();
            }
            return list;
    
        }
    }

*  DBHelper.java

package com.utility;
    
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.ResultSetMetaData;
    import java.sql.SQLException;
    import java.sql.Statement;
    import java.util.ArrayList;
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    import java.util.Properties;
    
    import org.springframework.stereotype.Service;
    
    
    
    @Service
    public class DBHelper { 
    
        private static String driverName;
        private static String dbURL;
        private static String userName;
        private static String password;
        private static Connection connection;
        private static Statement statement;
    
        public static Connection getConnection() throws SQLException
        {
            Connection conn =null;
            try {
                Properties prop =  new  Properties();   
                prop.load(DBHelper.class.getClassLoader().getResourceAsStream("DB.properties"));
                driverName=prop.getProperty("driverName");
                dbURL=prop.getProperty("dbURL");
                userName=prop.getProperty("userName");
                password=prop.getProperty("password");
                Class.forName(driverName);
                conn =DriverManager.getConnection(dbURL, userName, password);
            } catch (Exception e) {
                e.printStackTrace();
            }
            return conn;
        }
    
        /* * 释放资源 */
        public static void releaseResources(ResultSet resultSet, Statement statement, Connection connection) {
            try {
                if (resultSet != null)
                    resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                resultSet = null;
                try {
                    if (statement != null)
                        statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                } finally {
                    statement = null;
                    try {
                        if (connection != null)
                            connection.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    } finally {
                        connection = null;
                    }
                }
            }
    
        }
    
        public  List<Map<String,Object>>  ExecuteResultSet(String sql) throws SQLException {
            List<Map<String,Object>> list = new  ArrayList<Map<String,Object>>();
            ResultSet rs=null;
            try {
                connection = getConnection();
                statement = connection.createStatement();
                rs = statement.executeQuery(sql);
                ResultSetMetaData m=rs.getMetaData();
                int count=m.getColumnCount();
                while (rs.next()) {
                    Map<String,Object> rowData=new HashMap<String, Object>();
                    for (int i = 1; i < count; i++) {
                        Object value=rs.getObject(i)==null?"":rs.getObject(i);  
                        rowData.put(m.getColumnName(i), value);
                    }
                    list.add(rowData);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                releaseResources(rs, statement, connection);
            }
            return list;
        }
    
        /* * 更新操作 */
        public void Execute(String sql) {
            try {
                connection = getConnection();
                statement = connection.createStatement();
                // 可执行创建、修改、删除表，添加、删除、修改元组以及查询sql语句
                statement.execute(sql);
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                releaseResources(null, statement, connection);
            }
        }
    
    
    
    
    }

*  DB.properties数据库配置文件
 *  注意在SQL Server 配置管理器中开启TCP/IP 1433端口

driverName=com.microsoft.sqlserver.jdbc.SQLServerDriver dbURL=jdbc:sqlserver://127.0.0.1:1433;DatabaseName=ManagementWeb userName=sa password=vaecn7820221

**5、新建index.jsp页面和views文件夹、booking.jsp页面**

*  index.jsp

<%@ page language="java" contentType="text/html; charset=GB18030" pageEncoding="GB18030"%> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=GB18030"> <title>Insert title here</title> </head> <body> <h2>JAVA EE</h2> <a href="Booking/getBooking">getBooking</a> </body> </html>

*  booking.jsp

<%@ page language="java" contentType="text/html; charset=GB18030" pageEncoding="GB18030"%>  
     <%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
    
    
    <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
    <html>
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=GB18030">
    <title>Insert title here</title>
    </head>
    <body>
    <h1>booking</h1>
    
    ${list}
    <br>
    <c:forEach var="arry" items="${list}" > 
              <tr> 
              <td> <c:out value="${arry.User_UserID}" default="wang"/> 
              </td> 
    
              <td> 
               <c:out value="${arry.User_Name}" default="wang"/> 
              </td> 
              </tr> 
           </c:forEach> 
    
    </body>
    </html>

![这里写图片描述][SouthEast 5]

点击getBooking请求BookingController转发到/WEB-INF/views/booking.jsp页面

![这里写图片描述][SouthEast 6]

[Link 1]: http://blog.csdn.net/chinaplan/article/details/74644690
[SouthEast]: /images/20220612/9322b6dcd1de432d9b0bbbe668d5d31b.png
[SouthEast 1]: /images/20220612/8203ed4aa5c84dd7ab1c3ee91953ae6b.png
[SouthEast 2]: /images/20220612/1cbf064cf77b4c92a370269b04d1431e.png
[spring mvc_jar]: http://maven.springframework.org/release/org/springframework/spring/
[Link 2]: http://commons.apache.org/proper/commons-logging/download_logging.cgi
[SouthEast 3]: /images/20220612/237f267bd93e4ac99303f0dcad18538a.png
[SouthEast 4]: /images/20220612/6b0884d5ea3e4d90823c503769a13c5f.png
[SouthEast 5]: /images/20220612/84c08f89aa9d4898949e827eaf848843.png
[SouthEast 6]: /images/20220612/f11123a2578e42099725166c7d309a29.png