I have learned the basic knowledge of MySQL today and yesterday. This is the second part, the previous part Here.
There is still a little part that hasn't been written down. At night, the online thief card can't upload the picture. It will be uploaded tomorrow morning.

5. MySQL functions

5.1 common functions

--mathematics
SELECT ABS(-55)  -- absolute value
SELECT CEILING(22.995)  -- Round up
SELECT FLOOR(9.8) -- Round down
SELECT RAND () --Random number (0-1)
SELECT SIGN(99) -- Returns the symbol of the parameter

--string

SELECT CHAR_LENGTH('Honey snow ice city sweet honey') --String length
SELECT CONCAT('l','b','w') -- Splice string
SELECT INSERT ('Michelle binchen',3,2,'Ice City') -- Replace a length from a position
SELECT LOWER('DDWAFGAWEFA') -- Convert to lowercase
SELECT UPPER('sadasdasd')-- Convert to uppercase
SELECT INSTR ('awdawdasnwdaw','n') --Returns the index of the first occurrence string
SELECT REPLACE('Honey snow ice city sweet honey','Ice City','volcano')  -- Replace the occurrence of the specified string
SELECT SUBSTR('You love me I love you',2,3) --Returns the specified string (the length of the source string interception)

-- Change Liu into Niu
SELECT REPLACE(`name`,'Liu','cattle') FROM `student`
WHERE `name` LIKE ('Liu%')

---time
SELECT CURDATE() --Get current date
SELECT NOW() -- Get current time
SELECT LOCALTIME()  --Local time
SELECT SYSDATE() --system time

SELECT YEAR(NOW())



SELECT USER() 
SELECT VERSION() --Version information

5.2 aggregate function

Function name	describe
COUNT()	Returns the total number of records that meet the Select criteria, such as select count(*) [not recommended *, inefficient]
SUM()	Returns a numeric field or expression column for statistics, and returns the sum of a column.
AVG()	Statistics are usually made for numeric fields or expression columns, and the average value of a column is returned
MAX()	Statistics can be made for numeric fields, character fields or expression columns to return the maximum value
MIN()	Statistics can be made for numeric fields, character fields or expression columns to return the smallest value.

 /*COUNT:Not empty*/
 SELECT COUNT(studentname) FROM student;
 SELECT COUNT(*) FROM student;
 SELECT COUNT(1) FROM student;  /*recommend*/
 
 -- In meaning, count(1) And count(*) Both represent queries on all data rows.
 -- count(field) The number of occurrences of this field in the table will be counted. The ignored field is null The situation. That is, the statistics field is null Records of.
 -- count(*) All columns are included, which is equivalent to the number of rows. In the statistical results, the fields included are null Records of;
 -- count(1) Use 1 to represent the code line. In the statistics results, the fields included are null Records of.
 /*
 Many people think that the execution efficiency of count(1) is higher than that of count(*), because count(*) has a full table scan, and count(1) can query a field. In fact, count(1) and count(*) scan the whole table and count the number of all records, including those that are null. Therefore, their efficiency is almost the same. The count (field) is different from the first two. It counts the number of records whose field is not null.
 
 Here are some comparisons between them:
 
 1)When the table has no primary key, count(1) is faster than count(*)
 2)When there is a primary key, the primary key is used as the calculation condition, and the count (primary key) is the most efficient;
 3)If the table has only one field, count(*) is more efficient.
 */
 
 SELECT SUM(StudentResult) AS the sum FROM result;
 SELECT AVG(StudentResult) AS average FROM result;
 SELECT MAX(StudentResult) AS Highest score FROM result;
 SELECT MIN(StudentResult) AS Lowest score FROM result;

5.3 MD5 encryption at database level (extension)

5.3.1 introduction to MD5

MD5, namely message digest algorithm 5, is used to ensure complete and consistent information transmission. It is one of the hash algorithms widely used in computers (also known as digest algorithm and hash algorithm). MD5 has been widely implemented in mainstream programming languages. The basic principle of hash algorithm is to calculate data (such as Chinese characters) into another fixed length value. The predecessor of MD5 is MD2, MD3 and MD4.

