JDBC (Java database connection)

JDBC (Java database connectivity)

Is a Java API for executing SQL statements, which can provide unified access to a variety of relational databases. It is composed of a group of classes and interfaces written in Java language. JDBC provides a benchmark, according to which more advanced tools and interfaces can be built, so that database developers can write database applications. Java has the characteristics of firmness, security, easy to use, easy to understand and automatic download from the network. All you need is a way for Java applications to talk to different databases. The JDBC library includes APIs for each of the tasks mentioned below that are typically related to database usage.

  • Connect to the database.
  • Create SQL or MySQL statements.
  • Execute SQL or MySQL queries in the database.
  • View and modify generated records.

JDBC architecture

JDBC API supports two-tier and three-tier processing models for database access. Generally, JDBC architecture consists of two layers:

  • JDBC API: This provides an application connection to the JDBC manager.
  • Jdbc driver API: this supports JDBC manager to driver connections. The JDBC API uses the driver manager and database specific drivers to provide transparent connections to heterogeneous databases

JDBC core components

DriverManager: this class manages the list of database drivers. Use the communication sub protocol to send connection requests from java applications
Match the appropriate database driver.
Driver: this interface handles the communication with the database server. We rarely interact directly with the driver object. But use
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, with the database
All communication of is only through the connection object.
Statement: submit SQL statements to the database using objects created from this interface. In addition to executing stored procedures, some
The generation interface also accepts parameters.
ResultSet: after executing SQL queries using Statement objects, these objects save the data retrieved from the database. It as a
An iterator that allows us to move its data.
SQLException: this class handles any errors that occur in the database application

Use steps

  1. Import package: a package containing JDBC classes required for database programming. In most cases, using import java.sql. * is sufficient
  2. Register JDBC Driver: initialize the driver and open the communication channel with the database.
  3. Open Connection: you need to use the DriverManager.getConnection () method to create a Connection object that represents the physical Connection to the database.
  4. Execute query: you need to use an object of type Statement to build and submit SQL statements to the database.
  5. Extract data from the result set: you need to use the corresponding ResultSet.getXXX () method to retrieve data from the result set.
  6. Free resources: you need to explicitly close all database resources without relying on JVM garbage collection.

JDBC connection steps

-Import JDBC package: add the * import * statement of the Java language to the Java code to import the required classes.

-Register JDBC Driver: the JVM dynamically loads the required driver implementation into memory so that it can meet JDBC requests.

-Database URL configuration: This is to create a properly formatted address pointing to the database to connect to.

- create connection objects: finally, call the getConnection () method of the DriverManager object to establish the actual database connection.

Why does jdbc create driver object CSDN blog with reflection

Class.forName(); The most common way to register a driver is to use Java's class. Forname () method to dynamically load the driver's class file into memory and register it automatically

try {
    Class.forName("com.mysql.cj.jdbc.Driver");
}catch(ClassNotFoundException ex) {
    System.out.println("Error: unable to load driver class!");
    System.exit(1);
}

Database URL configuration

After loading the driver, you can establish a connection using the DriverManager.getConnection() method. Three overloaded DriverManager.getConnection () methods  

getConnection(String url)
getConnection(String url,Properties prop)
getConnection(String url,String user,String password)

  Create database connection object

String URL = "jdbc:mysql://localhost:3306/yhp2?serverTimezone=UTC";
String USER = "username";
String PASS = "password"
Connection conn = DriverManager.getConnection(URL, USER, PASS);

//Use Property
String URL = "jdbc:mysql://localhost:3306/yhp2?serverTimezone=UTC";
Properties info = new Properties( );
info.put( "user", "username" );
info.put( "password", "password" );
Connection conn = DriverManager.getConnection(URL, info);

//Close database connection
//To ensure that the connection is closed, you can provide a "finally" block in your code. A finally block is always executed regardless of whether an exception occurs.
To close the connection opened above, call close()
conn.close();

JDBC executes SQL statements

Once the connection is obtained, you can interact with the database. The JDBC Statement and PreparedStatement interfaces define methods and properties that enable you to send SQL commands and receive data from the database for general access to the database (state channel).

  • Statement is useful when using static SQL statements at run time. The statement interface cannot accept parameters.
  • PreparedStatement is used when SQL statements are used multiple times. The PreparedStatement interface accepts input parameters at run time.

Statement

To create a Statement object, you need to use the createStatement () method of the Connection object to create a Statement object before using the Statement object to execute an SQL Statement

Statement stmt = null;
try {
stmt = conn.createStatement( );
. . .
}
catch (SQLException e) {
. . .
}
finally {
...
}

