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 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 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[]; //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.
- How to get ResultSetMetaData: just call the getMetaData() method of ResultSet
- Get the number of columns in the ResultSet: call getColumnCount() of ResultSetMetaData
method - 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; } }