5.3.2 realize password encryption

--Create a table
 CREATE TABLE `testmd5` (
   `id` INT(4) NOT NULL,
   `name` VARCHAR(20) NOT NULL,
   `pwd` VARCHAR(50) NOT NULL,
   PRIMARY KEY (`id`)
 ) ENGINE=INNODB DEFAULT CHARSET=utf8

--insert data
 INSERT INTO testmd5 VALUES(1,'Tony','123456'),(2,'Martin','456789')

-- yes'Tony' Password for encryption
 update testmd5 set pwd = md5(pwd) where name = 'Tony';
-- Automatically encrypt when inserting data
 INSERT INTO testmd5 VALUES(4,'Peter',md5('123456'));
--Query login user information( md5 For comparison, check the encrypted password entered by the user for comparison)
  SELECT * FROM testmd5 WHERE `name`='Sam' AND pwd=MD5('123456');
 

6. Transactions

6.1 what is a transaction

• Transaction is to execute a group of SQL statements in the same batch
• If an SQL statement fails, all SQL statements in the batch will be canceled
• MySQL transaction processing only supports InnoDB and BDB data table types

6.2 ACID principle of transaction

You can read this article Blog , here is a brief explanation.

Atomic:

Either all of them succeed or all of them fail. It is impossible to complete half of them. If an error occurs during the execution of a transaction, it will be rolled back to the start of the transaction.

Consistency:

The data integrity before and after the transaction should be consistent. Take the transfer case as an example. Suppose there are five accounts, each with A balance of 100 yuan, then the total amount of the five accounts is 500 yuan. If multiple transfers occur between the five accounts at the same time, no matter how many are concurrent, for example, 5 yuan is transferred between accounts A and B, 10 yuan is transferred between accounts C and D, and 15 yuan is transferred between accounts B and E. the total amount of the five accounts should still be 500 yuan. This is protection and invariance.

Isolated:

Transaction isolation means that when multiple users access the database concurrently, the transactions opened by the database for each user cannot be disturbed by the operation data of other transactions. To prevent confusion between transaction operations, requests must be serialized or serialized so that only one request is used for the same data at the same time.

Durable:

Once a transaction is committed, it is irreversible, stored in the database, and will not be rolled back

Some problems caused by isolation

Dirty read:

It means that one transaction reads uncommitted data from another transaction.

Non repeatable:

When a row of data in a table is read in a transaction, the results will be different for multiple times. (this is not always wrong. Knowledge is wrong on some occasions.).

Unreal reading:

It refers to that data inserted by other transactions is read in one transaction, resulting in inconsistent reading.

Execute transaction

-- mysql Automatic transaction submission is enabled by default
SET autocommit=0 --close
SET autocommit=1 --open

-- Manual transaction processing
SET autocommit=0 --Turn off auto submit

-- Transaction start
START TRANSACTION --Mark the start of a transaction from the following sql All within the same transaction

INSERT xx
InSERT xx
--Submission; Persistence (success!)
COMMIT
--Rollback; Return to the original (failed)
ROLLBACK

--End of transaction 
SET autocommit=1 --Turn on auto submit

--understand
SAVEPOINT Save point name --Set a transaction savepoint
ROLLBACK TO SAVEPOINT --Rollback to savepoint
RELEASE SAVEPOINT --Undo savepoint

Maybe some people still don't know why it is so troublesome to execute a transaction. Isn't it automatically committed by default? Why close and then open?

Analysis: a transaction contains more than one statement. These statements should be completed or not according to atomicity. If Auto commit is not turned off, each statement is equivalent to a transaction, which violates the previous atomicity. Here is an example

Simulation scenario (bank transfer)

CREATE DATABASE `shop`CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `shop`;
 
CREATE TABLE `account` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(32) NOT NULL,
  `cash` DECIMAL(9,2) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
 
INSERT INTO account (`name`,`cash`)
VALUES('A',2000.00),('B',10000.00)
 