After creating the Statement object, use it to execute an SQL Statement with three execution methods.

- boolean execute(String SQL): If it can be retrieved ResultSet Object, returns a Boolean value true; Otherwise return
 return false. Use this method to perform SQL DDL Statements or need to use real dynamic SQL Time.
- int executeUpdate(String SQL): Return affected SQL The number of rows affected by statement execution. Execution using this method is expected to affect
 Of multiple rows SQL Statement, for example INSERT,UPDATE or DELETE sentence.
- ResultSet executeQuery(String SQL): Return a ResultSet Object. When you want a result set, make
 Using this method is like using SELECT Same as statement.

Close the Statement object

Similar to closing a Connection object to save database resources, the Statement object should also be closed for the same reason. Just call the close () method. If you close the Connection object first, the Statement object is also closed. However, you should always explicitly close the Statement object to ensure proper cleanup.

Statement stmt = null;
try {
    stmt = conn.createStatement( );
    . . .
}
catch (SQLException e) {
    . . .
}
finally {
    stmt.close();
}

SQL injection

By inserting the SQL command into the WEB form to submit or enter the query string of the domain name or page request, it can finally deceive the server to execute the malicious SQL command. Specifically, it uses existing applications to inject (malicious) SQL commands into the background database engine for execution. It can get a database on a website with security vulnerabilities by entering (malicious) SQL statements in a WEB form, rather than executing SQL statements according to the designer's intention. For example, many film and television websites disclose VIP member passwords mostly through WEB forms, and the query characters are burst. Such forms are particularly vulnerable to SQL injection attacks.

String username ="user";
String password=" '123' or 1=1 ";
String sql="select * from users where username= '"+username+"' and password=
"+password;
At this time, even username And password They do not exist in the database and can still log in successfully

Preparedstatement (pre state channel)

The PreparedStatement interface extends the Statement interface, which provides a general Statement object with two advantages and additional functions. This Statement can provide parameters dynamically.

PreparedStatement pstmt = null;
ResultSet resultSet=null;
try {
    String SQL = "Update Employees SET age = ? WHERE id = ?";
    pstmt = conn.prepareStatement(SQL);
    pstmt.setInt(1,23);
    pstmt.setInt(2,1);
    resultSet = pstmt.executeQuery();
    . . .
}
catch (SQLException e) {
    . . .
}
finally {
    . . .
}

All parameters in JDBC are controlled by? Symbolic representation, which is called parameter marking. You must provide a value for each parameter before executing the SQL statement. The setXXX() method binds the value to the parameter, where XXX represents the Java data type to be bound to the value of the input parameter. If no value is provided, an SQLException is received. Each parameter tag is referenced by its sequential position. The first mark represents position 1, the next position 2, and so on. This method is different from Java array index (starting from 0).

PreparedStatement also needs to be closed

statement VS PreparedStatement

  • statement belongs to the state channel and PreparedStatement belongs to the pre state channel
  • The pre status channel compiles sql statements first and then executes them, which is more efficient than statement execution
  • Pre status channels support placeholders?, When assigning a value to a placeholder, the position starts with 1
  • The pre status channel can prevent sql injection. The reason is that the pre status channel processes values in the form of strings

ResultSet

The interface in java.sql.ResultSet represents the result set database query. The ResultSet object maintains a cursor pointing to the current row in the result set. The term "result set" refers to the row and column data contained in the ResultSet object. If no ResultSet type is specified, a type is automatically obtained_ FORWARD_ ONLY.

try {
    Statement stmt = conn.createStatement(
    ResultSet.TYPE_FORWARD_ONLY,
    ResultSet.CONCUR_READ_ONLY);
}catch(Exception ex) {
    ....
}finally {
    ....
}
------------------------
resultSet.getString(2)//Index of column (starting with 1)
resultSet.getString(2)//Listing

Relationship between two tables of JAVA operation

Multi table relational data processing

  • The database establishes the relationship between two tables through foreign keys
  • Entity classes establish two table relationships through attributes

Entity class requirements: class name = table name, column name = attribute name (foreign key columns generally do not generate attributes)

Database transaction

Transaction started on
- Connect to the database and execute a DML sentence insert,update or delete
- After the previous transaction, another one is entered DML sentence
 Transaction ended on
- implement commit or rollback sentence.
- Execute one DDL Statement, for example create table Statement, in which case it is executed automatically commit sentence.
- Execute one DDL Statement, for example grant Statement, in which case it is executed automatically commit. 
- Disconnect from database
- One was executed DML Statement, but the statement fails. In this case, it is invalid DML Statement execution rollback language
 Sentence.

