MySQL advanced + JDBC actual combat
1, Database integrity
Data integrity (accuracy of data)
Function: ensure that the data entered by the user is saved correctly in the database.
Ensure data integrity = add constraints to the table when creating a table
1.1 physical integrity
Entity: that is, a row in the table represents an entity
Function of entity integrity: it means that each row of data is not repeated.
All constraints should be specified when creating tables.
1.1.1 primary key constraint
Each table should have a primary key, that is, each table needs to create a column of primary keys.
Features: the data is unique (not duplicate) and cannot be null.
The primary key column generally selects int type.
Primary key = unique constraint + non empty constraint (functionally)
1.1.2 auto_increment
Apply to int column.
Indicates the automatic growth of the value.
Often used with primary key columns.
1.1.3 unique constraint
Duplicate is not allowed, but null is allowed.
1.2 domain integrity
Function: restrict the data of the cell to be correct, and do not compare with other cells in this column.
Field represents the current cell
Domain integrity constraint: data type non NULL constraint (not null) default value constraint (default)
1.2.1 data type
1.2.2 non NULL constraint
The column must have a value.
1.2.3 default value constraint (default)
When the user does not specify the value of this column, it will be given according to the default value.
1.3 referential integrity
1.3.1 foreign key constraints
Based on multi table.
In order to ensure the accurate reference relationship between tables.
Foreign key constraints should be added to the foreign key table.
In the actual development, in order to ensure the efficiency in the database, some companies will require that foreign key constraints are not created, but foreign key relationships are saved.
2, JDBC
2.1 introduction to JDBC
java database connectivity is a java API for executing SQL statements. It can provide unified access to a variety of relational databases. It is composed of a group of classes and interfaces written in java language.
2.2 JDBC architecture
2.3 JDBC core components
DriverManager: this class manages the list of database drivers. Use the communication sub protocol to match connection requests from java applications with the appropriate database drivers.
Driver: this interface handles the communication with the database server. We rarely interact directly with the driver object, but use the DriverManager object to manage this type of object.
Connection: this interface has all the methods used to contact the database. The connection object represents the communication context, that is, all communication with the database is only through the connection object.
Statement: objects created using this interface submit SQL statements to the database. Some derived interfaces accept parameters in addition to executing stored procedures.
ResultSet: after executing SQL queries using Statement objects, these objects store the data retrieved from the database. It acts as an iterator and allows us to move its data.
SQLException: this class handles any errors that occur in the database application.
2.4 overall use steps
Note: the version of the jar package and the version of the database should match
Building a JDBC application involves the following six steps:
- Import package: a package containing JDBC classes required for database programming is required. In most cases, import java.sql is sufficient.
- Register JDBC Driver: initialize the driver so that the communication channel with the database can be opened.
- Open Connection: you need to use the DriverManager.getConnection() method to create a Connection object that represents the physical Connection to the database.
- Execute query: you need to use an object of type Statement/PrepareStatement to build and submit SQL statements to the database.
- Extract data from the result set: you need to use the corresponding ResultSet.getXXX() method to retrieve data from the result set.
- Free resources: you need to explicitly shut down all database resources instead of relying on jvm garbage collection.
example:
public static void select() {
// JDBC operation database - query all
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try{
// 1. Load driver 8.0 com.mysql.cj.jdbc.Driver 5.1 com.mysql.jdbc.Driver
Class.forName("com.mysql.cj.jdbc.Driver");
// 2. Get connected
// url = "jdbc:mysql://IP: Port number / database name? Characterencoding = utf8 & usessl = false & servertimezone = UTC ";
String url = "jdbc:mysql://localhost:3306/mydemo1?characterEncoding=utf8&useSSL=false&serverTimeZone=UTC";
String username = "root";
String password = "123456";
connection = DriverManager.getConnection(url, username, password);
// 3. Get the status Channel - establish an SQL channel between java and the database
statement = connection.createStatement();
String sql = "select * from mystudent";
// Execute query: executeQuery()
// resultSet result set - the result of the query (virtual table)
// 4. Execute SQL to get the returned result
resultSet = statement.executeQuery(sql);
// 5. Parse result set
while(resultSet.next()) {
int stuno = resultSet.getInt("stuno"); // resultSet.getXXX("column name")
String stuname = resultSet.getString("stuname");
Date birthday = resultSet.getDate("birthday");
System.out.println("stuno:" + stuno + ",stuname:" + stuname + ",birthday:" + birthday);
}
} catch (Exception e) {
System.out.println("Abnormal");
} finally {
// 6. Close the Connection, Statement and ResultSet
try {
// security
if (connection != null) {
connection.close();
}
if (statement != null) {
statement.close();
}
if (resultSet != null) {
resultSet.close();
}
} catch (SQLException e) {
System.out.println("SQLException");
}
}
}
// PrepareStatement precompiled SQL prevents SQL injection (can be used for user login authentication)
public static void select2(String uname, String upass) {
// JDBC operation database - query all
Connection connection = null;
PrepareStatement pps = null;
ResultSet resultSet = null;
try{
// 1. Load driver 8.0 com.mysql.cj.jdbc.Driver 5.1 com.mysql.jdbc.Driver
Class.forName("com.mysql.cj.jdbc.Driver");
// 2. Get connected
// url = "jdbc:mysql://IP: Port number / database name? Characterencoding = utf8 & usessl = false & servertimezone = UTC ";
String url = "jdbc:mysql://localhost:3306/mydemo1?characterEncoding=utf8&useSSL=false&serverTimeZone=UTC";
String username = "root";
String password = "123456";
connection = DriverManager.getConnection(url, username, password);
// 3. Get the status Channel - establish an SQL channel between java and the database - compile SQL in advance
// ? indicates occupancy
String sql = "select * from student where stuname=? and password=?";
pps = connection.prepareStatement(sql);
// Assign values to placeholders
pps.setString(1, uname);
pps.setString(2, upass);
// Execute query: executeQuery()
// resultSet result set - the result of the query (virtual table)
// 4. Execute SQL to get the returned result
resultSet = pps.executeQuery();
// 5. Parse result set
if (resultSet.next()) {
System.out.println("Login succeeded");
} else {
System.out.println("Login failed");
}
} catch (Exception e) {
System.out.println("Abnormal");
} finally {
// 6. Close the Connection, Statement and ResultSet
try {
// security
if (connection != null) {
connection.close();
}
if (statement != null) {
statement.close();
}
if (resultSet != null) {
resultSet.close();
}
} catch (SQLException e) {
System.out.println("SQLException");
}
}
}
public static void insert() {
Connection connection = null;
Statement statement = null;
//ResultSet resultSet = null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/mydemo1?characterEncoding=utf8&useSSL=false&serverTimezone=UTC";
connection = DriverManager.getConnection(url, "root", "123456");
statement = connection.createStatement();
String sql = "insert into student(stuid,stuname,stusex) values(null,'Liu Nannan','female')";
// Use executeUpdate() for addition and deletion. The returned result is the number of affected rows
int count = statement.executeUpdate(sql);
System.out.println(count > 0 ? "Successfully added" : "Failed to add");
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
try {
// security
if (connection != null) {
connection.close();
}
if (statement != null) {
statement.close();
}
if (resultSet != null) {
resultSet.close();
}
} catch (SQLException e) {
System.out.println("SQLException");
}
}
}
public static void update() {
Connection connection = null;
Statement statement = null;
//ResultSet resultSet = null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/mydemo1?characterEncoding=utf8&useSSL=false&serverTimezone=UTC";
connection = DriverManager.getConnection(url, "root", "123456");
statement = connection.createStatement();
String sql = "update student set stuage=18 where stuname='Liu Nannan'";
// Use executeUpdate() for addition and deletion. The returned result is the number of affected rows
int count = statement.executeUpdate(sql);
System.out.println(count > 0 ? "Update succeeded" : "Update failed");
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
try {
// security
if (connection != null) {
connection.close();
}
if (statement != null) {
statement.close();
}
if (resultSet != null) {
resultSet.close();
}
} catch (SQLException e) {
System.out.println("SQLException");
}
}
}
public static void delete() {
Connection connection = null;
Statement statement = null;
//ResultSet resultSet = null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/mydemo1?characterEncoding=utf8&useSSL=false&serverTimezone=UTC";
connection = DriverManager.getConnection(url, "root", "123456");
statement = connection.createStatement();
String sql = "delete from student where stuid=10";
// Use executeUpdate() for addition and deletion. The returned result is the number of affected rows
int count = statement.executeUpdate(sql);
System.out.println(count > 0 ? "Delete succeeded" : "Deletion failed");
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
try {
// security
if (connection != null) {
connection.close();
}
if (statement != null) {
statement.close();
}
if (resultSet != null) {
resultSet.close();
}
} catch (SQLException e) {
System.out.println("SQLException");
}
}
}
public static void main(String[] args) {
select();
insert();
update();
delete();
}
2.5 save the result data returned in the database (use entity classes to realize data encapsulation)
public static List<Student> select2(String uname, String upass) {
List<Student> list = new ArrayList<>();
// JDBC operation database - query all
Connection connection = null;
PrepareStatement pps = null;
ResultSet resultSet = null;
try{
// 1. Load driver 8.0 com.mysql.cj.jdbc.Driver 5.1 com.mysql.jdbc.Driver
Class.forName("com.mysql.cj.jdbc.Driver");
// 2. Get connected
// url = "jdbc:mysql://IP: Port number / database name? Characterencoding = utf8 & usessl = false & servertimezone = UTC ";
String url = "jdbc:mysql://localhost:3306/mydemo1?characterEncoding=utf8&useSSL=false&serverTimeZone=UTC";
String username = "root";
String password = "123456";
connection = DriverManager.getConnection(url, username, password);
// 3. Get the status Channel - establish an SQL channel between java and the database - compile SQL in advance
// ? indicates occupancy
String sql = "select * from student";
pps = connection.prepareStatement(sql);
// Execute query: executeQuery()
// resultSet result set - the result of the query (virtual table)
// 4. Execute SQL to get the returned result
resultSet = pps.executeQuery();
// 5. Parse result set
while (resultSet.next()) {
// Data table -- > class column name -- > attribute name = = > create a class with the same function (entity class entity) according to the table structure
// A row of data in the database -- > an object in Java
// This class is self built according to the table
Student student = new Student();
/* int stuid = resultSet.getInt("stuid");
String stuname = resultSet.getString("stuname");
String stusex = resultSet.getString("stusex");
int stuage = resultSet.getInt("stuage");
int managerid = resultSet.getInt("managerid");
String password1 = resultSet.getString("password");
student.setStuId(stuid);
student.setStuName(stuname);
student.setStuSex(stusex);
student.setStuAge(stuage);
student.setManagerId(managerid);
student.setPassword(password1); */
student.setStuId(resultSet.getInt("stuid"));
student.setStuName(resultSet.getString("stuname"));
student.setStuSex(resultSet.getString("stusex"));
student.setStuAge(resultSet.getInt("stuage"));
student.setManagerId(resultSet.getInt("managerid"));
student.setPassword(resultSet.getString("password"));
// Save results
list.add(student);
}
} catch (Exception e) {
System.out.println("Abnormal");
} finally {
// 6. Close the Connection, Statement and ResultSet
try {
// security
if (connection != null) {
connection.close();
}
if (statement != null) {
statement.close();
}
if (resultSet != null) {
resultSet.close();
}
} catch (SQLException e) {
System.out.println("SQLException");
}
}
return list;
}
public static void main(String[] args) {
// Save the result data returned in the database
List<Student> students = select2();
for (Student student : students) {
System.out.println(student);
}
}
3, MySQL multi table operation
3.1 data sheet
-- ----------------------------
-- Table structure for `grade`
-- ----------------------------
DROP TABLE IF EXISTS `grade`;
CREATE TABLE `grade` (
`gid` int NOT NULL AUTO_INCREMENT,
`gname` varchar(10) COLLATE utf8_bin DEFAULT NULL,
PRIMARY KEY (`gid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
-- ----------------------------
-- Records of grade
-- ----------------------------
INSERT INTO `grade` VALUES ('1', 'first grade');
INSERT INTO `grade` VALUES ('2', 'second grade');
INSERT INTO `grade` VALUES ('3', 'Third grade');
INSERT INTO `grade` VALUES ('4', 'fourth grade');
-- ----------------------------
-- Table structure for `husband`
-- ----------------------------
DROP TABLE IF EXISTS `husband`;
CREATE TABLE `husband` (
`husid` int NOT NULL AUTO_INCREMENT,
`husname` varchar(5) COLLATE utf8_bin DEFAULT NULL,
`wifeid` int DEFAULT NULL,
PRIMARY KEY (`husid`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
-- ----------------------------
-- Records of husband
-- ----------------------------
INSERT INTO `husband` VALUES ('1', 'Deng Chao', '1');
INSERT INTO `husband` VALUES ('2', 'Liu Bei', '2');
-- ----------------------------
-- Table structure for `scores`
-- ----------------------------
DROP TABLE IF EXISTS `scores`;
CREATE TABLE `scores` (
`scoreid` int NOT NULL AUTO_INCREMENT,
`score` double DEFAULT NULL,
`stuid` int DEFAULT NULL,
`subjectid` int DEFAULT NULL,
PRIMARY KEY (`scoreid`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
-- ----------------------------
-- Records of scores
-- ----------------------------
INSERT INTO `scores` VALUES ('1', '56', '1', '1');
INSERT INTO `scores` VALUES ('2', '78', '2', '1');
INSERT INTO `scores` VALUES ('3', '67', '3', '1');
INSERT INTO `scores` VALUES ('4', '77', '4', '1');
INSERT INTO `scores` VALUES ('5', '88', '5', '1');
INSERT INTO `scores` VALUES ('6', '99', '6', '1');
INSERT INTO `scores` VALUES ('7', '90', '6', '4');
-- ----------------------------
-- Table structure for `student`
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`stuid` int NOT NULL AUTO_INCREMENT,
`stuname` varchar(10) COLLATE utf8_bin DEFAULT NULL,
`stusex` char(1) COLLATE utf8_bin DEFAULT NULL,
`stuage` int DEFAULT NULL,
`gradeid` int DEFAULT NULL,
`managerid` int DEFAULT NULL COMMENT 'Monitor's id',
`password` varchar(255) COLLATE utf8_bin DEFAULT NULL,
PRIMARY KEY (`stuid`),
KEY `fk_student_gradeid` (`gradeid`),
CONSTRAINT `fk_student_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gid`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', 'Zhang San', 'female', '18', '1', '3', '8888');
INSERT INTO `student` VALUES ('2', 'Li Si', 'female', '20', '1', '3', '8888');
INSERT INTO `student` VALUES ('3', 'Wang Wu', 'female', '22', '1', '3', '8888');
INSERT INTO `student` VALUES ('4', 'Zhang Jie', 'male', '25', '2', '5', '8888');
INSERT INTO `student` VALUES ('5', 'Teda ', 'male', '32', '2', '5', '8888');
INSERT INTO `student` VALUES ('6', 'Xiao Yang', 'male', '18', '2', '5', '8888');
INSERT INTO `student` VALUES ('7', 'Liu yi good', 'male', '18', null, null, null);
-- ----------------------------
-- Table structure for `sub`
-- ----------------------------
DROP TABLE IF EXISTS `sub`;
CREATE TABLE `sub` (
`subid` int NOT NULL AUTO_INCREMENT,
`subjectname` varchar(10) COLLATE utf8_bin DEFAULT NULL,
PRIMARY KEY (`subid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
-- ----------------------------
-- Records of sub
-- ----------------------------
INSERT INTO `sub` VALUES ('1', 'language');
INSERT INTO `sub` VALUES ('2', 'mathematics');
INSERT INTO `sub` VALUES ('3', 'English');
INSERT INTO `sub` VALUES ('4', 'Chemistry');
-- ----------------------------
-- Table structure for `wife`
-- ----------------------------
DROP TABLE IF EXISTS `wife`;
CREATE TABLE `wife` (
`wifeid` int NOT NULL AUTO_INCREMENT,
`wifename` varchar(10) COLLATE utf8_bin DEFAULT NULL,
PRIMARY KEY (`wifeid`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
-- ----------------------------
-- Records of wife
-- ----------------------------
INSERT INTO `wife` VALUES ('1', 'Sun Li');
INSERT INTO `wife` VALUES ('2', 'Sun Shangxiang');
3.2 relevant requirements demonstration
# Query the student information with id 1 (display the grade information at the same time)
select * from student where stuid=1;
select * from grade where gid=1;
# Multi table associated query -- connection query
# 1. Internal connection 2. External connection 3. Natural connection
# 1.1 equivalent connection non equivalent connection self connection
# 2.1 left external connection, right external connection and full external connection (mysql does not support it)
# 1. Internal connection: query the associated data in N tables
# First syntax:
select Listing from Table 1,Table 2,Table 3
where Table 1.Listing=Table 2.Column name (primary foreign key relationship) and Table 1/Table 2.Listing=Table 3.Column name (primary foreign key relationship)
and Other conditions
select stuname,subjectname,score from sub su,student s,scores sc
where su.subid=sc.subjectid and s.stuid=sc.stuid;
# Second syntax:
select Listing from Table 1 inner join Table 2 on Table 1.Listing=Table 2.Column name (primary foreign key relationship)
inner join Table 3 on Table 1/Table 2.Listing=Table 3.Column name (primary foreign key relationship)
where Other conditions
select stuname,subjectname,score
from sub su
inner join scores sc on su.subid=sc.subjectid
inner join student s on s.stuid=sc.stuid;
# 2. External connection query (left external connection and right external connection)
# Display all the data in the primary table, display the data in the secondary table by matching, display the data that can be matched, and fill in null if it cannot be matched
# Left lateral connection
select Listing from Main table left join Secondary table on Main table.Listing=Secondary table.Listing
# In introverted queries, the order of tables can be interchanged. In external queries, the order of primary tables and secondary tables cannot be interchanged
# Find out all grade information (show it even if there are no students)
select * from grade g left join student s on g.gid=s.gradeid;
# Query all subjects and examination information (students)
select subjectname,score from sub left join scores s
on sub.subid=s.subjectid;
# Note: 1. There are relatively few scenarios for external query
# 2. Do not change the sequence of tables at will
# 3. Commonly used for sub query
# Supplement:
# 1. Cartesian product
select * from student s,grade g;
# 2. Self connection
select s1.stuname studentname, s2.stuname managername
from student s1,student s2
where s1.managerid=s2.stuid;
# 3. Merge query
# Merge the results of two select ions into the same interface for display
# union (de duplication)
select gid,gname from grade union
select subid,subjectname from sub;
# When merging and displaying result sets, the number, type and order of columns are required to be the same
# union all displays the data in the two tables (without de duplication)
select gid,gname from grade union all
select subid,subjectname from sub;
# Subquery
# Query nested query, which takes the query statement as the condition or data table of the new query statement
# 1. Use the query result as a table for another query
select stuname,gname from
(select * from student s,grade g where s.gradeid=g,gid) sg;
# 2. Query conditions as new statements
# Find out the information of all the students who have taken the Chinese test, which is higher than Li Si's Chinese score
# Step 1: find out Li Si's Chinese score
select score from student,sub,scores
where student.stuid=scores.stuid and sub.subid=scores.subjectid
and stuname='Li Si' and subjectname='language';
# Step 2: find out the student information with higher scores than Li Si's Chinese
select score from student,sub,scores
where student.stuid=scores.stuid and sub.subid=scores.subjectid
and score>(Li Si's achievements) and subjectname='language';
# Step 3: Consolidate
select score from student,sub,scores
where student.stuid=scores.stuid and sub.subid=scores.subjectid
and score>(select score from student,sub,scores
where student.stuid=scores.stuid and sub.subid=scores.subjectid
and stuname='Li Si' and subjectname='language')
and subjectname='language';
4, Business
For the database, there will be transactions every time a user modifies the data in the table. To put it bluntly, a transaction is a set of SQL statements that either succeed or fail at the same time. It is an execution unit of database operation.
4.1 transaction overview
Database transaction refers to a series of operations performed as a single logical unit of work, either completely or not. Transaction processing ensures that data oriented resources are not permanently updated unless all operations within the transactional unit are successfully completed. By combining a set of related operations into a unit that either succeeds or fails, you can simplify error recovery and make your application more reliable. To become a transaction, a logical unit of work must meet the so-called ACID (atomicity, consistency, isolation and persistence) attributes. Transaction is the logical work unit in database operation, and the transaction management subsystem in DBMS is responsible for transaction processing.
Scenario:
# At this time, the two statements belong to the same business logic - transfer operation. One person's account balance increases and the other balance decreases update account set money=money-1000 where username='A'; # An exception occurred after executing the first statement update account set money=money+1000 where username='B'; # At this time, the first statement is executed successfully and the data is permanently saved. An exception occurs before the second statement is executed and will not be executed.
Transaction started on
-Connect to the database and execute a DML statement insert, update, or delete
-After the previous transaction, another DML statement is entered
Transaction ended on
-Execute a commit or rollback statement.
-Execute a DDL statement, such as the create table statement. In this case, the commit statement will be executed automatically.
-Execute a DDL statement, such as grant statement, in which case the commit will be executed automatically.
-Disconnect from database
-A DML statement is executed but fails. In this case, a rollback statement is executed for the invalid DML statement.
4.2 four characteristics of affairs
(ACID)
-Atomicity
Indicates that all operations within a transaction are a whole, either all successful or all failed.
-Consistency
Indicates that when an operation fails in a transaction, all changed data must be rolled back to the state before modification.
-Isolation
When a transaction views data, the state of the data is either the state before another concurrent transaction modifies it or the state after another transaction modifies it. The transaction will not view the data in the intermediate state.
-Durability
After a persistent transaction is completed, its impact on the system is permanent. After submission, the data is saved permanently.
4.3 implementing transaction management in SQL
set autocommit=0; # Cancel auto submit start transaction; # Open transaction update account set money=money-1000 where username='A'; update account set money=money+1000 where username='B'; commit; # Commit transaction
4.4 java operation transaction
If the JDBC Connection is in auto submit mode, by default, each SQL statement will be submitted to the database after completion. To enable manual transaction support instead of the automatic commit mode used by the jdbc driver by default, use the setAutoCommit() method of the Connection object. If false is passed to setAutoCommit(), auto commit is turned off.
4.4.1 transaction commit and rollback
After the change is completed, we need to submit the change, and then call the * * commit() * * method on the connection object:
conn.commit( );
Otherwise, to roll back the update using a database named conn:
conn.rollback( );
public static void update() {
Connection connection = null;
PrepareStatement pps = null;
ResultSet resultSet = null;
try{
// 1. Load driver 8.0 com.mysql.cj.jdbc.Driver 5.1 com.mysql.jdbc.Driver
Class.forName("com.mysql.cj.jdbc.Driver");
// 2. Get connected
// url = "jdbc:mysql://IP: Port number / database name? Characterencoding = utf8 & usessl = false & servertimezone = UTC ";
String url = "jdbc:mysql://localhost:3306/mydemo1?characterEncoding=utf8&useSSL=false&serverTimeZone=UTC";
String username = "root";
String password = "123456";
connection = DriverManager.getConnection(url, username, password);
// 3. Get the status Channel - establish an SQL channel between java and the database - compile SQL in advance
// Set manual submission
connection.setAutoCommit(false);
String sql1 = "update account set money=money-1000 where username='A'";
pps = connection.prepareStatement(sql1);
pps.executeUpdate();
String sql2 = "update account set money=money+1000 where username='B'";
pps = connection.prepareStatement(sql2);
// 4. Execute SQL to get the returned result
int i = pps.executeUpdate();
// Manual submission
connection.commit();
// 5. Parse result set
System.out.println(i > 0 ? "success" : "fail");
} catch (Exception e) {
System.out.println("Abnormal");
// If an exception occurs, execute rollback
try {
connection.rollback();
} catch (SQLException e) {
System.out.println("SQLException");
}
} finally {
// 6. Close the Connection, Statement and ResultSet
try {
// security
if (connection != null) {
connection.close();
}
if (statement != null) {
statement.close();
}
if (resultSet != null) {
resultSet.close();
}
} catch (SQLException e) {
System.out.println("SQLException");
}
}
}
5, Batch processing
Batch processing allows related SQL statements to be grouped into batches and submitted through a single call to the database. When we send multiple SQL statements to the database at a time, we can reduce the overhead of connecting to the database and improve performance.
5.1 Statement batch processing
-
Use the * createStatement() * method to create a Statement object.
-
Use * setAutoCommit() * to set auto commit to false.
-
Use the * addBatch() * method to add your favorite SQL statements to the batch on the created statement object.
-
Use the * executeBatch() * method to execute all SQL statements on the created statement object.
-
Finally, commit all changes using the * commit() * method.
Statement statement = conn.createStatement(); connection.setAutoCommit(false); //sql1 String sql1 = "update account set money=money-1000 where username='A'"; statement.addBatch(sql1); //sql2 String sql2 = "update account set money=money+1000 where username='B'"; statement.addBatch(sql2); int[] res = stmt.executeBatch(); connection.commit();
5.2 PrepareStatement batch processing
-
Create SQL statements using placeholders.
-
Use the * prepareStatement() * method to create a PrepareStatement object.
-
Use * setAutoCommit() * to set auto commit to false.
-
Use the * addBatch() * method to add your favorite SQL statements to the batch on the created statement object.
-
Use the * executeBatch() * method to execute all SQL statements on the created statement object.
-
Finally, commit all changes using the * commit() * method.
// Set manual submission
connection.setAutoCommit(false);
String sql = "update account set money=money+? where username=?";
pps = connection.prepareStatement(sql);
// Assign values to placeholders in sql
// First assignment
pps.setInt(1, -1000);
pps.setString(2, "A");
pps.addBatch(); // Adding sql to a batch operation
// Second assignment
pps.setInt(1, 1000);
pps.setString(2, "B");
pps.addBatch(); // Adding sql to a batch operation
// Execute sql
int[] res = pps.executeBatch();
for (int a : res) {
System.out.println(a);
}
connection.commit();
6, Reflection processing result set
public static void test() {
Connection connection = null;
PrepareStatement pps = null;
ResultSet resultSet = null;
List<Student> list = new ArrayList<>();
try{
// 1. Load driver 8.0 com.mysql.cj.jdbc.Driver 5.1 com.mysql.jdbc.Driver
Class.forName("com.mysql.cj.jdbc.Driver");
// 2. Get connected
// url = "jdbc:mysql://IP: Port number / database name? Characterencoding = utf8 & usessl = false & servertimezone = UTC ";
String url = "jdbc:mysql://localhost:3306/mydemo1?characterEncoding=utf8&useSSL=false&serverTimeZone=UTC";
String username = "root";
String password = "123456";
connection = DriverManager.getConnection(url, username, password);
String sql = "select * from student";
pps = connection.prepareStatement(sql);
res = pps.executeQuery();
// Fetch result set
// Returns information about result set
ResultSetMetaData metaData = resultSet.getMetaData();
// Get the number of columns
int columnCount = metaData.getColumnCount();
// Remove column name
String[] columnNames = new String[columnCount];
for (int i = 0; i < columnCount; i++) {
columnNames[i] = metaData.getColumnName(i + 1);
}
// Compare column and attribute names
// Get all the methods in the class
Class<Student> studentClass = Student.class;
Method[] declaredMethods = studentClass.getDeclaredMethods();
// Take out each row of data and assign it to the corresponding attribute
while (resultSet.next()) {
// Create a student
Student student = studentClass.newInstance();
for (String columnName :columnNames) {
String methodname = "set" + columnName;
for (Method declareMethod : declaredMethods) {
if (declaredMethod.getName().equalsIgnoreCase(methodname)) {
declaredMethod.invoke(student,resultSet.getObject(columnName));
break;
}
}
}
// Save the encapsulated object to the collection
list.add(student);
}
} catch (Exception e) {
System.out.println("Abnormal");
} finally {
// 6. Close the Connection, Statement and ResultSet
try {
// security
if (connection != null) {
connection.close();
}
if (statement != null) {
statement.close();
}
if (resultSet != null) {
resultSet.close();
}
} catch (SQLException e) {
System.out.println("SQLException");
}
}
return list;
}
7, Encapsulation tool class
7.1 tools
public class DBUtils {
//1. Define the required tool class objects
protected Connection connection = null;
protected PreparedStatement pps = null;
protected ResultSet rs = null;
protected int k = 0;//Number of rows affected
private String url = "jdbc:mysql://localhost:3306/mydatabase?characterEncoding=utf8&useSSL=false&serverTimeZone=UTC";
private String username = "root";
private String password = "123456";
//2. Load drive
static{
try {
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
//3. Get connected
protected Connection getConnection() {
try {
connection = DriverManager.getConnection(url, username, password);
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
//4. Create channel
protected PreparedStatement getPps(String sql){
try {
getConnection();
pps = connection.prepareStatement(sql);
} catch (SQLException e) {
e.printStackTrace();
}
return pps;
}
//5. Assign a value to the placeholder. The value assigned to the placeholder is saved in the list
private void setParams(List list) {
try {
if(list != null && list.size() > 0) {
for (int i = 0; i < list.size(); i++) {
pps.setObject(i + 1, list.get(i));
}
}
} catch (SQLException e) {
e.printStackTrace();
}
}
//6. Method of adding, deleting, modifying and retrieving
protected int update(String sql, List params) {
try {
getPps(sql);
setParams(params);
k = pps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
return k;
}
//7. Retrieve a method during query
protected ResultSet query(String sql, List list) {
try {
getPps(sql);
setParams(list);
rs = pps.executeQuery();
return rs;
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
//8. Close resources
protected void closeall() {
try {
if (rs != null) {
rs.close();
}
if (pps != null) {
pps.close();
}
if (connection != null) {
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
8, Properties file
db.properties
driver=com.mysql.cj.jdbc.Driver url=jdbc:mysql://localhost:3306/airinfo?characterEncoding=utf8&useSSL=false&serverTimezone=UTC username=root password=123456
Tool class reading configuration file:
InputStream inputStream = Current class name.class.getClassLoader().getResourceAsStream("db.properties");
Properties properties = new Properties();
properties.load(inputStream);
dirverName = properties.getProperty("driver");
url = properties.getProperty("url");
username = properties.getProperty("username");
password = properties.getProperty("password");
9, Connection pool
The basic idea of connection pool is to store the database connection as an object in memory during system initialization. When users need to access the database, they do not establish a new connection, but take out an established idle connection object from the connection pool. After use, the user does not close the connection, but puts the connection back into the connection pool for access by the next request. The establishment and disconnection of connections are managed by the connection pool itself. At the same time, you can also set the parameters of the connection pool to control the initial number of connections in the connection pool, the upper and lower limits of connections, as well as the maximum usage times and maximum idle time of each connection. You can also monitor the number and usage of database connections through its own management mechanism.
9.1 Druid connection pool
Ali produces Taobao and Alipay dedicated database connection pool, but it is not only a database connection pool, it also includes a ProxyDriver (agent driven), a series of built-in JDBC component library, a SQL Parser(sql parser). Support all JDBC compatible databases, including Oracle, MySql, Derby, Postgresql, SQL Server, H2, etc.
Druid has made special optimizations for Oracle and MySql, such as Oracle's PS Cache memory usage optimization and MySql's ping detection optimization.
Druid provides complete support for SQL of MySql, Oracle, Postgresql and SQL-92. It is a handwritten high-performance SQL Parser and supports Visitor mode, which makes it convenient to analyze the abstract syntax tree of SQL.
Through the SQL Parser provided by Druid, you can intercept SQL at the JDBC layer and do corresponding processing, such as database and table segmentation, audit, etc. Druid's WallFilter for defending against SQL injection attacks is implemented through Druid's SQL Parser analysis semantics.
9.2 characteristics of Druid connection pool
Druid is a popular OLAP framework with high performance and distributed column storage (specifically MOLAP). It has the following characteristics:
1. Sub second level query
druid provides fast aggregation capability and sub second OLAP query capability. Multi tenant design is an ideal way for user oriented analysis applications.
2. Real time data injection
druid supports stream data injection and provides event driven data to ensure the effectiveness and unity of events in real-time and offline environments.
3. Scalable petabyte storage
druid clusters can be easily expanded to petabytes of data, with millions of data injections per second. Even when the data scale is increased, its effectiveness can be guaranteed.
4. Multi environment deployment
druid can run on both commercial hardware and cloud. It can inject data from a variety of data systems, including hadoop, spark, kafka, storm and samza.
5. Rich communities
druid has a rich community for everyone to learn from.
9.3 jar package used by Druid connection pool
jar package:
druid-1.1.21.jar
9.4 Druid connection pool profile
druid.properties:
url=jdbc:mysql://localhost:3306/test username=root password=123 #Driver class name. This item can be configured or not according to the url. If druid is not configured, the corresponding driverClassName will be automatically identified according to the url driverClassName=com.mysql.jdbc.Driver #The number of physical connections established during initialization. Initialization occurs when the display calls the init method or the first getConnection initialSize=5 #Maximum number of connection pools maxActive=10 #Minimum number of connection pools minIdle=5 #Maximum wait time to get a connection, in milliseconds. maxWait=3000
9.5 Druid connection pool java tool class
DruidUtil.java:
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class DruidUtil {
private static DataSource data = null;
static {
InputStream is = DruidUtil.class.getClassLoader().getResourceAsStream("druid.properties");
Properties ppt = new Properties();
try {
ppt.load(is);
data = DruidDataSourceFactory.createDataSource(ppt);
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* Used to obtain a connection from the DBCP connection pool
*
* @return DBCP A connection object in the connection pool
*/
public static Connection getConnection() {
try {
return data.getConnection();
} catch (SQLException e) {
e.printStackTrace();
return null;
}
}
public static void close(Connection con, SetResultBean bean) {
if (bean != null) {
close(con, bean.getPs(), bean.getRs());
} else {
close(con, null, null);
}
}
public static void close(Connection con, IntResultBean bean) {
if (bean != null) {
close(con, bean.getPs(), null);
} else {
close(con, null, null);
}
}
/**
* It is used to release resources such as connection, execution environment and result set
*
* @param conn Connection resources to release
* @param state Execution environment resources to release
* @param result Result set resources to release
*/
public static void close(Connection conn, Statement state, ResultSet result) {
if (result != null) {
try {
result.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (state != null) {
try {
state.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* Write query public methods
*/
public static SetResultBean query(Connection con, String sql, Object[] params) throws SQLException {
if (con != null) {
/**Do not create preparedStatement here*/
PreparedStatement ps = con.prepareStatement(sql);
if (params != null) {
for (int i = 0; i < params.length; i++) {
ps.setObject(i + 1, params[i]);
}
}
ResultSet rs = ps.executeQuery();
return new SetResultBean(rs, ps);
} else {
return null;
}
}
/**
* Prepare public methods for addition, deletion and modification
*/
public static IntResultBean update(Connection con, String sql, Object[] params) throws SQLException {
if (con == null) {
return null;
}
PreparedStatement ps = con.prepareStatement(sql);
if (params != null) {
for (int i = 0; i < params.length; i++) {
ps.setObject(i + 1, params[i]);
}
}
return new IntResultBean(ps.executeUpdate(), ps);
}
public static class SetResultBean {
private final ResultSet rs;
private final PreparedStatement ps;
public ResultSet getRs() {
return rs;
}
public PreparedStatement getPs() {
return ps;
}
public SetResultBean(ResultSet rs, PreparedStatement ps) {
this.rs = rs;
this.ps = ps;
}
}
public static class IntResultBean {
private final int result;
private final PreparedStatement ps;
public int getResult() {
return result;
}
public PreparedStatement getPs() {
return ps;
}
public IntResultBean(int result, PreparedStatement ps) {
this.result = result;
this.ps = ps;
}
}
}