JDBC core technology

Posted by davidosullivan on Tue, 30 Nov 2021 11:56:57 +0100

JDBC introduction

JDBC(Java Database Connectivity) is a common interface (a set of API s) independent of a specific database management system and general SQL database access and operation. It defines the standard Java class libraries used to access the database (java.sql,javax.sql). These class libraries can be used to access database resources in a standard and convenient way.
JDBC provides a unified way to access different databases and shields some details for developers.
The goal of JDBC is to enable Java programmers to use JDBC to connect to any database system that provides JDBC drivers, so that programmers do not need to know too much about the characteristics of a specific database system, which greatly simplifies and speeds up the development process.

Get database connection

Several important elements are required to obtain the connection to the database:

1.Driver drive

The java.sql.Driver interface is the interface that all JDBC drivers need to implement. This interface is provided for database manufacturers. Different database manufacturers provide different implementations.
In the program, you do not need to directly access the classes that implement the Driver interface, but the Driver manager class (java.sql.DriverManager) calls these Driver implementations.
Load driver: to load the JDBC driver, you need to call the static method forName() of Class class and pass the Class name of the JDBC driver to be loaded
- Class.forName("com.mysql.jdbc.Driver");
Register driver: DriverManager class is the driver manager class, which is responsible for managing drivers
- Use DriverManager.registerDriver(com.mysql.jdbc.Driver) to register drivers
- Usually, it is not necessary to explicitly call the registerDriver() method of the DriverManager class to register an instance of the Driver class, because the Driver classes of the Driver interface contain static code blocks. In this static code block, the DriverManager.registerDriver() method will be called to register an instance of itself.

2.url

JDBC URL is used to identify a registered driver. The driver manager selects the correct driver through this URL to establish a connection to the database.
The standard JDBC URL consists of three parts separated by colons.
give an example:

3. User name and password

User and password can tell the database by "attribute name = attribute value"
You can call the getConnection() method of the DriverManager class to establish a connection to the database

With these key elements, we can successfully obtain the connection to the database:

	@Test
    public  void testConnection5() throws Exception {
    	//1. Load configuration file
        InputStream is = ConnectionTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
        Properties pros = new Properties();
        pros.load(is);
        
        //2. Read configuration information
        String user = pros.getProperty("user");
        String password = pros.getProperty("password");
        String url = pros.getProperty("url");
        String driverClass = pros.getProperty("driverClass");

        //3. Load drive
        Class.forName(driverClass);

        //4. Get connection
        Connection conn = DriverManager.getConnection(url,user,password);
        System.out.println(conn);

    }

The configuration file is declared in the src directory of the project: [jdbc.properties]

user=root
password=abc123
url=jdbc:mysql://localhost:3306/test
driverClass=com.mysql.jdbc.Driver

Implement CRUD operation using PreparedStatement

The database connection is used to send commands and SQL statements to the database server and accept the results returned by the database server. In fact, a database connection is a Socket connection.
In the java.sql package, there are three interfaces that define different ways to call the database:
- Statement: an object used to execute a static SQL statement and return the results it generates.
- PrepatedStatement: the SQL statement is precompiled and stored in this object, which can be used multiple times to execute the statement efficiently.
- CallableStatement: used to execute SQL stored procedures

We use the Preparedstatement to perform the following operations because there are disadvantages in using the Statement to operate the data table:

Problem 1: there is string splicing operation, which is cumbersome
Problem 2: SQL injection problem
SQL injection is to inject illegal SQL statement segments OR commands into the user input data (such as: SELECT user, password FROM user_table WHERE user = 'a' OR 1 = 'AND password =' OR '1' = '1') without sufficient inspection of the user input data, so as to use the system's SQL Engine to complete malicious acts.
For Java, to prevent SQL injection, just replace the Statement with Preparedstatement (extended from the Statement).

Add, delete and modify operations using PreparedStatement

//General add, delete and change operations (embodiment 1: add, delete and change; embodiment 2: for different tables)
	public void update(String sql,Object ... args){
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			//1. Get database connection
			conn = JDBCUtils.getConnection();
			
			//2. Get the instance of PreparedStatement (or: precompiled sql statement)
			ps = conn.prepareStatement(sql);
			//3. Fill placeholder
			for(int i = 0;i < args.length;i++){
				ps.setObject(i + 1, args[i]);
			}
			
			//4. Execute sql statement
			ps.execute();
		} catch (Exception e) {
			
			e.printStackTrace();
		}finally{
			//5. Close resources
			JDBCUtils.closeResource(conn, ps);
			
		}
	}

Query operation using PreparedStatement

/**
 * 
 * @Description Use PreparedStatement to implement common query operations for different tables
 * @author shkstart Email:shkstart@126.com
 * @version
 * @date 11:32:55 am
 *
 */
public class PreparedStatementQueryTest {
	
	@Test
	public void testGetForList(){
		
		String sql = "select id,name,email from customers where id < ?";
		List<Customer> list = getForList(Customer.class,sql,12);
		list.forEach(System.out::println);
		
		String sql1 = "select order_id orderId,order_name orderName from `order`";
		List<Order> orderList = getForList(Order.class, sql1);
		orderList.forEach(System.out::println);
	}
	
	public <T> List<T> getForList(Class<T> clazz,String sql, Object... args){
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = JDBCUtils.getConnection();

			ps = conn.prepareStatement(sql);
			for (int i = 0; i < args.length; i++) {
				ps.setObject(i + 1, args[i]);
			}

			rs = ps.executeQuery();
			// Get metadata of result set: ResultSetMetaData
			ResultSetMetaData rsmd = rs.getMetaData();
			// Get the number of columns in the result set through ResultSetMetaData
			int columnCount = rsmd.getColumnCount();
			//Create collection object
			ArrayList<T> list = new ArrayList<T>();
			while (rs.next()) {
				T t = clazz.newInstance();
				// Process each column in a row of data in the result set: assign a value to the attribute specified by the t object
				for (int i = 0; i < columnCount; i++) {
					// Get column value
					Object columValue = rs.getObject(i + 1);

					// Gets the column name of each column
					// String columnName = rsmd.getColumnName(i + 1);
					String columnLabel = rsmd.getColumnLabel(i + 1);

					// The columnName attribute specified to the t object is assigned a value of columnvalue: through reflection
					Field field = clazz.getDeclaredField(columnLabel);
					field.setAccessible(true);
					field.set(t, columValue);
				}
				list.add(t);
			}
			
			return list;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.closeResource(conn, ps, rs);

		}

		return null;
	}

Release of resources

public static void closeResource(Connection conn,Statement ps,ResultSet rs){
		try {
			if(ps != null)
				ps.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			if(conn != null)
				conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			if(rs != null)
				rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

Release ResultSet, Statement,Connection.
Database Connection is a very rare resource. It must be released immediately after use. If the Connection cannot be closed in time and correctly, it will lead to system downtime. The use principle of Connection is to create as late as possible and release as early as possible.
It can be closed in finally to ensure that resources can be closed in case of exceptions in other codes.

Next episode preview

In the next blog, let's talk about how to use Alibaba's database connection pool and the jdbc tool class provided by Apache Company to complete all the above work and make the code more concise and efficient. First, let's release a preview of the figure:

Topics: Java JDBC SQL

Programmer Think