Transaction application in JDBC

If the JDBC connection is in auto submit mode, by default, each SQL statement will be submitted to the database after completion.

Transactions control whether and when changes are applied to the database. It treats a single SQL statement or a group of SQL statements as a logical unit. If any statement fails, the whole transaction will fail. (for example, increase and decrease should be put in the same transaction)

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. Pass boolean false to setAutoCommit() to turn off autocommit. Pass true to reopen it.

conn.commit( );//Submit changes
conn.rollback( );//RollBACK 

try{
//Assume a valid connection object conn
    conn.setAutoCommit(false);
    Statement stmt = conn.createStatement();
    String SQL = "INSERT INTO Employees values (106, 20, 'Rita', 'Tez')";
    stmt.executeUpdate(SQL);
//Submit a malformed SQL statement that breaks
    String SQL = "INSERTED IN Employees VALUES (107, 22, 'Sita', 'Singh')";
    stmt.executeUpdate(SQL);
// If there is no error.
    conn.commit();}
catch(SQLException se){
// If there is any error.
    conn.rollback();
}

Savepoints

When setting a savepoint, you can define a logical rollback point in a transaction. If an error occurs through a savepoint, you can use the rollback method to undo all changes or save only changes made after the savepoint.

The Connection object has two new ways to help manage savepoints

-setSavepoint (String savepointName): defines a new Savepoint. It also returns a Savepoint object.

-Release Savepoint (Savepoint savepointName): deletes a Savepoint. Note that it requires a Savepoint object as a parameter. This object is usually a Savepoint generated by the setSavepoint () method.

try{
//Assume a valid connection object conn
    conn.setAutoCommit(false);
    Statement stmt = conn.createStatement();
    String SQL = "INSERT INTO Employees VALUES (23,'person1')";
    stmt.executeUpdate(SQL);
    Savepoint savepoint1 = conn.setSavepoint("Savepoint1");
    int a=3/0;
    String SQL = "INSERTED IN Employees VALUES (34,'person2')";
    stmt.executeUpdate(SQL);
    conn.commit();
}catch(Exception e){
    try {
        conn.rollback(savepoint1);
        conn.commit()//This will fall back to savepoint 1, and the first new statement will be saved to the database
    } catch (SQLException e1) {
        e1.printStackTrace();
    }
}

JDBC batch

Batch processing allows related SQL statements to be grouped into batches and submitted through a single call to the database. When multiple SQL statements are sent to the database at one time, the overhead of connecting to the database can be reduced and the performance can be improved.

Statement batch

Typical steps for batch processing using statement objects
- use createStatement()Method creation Statement Object.
- use setAutoCommit()take auto-commit Set to false . 
- use addBatch()Method on the created statement object SQL Statement into a batch.
- Use on created statement objects executeBatch()Method executes all SQL sentence.
- Finally, use commit()Method to commit all changes.
Statement stmt = conn.createStatement();
conn.setAutoCommit(false);
String SQL = "INSERT INTO Employees (id, first, last, age) VALUES(200,'Zia',
'Ali', 30)";
stmt.addBatch(SQL);
String SQL = "INSERT INTO Employees (id, first, last, age) VALUES(201,'Raj',
'Kumar', 35)";
stmt.addBatch(SQL);
String SQL = "UPDATE Employees SET age = 35 WHERE id = 100";
stmt.addBatch(SQL);
int[] count = stmt.executeBatch();
conn.commit();

PreparedStatement batch

1. Create using placeholders SQL sentence.
2. use prepareStatement() Method creation PrepareStatement Object.
3. use setAutoCommit()take auto-commit Set to false . 
4. use addBatch()Method on the created statement object SQL Statement into a batch.
5. Use on created statement objects executeBatch()Method executes all SQL sentence.
6. Finally, use commit()Method to commit all changes.
String SQL = "INSERT INTO Employees (id, first, last, age) VALUES(?, ?, ?, ?)";
PreparedStatement pstmt = conn.prepareStatement(SQL);
conn.setAutoCommit(false);
// Set the variables
pstmt.setInt( 1, 400 );
pstmt.setString( 2, "Pappu" );
pstmt.setString( 3, "Singh" );
pstmt.setInt( 4, 33 );
// Add it to the batch
pstmt.addBatch();
// Set the variables
pstmt.setInt( 1, 401 );
pstmt.setString( 2, "Pawan" );
pstmt.setString( 3, "Singh" );
pstmt.setInt( 4, 31 );
// Add it to the batch
pstmt.addBatch();
//add more batches
//Create an int[] to hold returned values
int[] count = stmt.executeBatch();
conn.commit();

