MySQL in Java (No.25)

1. General software development classification

1.1 reception

Also known as "front end", it is generally a page and mainly displays data.

1.2. Backstage

Also known as "back-end", it is generally a connection point, a connection front end (control view jump or transfer front-end data) and a connection database (JDBC).

1.3. Database

Generally, data is stored in the background (such as txt, excel, word, etc.).

1.4 matters needing attention

• 1.4.1 junior programmer: he can only write business code and CRUD operations of database (i.e., add [Create], Retrieve [Retrieve], Update [Update] and Delete [Delete]).
• 1.4.2 intermediate programmers: on the basis of junior programmers, they can also operate systems, data structures and algorithms.
• 1.4.3 senior programmers: on the basis of intermediate programmers, they also have discrete mathematics, digital circuits, architecture, compilation principle and practical experience.

2. Database (DataBase)

Data warehouse is a kind of software that can store and manage data. It is generally divided into two types (relational database and non relational database).

2.1. Relational database

Generally, row and column data is stored through the relationship between tables and rows and columns.

• 2.1.1,MySQL.
• 2.1.2,Oracle.
• 2.1.3,SQL Server.
• 2.1.4,DB2.
• 2.1.5,SQLite.
• ....

2.2. Non relational database (Not Only SQL)

Generally, object data is stored through the attributes of the object itself.

• 2.2.1,Redis.
• 2.2.2,MongDB.
• ....

3. MySQL database management system

MySQL is a kind of relational database management system software.

MySQL is developed by MySQL AB company in Sweden and is now a product of Oracle.

The SQL language used by MySQL is the most commonly used standardized language for accessing databases. And because of its small size, fast speed and low total cost of ownership, especially the characteristics of open source, MySQL is generally selected as the website database for the development of small and medium-sized or large websites..

4. MySQL 5.7 non installation version configuration

4.1 software download.

