JDBC development steps

1. Registration driver
 ---Tell the JVM which database driver to use
 2. Get connected
 ---Use the classes in JDBC to complete the connection to the MySQL database
 3. Get statement execution platform
 ---Get the executor object of the SQL statement through the connection object
 4. Execute sql statement
 ---Execute SQL statement to database using executor object
        Get the result after execution of the database
 5. Treatment results
---
6. Release resources
 ---Call a bunch of close();

Import the jar package, copy it to the folder, and configure the path

First, prepare some data in MySQL

CREATE TABLE users(
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(100),
PASSWORD VARCHAR(100)
);

INSERT INTO users (username,PASSWORD) VALUES ('a','1'),('b','2');

SELECT * FROM users;

-- Login query of data table
SELECT  FROM users WHERE username = 'a' AND PASSWORD = '1';

① Using SQL statement INSERT INTO; DELETE; UPDATE data in JAVA

package jdbcCaoZuoLianXi;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

import com.mysql.jdbc.Driver;

/*
 	1.Registration driven
 	---Tell the JVM which database driver to use
	2.Get connected
	---Using classes in JDBC to connect to MySQL database
	3.Get statement execution platform
	---Get the executor object of the SQL statement through the connection object
	4.Execute sql statement
	---Execute SQL statement to database using executor object
	        Get the result after execution of the database
	5.Processing result
	---
	6.Release resources
	---Call a bunch of close();
*/
public class JDBCDemo {
	public static void main(String[] args) throws ClassNotFoundException, SQLException {
		// 1. Register driver - use reflection technology to add driver class to content---
		// Using the Java.sql.DriverManager class static method registerDriver(driver driver)
		// The interface, parameter passing, and implementation class in MySQL Driver that each Driver class must implement
		// --DriverManager.registerDriver(new Driver()); / / found in the source code, this method registers driver new twice
		// Two drivers ()
		Class.forName("com.mysql.cj.jdbc.Driver");
		// Use the above statement to register the driver

		// 2. Get the static methods in the connection DriverManager class of the database
		// static Connection getConnection(String url, String user,String password);
		// The return value is the implementation class of the Connection interface, which is in the mysql driver
		// url: database address jdbc:mysql: / / IP address of the host: port number / / database name
		String url = "jdbc:mysql://localhost:3306/mybase";
		String username = "root";
		String password = "123";
		Connection con = DriverManager.getConnection(url, username, password);
		//System.out.println(con);
		
		
		//3. Get the statement execution platform, connect the object through the database, and get the executor object of the SQL statement
		//The con object calls the Statement createStatement() method to get the Statement object and send the SQL Statement to the database
		//The return value is the implementation class object of the Statement interface, which is driven by mysql
		Statement stat = con.createStatement();
		System.out.println(stat);
		
		//4. Execute sql statement
		//Execute SQL statement through executor object calling method and return the result generated by him
		//int executeUpdate(String sql) executes the SQL statement in the database, insert delete update
		//The return value int is the number of rows of successful data
		int row = stat.executeUpdate("INSERT INTO sort(sname,sprice,sdesc) VALUES (' automobile',500000,'Crazy price rise');");
		System.out.println(row);
		
		//6. Release resources
		stat.close();
		con.close();
	}
}

① Using SQL statement SELECT in JAVA

package jdbcCaoZuoLianXi;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBCDemo01 {

	public static void main(String[] args) throws ClassNotFoundException, SQLException {
		// 1. Register driver - use reflection technology to add driver class to content---

		Class.forName("com.mysql.cj.jdbc.Driver");

		// 2. Get the static methods in the connection DriverManager class of the database

		String url = "jdbc:mysql://localhost:3306/mybase";
		String username = "root";
		String password = "123";
		Connection con = DriverManager.getConnection(url, username, password);

		// 3. Get the statement execution platform, connect the object through the database, and get the executor object of the SQL statement

		Statement stat = con.createStatement();
		// SQL statement for spelling query
		String sql = " SELECT * FROM sort";

		// 4. Call the executor object method and execute the sql statement to get the result
		// ResultSet executeQuery(String sql) executes select query statement in SQL statement
		// The implementation class object of the return value ResultSet interface, which is driven by mysql
		ResultSet rs = stat.executeQuery(sql);
		// 5. Processing result set
		// ---The ResultSet interface method boolean next() returns true with result set, false without result set
		System.out.println(rs.next());

		// Get the data of each column, using the method getXXX of the ResultSet interface. It is recommended to write stringlisting
		while (rs.next()) {
			System.out.println(rs.getInt("sid") + " " + rs.getString("sname") + " " + rs.getDouble("sprice") + " "
					+ rs.getString("sdesc"));
			//Here we are going to use getObject or getString to be lazy
		}

		// 6. Release resources
		rs.close();
		stat.close();
		con.close();

	}
}

③ User login

package jdbcCaoZuoLianXi;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

public class JDBCDemo2 {
	public static void main(String[] args) throws Exception {
		Class.forName("com.mysql.cj.jdbc.Driver");
		String url = "jdbc:mysql://localhost:3306/mybase";
		String username = "root";
		String password = "123";
		Connection con = DriverManager.getConnection(url, username, password);
		Statement stat = con.createStatement();
		System.out.println("enter one user name");
		Scanner sc = new Scanner(System.in);
		String name = sc.next();
		System.out.println("Please input a password");
		String pass = sc.next();
		
		String sql = "SELECT * FROM users WHERE username = '"+name+ "' AND PASSWORD = '"+ pass+"'";
		ResultSet rs = stat.executeQuery(sql);
		while(rs.next()) {
			System.out.println(rs.getString("username") + "  "+ rs.getString("password"));
		}
		sc.close();
		rs.close();
		stat.close();
		con.close();
	}
}