JDBC -- accessing database: using PreparedStatement to implement CRUD operation

Access database

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.

In the java.sql package, there are three interfaces that define different ways to call the database:
Statement
—|PreparedStatement
—| CallableStatement

SQL injection attack

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 use Preparedstatement (from
Statement can replace statement.

PreparedStatement

PreparedStatement interface is a sub interface of Statement, which represents a precompiled SQL Statement

The PreparedStatement object can be obtained by calling the preparedStatement(String sql) method of the Connection object.

The parameters in the SQL statement represented by the PreparedStatement object are represented by a question mark (?). Call the setXxx() method of the PreparedStatement object to set these parameters. The setXxx() method has two parameters. The first parameter is the index of the parameter in the SQL statement to be set (starting from 1), and the second is the value of the parameter in the SQL statement to be set.
eg:

//1. Get database connection
conn = JDBCUtils.getConnection();
//2. Precompile the sql statement and return the instance of PreparedStatement
String sql = "update customers set name = ? where id = ?";
ps = conn.prepareStatement(sql);
//3. Fill placeholder
ps.setObject(1,"Mozart");
ps.setObject(2, 18);
//4. Implementation
ps.execute();

Encapsulate the connection and release of the database into a class:

/**
 * @author guochao
 * @date 2021/11/22
 * @Description Tool class for operating database
 */

public class JDBCUtils {

    /**
     *
     * @Description Get connection to database
     * @throws Exception
     */
    public static Connection getConnection() throws Exception {
        // 1. Read the four basic information in the configuration file
        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");

        Properties pros = new Properties();
        pros.load(is);

        String user = pros.getProperty("user");
        String password = pros.getProperty("password");
        String url = pros.getProperty("url");
        String driverClass = pros.getProperty("driverClass");

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

        // 3. Get connection
        Connection conn = DriverManager.getConnection(url, user, password);
        return conn;
    }
    /**
     *
     * @Description Close the connection and Statement
     * @param conn
     * @param ps
     */
    public static void closeResource(Connection conn,Statement ps){
        try {
            if(ps != null)
                ps.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if(conn != null)
                conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    /**
     *
     * @Description Close resource operation
     * @param conn
     * @param ps
     * @param rs
     */
    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();
        }
    }
}

Add, delete and modify:

@Test
	public void testCommonUpdate(){
//		String sql = "delete from customers where id = ?";
//		update(sql,3);
		
		String sql = "update `order` set order_name = ? where order_id = ?";
		update(sql,"DD","2");
		
	}
	
	//General addition, deletion and modification operations
	public void update(String sql,Object ...args){//The number of placeholders in sql is the same as the length of variable parameters!
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			//1. Get database connection
			conn = JDBCUtils.getConnection();
			//2. Precompile the sql statement and return the instance of PreparedStatement
			ps = conn.prepareStatement(sql);
			//3. Fill placeholder
			for(int i = 0;i < args.length;i++){
				ps.setObject(i + 1, args[i]);//Beware of parameter declaration error!!
			}
			//4. Implementation
			ps.execute();
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			//5. Closure of resources
			JDBCUtils.closeResource(conn, ps);
			
		}
	}

Query operation

ResultSet

The PreparedStatement object is created by calling the exclutequery () method of the object.

The ResultSet object encapsulates the result set of database operation in the form of logical table, and the ResultSet interface is implemented by the database manufacturer.

The ResultSet object maintains a cursor pointing to the current data row. Initially, the cursor is before the first row and can be moved to the next row through the next() method of the ResultSet object.

