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&characterEncoding=utf8&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&characterEncoding=utf8&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