JDBC overview

Data persistence

• Persistence: save data to a power down storage device for later use. In most cases, especially for enterprise applications, data persistence means saving the data in memory to the hard disk for "solidification", and the implementation process of persistence is mostly completed through various relational databases.

The main application of persistence is to store the data in memory in relational database. Of course, it can also be stored in disk file and XML data file.

Data storage technology in Java

In Java, database access technology can be divided into the following categories:

• JDBC direct access to database

• JDO (Java Data Object) technology

• Third party O/R tools, such as Hibernate, Mybatis, etc

JDBC is the cornerstone of java accessing database. JDO, Hibernate and MyBatis just better encapsulate JDBC.

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.
• If there is no JDBC, the Java program accesses the database as follows:
  
• With JDBC, the Java program accesses the database as follows:
  
• The summary is as follows:
  

JDBC architecture

• JDBC interface (API) includes two levels:
  • Application oriented API: Java API, abstract interface, for application developers to use (connect to the database, execute SQL statements, and obtain results).
  • Database oriented API: Java Driver API for developers to develop database drivers.

JDBC is a set of interfaces provided by sun company for database operation. java programmers only need to program for this set of interfaces.

Different database manufacturers need to provide different implementations for this set of interfaces. The collection of different implementations is the driver of different databases———— Interface oriented programming

JDBC programming steps

Get database connection

	@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]

Implement CRUD operation using PreparedStatement

Data type conversion table corresponding to Java and SQL

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

	// General query for different tables: return an object (version 1.0)
	public <T> T getInstance(Class<T> clazz, String sql, Object... args) {

		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			// 1. Get database connection
			conn = JDBCUtils.getConnection();

			// 2. Precompile the sql statement to obtain the PreparedStatement object
			ps = conn.prepareStatement(sql);

			// 3. Fill placeholder
			for (int i = 0; i < args.length; i++) {
				ps.setObject(i + 1, args[i]);
			}

			// 4. Execute executeQuery() to get the result set: ResultSet
			rs = ps.executeQuery();

			// 5. Get metadata of result set: ResultSetMetaData
			ResultSetMetaData rsmd = rs.getMetaData();

			// 6.1 get columncount and columnlabel through ResultSetMetaData; Get column value through ResultSet
			int columnCount = rsmd.getColumnCount();
			if (rs.next()) {
				T t = clazz.newInstance();
				for (int i = 0; i < columnCount; i++) {// Traverse each column

					// Get column value
					Object columnVal = rs.getObject(i + 1);
					// Gets the alias of the column: the alias of the column, using the property name of the class as the alias
					String columnLabel = rsmd.getColumnLabel(i + 1);
					// 6.2 using reflection, assign values to the corresponding attributes of the object
					Field field = clazz.getDeclaredField(columnLabel);
					field.setAccessible(true);
					field.set(t, columnVal);

				}

				return t;

			}
		} catch (Exception e) {

			e.printStackTrace();
		} finally {
			// 7. Close resources
			JDBCUtils.closeResource(conn, ps, rs);
		}

		return null;

	}

Note: the query operation implemented by PreparedStatement can replace the query operation implemented by Statement, and solve the problems of Statement concatenation and SQL injection.