JDBC Programming Steps
1. Register the driver (inform the Java program which brand of database is being connected to)
2. Get the connection (indicates that the process of JVM and the database process have opened a channel, this is inter-process communication, remember to close the channel after use).
3. Obtain the database operation object (specialized in executing SQL statements)
Statement statement = connect.createStatement();
4. Execute SQL statements (DQL, DML... insert, delete, update)
String sql = "insert into actor values('2','a')";
int rows = statement.executeUpdate(sql);
5. Process the query result set (only when the fourth step is a select statement, the query is performed)
while (rs.next()){
System.out.println(rs.getString("ename"));
}
6. Release resources (after using the resources, make sure to close them, as Java and the database are in inter-process communication, once opened, they must be closed)
if (rs != null){
try {
rs.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (stmt != null){
try {
stmt.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (conn != null){
try {
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
Five Ways to Connect to the Database (First Two Steps)
One
Use a third-party Driver
//Method 1: static loading, low flexibility, strong dependency.
@Test
public void connect01() throws SQLException {
Driver driver = new Driver(); // create driver object
String url = "jdbc:mysql://localhost:3306/hsp_db02";
// put username and password into Properties object
Properties properties = new Properties();
// note user and password are predefined, the values are written according to actual situations
properties.setProperty("user", "root");// user
properties.setProperty("password", "hsp"); // password
Connection connect = driver.connect(url, properties);
System.out.println(connect);
}
Two
Use reflection mechanism to get Driver
//Method 2
@Test
public void connect02() throws ClassNotFoundException, IllegalAccessException, InstantiationException, SQLException {
// use reflection to load the Driver class, more flexible, reduce dependencies
Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");
Driver driver = (Driver)aClass.newInstance();
String url = "jdbc:mysql://localhost:3306/hsp_db02";
// put username and password into Properties object
Properties properties = new Properties();
// note user and password are predefined, the values are written according to actual situations
properties.setProperty("user", "root");// user
properties.setProperty("password", "hsp"); // password
Connection connect = driver.connect(url, properties);
System.out.println("Method 2=" + connect);
}
Three
Use DriverManager to register, no need to write configuration files, more convenient
//Method 3 using DriverManager instead of driver for unified management
@Test
public void connect03() throws IllegalAccessException, InstantiationException, ClassNotFoundException, SQLException {
// use reflection to load the Driver
Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");
Driver driver = (Driver) aClass.newInstance();
// create url, user, and password
String url = "jdbc:mysql://localhost:3306/hsp_db02";
String user = "root";
String password = "hsp";
DriverManager.registerDriver(driver);// register the Driver
Connection connection = DriverManager.getConnection(url, user, password);
System.out.println("Third method=" + connection);
}
Four
Directly can be used without registering the driver
//Method 4: use Class.forName to automatically complete the registration of the driver, simplifying code
// this method is used most often, recommended
@Test
public void connect04() throws ClassNotFoundException, SQLException {
// use reflection to load the Driver class
// when the Driver class is loaded, registration is completed
/*
source code: 1. static block, executed once when the class is loaded.
2. DriverManager.registerDriver(new Driver());
3. thus, the registration of the driver is already done
static {
try {
DriverManager.registerDriver(new Driver());
} catch (SQLException var1) {
throw new RuntimeException("Can't register driver!");
}
}
*/
Class.forName("com.mysql.jdbc.Driver");
// create url, user, and password
String url = "jdbc:mysql://localhost:3306/hsp_db02";
String user = "root";
String password = "hsp";
Connection connection = DriverManager.getConnection(url, user, password);
System.out.println("Fourth method~ " + connection);
}
Five
Move the hard-coded database connection configuration information to a properties file for retrieval.
// Method 5, improve on method 4, add configuration file, making the connection to MySQL more flexible
@Test
public void connect05() throws IOException, ClassNotFoundException, SQLException {
// get configuration file information through Properties object
Properties properties = new Properties();
properties.load(new FileInputStream("src\mysql.properties"));
// get related values
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String driver = properties.getProperty("driver");
String url = properties.getProperty("url");
Class.forName(driver); // higher version does not need to write, but it is recommended to write
Connection connection = DriverManager.getConnection(url, user, password);
System.out.println("Method 5 " + connection);
}
Usage
public class JDBC01 {
public static void main(String[] args) throws SQLException {
// pre-work: create a folder in the project, such as libs
// copy mysql.jar to this directory, click Add to Project ... to add to the project
// 1. Register the driver
Driver driver = new Driver(); // create driver object
// 2. get the connection
// teacher explanation
// (1) jdbc:mysql:// is a protocol, indicating that it connects to mysql via jdbc
// (2) localhost is the host, can be an IP address
// (3) 3306 indicates the port that mysql listens to
// (4) hsp_db02 indicates which database in the mysql dbms to connect to
// (5) the connection to mysql is essentially a socket connection as learned before
String url = "jdbc:mysql://localhost:3306/hsp_db02";
// put username and password into Properties object
Properties properties = new Properties();
// note user and password are predefined, the values are written according to actual situations
properties.setProperty("user", "root");// user
properties.setProperty("password", "zouwenhao"); // password
Connection connect = driver.connect(url, properties);
// 3. execute sql
// String sql = "insert into actor values(null, '刘德华', '男', '1970-11-11', '110')";
// String sql = "update actor set name='周星驰' where id = 1";
String sql = "delete from actor where id = 1";
// statement is used to execute static SQL statements and return the generated results
Statement statement = connect.createStatement();
// rows returns 0, indicating failure
int rows = statement.executeUpdate(sql); // if it is a DML statement, the number of affected rows is returned, executeUpdate is to execute the statement
System.out.println(rows > 0 ? "Success" : "Failure"); // if the number of affected rows is greater than 0, it is successful, otherwise it is failed
// 4. close the connection resources
statement.close();
connect.close();
}
}
ResultSet[Result Set]
Query the database and place the results in a result set object.
ResultSet is an interface, JDBC42ResultSet is the implementation of the interface.
rowData is used to store query data.
Each row of the query result is stored in rows as an ArrayList.
String sql = "Select * from actor";
ResultSet resultSet =statement.executeQuery(sql);
while(resultSet.next()){
int id = resultSet.getInt("id");
String name =resultSet.getString("name");
String sex = resultSet.getString("sex");
Date date = resultSet.getDate("borndate");
String phone = resultSet.getString("phone");
System.out.println(id + "\t" + name + "\t" + sex + "\t" + date+ "\t" + phone);
}
resultSet.close();
statement.close();
connection.close();
Note: The column names in the query should correspond one-to-one with the column names in the while loop.
If the query returns a date column but the while loop does not have it, an error will occur.
getString() can also use indexes to get the corresponding colum (index=1 is the first column), regardless of the data type, the retrieved type is always String, and the column index starts at 1.
You can retrieve specific types, getInt, getDouble.
After the query ends, we need to close the resultSet, and the query results will be lost.
If we want to continue using the result set later, we need to query again, which is inconvenient.
In the subsequent DAO introduction, we will create a JavaBean for each table to solve this reuse issue.
SQL Injection
User input contains SQL statement keywords, and these keywords participate in the compilation process of the SQL statement, leading to the original meaning of the SQL statement being distorted, thus achieving SQL injection.
String sql = "select name , pwd from admin where name ='"
+ admin_name + "' and pwd = '" + admin_pwd + "'";
ResultSet resultSet = statement.executeQuery(sql);
PreparedStatement (Prepared Statement)
Solve the problem of SQL injection. As long as the information provided by the user does not participate in the compilation process of the SQL statement, the problem is solved. PreparedStatement interface inherits java.sql.PreparedStatement First compile the framework of the SQL statement, then pass the value to the SQL statement.
Comparison
- Statement has the problem of SQL injection, PreparedStatement solves the problem of SQL injection.
- Statement compiles and executes once, PreparedStatement compiles once and can execute n times, slightly more efficient.
- PreparedStatement performs type safety checks during the compilation phase.
In summary: Most of the time, use PreparedStatement. Some cases use statement, such as needing to concatenate strings, input desc (descending) asc (ascending).
// get PreparedStatement
// 1. organize Sql, the ? in the Sql is equivalent to a placeholder
// String sql = "insert into admin values(?, ?)";
// String sql = "update admin set pwd = ? where name = ?";
String sql = "select name , pwd from admin where name =? and pwd = ?"; // ? is used as a placeholder
// 2. preparedStatement object implements the PreparedStatement interface's implementation class object
PreparedStatement preparedStatement = connection.prepareStatement(sql); // associated
// 3. assign values to ?, set type
preparedStatement.setString(1, admin_name);
preparedStatement.setString(2, admin_pwd);
// 4. execute select statement using executeQuery
// if it is dml (update, insert, delete), use executeUpdate()
// here execute executeQuery, do not write sql, because preparedStatement is already associated with sql
ResultSet resultSet = preparedStatement.executeQuery();
API Summary
JDBCUtils
public class JDBCUtils {
// define related properties (4)
// since only one is needed, use static modifier
private static String user;
private static String password;
private static String url;
private static String driver;
// get configuration file information in static block
static{
Properties properties = new Properties();
try {
// load the previously used configuration file
properties.load(new FileInputStream("src\mysql.properties"));
// read relevant properties
user = properties.getProperty("user");
password = properties.getProperty("password");
url = properties.getProperty("url");
driver = properties.getProperty("driver");
} catch (IOException e) {
// in actual development, you can handle it like this
// 1. convert the compile-time exception to a runtime exception
// 2. the call can choose to catch this exception or choose to default processing (reporting the compile-time exception)
throw new RuntimeException(e);
// e.printStackTrace();
}
}
// connect to the database and return Connection
public static Connection getConnection(){
try {
return DriverManager.getConnection(url,user,password);
}catch (SQLException e){
throw new RuntimeException(e);
}
}
// close related resources
/*
* 1. Result Set
* 2. Statement or PreparedStatement
* 3. Connection
*/
public static void close(ResultSet set, Statement statement, Connection connection){
// check for NULL
try {
if(set !=null){
set.close();
}
if(statement!=null){
statement.close();
}
if (connection!=null){
connection.close();
}
} catch (SQLException e) {
// convert to runtime exception and throw
throw new RuntimeException(e);
}
}
}
Use
package com.lyxlearn.utils;
import org.junit.jupiter.api.Test;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class JDBCUtils_Use {
@Test
//dml
public void testDML() throws SQLException {
Connection connection = JDBCUtils.getConnection();
String sql = "update actor1 set name = ? where number = ?";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1,"小红");
preparedStatement.setString(2,"2020200");
int rows = preparedStatement.executeUpdate();
System.out.println(rows > 0 ? "Success":"Failure");
JDBCUtils.close(null,preparedStatement,connection);
}
@Test
//query
public void testQuery() throws SQLException {
Connection connection = JDBCUtils.getConnection();
String sql = "select name , sex from actor1 where number = ?";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1,"2020200");
ResultSet resultSet = preparedStatement.executeQuery();
while (resultSet.next()){
String name = resultSet.getString(1);
String sex = resultSet.getString(2);
System.out.println(name + "\t" + sex);
}
JDBCUtils.close(resultSet,preparedStatement,connection);
}
}
Transaction
In JDBC, transactions are automaticaly committed. What is automatic commit? As soon as any DML statement is executed, it is automatically committed. This is the default transaction behavior in JDBC. However, in actual business scenarios, usually N DML statements are combined to complete a task, and it must be ensured that these DML statements succeed or fail together. Solution: three important lines of code conn.setAutoCommit(false); // manual commit conn.commit(); // commit transaction conn.rollback(); when an exception occurs or a program error happens, rollback.
package com.hspedu.jdbc.transaction_;
import com.hspedu.jdbc.utils.JDBCUtils;
import org.junit.jupiter.api.Test;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
/**
* Demonstrate how to use transactions in JDBC
*/
public class Transaction_ {
// no transaction used.
@Test
public void noTransaction() {
// perform transfer operations
// 1. get connection
Connection connection = null;
// 2. organize a sql
String sql = "update account set balance = balance - 100 where id = 1";
String sql2 = "update account set balance = balance + 100 where id = 2";
PreparedStatement preparedStatement = null;
// 3. create PreparedStatement object
try {
connection = JDBCUtils.getConnection(); // in default, connection is auto-committed
preparedStatement = connection.prepareStatement(sql);
preparedStatement.executeUpdate(); // execute first sql
int i = 1 / 0; // throw exception
preparedStatement = connection.prepareStatement(sql2);
preparedStatement.executeUpdate(); // execute third sql
} catch (SQLException e) {
e.printStackTrace();
} finally {
// close resources
JDBCUtils.close(null, preparedStatement, connection);
}
}
// use transaction to solve
@Test
public void useTransaction() {
// perform transfer operations
// 1. get connection
Connection connection = null;
// 2. organize a sql
String sql = "update account set balance = balance - 100 where id = 1";
String sql2 = "update account set balance = balance + 100 where id = 2";
PreparedStatement preparedStatement = null;
// 3. create PreparedStatement object
try {
connection = JDBCUtils.getConnection(); // in default, connection is auto-committed
// set connection to not auto-commit
connection.setAutoCommit(false); // start transaction
preparedStatement = connection.prepareStatement(sql);
preparedStatement.executeUpdate(); // execute first sql
int i = 1 / 0; // throw exception
preparedStatement = connection.prepareStatement(sql2);
preparedStatement.executeUpdate(); // execute third sql
// commit transaction
connection.commit();
} catch (Exception e) {
// we can roll back, i.e., undo the executed SQL
// default rolls back to the state at the beginning of the transaction.
System.out.println("An exception occurred, rolling back the executed SQL");
try {
connection.rollback();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
e.printStackTrace();
} finally {
// close resources
JDBCUtils.close(null, preparedStatement, connection);
}
}
}
Lock
Concepts of Pessimistic Lock and Optimistic Lock
Transaction 1 -> reads version number 1.1 Transaction 2 -> reads version number 1.1
Transaction 1 modifies first, after modification, it reads the version number as 1.1, then submits the modified data and changes the version number to 1.2 Transaction 2 modifies later, before submitting, it finds the version number is 1.2, which is inconsistent with the initial version number it read. Rollback.
Pessimistic lock: Transactions must be executed in sequence. Data is locked, no concurrency is allowed. (Row-level lock: add for update after select) Optimistic lock: Supports concurrency, transactions don't need to be queued, but requires a version number.
Row-level Lock - Pessimistic Lock
select ename, job, sal from emp where job= ' MANAGER ' for update;
Batch Processing
Before Batch Processing
After Batch Processing
Connection Pool
c3p0
user=root
password=123456
url=jdbc:mysql://localhost:3306/db01
driver=com.mysql.jdbc.Driver
public static void test() throws IOException, PropertyVetoException, SQLException {
// 1. Create a data source object
ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();
// 2. Get configuration information
Properties properties = new Properties();
properties.load(new FileInputStream("src\properties"));
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String url = properties.getProperty("url");
String driver = properties.getProperty("driver");
// 3. Set related parameters for the data source comboPooledDataSource
comboPooledDataSource.setDriverClass(driver);
comboPooledDataSource.setJdbcUrl(url);
comboPooledDataSource.setUser(user);
comboPooledDataSource.setPassword(password);
// 4. Set initial connection count
comboPooledDataSource.setInitialPoolSize(100);
// maximum number of connections
comboPooledDataSource.setMaxPoolSize(500);
long start = System.currentTimeMillis();
for(int i = 0; i < 5000; i ++) {
Connection connection = comboPooledDataSource.getConnection();
connection.close();
}
long end = System.currentTimeMillis();
System.out.println(end - start);
}
c3p0-config.xml Configuration File
<c3p0-config>
<!-- use the default configuration to read the database connection pool object -->
<named-config name="abc">
<!-- connection parameters -->
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/db01</property>
<property name="user">root</property>
<property name="password">123456</property>
<!-- connection pool parameters -->
<!-- the number of connections added each time -->
<property name="acquireIncrement">5</property>
<!-- the number of connections initially applied for -->
<property name="initialPoolSize">10</property>
<!-- the minimum number of connections -->
<property name="minPoolSize">5</property>
<!-- the maximum number of connections -->
<property name="maxPoolSize">50</property>
<!-- the maximum number of command objects that can be connected -->
<property name="maxStatements">5</property>
<!-- the maximum number of command objects per connection -->
<property name="maxStatementsPerConnection">2</property>
</named-config>
</c3p0-config>
public static void test() throws IOException, PropertyVetoException, SQLException {
// 1. Create a data source object
ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource("abc");
long start = System.currentTimeMillis();
for(int i = 0; i < 5000; i ++) {
Connection connection = comboPooledDataSource.getConnection();
connection.close();
}
long end = System.currentTimeMillis();
System.out.println(end - start);
}
druid
# druid.properties file configuration
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/db01
username=root
password=123456
# initial number of connections
initialSize=10
# minimum number of connections
minIdle=5
# maximum number of connections
maxActive=50
# maximum timeout
maxWait=3000
public void test() throws Exception {
Properties properties = new Properties();
properties.load(new FileInputStream("src\druid.properties"));
// create a database connection pool with specified parameters
DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
long start = System.currentTimeMillis();
for(int i = 0; i < 5000; i ++) {
Connection connection = dataSource.getConnection();
connection.close();
}
long end = System.currentTimeMillis();
System.out.println(end - start);
}
JDBCUtilsByDruid
public class JDBCUtilsByDruid {
private static DataSource ds;
static {
Properties properties = new Properties();
try {
properties.load(new FileInputStream("src//druid.properties"));
ds = DruidDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
throw new RuntimeException(e);
}
}
public static Connection getConnection() throws SQLException {
return ds.getConnection();
}
// return the connection to the connection pool, not close it
public static void close(ResultSet resultSet, Statement statement, Connection connection) {
try {
if(resultSet != null) {
resultSet.close();
}
if(statement != null) {
statement.close();
}
if(connection != null) {
connection.close();
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
Apache-DBUtils
public void test() throws Exception {
Connection connection = JDBCUtilsByDruid.getConnection();
QueryRunner queryRunner = new QueryRunner();
String sql = "select * from actor where id > ?";
// new BeanListHandler<>(Actor.class) converts resultset -> Actor object -> encapsulated into ArrayList
// the underlying uses reflection to get the attributes of the Actor class and encapsulates them
// the underlying resultset will be closed, and the PreparedStatment will also be closed
List<Actor> query =
queryRunner.query(connection, sql, new BeanListHandler<>(Actor.class), 1);
for (Actor actor : query) {
System.out.println(actor.getId() + actor.getName());
}
// return a single row record
String sql1 = "select * from actor where id = ?";
Actor query1 =
queryRunner.query(connection, sql, new BeanHandler<>(Actor.class), 1);
// return a single row and single column, returns Object
String sql2 = "select name from actor where id = ?";
Object query2 = queryRunner.query(connection, sql, new ScalarHandler<>(), 1);
JDBCUtilsByDruid.close(null,null,connection);
}
DML
public void test() throws Exception {
Connection connection = JDBCUtilsByDruid.getConnection();
QueryRunner queryRunner = new QueryRunner();
String sql = "update actor set name = ? where id = ?";
// execute DML operations with queryRunner.update()
// the return value is the number of affected rows
int row = queryRunner.update(connection, sql, "aaa", 1);
System.out.println(row > 0 ? "Execution successful" : "No impact");
JDBCUtilsByDruid.close(null,null,connection);
}
BasicDAO
public class BasicDAO<T> {
private QueryRunner qr = new QueryRunner();
public int update(String sql, Object...parameters) throws SQLException {
Connection connection = null;
try {
connection = JDBCUtilsByDruid.getConnection();
int update = qr.update(connection, sql, parameters);
return update;
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
JDBCUtilsByDruid.close(null,null,connection);
}
}
public List<T> queryMulti(String sql, Class<T> clazz, Object...parameters) throws SQLException {
Connection connection = null;
try {
connection = JDBCUtilsByDruid.getConnection();
List<T> query = qr.query(connection, sql, new BeanListHandler<T>(clazz), parameters);
return query;
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
JDBCUtilsByDruid.close(null,null,connection);
}
}
public T querySingle(String sql, Class<T> clazz, Object...parameters) {
Connection connection = null;
try {
connection = JDBCUtilsByDruid.getConnection();
return qr.query(connection, sql, new BeanHandler<T>(clazz), parameters);
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
JDBCUtilsByDruid.close(null,null,connection);
}
}
public Object queryScalar(String sql, Object...parameters) {
Connection connection = null;
try {
connection = JDBCUtilsByDruid.getConnection();
return qr.query(connection, sql, new ScalarHandler(), parameters);
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
JDBCUtilsByDruid.close(null,null,connection);
}
}
}
**ActorDAO, GoodsDAO can inherit BasicDAO, and be used in Service classes**