Java EE WEB工程师培训——JDBC+Servlet+JSP整合开发之21.Servlet_资源访问（访问数据库）

2021-10-21 21:35:57

? B/S结构程序、B/A/S结构程序简介
? Servlet 资源访问
–Servlet 和 JDBC
? 实例
–维护客户列表

#######################################

? B/S结构程序、B/A/S结构程序简介
–B/S结构程序是指：Browser（客户端）/Server(服务器)两层结构
–B/A/S结构程序是指：Browser(客户端）/Application Server(应用程序服务器）/DB Server 三层结构

? Servlet 资源访问
–Servlet 和 JDBC
? Servlet作为控制器
–响应用户请求
? 获得表单数据
–调用后台逻辑
? 业务逻辑
? 数据逻辑
–页面导航
? JDBC
–使用DAO模式封装数据逻辑（封装持久层）

? 实例
–维护客户列表

Java EE WEB工程师培训——JDBC+Servlet+JSP整合开发之21.Servlet_资源访问（访问数据库）

DBServlet.java

package com.michael.servlet;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

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

public class DBServlet extends HttpServlet {

        /**
         * Constructor of the object.
         */
        public DBServlet() {
                super();
        }

        /**
         * Destruction of the servlet. <br>
         */
        public void destroy() {
                super.destroy(); // Just puts "destroy" string in log
                // Put your code here
        }

        /**
         * The doGet method of the servlet. <br>
         *
         * This method is called when a form has its tag value method equals to get.
         *
         * @param request
         *                        the request send by the client to the server
         * @param response
         *                        the response send by the server to the client
         * @throws ServletException
         *                         if an error occurred
         * @throws IOException
         *                         if an error occurred
         */
        public void doGet(HttpServletRequest request, HttpServletResponse response)
                        throws ServletException, IOException {

                doPost(request, response);
        }

        /**
         * The doPost method of the servlet. <br>
         *
         * This method is called when a form has its tag value method equals to
         * post.
         *
         * @param request
         *                        the request send by the client to the server
         * @param response
         *                        the response send by the server to the client
         * @throws ServletException
         *                         if an error occurred
         * @throws IOException
         *                         if an error occurred
         */
        public void doPost(HttpServletRequest request, HttpServletResponse response)
                        throws ServletException, IOException {

                // jdbc
                // get Connection
                Connection conn = null;
                // 加载驱动
                try {
                        Class.forName("com.mysql.jdbc.Driver");
                        conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/Servlet_db", "root", "963963");
                } catch (Exception e) {
                        e.printStackTrace();
                }
                // get Statement
                ResultSet rs = null;
                try {
                        Statement stmt = conn.createStatement();
                        String sql = "select * from customertbl";
                        rs = stmt.executeQuery(sql);
                } catch (SQLException e) {
                        e.printStackTrace();
                }

                response.setContentType("text/html");
                PrintWriter out = response.getWriter();
                out
                                .println("<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.01 Transitional//EN\">");
                out.println("<HTML>");
                out.println("    <HEAD><TITLE>A Servlet</TITLE></HEAD>");
                out.println("    <BODY>");
                out.println("    <table>");
                out.println("    <tr>");

                out.println("    <th>");
                out.println("ID");
                out.println("    </th>");

                out.println("    <th>");
                out.println("Name");
                out.println("    </th>");

                out.println("    <th>");
                out.println("Email");
                out.println("    </th>");

                try {
                        while (rs.next()) {
                                int id = rs.getInt(1);
                                String name = rs.getString(2);
                                String email = rs.getString(3);
                                out.println("    <tr>");
                                out.println("    <td>");
                                out.println(id);
                                out.println("    </td>");
                                out.println("    <td>");
                                out.println(name);
                                out.println("    </td>");
                                out.println("    <td>");
                                out.println(email);
                                out.println("    </td>");
                                out.println("    </tr>");
                        }
                } catch (SQLException e) {
                        e.printStackTrace();
                } finally {
                        try {
                                conn.close();
                        } catch (SQLException e) {
                                e.printStackTrace();
                        }
                }

                out.println("    </tr>");
                out.println("    </table>");
                out.println("    </BODY>");
                out.println("</HTML>");
                out.flush();
                out.close();
        }

        /**
         * Initialization of the servlet. <br>
         *
         * @throws ServletException
         *                         if an error occurs
         */
        public void init() throws ServletException {
                // Put your code here
        }

}

web.xml

测试

以上代码实现了读取数据库数据功能，但是没有实现分层结构哈~下面我们实现分层结构来实现代码共用。

DBServlet.java

package com.michael.servlet;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

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

import com.michael.dao.CustomerDao;
import com.michael.dao.impl.CustomerDaoImpl;
import com.michael.vo.Customer;

public class DBServlet extends HttpServlet {

        /**
         * Constructor of the object.
         */
        public DBServlet() {
                super();
        }

        /**
         * Destruction of the servlet. <br>
         */
        public void destroy() {
                super.destroy(); // Just puts "destroy" string in log
                // Put your code here
        }

        /**
         * The doGet method of the servlet. <br>
         *
         * This method is called when a form has its tag value method equals to get.
         *
         * @param request
         *                        the request send by the client to the server
         * @param response
         *                        the response send by the server to the client
         * @throws ServletException
         *                         if an error occurred
         * @throws IOException
         *                         if an error occurred
         */
        public void doGet(HttpServletRequest request, HttpServletResponse response)
                        throws ServletException, IOException {

                doPost(request, response);
        }

        /**
         * The doPost method of the servlet. <br>
         *
         * This method is called when a form has its tag value method equals to
         * post.
         *
         * @param request
         *                        the request send by the client to the server
         * @param response
         *                        the response send by the server to the client
         * @throws ServletException
         *                         if an error occurred
         * @throws IOException
         *                         if an error occurred
         */
        public void doPost(HttpServletRequest request, HttpServletResponse response)
                        throws ServletException, IOException {
                /*
                // jdbc
                // get Connection
                Connection conn = null;
                // 加载驱动
                try {
                        Class.forName("com.mysql.jdbc.Driver");
                        conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/Servlet_db", "root", "963963");
                } catch (Exception e) {
                        e.printStackTrace();
                }
                // get Statement
                ResultSet rs = null;
                try {
                        Statement stmt = conn.createStatement();
                        String sql = "select * from customertbl";
                        rs = stmt.executeQuery(sql);
                } catch (SQLException e) {
                        e.printStackTrace();
                }
                */
                CustomerDao dao = new CustomerDaoImpl();
                List list = dao.getCustomerList();

                response.setContentType("text/html");
                PrintWriter out = response.getWriter();
                out
                                .println("<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.01 Transitional//EN\">");
                out.println("<HTML>");
                out.println("    <HEAD><TITLE>A Servlet</TITLE></HEAD>");
                out.println("    <BODY>");
                out.println("    <table>");
                out.println("    <tr>");

                out.println("    <th>");
                out.println("ID");
                out.println("    </th>");

                out.println("    <th>");
                out.println("Name");
                out.println("    </th>");

                out.println("    <th>");
                out.println("Email");
                out.println("    </th>");
                /*
                try {
                        while (rs.next()) {
                                int id = rs.getInt(1);
                                String name = rs.getString(2);
                                String email = rs.getString(3);
                                out.println("    <tr>");
                                out.println("    <td>");
                                out.println(id);
                                out.println("    </td>");
                                out.println("    <td>");
                                out.println(name);
                                out.println("    </td>");
                                out.println("    <td>");
                                out.println(email);
                                out.println("    </td>");
                                out.println("    </tr>");
                        }
                } catch (SQLException e) {
                        e.printStackTrace();
                } finally {
                        try {
                                conn.close();
                        } catch (SQLException e) {
                                e.printStackTrace();
                        }
                }
                */
                for (int i=0;i<list.size();i++){
                        Customer c = (Customer) list.get(i);
                        out.println("    <tr>");
                        out.println("    <td>");
                        out.println(c.getId());
                        out.println("    </td>");
                        out.println("    <td>");
                        out.println(c.getName());
                        out.println("    </td>");
                        out.println("    <td>");
                        out.println(c.getEmail());
                        out.println("    </td>");
                        out.println("    </tr>");
                }
                out.println("    </tr>");
                out.println("    </table>");
                out.println("    </BODY>");
                out.println("</HTML>");
                out.flush();
                out.close();
        }

        /**
         * Initialization of the servlet. <br>
         *
         * @throws ServletException
         *                         if an error occurs
         */
        public void init() throws ServletException {
                // Put your code here
        }

}

CustomerDaoImpl

ConnectionUtil

package com.michael.dao.impl;

import java.sql.Connection;
import java.sql.DriverManager;
import java.util.Properties;

public class ConnectionUtil {

        /**
         * @param args
         */
        public static void main(String[] args) {
                ConnectionUtil cu = new ConnectionUtil();
                System.out.println(cu.openConnection());
        }
        public Connection openConnection() {
                String url = "";
                String driver = "";
                String user = "";
                String password = "";
                Properties prop = new Properties();
                try {
                        prop.load(this.getClass().getClassLoader().getResourceAsStream("DBConfig.properties"));
                        driver = prop.getProperty("driver");
                        url = prop.getProperty("url");
                        user = prop.getProperty("user");
                        password = prop.getProperty("password");
                        Class.forName(driver);
                        Connection conn = DriverManager.getConnection(
                                        url, user, password);
                        return conn;
                } catch (Exception e) {
                        e.printStackTrace();
                }
                return null;
        }
        public Connection getConnection(String driver, String url, String user,
                        String password) {
                // Class.forName()
                try {
                        Class.forName(driver);
                        // DriverManager get connection
                        Connection conn = DriverManager.getConnection(url, user, password);
                        return conn;
                } catch (Exception e) {
                        e.printStackTrace();
                }
                return null;
        }

        public Connection getConnection() {
                // Class.forName()
                try {
                        Class.forName("com.mysql.jdbc.Driver");
                        // DriverManager get connection
                        Connection conn = DriverManager.getConnection(
                                        "jdbc:mysql://localhost:3306/Servlet_db", "root", "963963");
                        return conn;
                } catch (Exception e) {
                        e.printStackTrace();
                }
                return null;
        }

}

DBConfig.properties

Customer

测试

############################################

本文转自redking51CTO博客，原文链接：http://blog.51cto.com/redking/289971，如需转载请自行联系原作者

码农公寓

相关文章