-- Transfer realization
SET autocommit = 0; -- Turn off auto submit
START TRANSACTION;  -- Start a transaction,Mark the starting point of the transaction
UPDATE account SET cash=cash-500 WHERE `name`='A';
UPDATE account SET cash=cash+500 WHERE `name`='B';
COMMIT; -- Commit transaction
# rollback;
SET autocommit = 1; -- Resume auto commit

7. Index

MySQL's official definition of an index: an index is a data structure that helps MySQL obtain data efficiently

By extracting the sentence trunk, we can get the essence of index: index is a data structure.

7.1 function of index

• Improve query speed
• Ensure data uniqueness
• The connection between tables can be accelerated to realize the referential integrity between tables
• When using grouping and sorting clauses for data retrieval, the time for grouping and sorting can be significantly reduced
• Full text search field for search optimization

7.2 classification of index

• PRIMARY KEY index PRIMARY KEY

  • Unique identifier. The primary key cannot be repeated. Only one column can be used as the primary key
  • Ensure the uniqueness of data records

• UNIQUE KEY

  • Avoid duplicate values in a data column in the same table. Unique indexes can be duplicated, and multiple columns can be identified as unique indexes

• General index (KEY/INDEX)

  • By default, the index or key keyword is used to set
  • Too many regular indexes should not be added, which will affect the data insertion, deletion and modification operations

• Full text index (FULLTEXT)

  • Available only under a specific database engine to quickly locate data
  • Quick location data
  • Suitable for large datasets

Basic syntax:

-- Use of indexes
-- 1. Add indexes to fields when creating tables
-- 2. After creation, increase the index

-- Show all index information
SHOW INDEX FROM student

--Add a full-text index (index name) column name
ALTER TABLE school.student ADD FULLTEXT `studentName` (`studentName`) ;

-- EXPLAIN  analysis sql Implementation

EXPLAIN SELECT *FROM student;-- Non full text index

EXPLAIN SELECT * FROM student WHERE MATCH (studentName) AGAINST ('Liu');  --Full text index

7.3 test index

To test the function of the index, we added 100w pieces of data in batches

Create table first

CREATE TABLE `app_user` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT '' COMMENT 'User nickname',
  `email` varchar(50) NOT NULL COMMENT 'User mailbox',
  `phone` varchar(20) DEFAULT '' COMMENT 'cell-phone number',
  `gender` tinyint(4) unsigned DEFAULT '0' COMMENT 'Gender (0:Male; 1: Female)',
  `password` varchar(100) NOT NULL COMMENT 'password',
  `age` tinyint(4) DEFAULT '0' COMMENT 'Age',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='app User table'

Insert data:

DROP FUNCTION IF EXISTS mock_data;
DELIMITER $$
CREATE FUNCTION mock_data()
RETURNS INT
BEGIN
  DECLARE num INT DEFAULT 1000000;
  DECLARE i INT DEFAULT 0;
  WHILE i < num DO
   INSERT INTO app_user(`name`, `email`, `phone`, `gender`, `password`, `age`)
    VALUES(CONCAT('user', i), '666666666@qq.com', CONCAT('18', FLOOR(RAND()*(999999999-100000000)+100000000)),FLOOR(RAND()*2),UUID(), FLOOR(RAND()*100));
   SET i = i + 1;
  END WHILE;
  RETURN i;
END;

-- Query user 9999 (no index) 
SELECT * FROM app_user WHERE name = 'User 9999';

-- Analyze this sql sentence
EXPLAIN SELECT * FROM app_user WHERE `name` ='User 9999';

[the external link image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-hlufq3jl-1625585941006) (c:\users\zhang\desktop\nipaste\2021-07-06\17-17-45.png)]

It can be seen that it took us more than one second to query 99w of data [the external link image transfer failed, and the source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-16ayzfh3-1625585941009) (c:\users\zhang\desktop\ nipaste\2021-07-06\17-22-15.png)]

CREATE INDEX  id_app_user_name ON app_user(`name`);

After we create the index and execute the query statement, we can see that we only used 0.01 seconds and directly located the data.

