JavaWeb-JDBC
What is JDBC
JDBC: Java Database Connect, Java Database connection. Enables Java programs to connect to multiple databases of different brands, like drivers.
How to use JDBC
1. Preparation
Create a database named JDBC and create a user table where the fields are ID,name,password,email, birthday.
Create databasecreatedatabase jdbc; # to create tablecreate table users
(
`id` int primary key,
`name` varchar(40) comment 'name',
`password` varchar(40) comment 'password',
`email` varchar(40) comment 'email',
`birthday` varchar(40) comment Date of birth); # Add contentinsert into users
values (1.'Joe'.'123456'.'[email protected]'.'2001-01-01'),
(2.'bill'.'123456'.'[email protected]'.'2021-05-26'),
(3.'Cathy'.'123456'.'[email protected]'.'2015-05-05'); # lookup tableselect * from users;
Copy the code
2. Using JDBC
1. Import the JAR package
You need to import mysql-connector-java to connect to the database driver.
<dependencies>
<dependency>
<! -- Mysql driver -->
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.23</version>
</dependency>
</dependencies>
Copy the code2. Connect to the database in IDEA
Note: If the Database TAB is not available on the right, you can enable it in the top title bar View->Tool Windows->Database.
3. Configure information
// Resolve Chinese garbled characters
/ / JDBC: mysql: / / localhost: 3306 / database name? Parameters 1& parameters 2
String url = "jdbc:mysql://localhost:3306/jdbc? useUnicode=true&characterEncoding=utf-8";
String username = "root";
String password = "123456";
Copy the code4. Connect to the database
// Load the driver
Class.forName("com.mysql.jdbc.Driver");
// Connect to the database
Connection connection = DriverManager.getConnection(url, username, password);
Copy the code5. Execute the statement to obtain the result
Method 1:
Execute SQL statements through statement
Statement statement = connection.createStatement();
// Query statement
String query = "select * from users;";
// Execute the query and return the result set
ResultSet resultSet = statement.executeQuery(query);
// The executeUpdate() method is called
// int row = statement.executeUpdate(sql)
// Get the content
while (resultSet.next()) {
System.out.println("======= splitter ========");
System.out.printf(Locale.getDefault(), "id=%s%n", resultSet.getObject("id"));
System.out.printf(Locale.getDefault(), "name=%s%n", resultSet.getObject("name"));
System.out.printf(Locale.getDefault(), "password=%s%n", resultSet.getObject("password"));
System.out.printf(Locale.getDefault(), "email=%s%n", resultSet.getObject("email"));
System.out.printf(Locale.getDefault(), "birthday=%s%n", resultSet.getObject("birthday"));
}
Copy the codeEffect:
Method 2: Precompile
// Insert statement
String insert = "insert into users (id, name, password, email, birthday) values (? ,? ,? ,? ,?) ;";
// Precompile the insert statement
PreparedStatement preparedStatement = connection.prepareStatement(insert);
// Insert a specific value
// parameterIndex starts at 1
preparedStatement.setInt(1.4);
preparedStatement.setString(2."The Liang Warrior");
preparedStatement.setString(3."123456");
preparedStatement.setString(4."[email protected]");
preparedStatement.setDate(5.new Date(System.currentTimeMillis()));
/ / SQL execution
int row = preparedStatement.executeUpdate();
if (row > 0) {
System.out.println("Adding data succeeded!");
} else {
System.out.println("Add data failed!");
}
Copy the codeEffect:
6. Close the database to release resources
// Close the connection and release the resource.
// Method 1 closes
// resultSet.close();
// statement.close();
// Method 2 closes
preparedStatement.close();
connection.close();
Copy the codeThird, the transaction
1. What are transactions
To execute a single SQL or set of SQL in a batch.
The ACID principle
ACID Principle: Atomicity, Consistency, Isolation *, Durability
2.1 atomic
It’s all or nothing!
2.2 consistency
Data integrity is maintained before and after a transaction.
2.3 isolation,
The isolation of transactions means that when multiple users concurrently access the database, the transactions opened by the database for each user cannot be disturbed by the operation data of other transactions, and multiple concurrent transactions must be isolated from each other.
2.4 persistence
Once committed, transactions are irreversible and persist to the database.
2.5 Problems caused by isolation (dirty read, phantom read…)
Dirty read: A transaction reads uncommitted data from another transaction.
Virtual read: Indicates that data inserted by other transactions is read in one transaction. As a result, the total number of reads before and after a transaction is inconsistent. (Generally a row effect, as shown in the following figure: too many rows)
Non-repeatability: reading a row in a table within a transaction, with different results for multiple reads. (This is not necessarily a mistake, but in some cases)
3. Test transactions
3.1 Preparations
Create the data
create table account
(
`id` int primary key auto_increment,
`name` varchar(30),
`money` decimal(9.2));insert into account(`name`.`money`)
values ('A'.1000),
('B'.1000),
('C'.1000);
Copy the code3.2 the
Simulate the transfer of 100 from user A to user B
@Test
public void testTransaction(a) throws ClassNotFoundException {
String url = "jdbc:mysql://localhost:3306/jdbc? useUnicode=true&characterEncoding=utf-8";
String username = "root";
String password = "123456";
Class.forName("com.mysql.jdbc.Driver");
Connection connection = null;
try {
connection = DriverManager.getConnection(url, username, password);
// Turn off auto commit
connection.setAutoCommit(false);
// Simulate transfer
String transfer = "update account set money = money - 100 where name = 'A';";
connection.prepareStatement(transfer).executeUpdate();
// Simulation error
int i = 1 / 0;
// Simulate transfer
String update = "update account set money = money + 100 where name = 'B';";
connection.prepareStatement(update).executeUpdate();
/ / submit
connection.commit();
System.out.println("Transfer successful!");
} catch (Exception e) {
try {
assertconnection ! =null;
connection.rollback();
System.out.println("Rollback successful!");
} catch (SQLException throwables) {
throwables.printStackTrace();
}
System.out.println("Transfer failed!");
}
try {
connection.close();
} catch(SQLException e) { e.printStackTrace(); }}Copy the codeSimulation error occurs, transaction rollback:
After an error occurs, the original data is restored. If you do not write rollback, it will be rolled back by default.
Remove errors and submit normally:
Failed to turn off automatic submission, simulation error running results:
. connection = DriverManager.getConnection(url, username, password);// Turn off auto commit
// connection.setAutoCommit(false);
// Simulate transfer
String transfer = "update account set money = money - 100 where name = 'A';";
connection.prepareStatement(transfer).executeUpdate();
// Simulation error
int i = 1 / 0;
// Simulate transfer
String update = "update account set money = money + 100 where name = 'B';"; connection.prepareStatement(update).executeUpdate(); .Copy the code
SQL statements are executed without automatic commit turned off.
The following information is referenced here:
Introduction to JavaWeb (P28-P29)
MySQL > MySQL > MySQL > MySQL > MySQL > MySQL
Understanding transaction ACID