JDBC(Java DataBase Connectivity)
JDBC(Java DataBase Connectivity): a set of specifications (classes and interfaces) defined by sun company in order to simplify and unify Java database connection.
JDBC is a standard Java API for database independent connection between Java programming language and database.
Relationship between JDBC and MySQL driver: relationship between interface (JDBC) and Implementation (MySQL driver jar package)
JDBC API details
1 | DriverManager: Driver Manager (registered driver) | Driver: this interface handles communication with the database server |
2 | Connection: represents the connection object (get connection) | Transaction operation: 1.void setAutoCommit(boolean autoCommit) false -- start transaction manually 2.void commit(); Commit transaction 3.void rollback(); Rollback transaction |
3 | Statement: execute sql statement object | 1. Execute query Result executeQuery(String sql) returns the result set 2. Add, delete and modify int excuteUpdate(String sql) to return the number of affected rows |
4 | ResultSet: result set | After executing SQL queries using Statement objects, these objects save the data retrieved from the database. It acts as an iterator and moves the data queried by the ResultSet object. |
5 | PreparedStatement interface: inherits the Statement interface | Before executing the sql statement, compile the sql statement in advance. Use the PreparedStatement object to solve the sql injection vulnerability in the login case, which is more secure |
import com.mysql.jdbc.Driver;
import java.sql.*;
//0. Copy the mysql driver package to the module and add as library
//1. Register driver
//2. Get connected
//3. Create and execute sql statement objects
//4. Execute the sql statement to get the result
//5. Release resources
public class Test {
public static void main(String[] args) throws SQLException {
//1. Register driver
DriverManager.registerDriver(new Driver());
//2. Get connected
String url = "jdbc:mysql://localhost:3306/mysql_person";
String user = "root";
String password = "root";
Connection connection = DriverManager.getConnection(url, user, password);
//3. Create and execute sql statement objects
Statement statement = connection.createStatement();
//4. Execute the sql statement to get the result
String sql = "select * from user";
ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
System.out.println(resultSet.getObject("id"));
System.out.println(resultSet.getObject("username"));
System.out.println(resultSet.getObject("password"));
System.out.println(resultSet.getObject("nickname"));
}
//5. Release resources
resultSet.close();
statement.close();
connection.close();
}
}Extraction of JDBC utils tool class
db.properties configuration file:
driverClass=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mysql_person
username=root
password=root
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class JDBCUtils {
private static String driverClass;
private static String url;
private static String username;
private static String password;
static {
try {
// Create Properties object
Properties pro = new Properties();
// Load profile
InputStream is = JDBCUtils.class.getClassLoader().getResourceAsStream("db.properties");
pro.load(is);
// Fetch data
driverClass = pro.getProperty("driverClass");
url = pro.getProperty("url");
username = pro.getProperty("username");
password = pro.getProperty("password");
// Register driver
Class.forName(driverClass);
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* Get connection
* @return
* @throws Exception
*/
public static Connection getConnection() throws Exception {
// Get connection
Connection connection = DriverManager.getConnection(url, username, password);
// Return connection
return connection;
}
/**
* Release resources
* @param resultSet
* @param statement
* @param connection
*/
public static void release(ResultSet resultSet, Statement statement, Connection connection) {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
Why use connection pooling
The Connection object will create an object when JDBC is used, and the object will be closed after use. Each creation and destruction of an object is a time - consuming operation It needs to be optimized using Connection pooling.
When the program initializes, it initializes multiple connections and puts multiple connections into the pool (Collection). Every time you get it, you can get it directly from the connection pool After use, return the connection to the pool.
Connection pool principle
-
At the beginning of the program, a certain number of connections are created and placed in a container (Collection), which is called connection pool.
-
When using, directly take a created connection object from the connection pool and return it to the pool after use
-
If the connection in the pool is used up and the program needs to use the connection, wait for a period of time (eg: 3s). If the connection is returned within this period of time, take it for use; If the connection has not been returned, a new one will be created, but the newly created one will not be returned (destroyed)
-
Set selection LinkedList
-
Add and delete faster
-
The removeFirst() and addLast() methods in LinkedList are consistent with the principle of connection pool
-
1.C3P0 connection pool
-
Create C3P0 connection pool object, and put C3P0 config Copy the XML configuration file to the src path (the connection pool will automatically read the file)
-
The name of the configuration file cannot be modified
-
The configuration file must be placed under the src path
-
The value of the name attribute in the configuration file cannot be modified. Set must be removed for the set method that sets the connection parameters, and then the first letter becomes lowercase
-
db.properties configuration file:
driverClass=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mysql_person
username=root
password=root
import com.mchange.v2.c3p0.ComboPooledDataSource;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class C3P0Utils {
//-In the tool class, create a unique connection pool object (private static final)
private static final ComboPooledDataSource DATA_SOURCE = new ComboPooledDataSource();
//-In the tool class, define a public static method to obtain the connection pool object
/**
* Get connection pool
* @return
*/
public static DataSource getDataSource(){
return DATA_SOURCE;
}
//-In the tool class, define a public static method to obtain the connection object
/**
* Get connection
* @return
* @throws SQLException
*/
public static Connection getConnection() throws SQLException {
return DATA_SOURCE.getConnection();
}
//-In the tool class, define a public static method to release resources
/**
* Release resources
* @param resultSet
* @param statement
* @param connection
*/
public static void release(ResultSet resultSet, Statement statement, Connection connection) {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
2.druid connection pool
druid.properties configuration file:
# database connection parameters
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mysql_person
username=root
password=root
Parameters of # connection pool
initialSize=10
maxActive=20
maxWait=2000
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class DruidUtils {
//-In the tool class, define a member variable of connection pool type
private static DataSource dataSource;
//-In the static code block of the tool class, load the configuration file, create the connection pool object, and assign it to the member variable of the connection pool type
static {
try {
//-Create the Properties object and load the configuration file
Properties pro = new Properties();
InputStream is = DruidUtils.class.getClassLoader().getResourceAsStream("druid.properties");
pro.load(is);
//-Create the connection pool object of the DRUID and pass in the Properties object
dataSource = DruidDataSourceFactory.createDataSource(pro);
} catch (Exception e) {
e.printStackTrace();
}
}
//-In the tool class, a public static method is provided to obtain the connection pool object
public static DataSource getDataSource(){
return dataSource;
}
//-In the tool class, a public static method is provided to obtain the connection object
public static Connection getConnection() throws SQLException {
return dataSource.getConnection();
}
//-In the tool class, a public static method is provided to release resources
/**
* Release resources
* @param resultSet
* @param statement
* @param connection
*/
public static void release(ResultSet resultSet, Statement statement, Connection connection) {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
3.DbUtils
DbUtils is an open source tool class library that simply encapsulates JDBC provided by Apache organization. Using it can simplify the development of JDBC applications without affecting the performance of the program.
Be able to complete CRUD with DBUtils:
Create QueryRunner object: public QueryRunner (datasource);
Addition, deletion and modification: int update(String sql,Object... args);
Query: return value query (string SQL, resultsethandler < T > RSH, object... Args)
Implementation class of ResultSetHandler interface:
BeanHandler: if the query result is a record, the data of this record will be encapsulated into a javaBean object
BeanListHandler: it is suitable for multiple records in the query result. The data of each record will be encapsulated into a javaBean object, and then these javaBean objects will be added to the List collection
ColumnListHandler: if the query result is single column and multiple rows, all the data of this column will be stored in the List collection
ScalarHandler: if the query result is a single value, the value will be encapsulated into an object
Implementation class of ResultSetHandler interface:
//Requirement: use DBUtils to complete addition, deletion and modification
//1. Create a QueryRunner object and pass in the connection pool object
//2. Call the update method to pass in the sql statement and the parameter values required by the sql statement
public class Demo {
// 1. Create a QueryRunner object and pass in the connection pool object
QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
@Test
public void insert() throws SQLException {
// 2. Call the update method to pass in the sql statement and the parameter values required by the sql statement
int rows = qr.update("insert into user values(null,?,?,?)", "along", "123456", "Zhang San");
System.out.println("Number of rows affected:"+rows);
}
@Test
public void update() throws SQLException {
// 2. Call the update method to pass in the sql statement and the parameter values required by the sql statement
int rows = qr.update("update user set username = ? where id = ?", "al",10);
System.out.println("Number of rows affected:"+rows);
}
@Test
public void delete() throws SQLException {
// 2. Call the update method to pass in the sql statement and the parameter values required by the sql statement
int rows = qr.update("delete from user where id = ?", 10);
System.out.println("Number of rows affected:"+rows);
}
}MyBatis connection pool
Mybatis comes with its own connection pool. The DataSource data sources (connection pools) of mybatis are divided into three categories:
-
POOLED [default] uses the data source of the connection pool.
-
UNPOOLED does not use the data source of the connection pool.
-
JNDI uses JNDI to realize the data source. Different servers obtain different data sources Note: only web projects or Maven's war projects can be used. We use tomcat and the connection pool is dbcp.
During initialization, MyBatis parses the core configuration file and creates a corresponding type of data source DataSource according to the type attribute of < DataSource >, that is:
-
type = "POOLED": MyBatis will create PooledDataSource instance and use connection pool
-
type = "UNPOOLED": MyBatis will create an UnpooledDataSource instance. There is only one connection object that is not used
-
type = "JNDI": MyBatis will be from the JNDI service (tomcat... JBoss...) Find the DataSource instance and return to use. It is only available in web projects. It is used in the server. dbcp in tomcat will be used by default
-
Generally, we will not directly use the default connection pool of MyBatis because its efficiency is not high. When we learn Spring or in actual development, we will generally use the third-party connection pool [druid hikariCP]
//Requirements: use MyBatis to query all users and encapsulate them into the List set (MyBatis entry step)
//Create database, create Maven project [Javase], and add MyBatis dependency
//Create a java bean (User.java)
//Create dao interface (UserDao.java)
//Create dao interface mapping file (UserDao.xml)
//Create MyBatis core configuration file (MyBatis config. XML)
//Perform test
@Test
public void method01() throws IOException {
//1. You need to get the file stream of MyBatis core configuration file
InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
//2. Load the MyBatis core configuration file to obtain the SqlSessionFactory object
//Using builder mode and factory mode, SqlSessionFactory is equivalent to connection pool object
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
//3. Getting SqlSession object is equivalent to Connection object
SqlSession sqlSession = sqlSessionFactory.openSession();
//4. The proxy mode is used to obtain the proxy object of dao interface
UserDao userDao = sqlSession.getMapper(UserDao.class);
//5. Call method operation
List<User> list = userDao.findAll();
for (User user : list) {
System.out.println("user = " + user);
}
//6. Close object
sqlSession.close();
is.close();
}SqlSessionFactoryUtils tool class
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.IOException;
import java.io.InputStream;
public class SqlSessionFactoryUtils {
private static SqlSessionFactoryBuilder sqlSessionFactoryBuilder;
private static SqlSessionFactory sqlSessionFactory;
//Step 1.2 is defined in the static code block (the configuration file does not need to be read once, and only one sqlsessionfactory is required globally)
static {
//1. Read sqlmapconfig xml
InputStream is = null;
try {
is = Resources.getResourceAsStream("SqlMapConfig.xml");
//2. According to sqlmapconfig Create connection pool (sqlsessionxml)
sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
sqlSessionFactory = sqlSessionFactoryBuilder.build(is);
} catch (IOException e) {
e.printStackTrace();
} finally {
if (is != null) {
try {
is.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
/**
* Get SqlSession
* @return
* @throws IOException
*/
public static SqlSession openSqlSession() throws IOException {
//3. Open sqlsession (equivalent to Connection)
SqlSession sqlSession = sqlSessionFactory.openSession();
return sqlSession;
}
/**
* Commit to release resources
* @param sqlSession
*/
public static void commitAndClose(SqlSession sqlSession) {
if (sqlSession != null) {
sqlSession.commit();
sqlSession.close();
}
}
/**
* Rollback free resources
* @param sqlSession
*/
public static void rollbackAndClose(SqlSession sqlSession) {
if (sqlSession != null) {
sqlSession.rollback();
sqlSession.close();
}
}
}