[the external link image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-fibomnhi-1625585941012) (c:\users\zhang\desktop\nipaste\2021-07-06\17-28-08.png)] [the external link image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-fct7szwk-1625585941014) (c:\users\zhang\desktop\nipaste\2021-07-06\17-26-34.png)]

7.4 indexing principle

• The more indexes, the better
• Do not add indexes to frequently changing data
• Tables with small amounts of data do not need to be indexed
• The index is usually added to the fields commonly used for query

You can read this article article , which is particularly good for the underlying principle of indexing.

8. Rights management and backup

8.1 user management

SQL yog visual management

[external link image transfer failed. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-ln2lakxe-1625585941015) (c:\users\zhang\desktop\nipaste\2021-07-06\17-52-38.png)]

We can add a new user in this window, give it permission, or delete a user.

SQL command

Essence: for mysql Add, delete and modify the user table

-- Create user
CREATE USER Tony IDENTIFIED BY '123456'

-- Change password (current account)
SET PASSWORD =PASSWORD('123456')

-- Change password (specify account)
SET PASSWORD FOR Tony =PASSWORD('123456')

-- rename
RENAME USER Tony To Sam

-- User authorization grants all permissions except those granted to others
GRANT ALL PRIVLEGES ON *.* TO Tony

-- View permissions
SHOW GRANTS FOR Tony
SHOW GRANTS FOR root@localhost

-- Revoke permissions
REOMVE ALL PRIVILEGES ON *.* FROM Tony

-- delete user
DROP USER Tony

8.2 database backup

Why backup:

• Ensure that important data is not lost
• Data transfer

MySQL database backup method

• Copy physical file data
• Export manually on the SQLyog visualizer. Right click the database or table you want to export
• Use the command line to export mysqldump [the external chain image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-1ay6gh4c-1625585941016) (c:\users\zhang\desktop\nipaste\2021-07-06\18-13-31.png)]

cmd export:

#cmd mysqldump -h host -u user name -p password database > physical location / file name

mysqldump -hlocalhost -uroot -p123456 school student1 >D:/file/a.sql
#The name you want to save and the file suffix must be added at the end

cmd import:

#Import switch to the specified database source backup file when logging in
source d:/a.sql

#Without login 
mysql -u user name -p Password library name< Backup files

9. Standardize database design

9.1 why design is required

When the database is complex, we need to design

Bad database:

• Data redundancy, waste of space
• It is troublesome to insert and delete databases. [shielding the use of physical foreign keys]
• Poor program performance

Good database:

• Save memory space
• Ensure database integrity
• Convenient for us to develop the system

Database design in software development

• Analysis requirements: analyze business
• Outline design

Steps to design a database: (personal blog)

• Collect information and analyze requirements
  • User table (user login and logout, user's personal information, blogging, creating categories)
  • Classification table (classification of articles, who created it)
  • Article table (information about articles)
  • Comment form
  • Friends list (friendship connection information)
  • User defined table (system information, a keyword, or some main fields)
  • Talk about the table (express mood... id... content... create_time)
• Identify entities (implement requirements to each field)
• Identify relationships between entities
  • Blog user – >user
  • Create category: user – >category
  • Attention: user – >user
  • Friend chain: links
  • Comment: user user blog

9.2 three paradigms

Why do we need data normalization?

• Duplicate information
• Update exception
• Insert exception
  • Unable to display normal information
• Delete exception
  • Missing valid information

Three paradigms

First paradigm

Atomicity: ensure that each column cannot be subdivided

Second norm normal form

Premise: on the premise of meeting the first normal form, each table represents only one information

The third paradigm

Premise: meet the first and second paradigms

Ensure that each column of data in the data table is directly related to the primary key, but not related to opinions

If we all operate according to the three paradigms, a table may be split into more than a dozen tables, and the sub performance will be very poor

Normative and performance issues

No more than three tables can be associated with query

• Considering the needs and objectives of commercialization, the performance of (cost, user experience) databases is more important
• When standardizing performance, it is necessary to properly consider the normalization
• Intentionally add some redundant fields to some tables (from multi table query to single table query)
• Intentionally add some calculation columns (query from big data to small data: index)

10. JDBC (key)

10.1. Database driver

Drivers: sound card, graphics card, database

Our program cannot directly operate the database, so we need a driver. The application program operates on the driver, and the driver operates on the database.

10.2. JDBC[external link image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-isojgrc8-1625585941017) (c:\users\zhang\desktop\snipaste\2021-07-06\19-52-48.png)]