Reflection processing result set

Assuming that the table to be queried has many columns, it is very cumbersome to call the instance property method of the bean many times when traversing the Resultset. Since the set/get method of the bean is consistent with the column name, you can use the reflection clz.getDeclaredMethods() to obtain all the corresponding set methods, and then call invoke assignment

Method[] methods =cla.getDeclaredMethods();
while(rs.next()){
    Object s=cla.newInstance();//Create object by calling parameterless construction
    for (String columnname : columnnames) {
        String name="set"+columnname;//setstuid
        for (Method method : methods) {
            if(method.getName().equalsIgnoreCase(name)){
                method.invoke(s,rs.getObject(columnname));//The corresponding set method is executed
                break;
        }    
    }
}
}

Define a tool class: repetitive operations can be encapsulated into a tool class

//1. Define the required tool class object / / 2. Load the driver / / 3. Get the connection / / 4. Create the channel / / 5. Assign a value to the placeholder. The value assigned to the placeholder is saved in the list / / 6. Add, delete, modify and call the method / / 7. Call the method during query / / 8. Close the resource

properties file saves database information - features: key value storage mode

db.properties
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/database
user=root
password=1234
//Read the properties file from the tool class
InputStream inputStream = 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("user");
password = properties.getProperty("password");

//Mode 2
static{
    //The parameter only needs to write the attribute file name, and there is no need to write the suffix
    ResourceBundle bundle = ResourceBundle.getBundle("db");
    driver = bundle.getString("driver");
    url = bundle.getString("url");
    username = bundle.getString("user");
    password = bundle.getString("password");
}
use ResourceBundle Access local resources
 During design, it is often necessary to access some configuration information suitable for local modification. If it is used as a static variable, it will be changed every time
 Need to recompile one class,.config It is not appropriate to save such information, and we need to ResourceBundle. 
adopt ResourceBundle,Need access to/WEB-INF/classes A suffix under the directory is properties Text for
 Type file to read the required value from it.

Database 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.

Custom connection pool

Connection pool classes should contain specific properties and methods (third-party connection pools are based on this idea)

  • Property: Collection (generic type is Connection)
  • Method: get connection   Recycle connection
Minimum number of connections:
Is the number of database connections maintained by the database. Therefore, if the application does not use database connections, there will be a large number of databases
 Resources are wasted.
Number of initialized connections:
The number of initialized database connections created when the connection pool started.
Maximum connections:
Is the maximum number of connections that the connection pool can apply for. If the database connection request exceeds this number, the subsequent database connection requests will be added to the waiting queue
 Column.
Maximum waiting time:
When there is no available connection, the maximum time for the connection pool to wait for the connection to be returned. If the time exceeds, an exception will be thrown. You can set the parameter to 0 or negative
 Number makes infinite waiting(Configure according to different connection pools). 

  The database connection pool will create initialSize connections during initialization. When there are database operations, one connection will be taken out of the pool. If the number of connections in use in the current pool is equal to maxActive, it will wait for some time and wait for other operations to release a connection. If the waiting time exceeds maxWait, an error will be reported; If the number of connections currently in use does not reach maxActive, judge whether the current connection is idle. If yes, use the idle connection directly. If not, establish a new connection. After the connection is used, instead of closing its physical connection, it is put into the pool for reuse by other operations.

Druid connection pool

Use step 1 to import the jar package and 2 to write the tool class

public class DruidUtils {
//Declare connection pool object
private static DruidDataSource ds;
static{
///Instantiate database connection pool object
ds=new DruidDataSource();
//Instantiate configuration object
Properties properties=new Properties();
try {
//Load profile content
properties.load(DruidUtils.class.getResourceAsStream("dbcpconfig.properties"));
//Set the full name of the driver class
ds.setDriverClassName(properties.getProperty("driverClassName"));
//Set the connected database
ds.setUrl(properties.getProperty("url"));
//Set user name
ds.setUsername(properties.getProperty("username"));
//Set password
ds.setPassword(properties.getProperty("password"));
//Set the maximum number of connections
ds.setMaxActive(Integer.parseInt(properties.getProperty("maxActive")));
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//Get connection object
public static Connection getConnection() {
try {
return ds.getConnection();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null
}
}

Tags: Java Database MySQL

Posted on Mon, 06 Dec 2021 16:44:34 -0500 by mnewhart