Tip: After the article is written, the catalog can be generated automatically, how to generate the help document to the right

Preface

1. Database Operation

1.Statement

A database connection is used to send commands and SQL statements to the database server. After the connection is established, you need to access the database and execute SQL statements
SQL injection attacks (not recommended) can be resolved

2.PreparedStatement

The code is as follows (example):

; can be obtained by calling the preparedStatement() method of the Connection object
PreparedStatement object
The PreparedStatement interface is a subinterface of Statement and represents a precompiled SQL statement

update block:

// Add, delete and change operations
	public static void update(String sql, Object... args) {
		// 1. Get a connection to the database
		Connection conn = null;
		PreparedStatement ps = null;
		// 2. Pre-compile sql statements to return instances of PreparedStatement
		try {
			conn = JDBCUtil.getConnection();
			ps = conn.prepareStatement(sql);
			// 3. Fill placeholders
			for(int i = 0;i < args.length;i++){
				ps.setObject(i + 1, args[i]);
			}
			// 4. Execution
			ps.execute();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			// 5. Closing of resources
			JDBCUtil.closeResource(conn, ps);
		}

	}
public static void main(String[] args) {
		
		 String sql = "delete from customers where id = ?"; update(sql,3);
	}

2.ResultSet

The code is as follows (example):

Create the PreparedStatement object by calling its excuteQuery() method
The ResultSet object encapsulates the result set of database operations in a logical table, and the ResultSet interface is implemented by the database vendor
The ResultSet object maintains a cursor that points to the current row of data. Initially, the cursor can be passed before the first row
The next() method of the ResultSet object moves to the next line

public static void main(String[] args) throws Exception {
		Connection conn = null;
		PreparedStatement ps = null;

		ResultSet resultSet = null;
		try {
			conn = JDBCUtil.getConnection();
			String sql = "select id,name,email,birth from customers where id = ?";
			ps = conn.prepareStatement(sql);
			ps.setObject(1, 1);
			// Execute and return result set
			resultSet = ps.executeQuery();
			// Processing result set
			if (resultSet.next()) {// next(): Determines if the next item in the result set has data
				// Gets each field value of the current data
				int id = resultSet.getInt(1);
				String name = resultSet.getString(2);
				String email = resultSet.getString(3);
				Date birth = resultSet.getDate(4);
				// Encapsulate data as an object
				Customer customer = new Customer(id, name, email, birth);
				System.out.println(customer);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			// close resource
			JDBCUtil.closeResource(conn, ps, resultSet);
		}
 
	}

Encapsulate data as an object

public class Customer {
	
	private int id;
	private String name;
	private String email;
	private Date birth;
	public Customer() {
		super();
	}
	public Customer(int id, String name, String email, Date birth) {
		super();
		this.id = id;
		this.name = name;
		this.email = email;
		this.birth = birth;
	}
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getEmail() {
		return email;
	}
	public void setEmail(String email) {
		this.email = email;
	}
	public Date getBirth() {
		return birth;
	}
	public void setBirth(Date birth) {
		this.birth = birth;
	}
	@Override
	public String toString() {
		return "Customer [id=" + id + ", name=" + name + ", email=" + email + ", birth=" + birth + "]";
	}

2.ResultSetMetaData

The code is as follows (example):

An object that can be used to obtain information about the type and properties of columns in a ResultSet object
 ResultSetMetaData meta = rs.getMetaData();
getColumnName(int column): Gets the name of the specified column
getColumnLabel(int column): Gets the alias of the specified column
getColumnCount(): Returns the number of columns in the current ResultSet object.

//Query operations for Customers tables
	public static Customer uqeryfors(String sql, Object... args) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = JDBCUtil.getConnection();

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

			// Get metadata for the result set: ResultSetMetaData
			ResultSetMetaData rsmd = rs.getMetaData();
			// Get the number of columns in the result set from ResultSetMetaData
			int columnCount = rsmd.getColumnCount();

			if (rs.next()) {
				Customer cust = new Customer();
				// Processing each column in a row of data in a result set
				for (int i = 0; i < columnCount; i++) {
					// Get column values
					Object coulmValue = rs.getObject(i + 1);
					// Get the alias for each column
					/* String coulmnName = rsmd.getColumnClassName(i + 1); */
					String columnLabel = rsmd.getColumnLabel(i + 1);

					// The columnName property specified for the cust object, assigned to columValue: by reflection
					Field field = Customer.class.getDeclaredField(columnLabel);
					field.setAccessible(true);
					field.set(cust, coulmValue);

				}
				return cust;

			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JDBCUtil.closeResource(conn, ps, rs);
		}

		return null;

	}

public static void main(String[] args) {
		String sql = "select id,name,birth,email from customers where id = ?";
		Customer customer = uqeryfors(sql, 13);
		System.out.println(customer);
	}

 2. Return a record in a table for different table query operations

The code is as follows (example):

//Returns a record from a table for a common query operation on different tables
	public static <T> T getInstance(Class<T> clazz,String sql, Object... args) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = JDBCUtil.getConnection();

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

			rs = ps.executeQuery();
			// Get metadata for the result set: ResultSetMetaData
			ResultSetMetaData rsmd = rs.getMetaData();
			// Get the number of columns in the result set from ResultSetMetaData
			int columnCount = rsmd.getColumnCount();

			if (rs.next()) {
				T t = clazz.newInstance();
				// Processing each column in a row of data in a result set
				for (int i = 0; i < columnCount; i++) {
					// Get column values
					Object columValue = rs.getObject(i + 1);

					// Get column names for each column
					// String columnName = rsmd.getColumnName(i + 1);
					String columnLabel = rsmd.getColumnLabel(i + 1);

					// The columnName property specified for the t object, assigned to columValue: by reflection
					Field field = clazz.getDeclaredField(columnLabel);
					field.setAccessible(true);
					field.set(t, columValue);
				}
				return t;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.closeResource(conn, ps, rs);

		}

		return null;
	}

	public static void main(String[] args) {
		// TODO Auto-generated method stub
		String sql = "select id,name,email from customers where id = ?";
		Customer customer = getInstance(Customer.class,sql,12);
		System.out.println(customer);
		
		String sql1 = "select order_id orderId,order_name orderName from `order` where order_id = ?";
		Order order = getInstance(Order.class, sql1, 1);
		System.out.println(order);
	}