In order to simplify the operation of developers, SUN company provides a specification (Java operation database), commonly known as JDBC. The implementation of these specifications is done by specific manufacturers

For developers, we only need to master the JDBC interface!

10.3 JDBC program

10.3.1 create a database

CREATE DATABASE `jdbcstudy`

CREATE TABLE  users
(
`id` INT (4) PRIMARY KEY,
`name` VARCHAR(40),
`password` VARCHAR(40),
`birthday` DATE
);

INSERT INTO `users` (`id`,`name`,`password`,`birthday`) 
VALUES ('1','Zhang San','123456','1999-02-02'),('2','Lisi','1234556','1999-08-02'),('3','Wang Wu','1288556','1998-02-15')
,('4','Zhao Liu','752156','1999-12-12')

10.3.2 import drive

Here we download the corresponding version of the jar package from MAVEN: website . [external link image transfer failed. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-ndrmhsb8-1625585941017) (c:\users\zhang\desktop\nipaste\2021-07-06\20-33-10.png)]

Create a project on the ide, create a lib directory, and paste the downloaded jar package into it. At this time, we haven't imported it.

[the external link image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-ufgjdvee-1625585941018) (c:\users\zhang\desktop\nipaste\2021-07-06\20-35-26.png)]

Click Add as library. At this time, we will import successfully. You can click to open this package.

10.3.3 test code

package com.yubao;

import com.mysql.cj.protocol.Resultset;

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

//My first JDBC program
public class JdbcFirst {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1. if the loading driver is a new version of 8.0, there is no old version of cj
        Class.forName("com.mysql.cj.jdbc.Driver");// Fixed writing, load drive

        //2. the new version of the user information needs to add a time zone, which is the last one. If an error is reported, turn off SSL. I turned it off because of an error
        String url = "jdbc:mysql://localhost:3306/jdbcstudy?userUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=UTC";
        String username = "root";
        String password = "228934";

        //3. the Connection is successful. The database object Connection represents the database
        Connection connection= DriverManager.getConnection(url,username,password);

        //4. execute SQL object
        Statement statement=connection.createStatement();

        //5. the object executing SQL executes SQL. There may be a return object. View the return result
        String sql="SELECT* FROM users";

        ResultSet resultSet =statement.executeQuery(sql);//Return result set

        while(resultSet.next()){
            System.out.println("id"+resultSet.getObject("id"));
            System.out.println("name"+resultSet.getObject("name"));
            System.out.println("password"+resultSet.getObject("password"));
            System.out.println("birthday"+resultSet.getObject("birthday"));
        }
        //6. release the connection
        resultSet.close();;
        statement.close();
        connection.close();


    }
}

Step summary:

1. Load driver
2. Connect to database
3. Get the object Statement that executes sql
4. Get the returned value
5. Release connection

10.3.4 detailed explanation

1. DriverManager

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

2. URL

String  url ="jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=UTC"
    //mysql default 3306
    //Protocol: / / host address: port number / database name? Parameter 1& parameter 2& parameter 3

3. Statement is the object executing SQL PrepareStatement is the SQL object executing SQL

String sql="sekect * from users";
statement.executeQuery();//query
statement.execute();//Execute any SQL
statement.executeUpdate();//Update, insert, delete

4. ResultSet query result set: encapsulates all query results

// Gets the specified data type
resultset.getint();
resultset.getobject();
resultset.getDate();
...
    

//Pointer
resultset.beforFirst();//Move to first
resultset.afterLast();//Move to the back
resultset.absolute(row)//Move to specified class
resultset.next();//Move to next data

5. release resources

resultSet.close();
statement.close();
connection.close();

10.4 statement object

