1. Import jar package
Copy the jar to the lib directory, right-click the jar package - [Add as Library] and import it into the project
2 operation steps (addition, connection, language, execution and interpretation)
2.1 loading registration driver
Register the Driver through the following statement. The principle is that this statement will Class is loaded into the JVM, and then the JVM will execute the static code block of the bytecode. In the Driver package provided by mysql, the static code block in the bytecode of the Driver completes the creation and registration of the Driver object.
//Load registered driver
Class.forName("com.mysql.jdbc.Driver");
2.2 connecting to database
After registering the driver, we can get the Connection with the database through DriverManager. We need to pass in three parameters: database address, login user name and login password. Note that both the Connection and DriverManager classes are Java Under the SQL package, dbName is the name of the database.
//Connect database Connection conn = DriverManager.getConnection(jdbc:mysql://localhost:3306/dbName", "root", "123");
Verify that the connection has been obtained. You can view the running process using the command: show processlist on the mysql console.
2.3 create statement object
After getting the connection with the data, we also need to get a statement object through the connection. The statement object contains the operations we want to perform on the database, that is, SQL statements.
// Create statement object Statement statement = connection.createStatement();
2.4 execution statement
We generally use the executeUpdate and executeQuery methods of the statement object to execute SQL statements. The former is used for DDL and DML operations and returns the number of affected rows; The latter is used to perform DQL operations and return result set objects. The specific application methods are as follows:
// 4. Execute sql statement String sql = "create table t_student1 (id int primary key auto_increment, name varchar(20), age int)"; int state = statement.executeUpdate(sql);
2.5 releasing resources
// Release resources statement.close(); connection.close();
Conclusion: addition, connection, language, persistence and interpretation
package com.henu.demo.core;
import java.sql.*;
public class JdbcTest {
public static void main(String[] args) throws Exception {
String sql = "create table t_student1 (id int primary key auto_increment, name varchar(20), age int)";
// 1. Load the registered driver
Class.forName("com.mysql.jdbc.Driver");
// 2. Get the connection object
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?useSSL=false", "pangpd", "123123");
// 3. Create statement object
Statement statement = connection.createStatement();
// 4. Execute sql statement
int state = statement.executeUpdate(sql);
// 5. Release resources
statement.close();
connection.close();
}
}
3. Code refactoring
In 2.4, we demonstrate how to connect to the database for a table creation operation. In fact, there are such methods in the implementation class of DAO, such as adding, deleting, modifying and querying. If such processes are written in each method, it will cause a lot of trouble in later maintenance. If the connection password is changed, each method needs to be modified, which is obviously unrealistic, So we need to use tool classes and configuration documents to optimize the code.
The method in the implementation class should focus on the implementation of functions. Obtaining connections is a result required by the method. The method does not pay attention to this process and should not be placed in the method to confuse semantics. We can put the code to connect to the database in the tool class JdbcUtils. The members of this class are class members, and then we can directly call the getConnection() method through the class in the method of the implementation class to obtain the connection. At the same time, we only need to execute the step of registering the driver once. We can put this step in the static code block of the tool class, which will be automatically executed once when the class is initialized.
3.1 extract method jdbcutils (load the registered driver, obtain the connection object, and close the resource)
package com.henu.demo.core;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class JdbcUtils {
private static String url = "jdbc:mysql://localhost:3306/test?useSSL=false";
private static String user = "pangpd";
private static String password = "123123";
// Load registered driver
static {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
// Get connection object
protected static Connection getConnection() throws Exception {
Connection connection = null;
connection = DriverManager.getConnection(url, user, password);
return connection;
}
// close resource
protected static void colse(Connection connection, Statement statement) {
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
connection = null;
}
}
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
statement = null;
}
}
}
}
3.2 extract the information required for connection (jdbc.properties)
It is still inconvenient to put the information required for connection in the code block. If the user changes the account password or the host address, the code needs to be modified again, which does not meet the requirements of our usual applications. So we can put this information in one In the properties file, the Java program reads this information directly. After that, I changed the account password. I just need to open this myself Modify the corresponding fields in the properties file.
#key=value driverClassName=com.mysql.jdbc.Driver url=jdbc:mysql://127.0.0.1:3306/jdbc_demo username=root password=123123
Some key value pairs are stored in. properties. Note that there are no spaces around the equal sign and no punctuation at the end, # as comments
3.2.1 reading properties file
In Java, an input stream is obtained by reading the configuration file through the class loader. There are two methods to obtain the class loader:
//1. It can be obtained through a bytecode instance of a certain class
ClassLoader cl = Object.class.getContextClassLoader();
//2. Get through the current thread
ClassLoader cl = Thread.currentThread().getContextClassLoader();
//The input stream is obtained by reading the configuration file through the class loader
InputStream in = cl.getResourceAsStream("jdbc.properties");
How to get the corresponding information?
We can get the corresponding information through the Properties object. The Properties class is the next class under the Map abstract class, which is specially used to read the configuration file. Load the input stream, and you can get "value" through "key".
Properties p = new Properties();
p.load(in);
System.out.println(p.getProperty("driverClassName"));
3.2.2 reconstructed JdbcUtils
package com.henu.demo.core;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class JdbcUtils2 {
private static Properties p = new Properties();
// Load registered driver
static {
ClassLoader cl = Thread.currentThread().getContextClassLoader();
InputStream in = cl.getResourceAsStream("jdbc.properties");
try {
p.load(in);
Class.forName(p.getProperty("driverClass"));
} catch (Exception e) {
e.printStackTrace();
}
}
// Get connection object
protected static Connection getConnection() throws Exception {
Connection connection = null;
connection = DriverManager.getConnection(p.getProperty("url"), p.getProperty("user"), p.getProperty("password"));
return connection;
}
// close resource
protected static void colse(Connection connection, Statement statement) {
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
connection = null;
}
}
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
statement = null;
}
}
}
}
4. Introduce a connection pool (Druid) to manage connections
According to the above method, each time the user performs an operation, a connection will be established, and then the connection will be destroyed after execution. Although it does not take long to connect and disconnect each time, the consumption of system resources will become very high when the number of users comes. So we introduce connection pool to manage connections. There are a certain number of connections (generally 5 - 10) in the connection pool. When getConnection through the connection pool, the connection pool provides a connection for the method to use. When it is used, the method executes the close method of the connection. At this time, it does not close the connection directly, but returns the connection to the connection pool.
In Java, connection pooling uses javax sql. The datasource interface represents the connection pool.
Note: DataSource is only an interface, which is implemented by major server manufacturers. Implementation of common DataSource:
In Java, connection pooling uses javax sql. DataSource interface to represent the connection pool. Note: DataSource is only an interface, which is implemented by major server manufacturers. Implementation of common DataSource:
- DBCP: recommended by spring
- C3p0: recommended by hibernate
- Druid: (Druid) Alibaba is open source, with the best performance and the fastest speed
4.1Druid usage
Method 1:
//You need to import Druid's jar package
//Method 1:
//1 the DataSource type cannot be used when creating the connection pool object, because the setXxx method is unique to the DruidDataSource class
DruidDataSource dds = new DruidDataSource();
//2. Set the information of connecting to the database
dds.setDriverClassName(p.getProperty("driverClassName"));
dds.setUrl(p.getProperty("url"));
dds.setUsername(p.getProperty("username"));
dds.setPassword(p.getProperty("password"));
dds.setMaxActive(10); //maximum connection
Connection conn = dds.getConnection();
Method 2:
//Obtain connection pool through connection pool project
//1. Obtain the connection pool through the static method of the factory, pass in the Properties object above as the parameter, and the project will automatically read the configuration information DataSource ds = DruidDataSourceFactory.createDataSource(p); //2 get connected Connection conn = ds.getConnection();
4.2 tools after using Druid
package com.henu.demo.core;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class JdbcUtils3 {
private static Properties properties = new Properties();
private static DataSource dataSource = null;
// Load registered driver
static {
ClassLoader cl = Thread.currentThread().getContextClassLoader();
InputStream in = cl.getResourceAsStream("jdbc.properties");
try {
properties.load(in);
dataSource = DruidDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
e.printStackTrace();
}
}
// Get connection object
protected static Connection getConnection() throws Exception {
Connection connection = null;
connection = dataSource.getConnection();
return connection;
}
// close resource
protected static void colse(Connection connection, Statement statement) {
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
connection = null;
}
}
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
statement = null;
}
}
}
}