three And implement common query operations for different tables Multiple records

The code is as follows (example):

// Using PreparedStatement to implement common query operations for different tables
	public static <T> List<T> getForList(Class<T> clazz, String sql, Object... args) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = JDBCUtil.getConnection();

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

			rs = ps.executeQuery();
			// Get metadata for the result set: ResultSetMetaData
			ResultSetMetaData rsmd = rs.getMetaData();
			// Get the number of columns in the result set from ResultSetMetaData
			int columnCount = rsmd.getColumnCount();
			//Create Collection Object
			ArrayList<T> list = new ArrayList<T>();
			while (rs.next()) {
				T t = clazz.newInstance();
				// Processing each column in a row of data in the result set: assigning attributes to the t object
				for (int i = 0; i < columnCount; i++) {
					// Get column values
					Object columValue = rs.getObject(i + 1);

					// Get column names for each column
					// String columnName = rsmd.getColumnName(i + 1);
					String columnLabel = rsmd.getColumnLabel(i + 1);

					// The columnName property specified for the t object, assigned to columValue: by reflection
					Field field = clazz.getDeclaredField(columnLabel);
					field.setAccessible(true);
					field.set(t, columValue);
				}
				list.add(t);
			}
			
			return list;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.closeResource(conn, ps, rs);

		}

		return null;
	}
	
	public static void main(String[] args) {
		String sql = "select id,name,email from customers where id < ?";
		List<Customer> list = getForList(Customer.class,sql,11);
		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);
	}

4. Releasing Resources

Release ResultSet, Statement,Connection.
A database Connection is a very rare resource and must be released immediately after use.
If Connection does not shut down properly in time, it will cause system downtime. Cause of Connection
The principle is to create as late as possible and release as early as possible

summary

Two technologies

JDBC metadata: ResultSetMetaData

Gets the specified property by reflection and assigns it a value