JDBC ~ resultset, use of resultsetmetadata, ORM idea, arbitrary field query of any table (JDBC Implementation)

ORM thought

• ORM Mapping thought （Object relational mapping）
• One data table corresponds to one Java class
• A record in the table corresponds to Java An object of class
• A field in the table corresponds to java An attribute of a class

ResultSet、ResultSetMetaData Use

ResultSet

• PreparedStatement Of executeQuery Method returns a resultSet object
• Return a piece of data in the form of a table
• It comes with a pointer to the first field of data ,next Method can control this pointer
• The first field subscript defaults to 1

ResultSetMetaData

• You can get the type of column and other information
• adopt ResultSet Of getMetaData() Methods to get
• Relationship between them
  

utilize JDBC Implement query operation

• Query is different from addition, deletion and modification , The addition, deletion and modification did not return , Only the records in the database have been modified , And the query has a return value , How to deal with the return value is the problem
• JDBC of use executeQuery Method handles the return value , And return a resultSet Result set
• The key point of query operation is the processing of result set

  public static void testQuery() throws Exception{
    
        Connection connection=JDBCUtils.getConnection();
        String sql="select id,name,balance from account where id=?";
        PreparedStatement statement=connection.prepareStatement(sql);
        statement.setInt(1,1);
        // perform , And return the result set 
        ResultSet resultSet=statement.executeQuery();
        // Processing result set 
        if(resultSet.next()){
    
            //next Method ： Judge whether there is data in the next item , There's data coming back true, And move the pointer down , If there is no data , Just go back to false, The pointer no longer moves down 
            // Get the field values of the current data 
            int id=resultSet.getInt(1);
            String name=resultSet.getString(2);
            Double balance=resultSet.getDouble(3);
            // Mode one ： System.out.println("id="+id+...);
            //  Mode two ： Object[] objects=new Object[]{id,name,balance};
            // Mode three ： Encapsulating data as an object 
           Table table= new Table(id,name,balance);
            System.out.println(table);
        }
         // close resource 
        JDBCUtils.closeResource(connection,statement);
        resultSet.close();
    }

utilize JDBC Realize the query of any field of a table

 public static Table testQueryBetter(String sql,Object...args)throws Exception{
    
        Connection connection=JDBCUtils.getConnection();
       PreparedStatement statement=connection.prepareStatement(sql);
       for(int i=0;i<args.length;i++){
    
           statement.setObject(i+1,args[i]);
       }
       ResultSet resultSet=statement.executeQuery();
       // Get metadata of result set ：ResultSetMetaData
        ResultSetMetaData rsmd=resultSet.getMetaData();
        // Get the number of columns through the metadata of the result set 
        int columnCount=rsmd.getColumnCount();
        if(resultSet.next()){
    
            Table table=new Table();
            for(int i=0;i<columnCount;i++){
    
              Object value=resultSet.getObject(i+1);
              // Get the column name of each column 
                String columnName=rsmd.getColumnLaber(i+1);
                // to table The specified attribute of the object is copied as value, By reflection 
                Field field=Table.class.getDeclaredField(columnName);
                field.setAccessible(true);
                field.set(table,value);
            }
            return table;
        }
        // close resource 
        JDBCUtils.closeResource(connection,statement);
        resultSet.close();
        return null;
    }

• If the field name of the table is different from the attribute name of the class , You can use the property name of the class as an alias for the field , Add... When querying
• Get the alias of the column of the table ：getColumnLabel Method , When there is no alias, it is the original name
• Connection to database Connection It's a rare resource , When used up, it must be released , The system may crash if it is not released

Query process

utilize JDBC Realize the query of any field of any table

public static <T> T  testQueryTable(Class<T> clazz,String sql,Object...args)throws Exception{
    
        Connection connection=JDBCUtils.getConnection();
        PreparedStatement ps=connection.prepareStatement(sql);
        for(int i=0;i<args.length;i++){
    
            ps.setObject(i+1,args[i]);
        }
        ResultSet resultSet=ps.executeQuery();
        ResultSetMetaData rsmd=resultSet.getMetaData();
        // To get the column name 
        int columCount=rsmd.getColumnCount();
        // Processing result set 
        if(resultSet.next()){
    
          T t = clazz.newInstance();
          // Process each column of this record 
          for(int i=0;i<columCount;i++){
    
              Object value=resultSet.getObject(i+1);
              // To get the column name , And set it to the specified object attribute through the column name , By reflection 
              String columnLabel=rsmd.getColumnLabel(i+1);
              Field field=clazz.getDeclaredField(columnLabel);
              field.setAccessible(true);
              field.set(t,value);
          }
          return  t;
        }
        // close resource 
        JDBCUtils.closeResource(connection,ps);
        resultSet.close();
        return null;
    }

summary

• JDBC There are two ideas when using ： Interface oriented programming and ORM thought
• Interface oriented programming is oriented to JDBC API , Not involving other third-party databases API
• ORM The idea is to return the result as an object when processing the result set （ Use string or Object Arrays are also ok）
• JDBC There are two programming techniques involved ：

1. ResultSetMateData Get the number of columns and alias in ：getColumnCount、getColumnLabel
2. Process the result with reflection , Specify the class and assign a value