Official website download address( https://dev.mysql.com/downloads/mysql/5.7.html).

4.2 software configuration.

• 4.2.1 unzip the downloaded software package and put the unzipped file under the custom environment directory path (e.g. "E:\Environment\Java\MySQL\mysql-5.7.35").

• 4.2.2 the system environment variables of the configuration software are shown in the figure below.

  

• 4.2.3 create a new ini configuration file (its name is "my.ini", i.e. "E:\Environment\Java\MySQL\mysql-5.7.35\my.ini") under the custom environment directory path (such as "E:\Environment\Java\MySQL\mysql-5.7.35\my.ini"), and its configuration contents are as follows.

  [mysql]
  # Set the default character set of mysql client
  # default-character-set=utf8
  [mysqld]
  skip-grant-tables
  # Set 3306 port
  port = 3306
  # Set mysql installation directory
  basedir=E:\Environment\Java\MySQL\mysql-5.7.35\
  # Set the storage directory of mysql database data
  datadir=E:\Environment\Java\MySQL\mysql-5.7.35\data\
  # Maximum connections allowed
  # max_connections=20
  # The character set used by the server defaults to the 8-bit encoded latin1 character set
  # character-set-server=utf8
  # The default storage engine that will be used when creating new tables
  # default-storage-engine=INNODB
  

• 4.2.4 start the CMD command window in administrator mode, and then enter the CMD command (mysqld install) to install MySQL service, as shown in the following figure.

  

• 4.2.5 input CMD command (mysqld -- initialize execute -- user = MySQL) to initialize MySQL database file, as shown in the following figure.

  

• 4.2.6 enter CMD command (net start mysql) to start MySQL service, as shown in the figure below.

  

• 4.2.7. Enter CMD command (mysql -uroot -p) and log in to MySQL for the first time, as shown in the figure below.

  

• 4.2.8. Enter CMD command 1(use mysql;) and CMD Command 2(update user set authentication_string=password("123456") where user = "root") 😉, CMD command 3(flush privileges;) to modify the MySQL login password, as shown in the following figure.

  

• 4.2.9 modify the ini configuration file (E:\Environment\Java\MySQL\mysql-5.7.35\my.ini), and its contents are as follows.

  [mysql]
  # Set the default character set of mysql client
  # default-character-set=utf8
  [mysqld]
  # Commenting out "skip grant tables" means that the password must be verified when logging in to MySQL, otherwise the password will not be verified when logging in to MySQL
  # skip-grant-tables
  # Set 3306 port
  port = 3306
  # Set mysql installation directory
  basedir=E:\Environment\Java\MySQL\mysql-5.7.35\
  # Set the storage directory of mysql database data
  datadir=E:\Environment\Java\MySQL\mysql-5.7.35\data\
  # Maximum connections allowed
  # max_connections=20
  # The character set used by the server defaults to the 8-bit encoded latin1 character set
  # character-set-server=utf8
  # The default storage engine that will be used when creating new tables
  # default-storage-engine=INNODB
  

• 4.2.10. Enter CMD command (exit) to exit MySQL login, as shown in the figure below.

  

• 4.2.11. Enter CMD command (net stop mysql) to stop MySQL service, as shown in the figure below.

  

• 4.2.12. Enter CMD command (net start mysql) and restart MySQL service, as shown in the figure below.

  

• 4.2.13. Enter the CMD command (mysql -uroot -p123456) and log in to MySQL again (the password modification has taken effect), as shown in the figure below.

  

5. SQLyog download and installation and configuration

Reference website( http://www.xue51.com/soft/2982.html).

6. SQLyog use

Reference website( https://blog.csdn.net/su2231595742/article/details/107967198).

7. Four SQL languages

7.1 data query language (DQL)

The basic structure of data query language DQL is a query block composed of SELECT clause, FROM clause and WHERE clause: SELECT < field name table > FROM < table or view name > WHERE < query criteria >.

7.2 data manipulation language (DML)

DML has three main forms: 1. Insert; 2. Update; 3. Delete.

7.3 data definition language (DDL)

Data Definition Language DDL is used to create various objects in the database, such as tables, views, indexes, synonyms, clusters (CREATE TABLE / VIEW / INDEX / SYN / CLUSTER), etc. in addition, DDL operations are implicitly submitted and cannot be rolled back.

7.4 data control language (DCL)

Data control language DCL is used to grant or reclaim certain privileges to access the database, control the time and effect of database manipulation transactions, and monitor the database, as shown below.

• GRANT: authorization.

• 7.4.2. ROLLBACK [WORK] TO [SAVEPOINT]: ROLLBACK to a certain point. The ROLLBACK command returns the database state to the last submitted state. Its format is: SQL > ROLLBACK.

• 7.4.3, COMMIT [WORK] : commit. During the insert, delete and modify operations of the database, the transaction is completed only when it is committed to the database. Before the transaction is committed, only the person who operates the database can see what has been done, and others can see it only after the final commit is completed. There are three types of committed data: explicit commit, implicit commit and automatic commit. The following are respectively: Describe these three types.

  • 7.4.3.1 explicit submission.

    A COMMIT done directly with the COMMIT command is an explicit COMMIT. The format is: SQL > COMMIT.

  • 7.4.3.2 implicit submission.

    Indirect commit with SQL command is implicit commit. These commands are: ALTER, AUDIT, COMMENT, CONNECT, CREATE, DISCONNECT, DROP, EXIT, GRANT, NOAUDIT, QUIT, REVOKE, RENAME.

  • 7.4.3.3. Automatic submission.

    If AUTOCOMMIT is set to ON, the system will submit automatically after the insertion, modification and deletion statements are executed, which is called automatic submission. The format is: SQL > set AUTOCOMMIT ON.

8. Operation MySQL database

MySQL keyword is case insensitive, and the single line comment symbol is --, and the multi line comment symbol is / * * /.

8.1. Create database

CREATE DATABASE IF NOT EXISTS db_1;

8.2. Delete database

DROP DATABASE IF EXISTS db_1;

8.3. Using database

-- Label table or field names with backquotes
USE `test`;

8.4. View database

SHOW DATABASES;-- View all databases

9. MySQL database column type

9.1 value

name	describe	length	remarks
tinyint	Very small integer	1 byte
smallint	Smaller integer	2 bytes
mediumint	Medium size integer	3 bytes
int	Standard integer	4 bytes	Commonly used
bigint	Larger integer	8 bytes
float	Single-precision floating-point	4 bytes
double	Double precision floating point number	8 bytes	There is a problem of accuracy, which is generally not suitable for financial calculation
decimal	Floating point number in string form	16 bytes	Generally applicable to financial calculation

9.2 string

name	describe	Length size range	remarks
char	Fixed size string	0~255
varchar	Variable size string	0~65535	Commonly used
tinytext	Micro text	0~255	Binary storage
text	Text string	0~65535	Binary storage, saving large text

9.3 date and time

name	describe	remarks
date	Date format (e.g. YYYY-MM-DD)
time	Time format (e.g. HH:mm:ss)
datetime	Date time format (e.g. YYYY-MM-DD HH:mm:ss)	Commonly used
timestamp	Timestamp (i.e. the number of milliseconds from January 1, 1970 to the present, unified worldwide)	More commonly used
year	particular year

9.4,null

That is, null value, or no value, or unknown, and do not use null for operation, and the result can only be null.

10. MySQL database field properties

10.1,unsigned

That is, an unsigned integer cannot be negative.

10.2,zerofill

That is, for the number of digits with insufficient data, use 0 to fill < if the field is int(2), if you enter the value 1, the final value will be automatically filled as 01 >.

10.3 self increasing

That is, it is usually used to design a unique primary key, which must be an integer, and the value generated each time will be automatically accumulated.

10.4 non empty

That is, empty data is not allowed.

10.5 default

That is, the data defaults.

10.6 precautions

Generally, the following five fields exist in each table to indicate the meaning of a record, as shown below:
1,Primary key(id，Unique identification)
2,Optimistic lock(version，edition)
3,Pseudo deletion(is_delete，Flag bit)
4,Creation time(gmt_create，Creation time)
5,Modification time(gmt_update，Modification time)

11. Create MySQL database and table

Its sql statement is as follows.

-- establish school database
CREATE DATABASE IF NOT EXISTS `school` CHARACTER SET utf8 COLLATE utf8_general_ci;
-- establish student data sheet
CREATE TABLE IF NOT EXISTS `student`(
	`id` INT(10) NOT NULL AUTO_INCREMENT COMMENT 'Student number',
	`name` VARCHAR(10) NOT NULL DEFAULT 'anonymous' COMMENT 'full name',
	`pwd` VARCHAR(20) NOT NULL DEFAULT '12321' COMMENT 'password',
	`sex` VARCHAR(2) NOT NULL DEFAULT 'female' COMMENT 'Gender',
	`birthday` DATETIME DEFAULT NULL COMMENT 'date of birth',
	`address` VARCHAR(100) DEFAULT NULL COMMENT 'Home address',
	`email` VARCHAR(50) DEFAULT NULL COMMENT 'E-mail',
	PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci COMMENT 'Create student data table';
-- View create school Database sql sentence
SHOW CREATE DATABASE `school`;
-- View create student Of data sheet sql sentence
SHOW CREATE TABLE `student`;
-- display student Data table structure
DESC `student`;

12. MySQL data table type

All database files in MySQL exist in the data directory. A folder corresponds to a database. Its essence is file storage.

12.1. MySQL engine

MySQL engine is commonly used as "MYISAM engine" and "INNODB engine".

12.1.1 physical storage differences

• 12.1.1.1. MYISAM engine.

  In the data directory, there is only one. frm file in the database table and the ibdata1 file * in the parent directory.

• 12.1.1.2 INNODB engine.

  In the data directory, there are three files in the database table, as shown below.

  • 12.1.1.2.1 *. frm file (definition file of table structure).
  • 12.1.1.2.2 *. myd file (data file).
  • 12.1.1.2.3 *. myi file (index file).

12.1.2 function use differences

function	MYISAM engine	INNODB engine
Transaction processing	I won't support it	support
Data row locking	I won't support it	support
Foreign key constraint	I won't support it	support
Full text index	support	I won't support it

12.1.3 advantages and disadvantages

database engine	advantage	shortcoming
MYISAM	Space saving (small space) and fast speed	The security is not high and does not support transaction processing and multi table user operation
INNODB	High security, support transaction processing and multi table user operation	Large space (about twice as large as MYISAM space) and slow speed

12.2. MySQL character set

-- default MySQL Character set encoding is“ Latin1"，It does not support Chinese and needs to be set separately. At present, there are two methods, as shown below.
-- Mode 1(Recommended. It will not vary with different environments): Directly in SQL Statement to set the character set encoding of the database table(As“ CHARSET=utf8")；
-- Mode 2(Recommended. It may vary with different environments): Directly in"my.ini"Settings in configuration file MySQL Default character set encoding(As“ character-set-server=utf8")；

13. Modify and delete MySQL data table

13.1 modify data table

-- Modify data table name: ALTER TABLE `Old table name` RENAME AS `New table name`
ALTER TABLE `student` RENAME AS `student1`;
-- Add data table fields: ALTER TABLE `Table name` ADD `Listing` Column properties
ALTER TABLE `student1` ADD `age` INT(3);
-- Modify data table field column properties(Modify constraints): ALTER TABLE `Table name` MODIFY `Listing` Column properties
ALTER TABLE `student1` MODIFY `age` VARCHAR(11);
-- Modify data table field column name and column attribute(rename+Modify constraints): ALTER TABLE `Table name` CHANGE `Old column name` `New column name` Column properties
ALTER TABLE `student1` CHANGE `age` `age1` INT(5);
-- To delete a data table field: ALTER TABLE `Table name` DROP `Listing`
ALTER TABLE `student1` DROP `age1`;

13.2 delete data table

-- Delete data table(Delete if it exists, otherwise it will not be deleted): ALTER TABLE `Table name` DROP `Listing`
DROP TABLE IF EXISTS `student1`;

14. MySQL data management

14.1 foreign key

-- Method 1: add foreign key constraints when creating tables
-- establish grade surface
CREATE TABLE IF NOT EXISTS `grade`(
`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT 'grade id',
`gradename` VARCHAR(50) NOT NULL COMMENT 'Grade name',
PRIMARY KEY(`gradeid`) COMMENT 'Primary key gradeid'
)ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci COMMENT 'Create grade table';
-- establish student Table, adding foreign key constraints
CREATE TABLE IF NOT EXISTS `student`(
	`id` INT(10) NOT NULL AUTO_INCREMENT COMMENT 'Student number',
	`name` VARCHAR(10) NOT NULL DEFAULT 'anonymous' COMMENT 'full name',
	`pwd` VARCHAR(20) NOT NULL DEFAULT '12321' COMMENT 'password',
	`sex` VARCHAR(2) NOT NULL DEFAULT 'female' COMMENT 'Gender',
	`birthday` DATETIME DEFAULT NULL COMMENT 'date of birth',
	`address` VARCHAR(100) DEFAULT NULL COMMENT 'Home address',
	`email` VARCHAR(50) DEFAULT NULL COMMENT 'E-mail',
	`gradeid` INT(10) NOT NULL COMMENT 'Student grade id',
	PRIMARY KEY(`id`) COMMENT 'Primary key id',
	KEY `FK_gradeid`(`gradeid`) COMMENT 'Foreign key gradeid',
	CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`) 
)ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci COMMENT 'Create student table';

/*
-- Method 2: after creating a table, add a foreign key constraint
-- Create grade table
CREATE TABLE IF NOT EXISTS `grade`(
`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT 'Grade id ',
`gradename` VARCHAR(50) NOT NULL COMMENT 'Grade name ',
PRIMARY KEY(`gradeid`) COMMENT 'Primary key gradeid '
)ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci COMMENT 'Create grade table ';
-- Create student table
CREATE TABLE IF NOT EXISTS `student`(
	`id` INT(10) NOT NULL AUTO_INCREMENT COMMENT 'Student ID ',
	`name` VARCHAR(10) NOT NULL DEFAULT 'Anonymous' COMMENT 'name',
	`pwd` VARCHAR(20) NOT NULL DEFAULT '12321' COMMENT 'Password ',
	`sex` VARCHAR(2) NOT NULL DEFAULT 'Female 'COMMENT' gender ',
	`birthday` DATETIME DEFAULT NULL COMMENT 'Date of birth ',
	`address` VARCHAR(100) DEFAULT NULL COMMENT 'Home address',
	`email` VARCHAR(50) DEFAULT NULL COMMENT 'Email ',
	`gradeid` INT(10) NOT NULL COMMENT 'Student grade id ',
	PRIMARY KEY(`id`) COMMENT 'Primary key id '
)ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci COMMENT 'Create student table ';
-- Add a foreign key constraint after creating a table: ALTER TABLE ` reference table ` ADD CONSTRAINT ` constraint name ` FOREIGN KEY(` foreign key column ') REFERENCES ` referenced table (` referenced column')
ALTER TABLE `student` ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`);
*/

/*
matters needing attention:
1,When deleting a table with a foreign key relationship, you must first delete the reference table (slave table) and then the referenced table (master table).
2,It is not recommended to use a foreign key relationship table to prevent connecting multiple tables from affecting efficiency. It is best to implement it by the internal business of the program.
3,The best data table is only used to store data, only rows (data) and columns (fields), without other constraints.
*/

14.2. Add data

-- add to(insert)sentence: INSERT INTO `Table name`(`Column 1`,`Column 2`,...) VALUES(Column 1 value 1,'Column 2 value 1'),(Column 1 value 2,'Column 2 value 2'),...;
-- When inserting single row data, if the table field is not written, the values will be automatically inserted one by one
INSERT INTO `grade` VALUES(1,'1 grade');
-- When inserting single row data, if the table field is written, the table field needs to insert values one by one
INSERT INTO `grade`(`gradename`) VALUES('2 grade');
-- When inserting multiple rows of data, if the table field is written, the table field needs to insert values one by one
INSERT INTO `grade`(`gradeid`,`gradename`) VALUES(3,'3 grade'),(4,'4 grade');

14.3 modify data

-- modify(to update)sentence: UPDATE `Table name` SET `Column 1`=Column 1 value,`Column 2`='Column 2 value',... WHERE condition;
-- Modify all grade names(No condition specified)
UPDATE `grade` SET `gradename`='100 grade';
-- Modify grade id Grade name of 1
UPDATE `grade` SET `gradename`='11 grade' WHERE `gradeid`=1;
-- Modify grade id Grade 1 id,Grade name
UPDATE `grade` SET `gradeid`=1,`gradename`='111 grade' WHERE `gradeid`=1;

14.4 delete data

-- Delete data statement:
-- 1,DELETE Command: DELETE FROM `Table name` WHERE condition
-- Delete specified data
DELETE FROM `grade` WHERE `gradeid`=4;
-- Delete all data in the table(No condition specified)
DELETE FROM `grade`;
-- 2,TRUNCATE Command: TRUNCATE `Table name`，Its function is to "completely empty a data table, and the table structure and index constraints will not change".
TRUNCATE `grade`;
/*
1, The difference between DELETE command and TRUNCATE command:
1,The same point: the data table data can be cleared, and the table structure will not be deleted.
2,Differences: 2.1. TRUNCATE command will reset "auto increment column" (counter will return to zero); 2.2. TRUNCATE command will not affect transactions.
2, The special phenomenon of restarting the database after deleting empties the table data:
1,If the database engine is INNODB, the auto incrementing column will start counting from 1 again (if it exists in memory, it will lose power);
2,If the database engine is MYISAM, the self incrementing column will continue to count from the previous self increment + 1 (it exists in the file and will not be lost);
*/

14.5 query data

Query (select) data statement: the most core SQL language in the database and the most frequently used SQL statement (it is generally used for 80% of operations).

14.5.1 query SQL complete grammar

-- Select Full syntax:
SELECT [ALL | DISTINCT]
{*|table.*|[table.field1[AS alias1][,table.field2[AS alias2][,...]]}
FROM table_name1 [AS table_alias1]
[LEFT|RIGHT|INNER JOIN table_name2 [AS table_alias2]] -- Joint query
[WHERE ...] -- Specify the conditions that the results meet
[GROUP BY ...] -- Specify which fields the results are grouped by
[HAVING ...] -- Secondary conditions that must be met to filter grouped records
[ORDER BY ...] -- Specifies that query records are sorted by one or more criteria
[LIMIT {[OFFSET,]ROW_COUNT|row_countoffset OFFSET}]; -- Specify which records to query from
-- Note: brackets in the above syntax“[]"Represents an optional parameter, curly braces“{}"Represents a required parameter.

14.5.2 query specified fields

-- Query all fields
SELECT * FROM `student`;
-- Query the specified fields and give each field an alias
SELECT `id`,`name`,`pwd` FROM `student`;
-- Query the new field after splicing the specified field, and take an alias
SELECT CONCAT(`name`,'birthday:',`birthday`) AS 'Student birthday' FROM `student`;
-- De duplication: query the unique specified field
SELECT DISTINCT `name` FROM `student`;
-- query SQL Expressions: field columns, text values NULL,Functions, calculation expressions, system variables...
SELECT `address` AS 'address' FROM `student`; -- Query address(Field column)
SELECT '12321'; -- Query specified string(Text value)
SELECT NULL; -- Query specified value NULL(NULL)
SELECT VERSION(); -- Query system version(function)
SELECT 10*2-1 AS 'Calculation results'; -- Used to calculate(Calculation expression)
SELECT @@auto_increment_increment; -- Query self increasing step size(System variable)

14.5.3 query where sub condition

Its function is to "retrieve qualified values in data". Its condition consists of one or more expressions, and the result is Boolean.

• 14.5.3.1 logical operators.

  operator	grammar	describe
  AND &&	a AND b a && b	Logic and, both are true, and the result is true
  OR ||	a OR b a || b	Logical or, one of which is true and the result is true
  NOT !	NOT a NOT a=b ! a a!=b	Logical non, true is false, false is true

  -- 1,Logical operator
  -- Query grade id At 1~3 Student names between
  SELECT `name`,`gradeid` FROM `student` WHERE `gradeid`>=1 AND `gradeid`<=3;
  SELECT `name`,`gradeid` FROM `student` WHERE `gradeid`>=1 && `gradeid`<=3;
  -- Query grade id Is 1 or id Student name for 5
  SELECT `name`,`gradeid` FROM `student` WHERE `gradeid`=1 OR `gradeid`=5;
  SELECT `name`,`gradeid` FROM `student` WHERE `gradeid`=1 || `gradeid`=5;
  -- Query grade id Student name not 1
  SELECT `name`,`gradeid` FROM `student` WHERE NOT `gradeid`=1;
  SELECT `name`,`gradeid` FROM `student` WHERE `gradeid`!=1;
  

• 14.5.3.2 comparison operator.

  operator	grammar	describe
  IS NULL	a IS NULL	If the operator is NULL, the result is true
  IS NOT NULL	a IS NOT NULL	If the operator is not NULL, the result is true
  BETWEEN AND	a BETWEEN b AND c	If a is between b and c, the result is true
  LIKE	a LIKE b	If a fuzzy matches b, the result is true
  IN	a IN (a1,a2,...)	If a is equal to any of a1 or a2 or... The result is true

  -- Query the names of students without birth date
  SELECT `birthday`,`name` FROM `student` WHERE `birthday` IS NULL;
  -- Query the names of students with birth dates
  SELECT `birthday`,`name` FROM `student` WHERE `birthday` IS NOT NULL;
  -- Query grade id At 1~4 Student names between
  SELECT `gradeid`,`name` FROM `student` WHERE `gradeid` BETWEEN 1 AND 4;
  -- Find the name of the student surnamed Li:%Represents that 0 can be configured~Any character
  SELECT `name` FROM `student` WHERE `name` LIKE 'Lee%';
  -- Find the name of a student whose surname is Li and there is only one word after his name:_Represents that 1 character can be wildcarded
  SELECT `name` FROM `student` WHERE `name` LIKE 'Lee_';
  -- Find the student name with wind in the name
  SELECT `name` FROM `student` WHERE `name` LIKE '%wind%';
  -- Query students id Names of students 1, 2 and 3
  SELECT `id`,`name` FROM `student` WHERE `id` IN (1,2,3);
  

14.5.4 join table query

8 table linking operations	describe
Inner join	If at least one of the left and right tables matches, the matching row is returned
Left join / left outer join	The left join is consistent with the left outer join, and all rows will be returned from the left table, even if there is no match in the right table
Right join / right outer join	The right join is consistent with the right outer join, and all rows will be returned from the right table, even if there is no match in the left table
Cross join	A cross join is a join without join conditions. Each row of one table is merged with each row of another table. This type of result set is called Cartesian product or cross product.

-- Join table query
-- 1,Inner connection(INNER JOIN): If at least one of the left and right tables matches, the matching row is returned.
-- Use inner join to query all grades from grade table and student table id,Grade name, student id,Student name
SELECT a.`gradeid`,a.`gradename`,b.`id`,b.`name` FROM `grade` AS a
INNER JOIN `student` AS b ON(a.`gradeid`=b.`gradeid`);
-- 2,Left join(LEFT JOIN): All rows are returned from the left table, even if there is no match in the right table
-- Use left join to query all grades from grade table and student table id,Grade name, student id,Student name
SELECT a.`gradeid`,a.`gradename`,b.`id`,b.`name` FROM `grade` AS a
LEFT JOIN `student` AS b ON(a.`gradeid`=b.`gradeid`);
-- 3,Left outer connection(LEFT OUTER JOIN): Consistent with the left join, all rows are returned from the left table, even if there is no match in the right table
-- Use left outer join to query all grades from grade table and student table id,Grade name, student id,Student name
SELECT a.`gradeid`,a.`gradename`,b.`id`,b.`name` FROM `grade` AS a
LEFT OUTER JOIN `student` AS b ON(a.`gradeid`=b.`gradeid`);
-- 4,Right join(RIGHT JOIN): All rows are returned from the right table, even if there is no match in the left table
-- Use "right join" to query all grades from grade table and student table id,Grade name, student id,Student name
SELECT a.`gradeid`,a.`gradename`,b.`id`,b.`name` FROM `grade` AS a
RIGHT JOIN `student` AS b ON(a.`gradeid`=b.`gradeid`);
-- 5,Right outer connection(RIGHT OUTER JOIN): Consistent with the right join, all rows are returned from the right table, even if there is no match in the left table
-- Use "right outer join" to query all grades from grade table and student table id,Grade name, student id,Student name
SELECT a.`gradeid`,a.`gradename`,b.`id`,b.`name` FROM `grade` AS a
RIGHT OUTER JOIN `student` AS b ON(a.`gradeid`=b.`gradeid`);
-- 6,Complete connection(FULL JOIN): MySQL Not supported. Return all rows of the left table and the right table
-- 7,Complete outer connection(FULL OUTER JOIN): MySQL Not supported. Consistent with full join, all rows of the left table and the right table are returned
-- 8,cross join (CROSS JOIN): A cross join is a join without join conditions. Each row of one table is merged with each row of another table. This type of result set is called Cartesian product or cross product.
SELECT * FROM `grade` AS a CROSS JOIN `student` AS b;

14.5.5 self join query

Connect your own table with your own table, that is, split a table into two identical parent-child tables.

• Parent class table.

  id	name
  1	software development
  2	JavaSE
  3	HTML5
  4	CSS3
  5	JavaScript
  6	MySQL
  7	JavaWeb
  8	SSM framework
  9	Large front end advanced
  10	Microservice development
  11	Linux operation and maintenance
  12	Common middleware

• Subclass table.

  sub_id	id	name
  1	1	software development
  1	2	JavaSE
  2	3	HTML5
  3	4	CSS3
  3	5	JavaScript
  4	6	MySQL
  5	7	JavaWeb
  6	8	SSM framework
  7	9	Large front end advanced
  8	10	Microservice development
  9	11	Linux operation and maintenance
  10	12	Common middleware

-- Self join query: that is, join your own table with your own table, that is, split a table into two identical parent-child tables
SELECT a.`name` AS 'Parent class column name',b.`name` AS 'Subclass column name'
FROM `selftable` AS a,`selftable` AS b
WHERE a.`id`=b.`sub_id`;

14.5.6 pages and sorts query

/*
Paging and sorting queries
1,Pagination syntax: LIMIT query page start subscript and page size (e.g. LIMIT 0,10).
1.1,Page 1: limit 0, Num = > (1-1) * num, num,
1.2,Page 2: limit 1 * num, Num = > (2-1) * num, num,
1.3,Page 3: limit 2 * num, Num = > (3-1) * num, num,
...
1.n,Page n: limit (n-1) * num, Num = > (n-1) * num, num,
Derived paging formula: (n-1)*num,num; Where num represents the page size, n represents the total number of pages (total number of pages = total number of data / page size), and (n-1)*num represents the starting subscript of the query page;
2,Sorting syntax: ORDER BY field column 1, field column 2,... ASC (ascending) / desc (ascending).
*/
-- Query 1~5 grade id Information about students whose scores are in the top 3 of middle school students and whose scores are greater than or equal to 80(grade id,student id,Student name, student gender, student score)
SELECT `gradeid` grade id,`id` student id,`name` Student name,`sex` Student gender,`score` Student score FROM `student`
WHERE `gradeid` BETWEEN 1 AND 5 AND `score`>=80
ORDER BY `score` DESC
LIMIT 0,3;

14.5.7 sub query and nested query

The essence of subquery and nested query: "nest a subquery statement in the query where statement".

-- Subqueries and nested queries
-- Query 1~5 grade id All student information in(grade id,student id,Student name, student gender, student score)
select `gradeid` as grade id,`id` as student id,`name` as Student name,`sex` as Student gender,`score` as Student score from `student`
where `gradeid` in (select `gradeid` from `grade` where `gradeid` in (1,2,3,4,5));

14.5.8 group and filter query

-- Grouping and filtering queries
-- Query all students in different grades id The grade name with the highest score, lowest score and average score, and the average score is greater than or equal to 80
SELECT `gradename` Grade name,MAX(`score`) Highest score,MIN(`score`) Lowest score,AVG(`score`) average
FROM `student` s INNER JOIN `grade` g ON(s.`gradeid`=g.`gradeid`)
GROUP BY s.`gradeid` /*Group data by field "gradeid"*/
HAVING average>=80; /*Filter the data according to the new field "average score"*/

15. MySQL function

15.1 general function

-- General function
-- 1,Mathematical function
SELECT ABS(-1); -- absolute value
SELECT CEILING(5.4);-- Round up
SELECT FLOOR(5.4);-- Round down
SELECT RAND();-- Returns a 0~1 Random floating point numbers between
SELECT SIGN(0);-- Symbol for judging a number: 0 when it is 0, 1 when it is positive, and 1 when it is negative-1
-- 2,String function
SELECT CHAR_LENGTH('mysql');-- String length
SELECT CONCAT('happiness','Joyous','you');-- Splice string
SELECT INSERT('like you',1,3,'I love you!');-- The specified string replaces the string of the specified length from the specified position
SELECT UPPER('MySQL');-- capital
SELECT LOWER('MySQL');-- Lowercase letters
SELECT INSTR('MySQL MySQL','y');-- Returns the first occurrence of a substring in a string
SELECT REPLACE('MySQL MySQL','MySQL','mysql');-- Replaces all specified strings that appear in the string
SELECT SUBSTR('hijklMySQLmnopq',6,5);-- Returns a substring of a specified length at a specified position
SELECT REVERSE('MySQL');-- Reverse string
-- 3,Date time function
SELECT CURRENT_DATE();-- Get current date
SELECT CURDATE();-- Get current date
SELECT NOW();-- Get current date and time
SELECT LOCALTIME();-- Get local date and time
SELECT SYSDATE();-- Get the current system date and time
SELECT YEAR(NOW());-- Get current year
SELECT MONTH(NOW());-- Get current month
SELECT DAY(NOW());-- Get current day
SELECT HOUR(NOW());-- Get current hour
SELECT MINUTE(NOW());-- Gets the current minute
SELECT SECOND(NOW());-- Gets the current second
-- 4,System parameter function
SELECT SYSTEM_USER();-- Get system user
SELECT USER();-- Get current user
SELECT VERSION();-- Get system version

15.2 aggregate function

-- Aggregate function
SELECT COUNT(`score`) FROM `student`;-- Count, all are ignored null value
SELECT COUNT(*) FROM `student`;-- Count, not ignore all null Value, its essence: calculate the number of rows
SELECT COUNT(1) FROM `student`;-- Count, not ignore all null Value, its essence: calculate the number of rows
SELECT SUM(`score`) 'the sum' FROM `student`;-- Sum
SELECT AVG(`score`) 'average' FROM `student`;-- Average
SELECT MAX(`score`) 'Highest score' FROM `student`;-- Find the maximum value
SELECT MIN(`score`) 'Lowest score' FROM `student`;-- Find the minimum value

15.3. Database MD5 encryption function

MD5 message digest algorithm is a widely used algorithm Cryptographic hash function , a 128 bit (16 bit) can be generated byte )The hash value of is used to ensure the integrity and consistency of information transmission.

-- MD5 Encryption function: it mainly enhances the complexity and irreversibility of the algorithm, and its return value is in the form of a string of 32-bit hexadecimal digits.
-- establish MD5 Test table
CREATE TABLE `md5_table`(
`id` INT(10) NOT NULL AUTO_INCREMENT COMMENT 'Unique identification',
`name` VARCHAR(10) NOT NULL COMMENT 'user name',
`pwd` VARCHAR(20) NOT NULL COMMENT 'password',
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
-- Insert data for plaintext password
INSERT INTO `md5_table`(`name`,`pwd`) VALUES('Zhang San','123'),('Li Si','456');
-- Insert non plaintext password(MD5 encryption)Data
INSERT INTO `md5_table`(`name`,`pwd`) VALUES('Zhao Wu',MD5('123456')),('Qian Liu',MD5('123321'));
-- Password verification: verify the password passed by the user MD5 Encrypt and then compare the encrypted values
SELECT * FROM `md5_table` WHERE `name`='Zhao Wu' AND `pwd`=MD5('123456');
SELECT * FROM `md5_table` WHERE `name`='Qian Liu' AND `pwd`=MD5('123321');

16. MySQL transaction

Transactions are generally executed in a batch (both successful and failed), and a transaction corresponds to a complete business (such as bank account transfer). The transaction has four basic elements, namely, ACID principle, as shown below.

16.1 atomicity

Or indivisibility, atomicity means that a transaction is an indivisible work unit, and the operations in the transaction either occur or do not occur.

16.2 consistency

Consistency means that the integrity of data before and after a transaction must be consistent.

16.3 isolation

Or independence, 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, and multiple concurrent transactions should be isolated from each other.

• 16.3.1 isolation can solve the problem

  • 16.3.1.1 dirty reading.

    A transaction reads uncommitted data from another transaction.

  • 16.3.1.2. It cannot be read repeatedly.

    When a row of data in a table is read in a transaction, the results are different multiple times (this is not necessarily an error, but it is wrong in some cases).

  • 16.3.1.3 idleness (unreal reading).

    It refers to that data inserted by other transactions is read in one transaction, resulting in inconsistent total number of reads before and after.

• 16.3.2 four isolation level settings

  -- Set the isolation level from query transactions
  -- Set the transaction isolation level to "serialization": it can avoid dirty reads, non repeatable reads and virtual reads.
  SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
  -- Set the transaction isolation level to "repeatable read": to avoid dirty reading and non repeatable reading.
  SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
  -- Set the transaction isolation level to read committed to avoid dirty reads.
  SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
  -- Set the transaction isolation level to "read uncommitted": the lowest level. None of the above conditions can be guaranteed.
  SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
  -- Query the current transaction isolation level
  SELECT @@tx_isolation;
  

16.4 durability

Persistence means that once a transaction is committed, its changes to the data in the database are permanent. Then, even if the database fails, it should not have any impact on it.

-- MySQL affair
SET autocommit=1;-- Turn on transaction auto commit(MySQL Default case)
-- Manual processing of business
SET autocommit=0;-- Turn off transaction auto commit
-- Start a transaction: mark the beginning of a transaction, starting from the beginning SQL All within the same transaction
START TRANSACTION Transaction name;
/*
Execute SQL statement block:
SQL Statement 1;
SQL Statement 2;
......
SQL Statement n;
*/
-- if SQL If the statement is executed successfully, the transaction is committed
COMMIT Transaction name;
-- if SQL If the statement execution fails, the transaction is rolled back
ROLLBACK Transaction name;
-- End transaction
SET autocommit=1;-- Turn on transaction auto commit
-- Set transaction savepoint
SAVEPOINT Transaction savepoint name;
-- Rollback to transaction savepoint
ROLLBACK TO SAVEPOINT Transaction savepoint name;
-- Undo transaction savepoint
RELEASE SAVEPOINT Transaction savepoint name;

17. MySQL index

The essence of index is data structure, which can enable MySQL to obtain the data structure of data efficiently.

17.1 index classification

• 17.1.1. Primary key index.

PRIMARY KEY keyword is a unique identifier. The PRIMARY KEY cannot be repeated. Only one column can be used as the PRIMARY KEY.

• 17.1.2 unique index

UNIQUE KEY keyword to avoid duplicate columns. Unique indexes can be repeated, and multiple columns can be identified as unique indexes.

• 17.1.3 general index

KEY or INDEX keyword.

• 17.1.4 full text index

  FULLTEXT keyword, which can only be created for CHAR, VARCHAR and TEXT columns, can quickly locate data.

  • Before version 17.1.4.1 and MySQL 5.6, only MyISAM database engine supports full-text indexing.
  • After version 17.1.4.2 and MySQL 5.6, both InnoDB and MyISAM database engines support full-text indexing.

17.2 index use

-- MySQL Indexes
/*
Use of index: the efficiency of index is not obvious when the amount of data is small, but it is very obvious when the amount of data is large.
1,Add indexes to fields when creating tables
2,After creating the table, add indexes to the fields
*/
-- Displays all index information for the specified table
SHOW INDEX FROM `selftable`;
-- Add a full-text index
ALTER TABLE `selftable` ADD FULLTEXT INDEX `name`(`name`);
-- analysis SQL Statement execution status
EXPLAIN SELECT * FROM `selftable`；-- Non full text index
EXPLAIN SELECT * FROM `selftable` WHERE MATCH(`name`) AGAINST('Java');-- Full text index

17.3 index principle

• 17.3.1 the more indexes in the table, the better.
• 17.3.2. Do not add indexes to the columns of variable data fields in the table.
• 17.3.3 tables with small amount of data do not need to be indexed.
• 17.3.4 indexes in the table are generally added to frequently queried fields.

18. Rights management and data backup

18.1 rights management

18.1.1. SQLyog visual management operation

MySQL permission management Reference website.

18.1.2 SQL command statement operation

-- User rights management
-- Create user
CREATE USER `xueshanxuehai` IDENTIFIED BY '123456';
-- Modify current user secret
SET PASSWORD=PASSWORD('123456');
-- Modify the specified user password
SET PASSWORD FOR `xueshanxuehai`=PASSWORD('123');
-- Rename user name
RENAME USER `xueshanxuehai` TO `xsxh`;
-- User authorization: parameter“ ALL PRIVILEGES"Represents all rights except authorizing others
GRANT ALL PRIVILEGES ON *.* TO `xsxh`;
-- Query specified user permissions
SHOW GRANTS FOR `xsxh`;
SHOW GRANTS FOR root@localhost;
-- Revoke permissions
REVOKE ALL PRIVILEGES ON *.* FROM `xsxh`;
-- delete user
DROP USER `xsxh`;

18.2 data backup

18.2.1. SQLyog visual management operation

MySQL data backup Reference website.

18.2.2 CMD command statement operation

# Export backup data
# Export backup data of a single data table: mysqldump -h host name - u user name - p password library name table name > physical disk location path / file name.sql
mysqldump -hlocalhost -uroot -p123456 school student > D:/a.sql
# Export backup data of multiple data tables: mysqldump -h host name - u user name - p password library name table 1 name table 2 name... > physical disk location path / file name.sql
mysqldump -hlocalhost -uroot -p123456 school student grade md5_table > D:/b.sql
# Export database backup data: mysqldump -h host name - u user name - p password library name > physical disk location path / file name.sql
mysqldump -hlocalhost -uroot -p123456 school > D:/c.sql
# Import backup data
# If the database corresponding to the imported backup data does not exist, you need to create the specified empty database first
# Log in to MySQL
mysql -uroot -p123456
# Create the specified empty database
create database `school`;
# Import the backup data of the specified database data table (method 1)
# Log in to MySQL
mysql -uroot -p123456
# Switch to the specified database
use `school`;
# Import the backup data of the specified data table
source D:/c.sql
# Import the backup data of the specified database data table (method 2)
mysql -hlocalhost -uroot -p123456 school < D:/c.sql
# Special attention: 1. To prevent data loss, it is necessary to back up the database data; 2. If you need to test the data to others, you can directly back up the sql file.

19. Standardize database design

19.1 different database design

• 19.1.1 bad database design

  • 19.1.1.1 data redundancy and waste of space.
  • 19.1.1.2. Database insertion and deletion are inconvenient (foreign key constraint exceptions will occur).
  • 19.1.1.3 poor program performance.

• 19.1.2 good database design

  • 19.1.2.1 save memory space.
  • 19.1.2.2 ensure the integrity of the database.
  • 19.1.2.3 facilitate system development.

19.2 three paradigms

• 19.2.1 first paradigm

Ensure that each column is non separable (i.e., atomicity).

• 19.2.2 second paradigm

On the premise of meeting the first paradigm, ensure that each table describes only one thing.

• 19.2.3 third paradigm

  On the premise of meeting the first and second paradigms, ensure that each column of data in the data table is directly related to the primary key, not indirectly.

19.3 normative and performance issues

• 19.3.1 the number of associated query tables shall not exceed three.
• 19.3.2 when considering the needs and objectives of commercialization (cost and user experience), the performance of database is more important.
• 19.3.3 when considering performance, it is also necessary to properly consider the normalization.
• 19.3.4 sometimes, some redundant fields are deliberately added to some tables to improve performance (for example, multi table query can be changed to single table query).
• 19.3.5 sometimes, some calculation columns are added to some tables to improve performance (for example, adding index columns can change large data query into small data query, so as to improve query speed).

20,JDBC(Java DataBase Connectivity)

In order to simplify the unified operation of the database by developers, SUN company provides a specification for Java operation database (i.e. JDBC), and the implementation of these specifications is done by specific database manufacturers, so developers only need to master the operation of JDBC interface, as shown in the figure below.

​

20.1. Use IDEA write JDBC program

The specific implementation steps are as follows.

• 20.1.1 create an IDEA project.

• 20.1.2. Import the database driver file (i.e. mysql-connector-java-5.1.47.jar), as shown in the following figure.

  

• 20.1.3 add the database driver file (i.e. mysql-connector-java-5.1.47.jar) as the library, as shown in the following figure.

  

• 20.1.4 write JDBC program, as shown below.

  Its sample code is shown below.

  import java.sql.*;
  public class JDBC {
      public static void main(String[] args) throws ClassNotFoundException, SQLException {
          //1. Load JDBC driver (fixed writing method)
          Class.forName("com.mysql.jdbc.Driver");
          //2. URL: the format is "jdbc: Protocol (such as mysql): / / host address: port number / database name? Parameter 1 (use Unicode character set) & parameter 2 (specify character code) & parameter 3 (use SSL connection)".
          //Special note: delete the secure connection parameter behind the url (with warning) or change it to false [if the SQL version is greater than the connect version, it should be set to false]).
          //String url="jdbc:mysql://localhost:3306/jdbc_test?useUnicode=true&characterEncoding=utf8&useSSL=true";
          String url="jdbc:mysql://localhost:3306/jdbc_test?useUnicode=true&characterEncoding=utf8&useSSL=false";
          //3. User information
          String username="root";//user name
          String password="123456";//password
          //4. Database connection object
          Connection connection= DriverManager.getConnection(url,username,password);
          //5. SQL statement execution object
          Statement statement=connection.createStatement();
          //6. SQL statement
          String sql="select * from grade";
          //7. Execute SQL statement
          ResultSet resultSet=statement.executeQuery(sql);//Return query result set (including all query results)
          while(resultSet.next()){
              System.out.println("gradeid="+resultSet.getObject("gradeid"));
              System.out.println("gradename="+resultSet.getObject("gradename"));
          }
          //8. Release database connection object resources
          resultSet.close();
          statement.close();
          connection.close();
      }
  }
  

  The operation results are as follows.

  gradeid=1
  gradename=1 grade
  gradeid=2
  gradename=2 grade
  gradeid=3
  gradename=3 grade
  gradeid=5
  gradename=4 grade
  gradeid=6
  gradename=5 grade
  

20.2. JDBC objects

20.2.1 Connection object

//Database connection object
Connection connection= DriverManager.getConnection(url,username,password);
connection.commit();//Database transaction commit
connection.rollback();//Database transaction rollback
connection.setAutoCommit(true);//Set whether the database transaction is automatically committed. The default parameter is true, which means it is automatically committed; otherwise, the parameter is false, which means it is not automatically committed

20.2.2. ResultSet object

ResultSet resultSet=statement.executeQuery(sql);//Return query result set (including all query results)
resultSet.getObject("gradeid");//Do not know the column type "gradeid"
resultSet.getInt("gradeid");//Know that the "gradeid" column type is Int
resultSet.beforeFirst();//Move to first row of data
resultSet.afterLast();//Move to last row of data
resultSet.next();//Move to next row of data
resultSet.previous();//Move to previous row of data
resultSet.absolute(1);//Move to specified row data

20.2.3 Statement object

The Statement object in JDBC is used to send SQL statements to the database (add query, delete and modify operation, i.e. CRUD operation).

The executeUpdate method of the Statement object is used to send SQL statements for adding, deleting and modifying operations to the database. After execution, it will return an integer (representing the number of rows affected by the corresponding data table).

The executeQuery method of the Statement object is used to send the SQL Statement of the query operation to the database. After execution, it will return a ResultSet object (representing the result of the corresponding data table query).

20.2.3.1 extraction tools

The contents of the configuration file (db.properties) are as follows.

# Database driver name
driver=com.mysql.jdbc.Driver
# url, especially note that when the database installation version is lower than the database driver version, the "useSSL parameter setting value" must be "useSSL=true", otherwise it must be "useSSL=false".
url=jdbc:mysql://localhost:3306/jdbc_test?useUnicode=true&characterEncoding=utf8&useSSL=false
# user name
username=root
# password
password=123456

​ JDBCUtils_ The example code of sta tool class is as follows.

package jdbc.utils;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class JDBCUtils_STA {
    private static String driver=null;
    private static String url=null;
    private static String username=null;
    private static String password=null;
    //Static code is fast (only executed when the class is first loaded)
    static{
        try {
            InputStream inputStream= JDBCUtils_STA.class.getClassLoader().getResourceAsStream("resources/db.properties");
            Properties properties=new Properties();
            properties.load(inputStream);
            driver=properties.getProperty("driver");
            url=properties.getProperty("url");
            username=properties.getProperty("username");
            password=properties.getProperty("password");
            Class.forName(driver);//Fixed writing method, loading database driver
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //Get database connection
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url,username,password);
    }
    //Release database connection object resources
    public static void release(Connection conn, Statement sta, ResultSet res){
        if(res!=null){
            try {
                res.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(sta!=null){
            try {
                sta.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

20.2.3.2. Add query, delete and modify

• 20.2.3.2.1. Add (Create)

  Its sample code is shown below.

  package jdbc.statement;
  import jdbc.utils.JDBCUtils_STA;
  import java.sql.Connection;
  import java.sql.ResultSet;
  import java.sql.SQLException;
  import java.sql.Statement;
  //SQL statement add operation
  public class InsertSql {
      public static void main(String[] args) {
          Connection conn=null;
          Statement sta=null;
          ResultSet res=null;
          try {
              conn= JDBCUtils_STA.getConnection();//Get database connection object
              sta=conn.createStatement();//Gets the object specified in the SQL statement
              String sql="insert into grade(gradeid,gradename) VALUES(7,'7 grade')";//SQL statement string
              int rn=sta.executeUpdate(sql);
              if(rn>0){
                  System.out.println("Insert data succeeded");
              }
          } catch (SQLException throwables) {
              throwables.printStackTrace();
          }finally{
              JDBCUtils_STA.release(conn,sta,res);
          }
      }
  }
  

  The operation results are as follows.

  Insert data succeeded
  

• 20.2.3.2.2. Delete (delete)

  Its sample code is shown below.

  package jdbc.statement;
  import jdbc.utils.JDBCUtils_STA;
  import java.sql.Connection;
  import java.sql.ResultSet;
  import java.sql.SQLException;
  import java.sql.Statement;
  //SQL statement delete operation
  public class DeleteSql {
      public static void main(String[] args) {
          Connection conn=null;
          Statement sta=null;
          ResultSet res=null;
          try {
              conn= JDBCUtils_STA.getConnection();//Get database connection object
              sta=conn.createStatement();//Gets the object specified in the SQL statement
              String sql="delete from grade where gradename='8 grade'";//SQL statement string
              int rn=sta.executeUpdate(sql);
              if(rn>0){
                  System.out.println("Data deleted successfully");
              }
          } catch (SQLException throwables) {
              throwables.printStackTrace();
          }finally{
              JDBCUtils_STA.release(conn,sta,res);
          }
      }
  }
  

  The operation results are as follows.

  Data deleted successfully
  

• 20.2.3.2.3. Change operation (Update operation)

  Its sample code is shown below.

  package jdbc.statement;
  import jdbc.utils.JDBCUtils_STA;
  import java.sql.Connection;
  import java.sql.ResultSet;
  import java.sql.SQLException;
  import java.sql.Statement;
  //SQL statement modification
  public class UpdateSQL {
      public static void main(String[] args) {
          Connection conn=null;
          Statement sta=null;
          ResultSet res=null;
          try {
              conn= JDBCUtils_STA.getConnection();//Get database connection object
              sta=conn.createStatement();//Gets the object specified in the SQL statement
              String sql="update grade set gradename='8 grade' where gradename='7 grade'";//SQL statement string
              int rn=sta.executeUpdate(sql);
              if(rn>0){
                  System.out.println("Data update succeeded");
              }
          } catch (SQLException throwables) {
              throwables.printStackTrace();
          }finally{
              JDBCUtils_STA.release(conn,sta,res);
          }
      }
  }
  

  The operation results are as follows.

  Data update succeeded
  

• 20.2.3.2.4. Retrieve operation

  Its sample code is shown below.

  package jdbc.statement;
  import jdbc.utils.JDBCUtils_STA;
  import java.sql.Connection;
  import java.sql.ResultSet;
  import java.sql.SQLException;
  import java.sql.Statement;
  //SQL statement query operation
  public class SelectSql {
      public static void main(String[] args) {
          Connection conn=null;
          Statement sta=null;
          ResultSet res=null;
          try {
              conn= JDBCUtils_STA.getConnection();//Get database connection object
              sta=conn.createStatement();//Gets the object specified in the SQL statement
              String sql="select * from grade";//SQL statement string
              res=sta.executeQuery(sql);
              while(res.next()){
                  System.out.println("gradeid="+res.getInt("gradeid"));
                  System.out.println("gradename="+res.getString("gradename"));
              }
          } catch (SQLException throwables) {
              throwables.printStackTrace();
          }finally{
              JDBCUtils_STA.release(conn,sta,res);
          }
      }
  }
  

  The operation results are as follows.

  gradeid=1
  gradename=1 grade
  gradeid=2
  gradename=2 grade
  gradeid=3
  gradename=3 grade
  gradeid=4
  gradename=4 grade
  gradeid=5
  gradename=5 grade
  gradeid=6
  gradename=6 grade
  

• 20.2.3.2.5 SQL injection problems may occur

  Its sample code is shown below.

  package jdbc.statement;
  import jdbc.utils.JDBCUtils_STA;
  import java.sql.Connection;
  import java.sql.ResultSet;
  import java.sql.SQLException;
  import java.sql.Statement;
  /**
   * SQL Injection problem: because SQL will be spliced, there are loopholes, which will be vulnerable to attack and lead to data disclosure.
   */
  public class SqlInjectionProblem {
      public static void main(String[] args) {
          //login("Zhang San", "12321")// Get user information normally
          login(" 'or '1=1"," 'or '1=1");//Exception to get all user information (i.e. SQL injection problem)
      }
      //Login service
      public static void login(String username,String password){
          Connection conn=null;
          Statement sta=null;
          ResultSet res=null;
          try {
              conn= JDBCUtils_STA.getConnection();//Get database connection object
              sta=conn.createStatement();//Gets the object specified in the SQL statement
              String sql="select * from student where name='"+username+"' and pwd='"+password+"'";//SQL statement string
              res=sta.executeQuery(sql);
              while(res.next()){
                  System.out.println("name="+res.getString("name"));
                  System.out.println("pwd="+res.getString("pwd"));
              }
          } catch (SQLException throwables) {
              throwables.printStackTrace();
          }finally{
              JDBCUtils_STA.release(conn,sta,res);
          }
      }
  }
  

  The operation results are as follows.

  name=Liu Da
  pwd=12321
  name=WangTwo 
  pwd=12321
  name=Zhang San
  pwd=12321
  name=Li Si
  pwd=12321
  name=Zhao Wu
  pwd=12321
  name=Zhang Fenglei
  pwd=12321
  name=Li Fengyu
  pwd=12321
  

20.2.4 PreparedStatement object

PreparedStatement can prevent SQL injection problems caused by Statement, and the execution efficiency is better.

20.2.4.1 extraction tools

The contents of the configuration file (db.properties) are as follows.

# Database driver name
driver=com.mysql.jdbc.Driver
# url, especially note that when the database installation version is lower than the database driver version, the "useSSL parameter setting value" must be "useSSL=true", otherwise it must be "useSSL=false".
url=jdbc:mysql://localhost:3306/jdbc_test?useUnicode=true&characterEncoding=utf8&useSSL=false
# user name
username=root
# password
password=123456

​ JDBCUtils_ The sample code of pstA tool class is as follows.

package jdbc.utils;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class JDBCUtils_PSTA {
    private static String driver=null;
    private static String url=null;
    private static String username=null;
    private static String password=null;
    //Static code is fast (only executed when the class is first loaded)
    static{
        try {
            InputStream inputStream= JDBCUtils_PSTA.class.getClassLoader().getResourceAsStream("resources/db.properties");
            Properties properties=new Properties();
            properties.load(inputStream);
            driver=properties.getProperty("driver");
            url=properties.getProperty("url");
            username=properties.getProperty("username");
            password=properties.getProperty("password");
            Class.forName(driver);//Fixed writing method, loading database driver
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //Get database connection
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url,username,password);
    }
    //Release database connection object resources
    public static void release(Connection conn, PreparedStatement psta, ResultSet res){
        if(res!=null){
            try {
                res.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(psta!=null){
            try {
                psta.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

20.2.4.2. Add query, delete and modify

• 20.2.4.2.1. Add (Create)

  Its sample code is shown below.

  package jdbc.preparedstatement;
  import jdbc.utils.JDBCUtils_PSTA;
  import java.sql.*;
  //SQL statement add operation
  public class InsertSql {
      public static void main(String[] args) {
          Connection conn=null;
          PreparedStatement psta=null;
          ResultSet res=null;
          try {
              //Get database connection object
              conn= JDBCUtils_PSTA.getConnection();
              //SQL statement string: use placeholder (?) instead of parameter
              String sql="insert into student(name,pwd,sex,birthday,gradeid) values(?,?,?,?,?)";
              //Obtain the specified object of SQL statement: precompiled SQL, write SQL first, and do not execute SQL
              psta= conn.prepareStatement(sql);
              //Assign values to the parameters manually and correspond one by one according to the sequence of field columns
              psta.setString(1,"Xueshan");
              psta.setString(2,"123456789");
              psta.setString(3,"male");
              /**
               * Special attention:
               * 1,Java Date time: java.util.Date(), Java timestamp: java.util.Date().getTime();
               * 2,Database date and time: java.sql.Date(); Database timestamp: java.sql.Date().getTime();
               */
              psta.setDate(4,new java.sql.Date(new java.util.Date().getTime()));
              psta.setInt(5,1);
              int rn=psta.executeUpdate();//Execute SQL statement
              if(rn>0){
                  System.out.println("Insert data succeeded");
              }
          } catch (SQLException throwables) {
              throwables.printStackTrace();
          }finally{
              JDBCUtils_PSTA.release(conn,psta,res);
          }
      }
  }
  

  The operation results are as follows.

  Insert data succeeded
  

• 20.2.4.2.2. Delete (delete)

  Its sample code is shown below.

  package jdbc.preparedstatement;
  import jdbc.utils.JDBCUtils_PSTA;
  import java.sql.*;
  //SQL statement delete operation
  public class DeleteSql {
      public static void main(String[] args) {
          Connection conn=null;
          PreparedStatement psta=null;
          ResultSet res=null;
          try {
              //Get database connection object
              conn= JDBCUtils_PSTA.getConnection();
              //SQL statement string: use placeholder (?) instead of parameter
              String sql="delete from student where name=?";
              //Obtain the specified object of SQL statement: precompiled SQL, write SQL first, and do not execute SQL
              psta= conn.prepareStatement(sql);
              //Assign values to the parameters manually and correspond one by one according to the sequence of field columns
              psta.setString(1,"persevere in one's studies until one reaches the goal");
              int rn=psta.executeUpdate();//Execute SQL statement
              if(rn>0){
                  System.out.println("Data deleted successfully");
              }
          } catch (SQLException throwables) {
              throwables.printStackTrace();
          }finally{
              JDBCUtils_PSTA.release(conn,psta,res);
          }
      }
  }
  

  The operation results are as follows.

  Data deleted successfully
  

• 20.2.4.2.3. Change operation (Update operation)

  Its sample code is shown below.

  package jdbc.preparedstatement;
  import jdbc.utils.JDBCUtils_PSTA;
  import java.sql.*;
  //SQL statement modification
  public class UpdateSQL {
      public static void main(String[] args) {
          Connection conn=null;
          PreparedStatement psta=null;
          ResultSet res=null;
          try {
              //Get database connection object
              conn= JDBCUtils_PSTA.getConnection();
              //SQL statement string: use placeholder (?) instead of parameter
              String sql="update student set name=? where name=?";
              //Obtain the specified object of SQL statement: precompiled SQL, write SQL first, and do not execute SQL
              psta= conn.prepareStatement(sql);
              //Assign values to the parameters manually and correspond one by one according to the sequence of field columns
              psta.setString(1,"persevere in one's studies until one reaches the goal");
              psta.setString(2,"Xueshan");
              int rn=psta.executeUpdate();//Execute SQL statement
              if(rn>0){
                  System.out.println("Data update succeeded");
              }
          } catch (SQLException throwables) {
              throwables.printStackTrace();
          }finally{
              JDBCUtils_PSTA.release(conn,psta,res);
          }
      }
  }
  

  The operation results are as follows.

  Data update succeeded
  

• 20.2.4.2.4. Retrieve operation

  Its sample code is shown below.

  package jdbc.preparedstatement;
  import jdbc.utils.JDBCUtils_PSTA;
  import java.sql.*;
  //SQL statement query operation
  public class SelectSql {
      public static void main(String[] args) {
          Connection conn=null;
          PreparedStatement psta=null;
          ResultSet res=null;
          try {
              //Get database connection object
              conn= JDBCUtils_PSTA.getConnection();
              //SQL statement string: use placeholder (?) instead of parameter
              String sql="select name,pwd,sex,birthday,gradeid from student where name=?";
              //Obtain the specified object of SQL statement: precompiled SQL, write SQL first, and do not execute SQL
              psta= conn.prepareStatement(sql);
              //Assign values to the parameters manually and correspond one by one according to the sequence of field columns
              psta.setString(1,"Zhang San");
              res=psta.executeQuery();//Execute SQL statement
              while(res.next()){
                  System.out.println("name="+res.getString("name"));
                  System.out.println("pwd="+res.getString("pwd"));
                  System.out.println("sex="+res.getString("sex"));
                  System.out.println("birthday="+res.getDate("birthday"));
                  System.out.println("gradeid="+res.getInt("gradeid"));
              }
          } catch (SQLException throwables) {
              throwables.printStackTrace();
          }finally{
              JDBCUtils_PSTA.release(conn,psta,res);
          }
      }
  }
  

  The operation results are as follows.

  name=Zhang San
  pwd=12321
  sex=male
  birthday=2021-10-03
  gradeid=3
  

• 20.2.4.2.5. SQL injection will not occur

  Its sample code is shown below.

  package jdbc.preparedstatement;
  import jdbc.utils.JDBCUtils_PSTA;
  import java.sql.*;
  /**
   * SQL Injection problem: because SQL will be spliced, there are loopholes, which will be vulnerable to attack and lead to data disclosure.
   */
  public class SqlInjectionProblem {
      public static void main(String[] args) {
          //login("Zhang San", "12321")// Get user information normally
          login(" 'or '1=1"," 'or '1=1");//Unable to get user information (i.e. SQL injection problem will not occur)
      }
      //Login service
      public static void login(String username,String password){
          Connection conn=null;
          PreparedStatement psta=null;
          ResultSet res=null;
          try {
              //Get database connection object
              conn= JDBCUtils_PSTA.getConnection();
              //SQL statement string: use placeholder (?) instead of parameter
              String sql="select name,pwd,sex,birthday,gradeid from student where name=? and pwd=?";
              //Obtain the specified object of SQL statement: precompiled SQL, write SQL first, and do not execute SQL
              psta= conn.prepareStatement(sql);
              //Assign values to the parameters manually and correspond one by one according to the sequence of field columns
              psta.setString(1,username);
              psta.setString(2,password);
              res=psta.executeQuery();//Execute SQL statement
              while(res.next()){
                  System.out.println("name="+res.getString("name"));
                  System.out.println("pwd="+res.getString("pwd"));
                  System.out.println("sex="+res.getString("sex"));
                  System.out.println("birthday="+res.getDate("birthday"));
                  System.out.println("gradeid="+res.getInt("gradeid"));
              }
          } catch (SQLException throwables) {
              throwables.printStackTrace();
          }finally{
              JDBCUtils_PSTA.release(conn,psta,res);
          }
      }
  }
  

20.3. Use IDEA connection database

The specific implementation steps are as follows.

• 20.3.1 select MySQL connection item, as shown in the figure below.

  

• 20.3.2 configure MySQL data source driver, as shown in the figure below.

  

• 20.3.3 test MySQL connection, as shown in the figure below.

  

• 20.3.4 select the MySQL database "jdbc_test", as shown in the figure below.

  

• 20.3.5 MySQL database configuration is completed, as shown in the figure below.

  

20.4. Use IDEA operation database connection pool

Generally, when operating the database, it will frequently connect, process and release, which is very resource consuming. Therefore, it is recommended to operate the database connection pool (that is, adopt pooling technology < for example, prepare some cache resources in advance, and give priority to using these cache resources during database connection, so as to avoid frequent creation in releasing resources >).

The essence of database connection pool is to implement the DataSource interface.

20.4.1 DBCP database connection pool

Prerequisite: first import the corresponding jar packages (i.e., commons-dbcp-1.4.jar and commons-pool-1.6.jar). For specific operations, please refer to the previous operation "import database driver file (i.e., mysql-connector-java-5.1.47.jar)".

20.4.1.1 extraction tools

The contents of the configuration file (db_dbcp.properties) are as follows.

# Connection settings: all names are defined in the DBCP data source (i.e. DBCP database connection pool).
# Database driver name
driverClassName=com.mysql.jdbc.Driver
# url, especially note that when the database installation version is lower than the database driver version, the "useSSL parameter setting value" must be "useSSL=true", otherwise it must be "useSSL=false".
url=jdbc:mysql://localhost:3306/jdbc_test?useUnicode=true&characterEncoding=utf8&useSSL=false
# user name
username=root
# password
password=123456
# Number of initialized connections
initialSize=10
# maximum connection
maxActive=50
# Maximum number of free connections
maxIdle=20
# Minimum number of free connections
minIdle=5
# Timeout wait time in milliseconds
maxWait=60000
# The connection property attached when the JDBC driver establishes a connection: its format is "property name = property"
connectionProperties=useUnicode=true;characterEncoding=utf8
# Specifies the auto commit status of connection objects created by the connection pool
defaultAutoCommit=true
# Specifies the read-only state of the connection object created by the connection pool: if the value is not set, the "setReadOnly" method will not be called (some drivers do not support read-only mode, such as Informix)
defaultReadOnly=
# Specify the transaction isolation level of the connection object created by the connection pool: NONE, read_ Uncommitted, read_ Committed, repeatable_ Read, serializable
defaultTransactionIsolation=READ_UNCOMMITTED

​ JDBCUtils_ The DBCP tool class sample code is shown below.

package jdbc.utils;
import org.apache.commons.dbcp.BasicDataSourceFactory;
import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
public class JDBCUtils_DBCP {
    private static DataSource dataSource = null;
    //Static code is fast (only executed when the class is first loaded)
    static {
        try {
            InputStream inputStream = JDBCUtils_DBCP.class.getClassLoader().getResourceAsStream("resources/db_dbcp.properties");
            Properties properties = new Properties();
            properties.load(inputStream);
            //Create data source (database connection pool): fixed mode, created using factory mode
            dataSource = BasicDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //Get database connection
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();//Get connection from data source (database connection pool)
    }
    //Release database connection object resources
    public static void release(Connection conn, PreparedStatement psta, ResultSet res) {
        if (res != null) {
            try {
                res.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (psta != null) {
            try {
                psta.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

20.4.1.2. Add query, delete and modify

• 20.4.1.2.1. Add (Create)

  Its sample code is shown below.

  package jdbc.dbcp;
  import jdbc.utils.JDBCUtils_DBCP;
  import java.sql.*;
  //SQL statement add operation
  public class InsertSql {
      public static void main(String[] args) {
          Connection conn=null;
          PreparedStatement psta=null;
          ResultSet res=null;
          try {
              //Get database connection object
              conn= JDBCUtils_DBCP.getConnection();
              //SQL statement string: use placeholder (?) instead of parameter
              String sql="insert into student(name,pwd,sex,birthday,gradeid) values(?,?,?,?,?)";
              //Obtain the specified object of SQL statement: precompiled SQL, write SQL first, and do not execute SQL
              psta= conn.prepareStatement(sql);
              //Assign values to the parameters manually and correspond one by one according to the sequence of field columns
              psta.setString(1,"Xueshan");
              psta.setString(2,"123456789");
              psta.setString(3,"male");
              /**
               * Special attention:
               * 1,Java Date time: java.util.Date(), Java timestamp: java.util.Date().getTime();
               * 2,Database date and time: java.sql.Date(); Database timestamp: java.sql.Date().getTime();
               */
              psta.setDate(4,new Date(new java.util.Date().getTime()));
              psta.setInt(5,1);
              int rn=psta.executeUpdate();//Execute SQL statement
              if(rn>0){
                  System.out.println("Insert data succeeded");
              }
          } catch (SQLException throwables) {
              throwables.printStackTrace();
          }finally{
              JDBCUtils_DBCP.release(conn,psta,res);
          }
      }
  }
  

  The operation results are as follows.

  Insert data succeeded
  

• 20.4.1.2.2. Delete (delete)

  Its sample code is shown below.

  package jdbc.dbcp;
  import jdbc.utils.JDBCUtils_DBCP;
  import java.sql.Connection;
  import java.sql.PreparedStatement;
  import java.sql.ResultSet;
  import java.sql.SQLException;
  //SQL statement delete operation
  public class DeleteSql {
      public static void main(String[] args) {
          Connection conn=null;
          PreparedStatement psta=null;
          ResultSet res=null;
          try {
              //Get database connection object
              conn= JDBCUtils_DBCP.getConnection();
              //SQL statement string: use placeholder (?) instead of parameter
              String sql="delete from student where name=?";
              //Obtain the specified object of SQL statement: precompiled SQL, write SQL first, and do not execute SQL
              psta= conn.prepareStatement(sql);
              //Assign values to the parameters manually and correspond one by one according to the sequence of field columns
              psta.setString(1,"persevere in one's studies until one reaches the goal");
              int rn=psta.executeUpdate();//Execute SQL statement
              if(rn>0){
                  System.out.println("Data deleted successfully");
              }
          } catch (SQLException throwables) {
              throwables.printStackTrace();
          }finally{
              JDBCUtils_DBCP.release(conn,psta,res);
          }
      }
  }
  

  The operation results are as follows.

  Data deleted successfully
  

• 20.4.1.2.3. Change operation (Update operation)

  Its sample code is shown below.

  package jdbc.dbcp;
  import jdbc.utils.JDBCUtils_DBCP;
  import java.sql.Connection;
  import java.sql.PreparedStatement;
  import java.sql.ResultSet;
  import java.sql.SQLException;
  //SQL statement modification
  public class UpdateSQL {
      public static void main(String[] args) {
          Connection conn=null;
          PreparedStatement psta=null;
          ResultSet res=null;
          try {
              //Get database connection object
              conn= JDBCUtils_DBCP.getConnection();
              //SQL statement string: use placeholder (?) instead of parameter
              String sql="update student set name=? where name=?";
              //Obtain the specified object of SQL statement: precompiled SQL, write SQL first, and do not execute SQL
              psta= conn.prepareStatement(sql);
              //Assign values to the parameters manually and correspond one by one according to the sequence of field columns
              psta.setString(1,"persevere in one's studies until one reaches the goal");
              psta.setString(2,"Xueshan");
              int rn=psta.executeUpdate();//Execute SQL statement
              if(rn>0){
                  System.out.println("Data update succeeded");
              }
          } catch (SQLException throwables) {
              throwables.printStackTrace();
          }finally{
              JDBCUtils_DBCP.release(conn,psta,res);
          }
      }
  }
  

  The operation results are as follows.

  Data update succeeded
  

• 20.4.1.2.4. Retrieve operation

  Its sample code is shown below.

  package jdbc.dbcp;
  import jdbc.utils.JDBCUtils_DBCP;
  import java.sql.Connection;
  import java.sql.PreparedStatement;
  import java.sql.ResultSet;
  import java.sql.SQLException;
  //SQL statement query operation
  public class SelectSql {
      public static void main(String[] args) {
          Connection conn=null;
          PreparedStatement psta=null;
          ResultSet res=null;
          try {
              //Get database connection object
              conn= JDBCUtils_DBCP.getConnection();
              //SQL statement string: use placeholder (?) instead of parameter
              String sql="select name,pwd,sex,birthday,gradeid from student where name=?";
              //Obtain the specified object of SQL statement: precompiled SQL, write SQL first, and do not execute SQL
              psta= conn.prepareStatement(sql);
              //Assign values to the parameters manually and correspond one by one according to the sequence of field columns
              psta.setString(1,"Zhang San");
              res=psta.executeQuery();//Execute SQL statement
              while(res.next()){
                  System.out.println("name="+res.getString("name"));
                  System.out.println("pwd="+res.getString("pwd"));
                  System.out.println("sex="+res.getString("sex"));
                  System.out.println("birthday="+res.getDate("birthday"));
                  System.out.println("gradeid="+res.getInt("gradeid"));
              }
          } catch (SQLException throwables) {
              throwables.printStackTrace();
          }finally{
              JDBCUtils_DBCP.release(conn,psta,res);
          }
      }
  }
  

  The operation results are as follows.

  name=Zhang San
  pwd=12321
  sex=male
  birthday=2021-10-03
  gradeid=3
  

20.4.2 C3P0 database connection pool

Prerequisite: first import the corresponding jar package (i.e. c3p0-0.9.5.5.jar, mchange-commons-java-0.2.19.jar). Please refer to the previous operation "import database driver file (i.e. MySQL connector-java-5.1.47. Jar)".

20.4.2.1 extraction tools

The contents of the configuration file (c3p0-config.xml) are as follows.

<?xml version="1.0" encoding="UTF-8"?>
<!--This profile name must be“ c3p0-config"，And this configuration file is placed in the project“ src"Under the root directory, otherwise it cannot be loaded and used normally-->
<c3p0-config>
    <!--
    C3P0 Default(default)Configuration: the corresponding code is“ ComboPooledDataSource dataSource = new ComboPooledDataSource();". 
    -->
    <default-config>
        <!--Database driver name-->
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <!--url，Special attention should be paid to when the database installation version is lower than the database driver version“ useSSL Parameter set value must be"useSSL=true"，Otherwise, it must be"useSSL=false". -->
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbc_test?useUnicode=true&amp;characterEncoding=utf8&amp;useSSL=false</property>
        <!--user name-->
        <property name="user">root</property>
        <!--password-->
        <property name="password">123456</property>
        <!--If the data connection in the database connection pool is insufficient, how many data connections will be increased at a time-->
        <property name="acquireIncrement">5</property>
        <!--Number of initialized database connection pools-->
        <property name="initialPoolSize">10</property>
        <!--Maximum number of connections to the database connection pool-->
        <property name="maxPoolSize">20</property>
        <!--Minimum number of connections to the database connection pool-->
        <property name="minPoolSize">2</property>
        <!--How many batch statements can be executed at a time-->
        <property name="maxStatements">30</property>
    </default-config>
    <!--
    C3P0 User defined naming configuration: the corresponding code is“ ComboPooledDataSource dataSource = new ComboPooledDataSource("MySQL");". 
    -->
    <named-config name="MySQL">
        <!--Database driver name-->
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <!--url，Special attention should be paid to when the database installation version is lower than the database driver version“ useSSL Parameter set value must be"useSSL=true"，Otherwise, it must be"useSSL=false". -->
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbc_test?useUnicode=true&amp;characterEncoding=utf8&amp;useSSL=false</property>
        <!--user name-->
        <property name="user">root</property>
        <!--password-->
        <property name="password">123456</property>
        <!--If the data connection in the database connection pool is insufficient, how many data connections will be increased at a time-->
        <property name="acquireIncrement">5</property>
        <!--Number of initialized database connection pools-->
        <property name="initialPoolSize">10</property>
        <!--Maximum number of connections to the database connection pool-->
        <property name="maxPoolSize">20</property>
        <!--Minimum number of connections to the database connection pool-->
        <property name="minPoolSize">2</property>
        <!--How many batch statements can be executed at a time-->
        <property name="maxStatements">30</property>
    </named-config>
</c3p0-config>

The sample code of JDBCUtils_C3P0 tool class is as follows.

package jdbc.utils;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class JDBCUtils_C3P0 {
    private static ComboPooledDataSource dataSource = null;
    //Static code is fast (only executed when the class is first loaded)
    static {
        try {
//            //Method 1: code configuration version
//            dataSource=new ComboPooledDataSource();
//            dataSource.setDriverClass("");
//            dataSource.setJdbcUrl("");
//            dataSource.setUser("");
//            dataSource.setPassword("");
//            dataSource.setMaxPoolSize(20);
//            ......
            //Method 2: xml file configuration version, creating data source (database connection pool): fixed mode, using factory mode
            dataSource = new ComboPooledDataSource("MySQL");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //Get database connection
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();//Get connection from data source (database connection pool)
    }
    //Release database connection object resources
    public static void release(Connection conn, PreparedStatement psta, ResultSet res) {
        if (res != null) {
            try {
                res.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (psta != null) {
            try {
                psta.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

20.4.2.2. Add query, delete and modify

• 20.4.2.2.1. Add (Create)

  Its sample code is shown below.

  package jdbc.c3p0;
  import jdbc.utils.JDBCUtils_C3P0;
  import java.sql.*;
  //SQL statement add operation
  public class InsertSql {
      public static void main(String[] args) {
          Connection conn=null;
          PreparedStatement psta=null;
          ResultSet res=null;
          try {
              //Get database connection object
              conn= JDBCUtils_C3P0.getConnection();
              //SQL statement string: use placeholder (?) instead of parameter
              String sql="insert into student(name,pwd,sex,birthday,gradeid) values(?,?,?,?,?)";
              //Obtain the specified object of SQL statement: precompiled SQL, write SQL first, and do not execute SQL
              psta= conn.prepareStatement(sql);
              //Assign values to the parameters manually and correspond one by one according to the sequence of field columns
              psta.setString(1,"Xueshan");
              psta.setString(2,"123456789");
              psta.setString(3,"male");
              /**
               * Special attention:
               * 1,Java Date time: java.util.Date(), Java timestamp: java.util.Date().getTime();
               * 2,Database date and time: java.sql.Date(); Database timestamp: java.sql.Date().getTime();
               */
              psta.setDate(4,new Date(new java.util.Date().getTime()));
              psta.setInt(5,1);
              int rn=psta.executeUpdate();//Execute SQL statement
              if(rn>0){
                  System.out.println("Insert data succeeded");
              }
          } catch (SQLException throwables) {
              throwables.printStackTrace();
          }finally{
              JDBCUtils_C3P0.release(conn,psta,res);
          }
      }
  }
  

  The operation results are as follows.

  10 April, 2021 3:55:09 afternoon com.mchange.v2.log.MLog 
  information: MLog clients using java 1.4+ standard logging.
  10 April, 2021 3:55:09 afternoon com.mchange.v2.c3p0.C3P0Registry 
  information: Initializing c3p0-0.9.5.5 [built 11-December-2019 22:07:46 -0800; debug? true; trace: 10]
  10 April, 2021 3:55:09 afternoon com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource 
  information: Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 5, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, contextClassLoaderSource -> caller, dataSourceName -> MySQL, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.jdbc.Driver, extensions -> {}, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, forceSynchronousCheckins -> false, forceUseNamedDriverClass -> false, identityToken -> 2t2kpaakkm5m7ikcfb80|9629756, idleConnectionTestPeriod -> 0, initialPoolSize -> 10, jdbcUrl -> jdbc:mysql://localhost:3306/jdbc_test?useUnicode=true&characterEncoding=utf8&useSSL=false, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 0, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 20, maxStatements -> 30, maxStatementsPerConnection -> 0, minPoolSize -> 2, numHelperThreads -> 3, preferredTestQuery -> null, privilegeSpawnedThreads -> false, properties -> {password=******, user=******}, propertyCycle -> 0, statementCacheNumDeferredCloseThreads -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, userOverrides -> {}, usesTraditionalReflectiveProxies -> false ]
  Insert data succeeded
  

• 20.4.2.2.2. Delete (delete)

  Its sample code is shown below.

  package jdbc.c3p0;
  import jdbc.utils.JDBCUtils_C3P0;
  import java.sql.Connection;
  import java.sql.PreparedStatement;
  import java.sql.ResultSet;
  import java.sql.SQLException;
  //SQL statement delete operation
  public class DeleteSql {
      public static void main(String[] args) {
          Connection conn=null;
          PreparedStatement psta=null;
          ResultSet res=null;
          try {
              //Get database connection object
              conn= JDBCUtils_C3P0.getConnection();
              //SQL statement string: use placeholder (?) instead of parameter
              String sql="delete from student where name=?";
              //Obtain the specified object of SQL statement: precompiled SQL, write SQL first, and do not execute SQL
              psta= conn.prepareStatement(sql);
              //Assign values to the parameters manually and correspond one by one according to the sequence of field columns
              psta.setString(1,"persevere in one's studies until one reaches the goal");
              int rn=psta.executeUpdate();//Execute SQL statement
              if(rn>0){
                  System.out.println("Data deleted successfully");
              }
          } catch (SQLException throwables) {
              throwables.printStackTrace();
          }finally{
              JDBCUtils_C3P0.release(conn,psta,res);
          }
      }
  }
  

  The operation results are as follows.

  10 April, 2021 4:00:48 afternoon com.mchange.v2.log.MLog 
  information: MLog clients using java 1.4+ standard logging.
  10 April, 2021 4:00:48 afternoon com.mchange.v2.c3p0.C3P0Registry 
  information: Initializing c3p0-0.9.5.5 [built 11-December-2019 22:07:46 -0800; debug? true; trace: 10]
  10 April, 2021 4:00:48 afternoon com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource 
  information: Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 5, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, contextClassLoaderSource -> caller, dataSourceName -> MySQL, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.jdbc.Driver, extensions -> {}, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, forceSynchronousCheckins -> false, forceUseNamedDriverClass -> false, identityToken -> 2t2kpaakkmcvq01omtpg3|9629756, idleConnectionTestPeriod -> 0, initialPoolSize -> 10, jdbcUrl -> jdbc:mysql://localhost:3306/jdbc_test?useUnicode=true&characterEncoding=utf8&useSSL=false, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 0, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 20, maxStatements -> 30, maxStatementsPerConnection -> 0, minPoolSize -> 2, numHelperThreads -> 3, preferredTestQuery -> null, privilegeSpawnedThreads -> false, properties -> {password=******, user=******}, propertyCycle -> 0, statementCacheNumDeferredCloseThreads -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, userOverrides -> {}, usesTraditionalReflectiveProxies -> false ]
  Data deleted successfully
  

• 20.4.2.2.3. Change operation (Update operation)

  Its sample code is shown below.

  package jdbc.c3p0;
  import jdbc.utils.JDBCUtils_C3P0;
  import java.sql.Connection;
  import java.sql.PreparedStatement;
  import java.sql.ResultSet;
  import java.sql.SQLException;
  //SQL statement modification
  public class UpdateSQL {
      public static void main(String[] args) {
          Connection conn=null;
          PreparedStatement psta=null;
          ResultSet res=null;
          try {
              //Get database connection object
              conn= JDBCUtils_C3P0.getConnection();
              //SQL statement string: use placeholder (?) instead of parameter
              String sql="update student set name=? where name=?";
              //Obtain the specified object of SQL statement: precompiled SQL, write SQL first, and do not execute SQL
              psta= conn.prepareStatement(sql);
              //Assign values to the parameters manually and correspond one by one according to the sequence of field columns
              psta.setString(1,"persevere in one's studies until one reaches the goal");
              psta.setString(2,"Xueshan");
              int rn=psta.executeUpdate();//Execute SQL statement
              if(rn>0){
                  System.out.println("Data update succeeded");
              }
          } catch (SQLException throwables) {
              throwables.printStackTrace();
          }finally{
              JDBCUtils_C3P0.release(conn,psta,res);
          }
      }
  }
  

  The operation results are as follows.

  10 April, 2021 3:59:38 afternoon com.mchange.v2.log.MLog 
  information: MLog clients using java 1.4+ standard logging.
  10 April, 2021 3:59:39 afternoon com.mchange.v2.c3p0.C3P0Registry 
  information: Initializing c3p0-0.9.5.5 [built 11-December-2019 22:07:46 -0800; debug? true; trace: 10]
  10 April, 2021 3:59:39 afternoon com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource 
  information: Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 5, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, contextClassLoaderSource -> caller, dataSourceName -> MySQL, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.jdbc.Driver, extensions -> {}, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, forceSynchronousCheckins -> false, forceUseNamedDriverClass -> false, identityToken -> 2t2kpaakkmbe2l1wto9dk|9629756, idleConnectionTestPeriod -> 0, initialPoolSize -> 10, jdbcUrl -> jdbc:mysql://localhost:3306/jdbc_test?useUnicode=true&characterEncoding=utf8&useSSL=false, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 0, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 20, maxStatements -> 30, maxStatementsPerConnection -> 0, minPoolSize -> 2, numHelperThreads -> 3, preferredTestQuery -> null, privilegeSpawnedThreads -> false, properties -> {password=******, user=******}, propertyCycle -> 0, statementCacheNumDeferredCloseThreads -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, userOverrides -> {}, usesTraditionalReflectiveProxies -> false ]
  Data update succeeded
  

• 20.4.2.2.4. Retrieve operation

  Its sample code is shown below.

  package jdbc.c3p0;
  import jdbc.utils.JDBCUtils_C3P0;
  import java.sql.Connection;
  import java.sql.PreparedStatement;
  import java.sql.ResultSet;
  import java.sql.SQLException;
  //SQL statement query operation
  public class SelectSql {
      public static void main(String[] args) {
          Connection conn=null;
          PreparedStatement psta=null;
          ResultSet res=null;
          try {
              //Get database connection object
              conn= JDBCUtils_C3P0.getConnection();
              //SQL statement string: use placeholder (?) instead of parameter
              String sql="select name,pwd,sex,birthday,gradeid from student where name=?";
              //Obtain the specified object of SQL statement: precompiled SQL, write SQL first, and do not execute SQL
              psta= conn.prepareStatement(sql);
              //Assign values to the parameters manually and correspond one by one according to the sequence of field columns
              psta.setString(1,"Zhang San");
              res=psta.executeQuery();//Execute SQL statement
              while(res.next()){
                  System.out.println("name="+res.getString("name"));
                  System.out.println("pwd="+res.getString("pwd"));
                  System.out.println("sex="+res.getString("sex"));
                  System.out.println("birthday="+res.getDate("birthday"));
                  System.out.println("gradeid="+res.getInt("gradeid"));
              }
          } catch (SQLException throwables) {
              throwables.printStackTrace();
          }finally{
              JDBCUtils_C3P0.release(conn,psta,res);
          }
      }
  }
  

  The operation results are as follows.

  10 April, 2021 4:02:11 afternoon com.mchange.v2.log.MLog 
  information: MLog clients using java 1.4+ standard logging.
  10 April, 2021 4:02:11 afternoon com.mchange.v2.c3p0.C3P0Registry 
  information: Initializing c3p0-0.9.5.5 [built 11-December-2019 22:07:46 -0800; debug? true; trace: 10]
  10 April, 2021 4:02:11 afternoon com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource 
  information: Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 5, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, contextClassLoaderSource -> caller, dataSourceName -> MySQL, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.jdbc.Driver, extensions -> {}, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, forceSynchronousCheckins -> false, forceUseNamedDriverClass -> false, identityToken -> 2t2kpaakkmenid9n5xs1|9629756, idleConnectionTestPeriod -> 0, initialPoolSize -> 10, jdbcUrl -> jdbc:mysql://localhost:3306/jdbc_test?useUnicode=true&characterEncoding=utf8&useSSL=false, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 0, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 20, maxStatements -> 30, maxStatementsPerConnection -> 0, minPoolSize -> 2, numHelperThreads -> 3, preferredTestQuery -> null, privilegeSpawnedThreads -> false, properties -> {password=******, user=******}, propertyCycle -> 0, statementCacheNumDeferredCloseThreads -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, userOverrides -> {}, usesTraditionalReflectiveProxies -> false ]
  name=Zhang San
  pwd=12321
  sex=male
  birthday=2021-10-03
  gradeid=3
  

20.5. Use IDEA related tool classes operation database transaction

20.5.1. Using JDBC utils_ Sta tool class operates database transactions

Its sample code is shown below.

package jdbc.transaction;
import jdbc.utils.JDBCUtils_STA;
import java.sql.*;
//Database transaction operation
public class Transaction_STA {
    public static void main(String[] args) {
        Connection conn = null;
        Statement sta = null;
        ResultSet res = null;
        try {
            conn = JDBCUtils_STA.getConnection();//Get database connection object
            conn.setAutoCommit(false);//Turn off automatic submission of database transactions (the default parameter is true, which means automatic submission of transactions)
            sta = conn.createStatement();//Gets the object specified in the SQL statement
            String sql = "update student set name='persevere in one's studies until one reaches the goal' where name='Xueshan'";//SQL statement string
            sta.executeUpdate(sql);//Execute SQL statement
            sql = "update student set sex='female' where name='persevere in one's studies until one reaches the goal'";
            sta.executeUpdate(sql);
            conn.commit();//Business completion, commit transaction
            System.out.println("Transaction committed successfully");
        } catch (SQLException throwables) {
            try {
                throwables.printStackTrace();
                System.out.println("Transaction commit failed");
                conn.rollback();//Business failure, rollback transaction
                System.out.println("Transaction rollback succeeded");
            } catch (SQLException e) {
                System.out.println("Transaction rollback failed");
                e.printStackTrace();
            }
        } finally {
            JDBCUtils_STA.release(conn, sta, res);
        }
    }
}

The operation results are as follows.

Transaction committed successfully

20.5.2. Using JDBC utils_ PstA tool class operates database transactions

Its sample code is shown below.

package jdbc.transaction;
import jdbc.utils.JDBCUtils_PSTA;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
//Database transaction operation
public class Transaction_PSTA {
    public static void main(String[] args) {
        Connection conn=null;
        PreparedStatement psta=null;
        ResultSet res=null;
        try {
            //Get database connection object
            conn= JDBCUtils_PSTA.getConnection();
            //Turn off automatic submission of database transactions (the default parameter is true, which means automatic submission of transactions)
            conn.setAutoCommit(false);
            //SQL statement string: use placeholder (?) instead of parameter
            String sql="update student set name=? where name=?";
            //Obtain the specified object of SQL statement: precompiled SQL, write SQL first, and do not execute SQL
            psta= conn.prepareStatement(sql);
            //Assign values to the parameters manually and correspond one by one according to the sequence of field columns
            psta.setString(1,"persevere in one's studies until one reaches the goal");
            psta.setString(2,"Xueshan");
            psta.executeUpdate();//Execute SQL statement
            //SQL statement string: use placeholder (?) instead of parameter
            sql="update student set sex=? where name=?";
            //Obtain the specified object of SQL statement: precompiled SQL, write SQL first, and do not execute SQL
            psta= conn.prepareStatement(sql);
            //Assign values to the parameters manually and correspond one by one according to the sequence of field columns
            psta.setString(1,"female");
            psta.setString(2,"persevere in one's studies until one reaches the goal");
            psta.executeUpdate();//Execute SQL statement
            conn.commit();//Business completion, commit transaction
            System.out.println("Transaction committed successfully");
        } catch (SQLException throwables) {
            try {
                throwables.printStackTrace();
                System.out.println("Transaction commit failed");
                conn.rollback();//Business failure, rollback transaction
                System.out.println("Transaction rollback succeeded");
            } catch (SQLException e) {
                System.out.println("Transaction rollback failed");
                e.printStackTrace();
            }
        }finally{
            JDBCUtils_PSTA.release(conn,psta,res);
        }
    }
}

The operation results are as follows.

Transaction committed successfully

20.5.3. Using JDBC utils_ DBCP tool class operates database transactions

Its sample code is shown below.

package jdbc.transaction;
import jdbc.utils.JDBCUtils_DBCP;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
//Database transaction operation
public class Transaction_DBCP {
    public static void main(String[] args) {
        Connection conn=null;
        PreparedStatement psta=null;
        ResultSet res=null;
        try {
            //Get database connection object
            conn= JDBCUtils_DBCP.getConnection();
            //Turn off automatic submission of database transactions (the default parameter is true, which means automatic submission of transactions)
            conn.setAutoCommit(false);
            //SQL statement string: use placeholder (?) instead of parameter
            String sql="update student set name=? where name=?";
            //Obtain the specified object of SQL statement: precompiled SQL, write SQL first, and do not execute SQL
            psta= conn.prepareStatement(sql);
            //Assign values to the parameters manually and correspond one by one according to the sequence of field columns
            psta.setString(1,"persevere in one's studies until one reaches the goal");
            psta.setString(2,"Xueshan");
            psta.executeUpdate();//Execute SQL statement
            //SQL statement string: use placeholder (?) instead of parameter
            sql="update student set sex=? where name=?";
            //Obtain the specified object of SQL statement: precompiled SQL, write SQL first, and do not execute SQL
            psta= conn.prepareStatement(sql);
            //Assign values to the parameters manually and correspond one by one according to the sequence of field columns
            psta.setString(1,"female");
            psta.setString(2,"persevere in one's studies until one reaches the goal");
            psta.executeUpdate();//Execute SQL statement
            conn.commit();//Business completion, commit transaction
            System.out.println("Transaction committed successfully");
        } catch (SQLException throwables) {
            try {
                throwables.printStackTrace();
                System.out.println("Transaction commit failed");
                conn.rollback();//Business failure, rollback transaction
                System.out.println("Transaction rollback succeeded");
            } catch (SQLException e) {
                System.out.println("Transaction rollback failed");
                e.printStackTrace();
            }
        }finally{
            JDBCUtils_DBCP.release(conn,psta,res);
        }
    }
}

The operation results are as follows.

Transaction committed successfully

20.5.4. Using JDBC utils_ C3p0 tool class operates database transactions

Its sample code is shown below.

package jdbc.transaction;
import jdbc.utils.JDBCUtils_C3P0;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
//Database transaction operation
public class Transaction_C3P0 {
    public static void main(String[] args) {
        Connection conn=null;
        PreparedStatement psta=null;
        ResultSet res=null;
        try {
            //Get database connection object
            conn= JDBCUtils_C3P0.getConnection();
            //Turn off automatic submission of database transactions (the default parameter is true, which means automatic submission of transactions)
            conn.setAutoCommit(false);
            //SQL statement string: use placeholder (?) instead of parameter
            String sql="update student set name=? where name=?";
            //Obtain the specified object of SQL statement: precompiled SQL, write SQL first, and do not execute SQL
            psta= conn.prepareStatement(sql);
            //Assign values to the parameters manually and correspond one by one according to the sequence of field columns
            psta.setString(1,"persevere in one's studies until one reaches the goal");
            psta.setString(2,"Xueshan");
            psta.executeUpdate();//Execute SQL statement
            //SQL statement string: use placeholder (?) instead of parameter
            sql="update student set sex=? where name=?";
            //Obtain the specified object of SQL statement: precompiled SQL, write SQL first, and do not execute SQL
            psta= conn.prepareStatement(sql);
            //Assign values to the parameters manually and correspond one by one according to the sequence of field columns
            psta.setString(1,"female");
            psta.setString(2,"persevere in one's studies until one reaches the goal");
            psta.executeUpdate();//Execute SQL statement
            conn.commit();//Business completion, commit transaction
            System.out.println("Transaction committed successfully");
        } catch (SQLException throwables) {
            try {
                throwables.printStackTrace();
                System.out.println("Transaction commit failed");
                conn.rollback();//Business failure, rollback transaction
                System.out.println("Transaction rollback succeeded");
            } catch (SQLException e) {
                System.out.println("Transaction rollback failed");
                e.printStackTrace();
            }
        }finally{
            JDBCUtils_C3P0.release(conn,psta,res);
        }
    }
}

The operation results are as follows.

10 April, 2021 4:42:08 afternoon com.mchange.v2.log.MLog 
information: MLog clients using java 1.4+ standard logging.
10 April, 2021 4:42:08 afternoon com.mchange.v2.c3p0.C3P0Registry 
information: Initializing c3p0-0.9.5.5 [built 11-December-2019 22:07:46 -0800; debug? true; trace: 10]
10 April, 2021 4:42:09 afternoon com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource 
information: Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 5, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, contextClassLoaderSource -> caller, dataSourceName -> MySQL, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.jdbc.Driver, extensions -> {}, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, forceSynchronousCheckins -> false, forceUseNamedDriverClass -> false, identityToken -> 2t2kpaakknu1itbsr0ym|9629756, idleConnectionTestPeriod -> 0, initialPoolSize -> 10, jdbcUrl -> jdbc:mysql://localhost:3306/jdbc_test?useUnicode=true&characterEncoding=utf8&useSSL=false, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 0, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 20, maxStatements -> 30, maxStatementsPerConnection -> 0, minPoolSize -> 2, numHelperThreads -> 3, preferredTestQuery -> null, privilegeSpawnedThreads -> false, properties -> {password=******, user=******}, propertyCycle -> 0, statementCacheNumDeferredCloseThreads -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, userOverrides -> {}, usesTraditionalReflectiveProxies -> false ]
Transaction committed successfully

Reference data: mySQL,Concepts and differences of DQL, DML, DDL and DCL,MD5,Transaction ACID

Learning website address (i.e. "learning website address"): MySQL in Java