1. JDBC overview
1.1 JDBC concept
JDBC is a set of API s that use Java language to operate relational databases
Full name: (Java database connectivity) Java database connectivity
The same set of Java code we developed cannot operate different relational databases, because the underlying implementation details of each relational database are different. If so, the problem will be great. In the company, MySQL database can be used in the development stage, and when the company finally selects oracle database, we need to modify the code in large quantities, which is obviously not what we want to see. What we need to do is that the same set of Java code operates different relational databases. At this time, sun company specifies a set of standard interface (JDBC), which defines all the rules for operating relational databases. As we all know, the interface cannot be used directly. We need to use the implementation class of the interface, and this set of implementation class (called driver) is given by their respective database manufacturers.
1.2 JDBC essence
- A set of rules defined by the official (sun company) to operate all relational databases, namely the interface
- Each database manufacturer implements this interface and provides database driver jar package
- We can use this set of interfaces (JDBC) to program, and the real executed code is to drive the implementation classes in the jar package
1.3 JDBC benefits
- Each database manufacturer uses the same interface, and Java code does not need to be developed separately for different databases
- The underlying database can be replaced at any time, and the Java code accessing the database is basically unchanged
In the future, the code for operating the database only needs to be oriented towards JDBC (Interface). The driver package of the database needs to be imported to operate any relational database. If you need to operate MySQL database, you need to import the driver package of MySQL database in the project. The figure below shows the MySQL driver package
1,4 JDBC quick start
Let's first look at the process of operating the database through Java
Step 1: write Java code
Step 2: Java code sends SQL to MySQL server
Step 3: the MySQL server receives the SQL statement and executes it
Step 4: return the result of SQL statement execution to Java code
Coding steps
- Create a project and import the driver jar package
-
Register driver
Class.forName("com.mysql.jdbc.Driver"); -
Get connection
Connection conn = DriverManager.getConnection(url, username, password);
If the Java code needs to send SQL to the MySQL server, you need to establish a connection first
-
Define SQL statement
String sql = "update..." ;
-
Get execute SQL object
Executing SQL statements requires an SQL execution object, which is the Statement object
Statement stmt = conn.createStatement();
-
Execute SQL
stmt.executeUpdate(sql);
-
Processing returned results
-
Release resources
The code is as follows:
/**
* JDBC quick get start
*/
public class JDBCDemo {
public static void main(String[] args) throws Exception {
//1. Register driver
//Class.forName("com.mysql.jdbc.Driver");
//2. Get connection
String url = "jdbc:mysql://127.0.0.1:3306/db1";
String username = "root";
String password = "1234";
Connection conn = DriverManager.getConnection(url, username, password);
//3. Define sql
String sql = "update account set money = 2000 where id = 1";
//4. Get the object Statement executing sql
Statement stmt = conn.createStatement();
//5. execute sql
int count = stmt.executeUpdate(sql);//Number of rows affected
//6. Treatment results
System.out.println(count);
//7. Release resources
stmt.close();
conn.close();
}
}
2. Detailed explanation of JDBC API
2.1 DriverManager
DriverManager (driver management class) functions:
-
Register driver
The registerDriver method is used to register drivers, but the entry case we did before is not written like this. Instead, it is implemented as follows
Class.forName("com.mysql.jdbc.Driver");We query the Driver class provided by MySQL to see how it is implemented. The source code is as follows:
In the static code block in this class, the registerDriver() method of the DriverManager object has been executed to register the driver. Then we only need to load the driver class and the static code block will be executed. And class forName("com.mysql.jdbc.Driver"); You can load the driver class.
Tips:
- For the driver package after MySQL 5, the steps of registering the driver can be omitted
- Automatically load meta-inf / services / java.xml in jar package sql. Driver class in driver file
-
Get database connection
Parameter Description:
-
url: connection path
Syntax: jdbc:mysql://ip Address (domain name): port number / database name? Parameter key value pair 1 & parameter key value pair 2
Example: jdbc:mysql://127.0.0.1:3306/db1
Details:
-
If the local mysql server is connected and the default port of mysql service is 3306, the url can be abbreviated as: jdbc:mysql: / / / database name? Parameter key value pair
-
Configure the useSSL=false parameter, disable the secure connection mode, and solve the warning prompt
-
-
User: user name
-
poassword: password
2.2 Connection
Function of Connection object:
- Gets the object that executes SQL
- Management services
2.2.1 get execution object
-
Normal execution SQL object
Statement createStatement()
The entry case is the execution object obtained through this method.
-
Execution SQL object of precompiled SQL: preventing SQL injection
PreparedStatement prepareStatement(sql)
The PreparedStatement SQL statement execution object obtained in this way is what we will focus on later. It can prevent SQL injection.
-
The object that executes the stored procedure
CallableStatement prepareCall(sql)
The CallableStatement execution object obtained in this way is used to execute stored procedures, which are not commonly used in MySQL, so we will not explain this.
2.2.2 transaction management
First review the operation of MySQL transaction management:
- START TRANSACTION: BEGIN; Or START TRANSACTION;
- COMMIT transaction: COMMIT;
- ROLLBACK transaction: ROLLBACK;
MySQL automatically commits transactions by default
Next, learn the method of JDBC transaction management.
Three corresponding methods are defined in Connection ports:
-
Open transaction
Participating in autoCommit indicates whether to automatically commit transactions, true indicates to automatically commit transactions, and false indicates to manually commit transactions. To start a transaction, you need to set this parameter to false.
-
Commit transaction
- Rollback transaction
The specific code is as follows:
/**
* JDBC API Detailed explanation: Connection
*/
public class JDBCDemo3_Connection {
public static void main(String[] args) throws Exception {
//1. Register driver
//Class.forName("com.mysql.jdbc.Driver");
//2. Get connection: if the connection is local mysql and the port is the default 3306, writing can be simplified
String url = "jdbc:mysql:///db1?useSSL=false";
String username = "root";
String password = "1234";
Connection conn = DriverManager.getConnection(url, username, password);
//3. Define sql
String sql1 = "update account set money = 3000 where id = 1";
String sql2 = "update account set money = 3000 where id = 2";
//4. Get the object Statement executing sql
Statement stmt = conn.createStatement();
try {
// ============Open transaction==========
conn.setAutoCommit(false);
//5. Execute sql
int count1 = stmt.executeUpdate(sql1);//Number of rows affected
//6. Treatment results
System.out.println(count1);
int i = 3/0;
//5. Execute sql
int count2 = stmt.executeUpdate(sql2);//Number of rows affected
//6. treatment results
System.out.println(count2);
// ============Commit transaction==========
//When the program runs here, it indicates that there is no problem, and the transaction needs to be committed
conn.commit();
} catch (Exception e) {
// ============Rollback transaction==========
//When an exception occurs, the program will execute to this place. At this time, the transaction needs to be rolled back
conn.rollback();
e.printStackTrace();
}
//7. Release resources
stmt.close();
conn.close();
}
}
2.3 Statement
2.3.1 general
The Statement object is used to execute SQL statements. The methods used for different types of SQL statements are also different.
- Execute DDL and DML statements
- Execute DQL statement
This method involves the ResultSet object, which we haven't learned yet. We'll focus on it later.
2.3.2 code implementation
-
Execute DML statement
/** * Execute DML statement * @throws Exception */ @Test public void testDML() throws Exception { //1. Register driver //Class.forName("com.mysql.jdbc.Driver"); //2. Get connection: if the connection is local mysql and the port is the default 3306, writing can be simplified String url = "jdbc:mysql:///db1?useSSL=false"; String username = "root"; String password = "1234"; Connection conn = DriverManager.getConnection(url, username, password); //3. Define sql String sql = "update account set money = 3000 where id = 1"; //4. Get the object Statement executing sql Statement stmt = conn.createStatement(); //5. Execute sql int count = stmt.executeUpdate(sql);//The number of rows affected after the execution of DML statement //6. Treatment results //System.out.println(count); if(count > 0){ System.out.println("Modified successfully~"); }else{ System.out.println("Modification failed~"); } //7. Release resources stmt.close(); conn.close(); } -
Execute DDL statement
/** * Execute DDL statement * @throws Exception */ @Test public void testDDL() throws Exception { //1. Register driver //Class.forName("com.mysql.jdbc.Driver"); //2. Get connection: if the connection is local mysql and the port is the default 3306, writing can be simplified String url = "jdbc:mysql:///db1?useSSL=false"; String username = "root"; String password = "1234"; Connection conn = DriverManager.getConnection(url, username, password); //3. Define sql String sql = "drop database db2"; //4. Get the object Statement executing sql Statement stmt = conn.createStatement(); //5. Execute sql int count = stmt.executeUpdate(sql);//After executing DDL statement, it may be 0 //6. Treatment results System.out.println(count); //7. Release resources stmt.close(); conn.close(); }be careful:
- Later development rarely uses java code to operate DDL statements
2.4 ResultSet
2.4.1 general
ResultSet (result set object) functions:
- Encapsulates the results of SQL query statements.
After executing the DQL statement, the object will be returned. The corresponding method of executing the DQL statement is as follows:
ResultSet executeQuery(sql): implement DQL Statement, return ResultSet object
Then we need to get the data we want from the ResultSet object. The ResultSet object provides methods to operate query result data, as follows:
boolean next()
- Moves the cursor forward one line from the current position
- Judge whether the current line is a valid line
Method return value Description:
- true: valid data. There is data in the current line
- false: invalid row. There is no data in the current row
XXX getxxx (parameter): get data
- xxx: data type; For example: int getInt (parameter); String getString (parameter)
- parameter
- Parameter of type int: the number of the column, starting from 1
- Parameter of String type: the name of the column
The following figure shows the results after executing the SQL statement
At the beginning, the cursor is assigned in front of the first line, as shown in the figure, and the red arrow points to the header line. When we call the next() method, the cursor moves down to the first row of data, and the method returns true. At this time, we can get the value of the id field of the current row through getInt("id"), or get the value of the name field of the current row through getString("name"). If you want to get the data of the next row, continue to call the next() method, and so on.
2.4.2 code implementation
/**
* Execute DQL
* @throws Exception
*/
@Test
public void testResultSet() throws Exception {
//1. Register driver
//Class.forName("com.mysql.jdbc.Driver");
//2. Get connection: if the connection is local mysql and the port is the default 3306, writing can be simplified
String url = "jdbc:mysql:///db1?useSSL=false";
String username = "root";
String password = "1234";
Connection conn = DriverManager.getConnection(url, username, password);
//3. Define sql
String sql = "select * from account";
//4. Get the statement object
Statement stmt = conn.createStatement();
//5. Execute sql
ResultSet rs = stmt.executeQuery(sql);
//6. Process the result and traverse all data in rs
/* // 6.1 Move the cursor down one line and judge whether there is data in the current line
while (rs.next()){
//6.2 Get data getXxx()
int id = rs.getInt(1);
String name = rs.getString(2);
double money = rs.getDouble(3);
System.out.println(id);
System.out.println(name);
System.out.println(money);
System.out.println("--------------");
}*/
// 6.1 move the cursor down one line and judge whether there is data in the current line
while (rs.next()){
//6.2 get data (getxxx)
int id = rs.getInt("id");
String name = rs.getString("name");
double money = rs.getDouble("money");
System.out.println(id);
System.out.println(name);
System.out.println(money);
System.out.println("--------------");
}
//7. Release resources
rs.close();
stmt.close();
conn.close();
}
2.5 cases
-
Requirement: query the account table data, encapsulate it in the account object, and store it in the ArrayList collection
-
code implementation
/** * Query the account table data, encapsulate it in the account object, and store it in the ArrayList collection * 1. Define entity class Account * 2. Query the data and encapsulate it into the Account object * 3. Store the Account object in the ArrayList collection */ @Test public void testResultSet2() throws Exception { //1. Register driver //Class.forName("com.mysql.jdbc.Driver"); //2. Get connection: if the connection is local mysql and the port is the default 3306, writing can be simplified String url = "jdbc:mysql:///db1?useSSL=false"; String username = "root"; String password = "1234"; Connection conn = DriverManager.getConnection(url, username, password); //3. Define sql String sql = "select * from account"; //4. Get the statement object Statement stmt = conn.createStatement(); //5. Execute sql ResultSet rs = stmt.executeQuery(sql); // Create collection List<Account> list = new ArrayList<>(); // 6.1 move the cursor down one line and judge whether there is data in the current line while (rs.next()){ Account account = new Account(); //6.2 get data (getxxx) int id = rs.getInt("id"); String name = rs.getString("name"); double money = rs.getDouble("money"); //assignment account.setId(id); account.setName(name); account.setMoney(money); // Save to collection list.add(account); } System.out.println(list); //7. Release resources rs.close(); stmt.close(); conn.close(); }
2.6 PreparedStatement
PreparedStatement functions:
- Precompiling and executing SQL statements: preventing SQL injection problems
We certainly don't understand the problem of SQL injection in the above functions. Let's explain SQL injection first
2.6.1 SQL injection
SQL injection is a method of modifying pre-defined SQL statements through operation input to attack the server by executing code.
2.6.2 code simulation SQL injection
@Test
public void testLogin() throws Exception {
//2. Get connection: if the connection is local mysql and the port is the default 3306, writing can be simplified
String url = "jdbc:mysql:///db1?useSSL=false";
String username = "root";
String password = "1234";
Connection conn = DriverManager.getConnection(url, username, password);
// Receive user input user name and password
String name = "sjdljfld";
String pwd = "' or '1' = '1";
String sql = "select * from tb_user where username = '"+name+"' and password = '"+pwd+"'";
// Get stmt object
Statement stmt = conn.createStatement();
// Execute sql
ResultSet rs = stmt.executeQuery(sql);
// Judge whether the login is successful
if(rs.next()){
System.out.println("Login successful~");
}else{
System.out.println("Login failed~");
}
//7. Release resources
rs.close();
stmt.close();
conn.close();
}
The above code splices the user name and password into the sql statement. The spliced sql statement is as follows
select * from tb_user where username = 'sjdljfld' and password = ''or '1' = '1'
2.6.3 PreparedStatement overview
PreparedStatement functions:
- Precompiling and executing SQL statements: preventing SQL injection problems
-
Gets the PreparedStatement object
// Parameter values in SQL statements, using? Placeholder substitution String sql = "select * from user where username = ? and password = ?"; // Get through the Connection object and pass in the corresponding sql statement PreparedStatement pstmt = conn.prepareStatement(sql);
-
Set parameter value
In the above sql statement, the parameter use? Do you have to set these before occupying? Value of.
PreparedStatement object: setXXX (parameter 1, parameter 2): to? assignment
-
Xxx: data type; Such as setInt (parameter 1, parameter 2)
-
Parameters:
-
Parameter 1:? Position number of the, starting from 1
-
Parameter 2:? Value of
-
-
-
Execute SQL statement
executeUpdate(); Execute DDL and DML statements
executeQuery(); Execute DQL statement
be careful:
- When calling these two methods, you do not need to pass the SQL statement, because the SQL statement has been precompiled when obtaining the execution object of the SQL statement.
2.6.4 using PreparedStatement to improve
@Test
public void testPreparedStatement() throws Exception {
//2. Get connection: if the connection is local mysql and the port is the default 3306, writing can be simplified
String url = "jdbc:mysql:///db1?useSSL=false";
String username = "root";
String password = "1234";
Connection conn = DriverManager.getConnection(url, username, password);
// Receive user input user name and password
String name = "zhangsan";
String pwd = "' or '1' = '1";
// Define sql
String sql = "select * from tb_user where username = ? and password = ?";
// Get pstmt object
PreparedStatement pstmt = conn.prepareStatement(sql);
// set up? Value of
pstmt.setString(1,name);
pstmt.setString(2,pwd);
// Execute sql
ResultSet rs = pstmt.executeQuery();
// Judge whether the login is successful
if(rs.next()){
System.out.println("Login successful~");
}else{
System.out.println("Login failed~");
}
//7. Release resources
rs.close();
pstmt.close();
conn.close();
}
By executing the above statement, you can find that there will be no SQL injection vulnerability. So how is PreparedStatement solved? It is used to escape special characters. The escape SQL is as follows:
select * from tb_user where username = 'sjdljfld' and password = '\'or \'1\' = \'1'
2.6.5 PreparedStatement principle
PreparedStatement benefits:
- Precompiled SQL for better performance
- Prevent SQL injection: Escape sensitive characters
The process of Java code operating database is shown in the figure below:
-
Send sql statements to MySQL server
-
MySQL server will perform the following operations on sql statements
-
Check SQL statements
Check whether the syntax of the SQL statement is correct.
-
Compile SQL statements. Compile SQL statements into executable functions.
Checking and compiling SQL takes longer than executing SQL. If we just reset the parameters, the check SQL statement and compile SQL statement will not need to be executed repeatedly. This improves performance.
-
Execute SQL statement
-
Next, let's look at the principle by querying the log.
-
Enable precompiling
When writing the url in the code, you need to add the following parameters. We didn't turn on the precompile function before, but we just solved the SQL injection vulnerability.
useServerPrepStmts=true
-
Configure MySQL execution log (effective after restarting MySQL service)
Add the following configuration in the mysql configuration file (my.ini)
log-output=FILE general-log=1 general_log_file="D:\mysql.log" slow-query-log=1 slow_query_log_file="D:\mysql_slow.log" long_query_time=2
-
The java test code is as follows:
/** * PreparedStatement principle * @throws Exception */ @Test public void testPreparedStatement2() throws Exception { //2. Get connection: if the connection is local mysql and the port is the default 3306, writing can be simplified // The useServerPrepStmts=true parameter enables precompiling String url = "jdbc:mysql:///db1?useSSL=false&useServerPrepStmts=true"; String username = "root"; String password = "1234"; Connection conn = DriverManager.getConnection(url, username, password); // Receive user input user name and password String name = "zhangsan"; String pwd = "' or '1' = '1"; // Define sql String sql = "select * from tb_user where username = ? and password = ?"; // Get pstmt object PreparedStatement pstmt = conn.prepareStatement(sql); Thread.sleep(10000); // set up? Value of pstmt.setString(1,name); pstmt.setString(2,pwd); ResultSet rs = null; // Execute sql rs = pstmt.executeQuery(); // set up? Value of pstmt.setString(1,"aaa"); pstmt.setString(2,"bbb"); // Execute sql rs = pstmt.executeQuery(); // Judge whether the login is successful if(rs.next()){ System.out.println("Login successful~"); }else{ System.out.println("Login failed~"); } //7. Release resources rs.close(); pstmt.close(); conn.close(); } -
Execute SQL statement and check D: \ mysql The log is as follows:
Prepare in the third line in the above figure is to precompile the SQL statement. The fourth and fifth lines execute the SQL statement twice, and the SQL is not precompiled before the second execution.
Summary:
- When obtaining the PreparedStatement object, send the sql statement to the mysql server for inspection and compilation (these steps are time-consuming)
- These steps are no longer needed when executing, and the speed is faster
- If the sql template is the same, you only need to check and compile it once
3. Database connection pool
3.1 introduction to database connection pool
-
Database connection pool is a container that is responsible for allocating and managing database connections
-
It allows applications to reuse an existing database connection instead of re establishing one;
-
Release the database connection whose idle time exceeds the maximum idle time to avoid database connection omission caused by not releasing the database connection
-
benefit
- resource reuse
- Improve system response speed
- Avoid missing database connections
Previously, we created a Connection object without using the Connection in our code, and it will be destroyed after use. This repeated creation and destruction process is particularly time-consuming and time-consuming.
After the database uses the database Connection pool, the Connection object can be reused, as shown in the following figure
Connection pool is to store some connection objects created at the beginning. When users need to connect to the database, they do not need to create their own connection, but only need to obtain a connection from the connection pool for use, and return the connection object to the connection pool after use; In this way, resources can be reused, and the time spent on frequent connection creation and destruction can be saved, so as to improve the speed of system response.
3.2 implementation of database connection pool
-
Standard interface: DataSource
The standard interface of database connection pool provided by sun, which is implemented by a third-party organization. This interface provides the function of obtaining connection:
Connection getConnection()
In the future, you do not need to obtain the Connection object through the DriverManager object, but through the Connection pool (DataSource).
-
Common database connection pools
- DBCP
- C3P0
- Druid
We now use Druid more, and its performance will be better than the other two.
-
Druid (Druid)
-
Druid connection pool is an open source database connection pool project of Alibaba
-
With powerful functions and excellent performance, it is one of the best database connection pools in Java language
-
3.3 Driud usage
- Import the jar package druid-1.1.12 jar
- Define profile
- Load profile
- Get database connection pool object
- Get connection
Write the configuration file as follows:
driverClassName=com.mysql.jdbc.Driver url=jdbc:mysql:///db1?useSSL=false&useServerPrepStmts=true username=root password=1234 # Number of initialization connections initialSize=5 # maximum connection maxActive=10 # Maximum waiting time maxWait=3000
The code for using druid is as follows:
/**
* Druid Database connection pool demo
*/
public class DruidDemo {
public static void main(String[] args) throws Exception {
//1. Import jar package
//2. Define profile
//3. Load configuration file
Properties prop = new Properties();
prop.load(new FileInputStream("jdbc-demo/src/druid.properties"));
//4. Get connection pool object
DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
//5. Get database Connection
Connection connection = dataSource.getConnection();
System.out.println(connection); //After obtaining the connection, you can continue to do other operations
//System.out.println(System.getProperty("user.dir"));
}
}
4. JDBC practice
4.1 requirements
Complete the addition, deletion, modification and query of commodity brand data
- Query: query all data
- Add: add brand
- Modify: modify by id
- Delete: delete by id
4.2 case realization
4.2.1 environmental preparation
-
Database table tb_brand
-- delete tb_brand surface drop table if exists tb_brand; -- establish tb_brand surface create table tb_brand ( -- id Primary key id int primary key auto_increment, -- Brand name brand_name varchar(20), -- Enterprise name company_name varchar(20), -- sort field ordered int, -- Description information description varchar(100), -- Status: 0: Disabled 1: enabled status int ); -- Add data insert into tb_brand (brand_name, company_name, ordered, description, status) values ('Three squirrels', 'Three squirrels Co., Ltd', 5, 'Delicious but not hot', 0), ('Huawei', 'Huawei Technology Co., Ltd', 100, 'Huawei is committed to bringing the digital world into everyone, every family and every organization to build an interconnected intelligent world', 1), ('millet', 'Xiaomi Technology Co., Ltd', 50, 'are you ok', 1); -
Entity class Brand under pojo package
/** * brand * alt + Left mouse button: edit the whole column * In the entity class, it is recommended to use its corresponding wrapper type for the basic data type */ public class Brand { // id primary key private Integer id; // Brand name private String brandName; // Enterprise name private String companyName; // sort field private Integer ordered; // Description information private String description; // Status: 0: Disabled 1: enabled private Integer status; public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getBrandName() { return brandName; } public void setBrandName(String brandName) { this.brandName = brandName; } public String getCompanyName() { return companyName; } public void setCompanyName(String companyName) { this.companyName = companyName; } public Integer getOrdered() { return ordered; } public void setOrdered(Integer ordered) { this.ordered = ordered; } public String getDescription() { return description; } public void setDescription(String description) { this.description = description; } public Integer getStatus() { return status; } public void setStatus(Integer status) { this.status = status; } @Override public String toString() { return "Brand{" + "id=" + id + ", brandName='" + brandName + '\'' + ", companyName='" + companyName + '\'' + ", ordered=" + ordered + ", description='" + description + '\'' + ", status=" + status + '}'; } }
4.2.2 query all
/**
* Query all
* 1. SQL: select * from tb_brand;
* 2. Parameter: not required
* 3. Results: List < brand >
*/
@Test
public void testSelectAll() throws Exception {
//1. Get Connection
//3. Load configuration file
Properties prop = new Properties();
prop.load(new FileInputStream("jdbc-demo/src/druid.properties"));
//4. Get connection pool object
DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
//5. Get database Connection
Connection conn = dataSource.getConnection();
//2. Define SQL
String sql = "select * from tb_brand;";
//3. Get pstmt object
PreparedStatement pstmt = conn.prepareStatement(sql);
//4. Parameter setting
//5. Execute SQL
ResultSet rs = pstmt.executeQuery();
//6. The processing result List < Brand > encapsulates the Brand object and loads the List set
Brand brand = null;
List<Brand> brands = new ArrayList<>();
while (rs.next()){
//get data
int id = rs.getInt("id");
String brandName = rs.getString("brand_name");
String companyName = rs.getString("company_name");
int ordered = rs.getInt("ordered");
String description = rs.getString("description");
int status = rs.getInt("status");
//Encapsulate Brand objects
brand = new Brand();
brand.setId(id);
brand.setBrandName(brandName);
brand.setCompanyName(companyName);
brand.setOrdered(ordered);
brand.setDescription(description);
brand.setStatus(status);
//Mount collection
brands.add(brand);
}
System.out.println(brands);
//7. Release resources
rs.close();
pstmt.close();
conn.close();
}
4.2.3 adding data
/**
* add to
* 1. SQL: insert into tb_brand(brand_name, company_name, ordered, description, status) values(?,?,?,?,?);
* 2. Parameters: all parameter information except id is required
* 3. Result: boolean
*/
@Test
public void testAdd() throws Exception {
// Receive parameters submitted by the page
String brandName = "Fragrance floating";
String companyName = "Fragrance floating";
int ordered = 1;
String description = "Circle the earth";
int status = 1;
//1. Get Connection
//3. Load configuration file
Properties prop = new Properties();
prop.load(new FileInputStream("jdbc-demo/src/druid.properties"));
//4. Get connection pool object
DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
//5. Get database Connection
Connection conn = dataSource.getConnection();
//2. Define SQL
String sql = "insert into tb_brand(brand_name, company_name, ordered, description, status) values(?,?,?,?,?);";
//3. Get pstmt object
PreparedStatement pstmt = conn.prepareStatement(sql);
//4. Set parameters
pstmt.setString(1,brandName);
pstmt.setString(2,companyName);
pstmt.setInt(3,ordered);
pstmt.setString(4,description);
pstmt.setInt(5,status);
//5. Execute SQL
int count = pstmt.executeUpdate(); // Number of rows affected
//6. Treatment results
System.out.println(count > 0);
//7. Release resources
pstmt.close();
conn.close();
}
4.2.4 modify data
/**
* modify
* 1. SQL:
update tb_brand
set brand_name = ?,
company_name= ?,
ordered = ?,
description = ?,
status = ?
where id = ?
* 2. Parameters: required, all data
* 3. Result: boolean
*/
@Test
public void testUpdate() throws Exception {
// Receive parameters submitted by the page
String brandName = "Fragrance floating";
String companyName = "Fragrance floating";
int ordered = 1000;
String description = "Three circles around the earth";
int status = 1;
int id = 4;
//1. Get Connection
//3. Load configuration file
Properties prop = new Properties();
prop.load(new FileInputStream("jdbc-demo/src/druid.properties"));
//4. Get connection pool object
DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
//5. Get database Connection
Connection conn = dataSource.getConnection();
//2. Define SQL
String sql = " update tb_brand\n" +
" set brand_name = ?,\n" +
" company_name= ?,\n" +
" ordered = ?,\n" +
" description = ?,\n" +
" status = ?\n" +
" where id = ?";
//3. Get pstmt object
PreparedStatement pstmt = conn.prepareStatement(sql);
//4. Set parameters
pstmt.setString(1,brandName);
pstmt.setString(2,companyName);
pstmt.setInt(3,ordered);
pstmt.setString(4,description);
pstmt.setInt(5,status);
pstmt.setInt(6,id);
//5. Execute SQL
int count = pstmt.executeUpdate(); // Number of rows affected
//6. Treatment results
System.out.println(count > 0);
//7. Release resources
pstmt.close();
conn.close();
}
4.2.5 deleting data
/**
* delete
* 1. SQL:
delete from tb_brand where id = ?
* 2. Parameter: required, id
* 3. Result: boolean
*/
@Test
public void testDeleteById() throws Exception {
// Receive parameters submitted by the page
int id = 4;
//1. Get Connection
//3. Load configuration file
Properties prop = new Properties();
prop.load(new FileInputStream("jdbc-demo/src/druid.properties"));
//4. Get connection pool object
DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
//5. Get database Connection
Connection conn = dataSource.getConnection();
//2. Define SQL
String sql = " delete from tb_brand where id = ?";
//3. Get pstmt object
PreparedStatement pstmt = conn.prepareStatement(sql);
//4. Set parameters
pstmt.setInt(1,id);
//5. Execute SQL
int count = pstmt.executeUpdate(); // Number of rows affected
//6. Treatment results
System.out.println(count > 0);
//7. Release resources
pstmt.close();
conn.close();
}