[原创]java WEB学习笔记12：一个简单的serlet连接数据库实验

2023-08-29 15:30:58

本博客为原创：综合尚硅谷（http://www.atguigu.com）的系统教程（深表感谢）和网络上的现有资源(博客，文档，图书等)，资源的出处我会标明

本博客的目的：①总结自己的学习过程，相当于学习笔记 ②将自己的经验分享给大家，相互学习，互相交流，不可商用

内容难免出现问题，欢迎指正，交流，探讨，可以留言，也可以通过以下方式联系。

本人互联网技术爱好者，互联网技术发烧友

微博：伊直都在0221

QQ：951226918

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

1.需求和步骤

1）在MySql 数据库中创建users:id ,user,password 。添加记录

2）定义一个login.html ，定义两个字段：user，password 。发送请求到LoginServlet

3）创建一个LoginServlet（继承HttpServlet，重写doPost方法），在其中获取请求的user，password

4) 利用JDBC 从users 中查询有没有和页面输入的user，password 对应的记录。若有，响应hello：xxx，若没有，sorry：xxx ，其中xxx为user

2.代码结构

1）com.jason.testweb.LoginServlet：Servlet类，处理前台提交的数据

 package com.jason.testweb;

 import java.io.IOException;

 import java.io.PrintWriter;

 import java.sql.Connection;

 import java.sql.PreparedStatement;

 import java.sql.ResultSet;

 import java.sql.SQLException;

 import javax.servlet.ServletException;

 import javax.servlet.http.HttpServlet;

 import javax.servlet.http.HttpServletRequest;

 import javax.servlet.http.HttpServletResponse;

 import com.jason.util.JDBCUtil;

 public class LoginServlet extends HttpServlet {

     /**

      *

      */

     private static final long serialVersionUID = 2197552322420611512L;

     @Override

     protected void doPost(HttpServletRequest request,

             HttpServletResponse response) throws ServletException, IOException {

         String userName = request.getParameter("username");

         String passWord = request.getParameter("password");

         PrintWriter printWriter = response.getWriter();

         Connection connection = null;

         PreparedStatement preparedStatement = null;

         ResultSet resultSet = null;

         try {

             connection = JDBCUtil.getConnection();

             String sql = "SELECT count(id) FROM users WHERE username = ? AND password = ? ";

             preparedStatement = connection.prepareStatement(sql);

             preparedStatement.setString(1, userName);

             preparedStatement.setString(2, passWord);

             resultSet = preparedStatement.executeQuery();

             if (resultSet.next()) {

                 int count = resultSet.getInt(1);

                 if (count > 0) {

                     printWriter.print("hello : " + userName);

                 } else {

                     printWriter.print("sorry : " + userName);

                 }

             }

         } catch (Exception e) {

             e.printStackTrace();

         } finally {

             JDBCUtil.closeAll(connection, preparedStatement, resultSet);

         }

     }

 }

2）com.jason.testweb.JDBCUtil：JDBU工具类，获取连接，和关闭资源

 package com.jason.util;

 import java.sql.Connection;

 import java.sql.DriverManager;

 import java.sql.PreparedStatement;

 import java.sql.ResultSet;

 import java.sql.SQLException;

 public class JDBCUtil {

     // 获取连接

     public static Connection getConnection() throws Exception {

         String driverClass = "com.mysql.jdbc.Driver";

         String JDBCurl = "jdbc:mysql://127.0.0.1:3306/atguigu";

         String user = "root";

         String password2 = "zhangzhen";

         Class.forName(driverClass);

         return DriverManager.getConnection(JDBCurl, user, password2);

     }

     // 关闭流

     public static void closeAll(Connection con, PreparedStatement ps,

             ResultSet rs) {

         if (rs != null) {

             try {

                 rs.close();

             } catch (SQLException e) {

                 // TODO Auto-generated catch block

                 e.printStackTrace();

             }

         }

         if (ps != null) {

             try {

                 ps.close();

             } catch (SQLException e) {

                 // TODO Auto-generated catch block

                 e.printStackTrace();

             }

         }

         if (con != null) {

             try {

                 con.close();

             } catch (SQLException e) {

                 // TODO Auto-generated catch block

                 e.printStackTrace();

             }

         }

     }

 }

3）login.html：显示页面

 <!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=UTF-8">

 <title>this is a test </title>

 </head>

 <body>

     <form action="login" method="post">

         user:<input type="text" name="username"/>

         password:<input type="password" name="password"/>

         <input type="submit" value="submit"/>

     </form>

 </body>

 </html>

4)web.xml：配置Servlet 和映射 Servlet

 <?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_3_0.xsd"

     id="WebApp_ID" version="3.0">

 <!-- 配置和映射servlet -->

     <servlet>

         <servlet-name>LoginServlet</servlet-name>

         <servlet-class>com.jason.testweb.LoginServlet</servlet-class>

     </servlet>

     <servlet-mapping>

         <servlet-name>LoginServlet</servlet-name>

         <url-pattern>/login</url-pattern> <!-- 和提表单提交的 action 一致 -->

     </servlet-mapping>

 </web-app>

3.总结分析

1）本实验只是模拟了一次完整的由 view -> control -> model

2) 本实验未使用连接池：c3p0

3）缺少模块化设计

码农公寓