current position:Home>JDBC ~ resultset, use of resultsetmetadata, ORM idea, arbitrary field query of any table (JDBC Implementation)

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

2022-05-15 07:30:14Salute-Y

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
     Insert picture description here

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
 Insert picture description here

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

copyright notice
author[Salute-Y],Please bring the original link to reprint, thank you.
https://en.chowdera.com/2022/135/202205142334279285.html

Random recommended