The statement object in Jdbc is used to send sql statements to the database. To complete the addition, deletion, modification and query of the database, you only need to send the statements of addition, deletion, modification and query to the database through this object

The executeUpdate method of the Statement object is used to send sql statements of addition, deletion and modification to the database. After executeUpdate is executed, an integer (that is, the number of rows affected) will be returned.

Statement. The executeQuery method is used to send query statements to the database, and the executeQuery method returns the ResultSet object representing the query results

10.4.1CRUD operation

configuration file

driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=flase&serverTimezone=UTC
username=root
password=123456
##It is best to write it in the src directory file. Pay attention to where it is written

Extraction tool class

package com.yubao.lesson02.utils;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class JdbcUtils {

    private static String driver=null;
    private static String url=null;
    private static String username=null;
    private static String password=null;
    static{
        try{
		    InputStream in=JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
            Properties properties=new Properties();
            properties.load(in);
            driver =properties.getProperty("driver");
            url =properties.getProperty("url");
            username =properties.getProperty("username");
            password =properties.getProperty("password");
            Class.forName(driver);
            //The driver is loaded only once

        }catch(IOException e){
            e.printStackTrace();
        }catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
    //Get connection
    public static Connection getConnection() throws SQLException
    {
        return DriverManager.getConnection(url,username,password);
    }

    //Release connection resources

    public static void release(Connection conn,Statement st, ResultSet rs) throws SQLException
    {
        if(rs!=null)
        {
            rs.close();
        }
        if(st!=null)
        {
            st.close();
        }
        if(conn!=null)
        {
            conn.close();
        }

    }

}

Add, delete and modify code

All additions, deletions and changes are made using executeUpdate, so the usage of Insert is demonstrated here

package com.yubao.lesson02;

import com.yubao.lesson02.utils.JdbcUtils;

import javax.swing.plaf.nimbus.State;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TestInsert {
    public static void main(String[] args) throws SQLException {
        Connection conn=null;
        Statement st=null;
        ResultSet rs=null;

        try {
            conn=JdbcUtils.getConnection();
            st=conn.createStatement();
            String sql="INSERT INTO users (id,`name`,`password`,`birthday`) VALUE (5,'Tony','12121','1988-2-5')" ;  //To add, delete or modify, you only need to change the code here

            int i=st.executeUpdate(sql);
            if(i>0){
                System.out.println("Insert successful");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }

}

Check code

package com.yubao.lesson02;

import com.yubao.lesson02.utils.JdbcUtils;

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

public class TestQuery {
    public static void main(String[] args) throws SQLException {
        Connection conn=null;
        Statement st=null;
        ResultSet rs=null;

        try {
            conn= JdbcUtils.getConnection();
            st=conn.createStatement();

            String sql="SELECT * FROM users where id=1";

            rs=st.executeQuery(sql);
            while (rs.next()){
                System.out.println(rs.getObject("name"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally{
            JdbcUtils.release(conn,st,rs);
        }
    }
}

10.4.2 SQL injection

SQL has a vulnerability and will be attacked. Through SQL statements, you can log in without an account, or even tamper with the database.

There is no detailed explanation here, just as an understanding

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

public class sql {

	public static void main(String[] args) {
		sql.login(" 'or '1=1", "'or '1=1");
	}
	public static void login(String username,String password)
	{
		Connection conn=null;
		Statement sta=null;
		ResultSet re=null;
		try {
			conn= jdbcutils.getConnection();
			sta=conn.createStatement();
			String sql="SELECT * FROM `users`  WHERE `name`='"+username+"' AND `password`='"+password+"'";
		    re=sta.executeQuery(sql);
		    while(re.next())
		    {
		    	System.out.print(re.getString("name")+"  ");
		    	System.out.println(re.getString("password"));
		    }
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
	}
}

How does the above code implement SQL injection?

Joining username and password together, we get

’’or '1=1' this is a constant statement, so our database can directly obtain all information through this. [external link image transfer failed. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-15acwv9w-1625585941018) (c:\users\zhang\desktop\nipaste\2021-07-06\23-26-55.png)]