@Test
	public void testQuery1() {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet resultSet = null;
		try {
			conn = JDBCUtils.getConnection();
			String sql = "select id,name,email,birth from customers where id = ?";
			ps = conn.prepareStatement(sql);
			ps.setObject(1, 1);
			
			//Execute and return the result set
			resultSet = ps.executeQuery();
			//Processing result set
			if(resultSet.next()){//next(): judge whether the next item in the result set has data. If there is data, return true and move the pointer down; If false is returned, the pointer does not move down.
				
				//Get the field values of the current data
				int id = resultSet.getInt(1);
				String name = resultSet.getString(2);
				String email = resultSet.getString(3);
				Date birth = resultSet.getDate(4);
				
//Mode 1:
//System.out.println("id = " + id + ",name = " + name + ",email = " + email + ",birth = " + birth);
				
//Mode 2:
//	Object[] data = new Object[]{id,name,email,birth};
//Method 3: encapsulate data into an object (recommended)
				Customer customer = new Customer(id, name, email, birth);
				System.out.println(customer);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			//close resource
			JDBCUtils.closeResource(conn, ps, resultSet);
		}
	}

ResultSetMetaData class

An object that can be used to get information about the types 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.
getColumnTypeName(int column): retrieves the database specific type name of the specified column.
getColumnDisplaySize(int column): indicates the maximum standard width of the specified column, in characters.
isNullable(int column): indicates whether the value in the specified column can be null.
isAutoIncrement(int column): indicates whether the specified columns are automatically numbered so that they are still read-only

1. After getting the result set, how do you know which columns are in the result set? What is the column name - a description is required
The object of the ResultSet, that is, ResultSetMetaData

2. ResultSetMetaData: you can get the number of columns in the corresponding ResultSet and the column name of each column
Yeah.

  1. How to get ResultSetMetaData: just call the getMetaData() method of ResultSet
  2. Get the number of columns in the ResultSet: call getColumnCount() of ResultSetMetaData
    method
  3. Gets the alias of each column in the ResultSet. getColumnLabel() method

public class OrderForQuery {
	/*
	 * When the field name of the table is different from the attribute name of the class:
	 * 1. When you must declare sql, use the attribute name of the class to name the alias of the field
	 * 2. When using ResultSetMetaData, you need to use getColumnLabel() to replace getColumnName(),
	 *    Gets the alias of the column.
	 *  Note: if there is no alias for the field in sql, getColumnLabel() gets the column name
	 */
	@Test
	public void testOrderForQuery(){
		String sql = "select order_id orderId,order_name orderName,order_date orderDate from `order` where order_id = ?";
		Order order = orderForQuery(sql,1);
		System.out.println(order);
	}
	
	/**
	 * 
	 * @Description General query operation for order table
	 * @date 10:51:12 am
	 * @return
	 * @throws Exception 
	 */
	public Order orderForQuery(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]);
			}
			//Execute, get result set
			rs = ps.executeQuery();
			//Get metadata of result set
			ResultSetMetaData rsmd = rs.getMetaData();
			//Get the number of columns
			int columnCount = rsmd.getColumnCount();
			if(rs.next()){
				Order order = new Order();
				for(int i = 0;i < columnCount;i++){
					//Get the column value of each column: through ResultSet
					Object columnValue = rs.getObject(i + 1);
					//Through ResultSetMetaData
					//Get column name of column: getColumnName() -- not recommended
					//Get alias of column: getColumnLabel()
//					String columnName = rsmd.getColumnName(i + 1);
					String columnLabel = rsmd.getColumnLabel(i + 1);
					
					//Through reflection, assign the property of the object named columnName to the specified value columnValue
					Field field = Order.class.getDeclaredField(columnLabel);
					field.setAccessible(true);
					field.set(order, columnValue);
				}
				return order;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			
			JDBCUtils.closeResource(conn, ps, rs);
		}
		return null;
	}
}	

Order class: store query results

public class Order {
	private int orderId;
	private String orderName;
	private Date orderDate;
	
	
	public Order() {
		super();
	}
	public Order(int orderId, String orderName, Date orderDate) {
		super();
		this.orderId = orderId;
		this.orderName = orderName;
		this.orderDate = orderDate;
	}
	public int getOrderId() {
		return orderId;
	}
	public void setOrderId(int orderId) {
		this.orderId = orderId;
	}
	public String getOrderName() {
		return orderName;
	}
	public void setOrderName(String orderName) {
		this.orderName = orderName;
	}
	public Date getOrderDate() {
		return orderDate;
	}
	public void setOrderDate(Date orderDate) {
		this.orderDate = orderDate;
	}
	@Override
	public String toString() {
		return "Order [orderId=" + orderId + ", orderName=" + orderName + ", orderDate=" + orderDate + "]";
	}
}

PreparedStatement implements common query operations for different tables

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;
	}
	
	@Test
	public void testGetInstance(){
		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);
	}
	/**
	 * 
	 * @Description For general query operations on different tables, a record in the table is returned	
	 * @date 11:42:23 am
	 * @param clazz
	 * @param sql
	 * @param args
	 * @return
	 */
	public <T> T getInstance(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();

			if (rs.next()) {
				T t = clazz.newInstance();
				// Process each column in a row of data in the result set
				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);
				}
				return t;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.closeResource(conn, ps, rs);
		}
		return null;
	}
}

Tags: JDBC

Posted on Mon, 22 Nov 2021 13:03:59 -0500 by jakeruston