android-web-Mysql连接-------Web服务器连接Mysql数据库测试（实现登录查询功能）

超、凢脫俗 2023-07-14 06:00 1阅读 0赞

## Web服务器连接Mysql登录查询功能小测试 ##

### 需要的工具 ###

Mysql、Eclipse ide、tomcat（工程中需要的jar包：mysql-connector-java-8.0.19.jar）

#### 因为我的Mysql是8.0.19版本的所以下载是这个，如果是5开头的版本，最好是下载5版本开头的jar，博主就因为这个的错误失败很多次，其中网上很多的资源使用还会有错误。具体添加方法见博客[link][] ####

需要jar资源的可以私我shifeng@stu.cdu.edu.cn | 1019706420@qq.com

### 一、数据库中的配置：见下图。 ###

数据库名称mydb 表：user 表中内容（username，password）  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NmOTA5MA_size_16_color_FFFFFF_t_70]

### 二、数据库配置好后，开始写程序 ###

下面是建立工程的目录  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NmOTA5MA_size_16_color_FFFFFF_t_70 1]  
多余的我就不说了，直接上代码

### MyServlet.java中的内容 ###

package cdu.nls.login;
    
    import java.io.IOException;
    import java.io.PrintWriter;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.SQLException;
    
    import javax.servlet.ServletException;
    import javax.servlet.annotation.WebServlet;
    import javax.servlet.http.HttpServlet;
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    
    
    /**
     * Servlet implementation class MyServlet
     */
    @WebServlet("/MyServlet")
    public class MyServlet extends HttpServlet {
        
    	private static final long serialVersionUID = 1L;
    	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        
    	
    		
    		 String ID = request.getParameter("ID"); 
    	     String PW= request.getParameter("PW");
    	 
    	     boolean type=false;
    	     response.setContentType("text/html; charset=UTF-8");
    	     PrintWriter out = response.getWriter();    
    	     try {
        
    	    	 Class.forName("com.mysql.jdbc.Driver");//加载数据库驱动，注册到管理器
    	    	 String url = "jdbc:mysql://localhost:3306/mydb?serverTimezone= UTC&characterEncoding=utf-8";
    	    	 Connection con = DriverManager.getConnection(url,ID,PW);
    	    	 if(con != null) {
        
    	    		 out.println("mysql connection successful!");
    	    		 con.close();
    	    		 request.getRequestDispatcher("user_login.jsp").forward(request,response);
    	    		 System.out.print("login_mysql_successful\n");
    	    		 out.print("haha");
    	    		
    	    	 }
    	    	 else {
        
    	    		 out.println("mysql connection failture!");
    	    	 }
    	    	 
    	     }   catch(ClassNotFoundException e) {
        
    	    	 e.printStackTrace();
    	     }catch (SQLException e) {
        
    	    	 e.printStackTrace();
    	     }
    		              out.println(type);
    		              
    		              out.flush();
    		              out.close();
    		              
    	}
    
    	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        
    		          		doGet(request, response);
    	}
    
    }

### Myservlet1.java中的内容：此部分功能是连接Mysql并查询内容 ###

package cdu.nls.login;
    
    import java.io.IOException;
    import java.io.PrintWriter;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import javax.servlet.ServletException;
    import javax.servlet.annotation.WebServlet;
    import javax.servlet.http.HttpServlet;
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    import java.sql.Connection;
    
    @WebServlet("/MyServlet1")
    public class MyServlet1 extends HttpServlet {
        
    	private static final long serialVersionUID = 1L;
         
    
    	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        
    	  String user2 = request.getParameter("username"); 
    	  String pass2= request.getParameter("password");
    	  boolean type= false;
    	  PrintWriter out = response.getWriter(); 
    	  java.sql.Statement stmt = null;//设置stmt全局变量
    	  System.out.print("获取jsp界面的：");
    	  System.out.print("  "+user2);
    	  System.out.print("  ");
    	  System.out.print("length:"+user2.length());
    	  System.out.print("  "+pass2);
    	  System.out.print("  ");
    	  System.out.print("length:"+pass2.length());
    	  
    	  try
          {
        
    		 Class.forName("com.mysql.jdbc.Driver");//加载数据库驱动，注册到管理器
    	     String url = "jdbc:mysql://localhost:3306/mydb?serverTimezone= UTC&characterEncoding=utf-8";
    	     Connection con = DriverManager.getConnection(url,"root","123456");//此处
    	     stmt = con.createStatement();//实例化Statement对象，用stmt接收
             String sql;//定义一个string类型的数据变量sql
             sql = "SELECT username, password FROM user";//用sql接收数据库语句
             ResultSet rs = stmt.executeQuery(sql); //执行sql
         
             // 展开结果集数据库
             while(rs.next()){
        
                 // 通过字段检索
                 String use1  = rs.getString("username");
                 String pass1 = rs.getString("password");
          
                 System.out.print("\nmysql中获取的内容：\nmysql_username: " + use1);
                 System.out.print("  ");System.out.print("length:"+use1.length());
                 System.out.print(" mysql_password: " + pass1);System.out.print("  ");
                 System.out.print("length:"+pass1.length());
                if(use1.contentEquals(user2) &&pass1.contentEquals(pass2))
                 {
         
                	 type=true;
                	 System.out.print("\nlogin mysql successful!");
                	 out.print("\nlogin mysql successful!");
                 }
      
             }
             // 完成后关闭
             rs.close();
             stmt.close();
             con.close();
    	}
            catch(Exception ex)
           {
        
                ex.printStackTrace();
           }
            finally
            {
        
            //	con.Close();
                out.print(type);
                out.flush();
                out.close();
            }
    	
    	}
    
    	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        
    
    		doGet(request, response);
    	}
    
    }

