• execute(String  sql)   

Any sql statement can be executed, but the return value is void, so it is generally used for database creation, modification, deletion and data table record addition, deletion and modification.

 

 

• int  update(String sql) 
• int  update(String sql, Object...args)

Add, delete, args passes the actual parameter, and returns the number of affected records.

 

 

• int[]   batchUpdate(String...sql)   
• int[]   batchUpdate(String...sql, List<Object[]>  args)    

Execute sql statement in batch (add, delete and modify), and return the number of affected records.

 

 

• setMaxRows(int max) / / sets the maximum number of records returned
• setQueryTimeout(int timeout) / / set query timeout
• setDataSource(DataSource dataSource) / / set datasource

All the above three methods have corresponding get methods.

 

  

 

 

queryForObject() ා only one record can be returned and only one field can be queried

• <T> queryForObject(String sql, T.class) / / no parameters need to be passed to the sql statement
• <T> queryForObject(String sql, Object [] args, T.class) / / args is in the sql statement? Corresponding value
• <T>  queryForObject(String sql, T.class, Object... args)

     String sql="select id from student_tb where name = ? and gender = ?"
     Object[] args=new Object[]{"Zhang San",1};
     int id=jdbcTemplate.queryForObject(sql,args,int.class);

ForObject, as the name implies, must return only one record. If multiple records are returned or no records match, an error will be reported. Only one field can be queried.  

 

 

  

 

queryForList() ා can return 0 or more records, a List of common type can only query one field, and a List of Map type can query multiple fields

• List<T>  queryForList(String sql, T.class)
• List < T > queryForList(String sql, Object [] args, T.class) / / args is in the sql statement? Corresponding value of (actual parameter)
• List<T>  queryForList(String sql, T.class, Object...args)

        String sql="select name from student_tb";
        List<String> list=jdbcTemplate.queryForList(sql,String.class);
        for (String name:list){
            System.out.println(name);
        }  

List of basic data type, only one column of data table can be selected.

 

 

• List<Map<String,Object>>   queryForList(String sql)
• List<Map<String,Object>>   queryForList(String sql, Object...args)

    　　 String sql="select * from student_tb";
        List<Map<String,Object>> list=jdbcTemplate.queryForList(sql);
        //list All the records in the result set, one map Load a record
        for (Map<String,Object> map:list){
            //map Of key Is the field name, value Is the value of the field. get()The return value of is Object. 
            Object id = map.get("id");
            Object name = map.get("name");
            Object age = map.get("age");
            System.out.println("id: "+id+"\tname: "+name+"\tage: "+age);
        }

List of Map type, multiple columns can be selected. The field names are all strings, and the values may be of various types, so Map < String, Object > is used.

 

 

 

 

queryForMap() ා multiple fields can be queried, but only one record can be loaded

• Map<String, Obejct>   queryForMap(String sql)
• Map<String, Object>   queryForMap(String sql, Object...args)

        String sql="select * from student_tb where id = 1";
        Map<String,Object> map=jdbcTemplate.queryForMap(sql);
        Object name = map.get("name");
        Object age = map.get("age");
        System.out.println("name: "+name+"\tage: "+age);

Because it is a Map, you can install multiple fields, but only one record.

If more than one record is returned, only the first one will be loaded; if there is no matching record, an error will be reported.

 

 

 

 

queryForRowSet() (return result set)

• SqlRowSet  queryForRowSet(String sql)     
• SqlRowSet queryForRowSet(String sql, Object...args) / / args in sql statement? The corresponding value.

   　   String sql="select * from student_tb";
        SqlRowSet rowSet=jdbcTemplate.queryForRowSet(sql);
        //Traversal result set
        while (rowSet.next()){
            // Parameter specifies the column name. You can use the String You can also use the int Value of type (this field is in the first column of the result set, starting with 1)
            // int id = rowSet.getInt(1);
            int id = rowSet.getInt("id");
            String name = rowSet.getString("name");
            //......
        }

 

 

 

 

query() ා map the record of result set to List of bean type

• List<T>   query(String sql, RowMapper<T>  rowMapper)
• List<T>   query(String sql, Object[]  args, RowMapper<T>  rowMapper)
• List<T>   query(String sql, RowMapper<T>  rowMapper, Object...args)

 

bean:

public class Student {
    private int id;
    private String name;
    private int age;

    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 int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", age=" + age +
                '}';
    }
}

 

 

Use:

    　　 String sql="select * from student_tb";
        RowMapper<Student> rowMapper=new BeanPropertyRowMapper<>(Student.class);
        List<Student> list=jdbcTemplate.query(sql,rowMapper);
        System.out.println(list);

RowMapper is the interface, and BeanPropertyRowMapper is the only implementation class provided by spring.

 

query() can only map the records of the result set to a List of Bean type, not to a List of basic type.

When mapping, the fields in the result set will be automatically assigned to the member variable with the same name, so it is required that the member variable name of the Bean should be the same as the field name of the result set, which is rigid.

If there is no field with the same name in the result set, the member variable is not mapped (the value is the initial value assigned by the JVM).

When mapping, the setter method is called to assign a value to the Bean's member variable, so the Bean needs to provide a setter method.

 

 

In Spring, try not to use new to create Bean instances. The RowMapper instance above can be created as follows:

<bean name="beanPropertyRowMapper" class="org.springframework.jdbc.core.BeanPropertyRowMapper">
　　　   <!-- value Specify target type -->
        <property name="mappedClass" value="com.chy.model.Student" />
</bean>

RowMapper<Student> rowMapper = applicationContext.getBean("beanPropertyRowMapper",BeanPropertyRowMapper.class);