JDBC connection database

1, jdbc connection to mysql above 8.0

After the mysql version above 8.0 is updated, the corresponding jdbc connection has also changed.

First of all, we need to configure the mysql environment. It is recommended that you go to the Internet to find a tutorial and follow it. I won't talk about the installation and configuration of mysql here.

Then, we need to download the connector based on java platform. Here is a portal for you to download. mysql::::download

After we download the connector, we will configure it in our own environment. Here is the configuration process of sts and idea compilation environment.

(1)sts:

Step 1: find your own project, right-click to find Bulid Path and enter it

Step 2:

Click Add External JARS on the Libraries interface to load additional war packages

 

Step 3: select the jar package we extracted from the zip, add it, and you're done.

If you find that the jar package still doesn't work, try putting the jar package into the lib folder under the project.

 (2)idea:

Step 1: click File to enter the following interface

 

Step 2: click the plus sign, add your own dependencies, and select the jar package we extracted from the zip

Step 3: put it in the lib folder

 

When the service starts, it will look for dependencies in it.

After configuring the environment, let's start.

(1) First, we need to load the driver

 Class.forName("com.mysql.cj.jdbc.Driver");  //This is the driver loading after version 8.0
 
 Class.forName("com.mysql.jdbc.Driver");  //This is a driver load before version 8.0

We load different drivers according to different versions.

(2) Then we need to prepare to connect to the database

It is recommended that you encapsulate it into a class, and then call the functions inside by importing the package to avoid writing the same code repeatedly.

package mySqlCon;
 
import java.sql.*;
 
public class mySqlJdbcCon {
    private  static Connection con=null;
    public static  Connection getCon(){
        try{
            Class.forName("com.mysql.cj.jdbc.Driver");//Load driver
            String username="root";
            String password="root";
            //This is the way to write URLs after version 8.0
            String   url="jdbc:mysql://localhost:3306/datebaseName?"+
            "useSSL=false&serverTimezone=GMT";
    
            //url="jdbc:mysql://localhost:3306/datebaseName This is the way to write URLs before version 8.0
 
            con=DriverManager.getConnection(url,username,password);//Connect database
        }
        catch (Exception e) {
            // TODO: handle exception
        }
        return con;//Return connection
    }
}

So now we have successfully connected to the database.

2, Query the login table in the database and compare it with the account password from the form to verify the login

package majie;
 
import javax.servlet.ServletException;
import java.io.PrintWriter;
//import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
 
import  mySqlCon.mySqlJdbcCon;
 
//@WebServlet("/loginServlet")
public class loginServlet extends HttpServlet {
	
	public loginServlet() {
		super();
	}
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException{
        request.setCharacterEncoding("UTF-8");
        response.setContentType("text/html;charset=UTF-8");//Control format output
        int judge_is_ture=0;
        PrintWriter out = response.getWriter();
        Statement sql=null;
        ResultSet rs=null;
        Connection con=null;
        //Get the account password from the form
        String username_html=request.getParameter("user");
        String password_html=request.getParameter("pass");
        //Start looking for the corresponding account and password in the database
		try{
			con=mySqlJdbcCon.getCon();//Connect database
			sql=con.createStatement();
			rs=sql.executeQuery("SELECT * FROM login");
			while(rs.next()) {
				String username=rs.getString(1);
				String pass=rs.getString(2);
				if(username.equals(username_html)&&pass.equals(password_html)) {
					judge_is_ture++;
					break;
				}
//Flag observation
				System.out.printf("%s %s\n", username,pass);
			}
			rs.close();
			sql.close();
			con.close();
		}catch (SQLException e) {
			System.out.println(e);
			// TODO: handle exception
		}catch(Exception e) {
           // Process class Forname error
           e.printStackTrace();
	    }finally{
	           // Finally, there is the block used to close the resource
	           try{
	               if(sql!=null)
	              sql.close();
	           }catch(SQLException se2){
	           }
	           try{
	               if(con!=null)
	               con.close();
	           }catch(SQLException se){
	               se.printStackTrace();
	           }
	       }
		//Successfully find the matching student number and password from the database, and enter the system through verification
		if(judge_is_ture==1) {
			response.sendRedirect(request.getContextPath()+"/index.jsp");
		}
		//The corresponding student number and password are not found in the database. If the verification fails, return to the login page
		else {
			response.sendRedirect(request.getContextPath()+"/login.jsp");
		}
	}
 
    @Override
    protected void doPost(HttpServletRequest request,HttpServletResponse response) throws  ServletException,IOException{
    	doGet(request, response);
    }
    public  void destroy() {
		
	}
}

We compare the data transmitted from the form with the data in the database. If there is the same, we jump out of the data query and start page Jump. We jump to the page after successful login. If we haven't found the same account and password, we will guide the user to return to the login page again.

(1) Successfully logged in

 

 

 

(2) Login failed

 

 ​​​​​​​

 

(3) at the same time, we observed the access to the specific data in the database by inserting the flag. We can see that the data in the database is actually read and compared

 

Tags: Database MySQL

Posted by mjl4975 on Fri, 20 May 2022 03:06:37 +0300