### index.jsp(首页：登录Mysql的用户名密码，成功跳转到user\_login.jsp) ###

<%@ page language="java" contentType="text/html; charset=UTF-8"
        pageEncoding="UTF-8"%>
      <%@ page import = "java.sql.*" %>
    <!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=ISO-8859-1">
    <title>Insert title here</title>
    </head>
    <body>
    <h1>hello（此页登录您的数据库用户名和密码）</h1>
    <form id="from1" action="MyServlet" method="post">
    <table>
    <tr><td>用户名</td><td><input type="text"  name="ID"></td></tr>
    <tr><td>密码</td><td><input type="password"  name="PW"></td></tr>
    
    <tr><td colspan = "3" align = "center"><input type = "submit" value = "login_mysql" id = "btnde1" onclick="del(id)"/></td></tr>
    
    
    </table>
    </form>
    
    </body>
    </html>

### user\_login.jsp(登录数据表中存放的内容，功能就是查询数据库信息) ###

<%@ page language="java" contentType="text/html; charset=UTF-8"
        pageEncoding="UTF-8"%>
    <!DOCTYPE html>
    <html>
    <head>
    <meta charset="UTF-8">
    <title>Insert title here</title>
    </head>
    <body>
    
    <td colspan = "3" align = "center">
    <h1>user login（此页是您数据库中数据表中存的内容）</h1></td>
    <form id="from1" action="MyServlet1" method="post">
    <table>
    <tr><td>username</td><td><input type="text"  name="username"></td></tr>
    <tr><td>password</td><td><input type="password"  name="password"></td></tr>
    
    <tr><td colspan = "3" align = "center"><input type = "submit" value = "login" id = "btnde2" onclick="del(id)"/></td></tr>
    
    </table>
    </form>
    </body>
    </html>

### 运行成功 ###

#### 1.index.jsp(输入您的数据库用户名密码) ####

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NmOTA5MA_size_16_color_FFFFFF_t_70 2]

#### 2.成功后跳转界面user\_login.jsp(现在输入user表中的存放内容) ####

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NmOTA5MA_size_16_color_FFFFFF_t_70 3]

#### 3.成功返回信息（下面打印你输入的信息及mysql中查询的信息） ####

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NmOTA5MA_size_16_color_FFFFFF_t_70 4]

### 就此大功告成，前前后后进了一个又一个的坑，重复进相同的大坑，后续的完善代码会陆续更新（增删查改，界面设计等问题） ###

### 源代码地址link ###

[link]: https://blog.csdn.net/sf9090/article/details/104847065
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NmOTA5MA_size_16_color_FFFFFF_t_70]: /images/20230529/9895672b9da24c4db1138c9921b3d999.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NmOTA5MA_size_16_color_FFFFFF_t_70 1]: /images/20230529/3da83ee5852f43b185c17595ceaf3096.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NmOTA5MA_size_16_color_FFFFFF_t_70 2]: /images/20230529/b6bd68e96d4f44c4b027ae6b958ea2bb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NmOTA5MA_size_16_color_FFFFFF_t_70 3]: /images/20230529/39e840ca896648dcb1b55237d695f2cd.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NmOTA5MA_size_16_color_FFFFFF_t_70 4]: /images/20230529/e687ed88f37e44fdb748053706803427.png