preface

Recently contacted projects use JdbcTemplate so frequently that they often have to check the API. Here, write about the common operations of adding, deleting, modifying and checking.

Introduction to JdbcTemplate

JdbcTemplate is Spring's encapsulation of JDBC to make JDBC easier to use. It is a part of Spring, and we don't need relationships when to establish connections and release resources.

The methods for executing SQL statements in the JdbcTemplate are roughly divided into three categories:

1. Execute method: all SQL statements can be executed. It is generally used to execute DDL statements (operations on table structures).
2. UPDATE method and batchUpdate method: used to execute DML statements such as INSERT, UPDATE and DELETE (addition, deletion and modification of data). batchUpdate method is used to execute batch related statements.
3. Query method and queryXxx method: used for DQL data query statement (query of data).
4. call method: used to execute stored procedures, functions and related statements.

The configuration of connection pool is not introduced here, only the operations of adding, deleting, modifying and querying are introduced.

Addition, deletion and modification

Using the Update method

API introduction:

public int update(final String sql): used to execute DML statements such as INSERT, UPDATE and DELETE.

There are entity classes

public class User implement Serializable {
  private int id;
  private String name;
  private age;
  ...
  //setter,getter
  ...
}

Data access layer operations

@Repository
public class UserDao{
  @Resource
  private JdbcTemplate jdbcTemplate;
  
  // JDBC template add operation
  public static void create01() throws Exception {
     //Like jdbc, use? Placeholder anti sql injection
     String sql = "INSERT INTO TB_USER VALUES (NULL, ?, ?);";

    jdbcTemplate.update(sql, "zhangsan", 22);
    jdbcTemplate.update(sql, "lisi", 24);
 	   
  }
  
  // JDBCTemplate add action 2
  public static void create02() throws Exception {
     String sql = "INSERT INTO TB_USER VALUES (NULL, ?, ?);";
	//Use the Object [] array as a parameter to update multiple pieces of data
    Object[] args = new Object[]{"zhangsan",23,"lisi",24};
    jdbcTemplate.update(sql, args);
  }

  // JDBC template update operation
  public static void update01() throws Exception {
      String sql = "UPDATE TB_USER SET name=?, age=? WHERE id=?;";

     Object[] args = new Object[]{"lucy",24,10};
    jdbcTemplate.update(sql, args);
  }
  
  
  // JDBC template delete operation
  public static void update01() throws Exception {
      String sql = "DELETE FROM TB_USER WHERE id=?;";

     Object[] args = new Object[]{9,10};
    jdbcTemplate.update(sql, args);
  }
  
}

Batch operation -- batchUpdate method

@Repository
public class UserDao{
  @Resource
  private JdbcTemplate jdbcTemplate;
  
  @AutoWired
  private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
  
  public void batchCreate(List<User> list) {
    SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(list.toArray());
    String sql = "INSERT INTO TB_USER(NAME,AGE) VALUES(:name, :age)";
    namedParameterJdbcTemplate.batchUpdate(sql, batch);
  }
  
  //Delete and modify operations are similar, which is the difference between sql statements.
  
}

Query operation

queryForInt returns an int integer

public int queryForInt(String sql): execute a query statement and return a value of type int.

// queryForInt returns an integer -- equivalent to using COUNT(*) 
public static void demo01() throws Exception {
   String sql = "SELECT id FROM TB_USER WHERE age > 18;";
   int forInt = jdbcTemplate.queryForInt(sql);
   System.out.println(forInt);
}

queryForObject returns String

Public t queryforobject (string SQL, class requiredtype): execute a query statement and return a specified type of data.

public static void demo02() throws Exception {
   String sql = "SELECT name FROM FROM WHERE id=10;";
   String name = jdbcTemplate.queryForObject(sql, String.class);
   System.out.println(name);
}

queryForMap returns a Map collection

Public Map < string, Object > queryformap (string SQL, object... Args): execute a query statement and put a record into a Map.

public static void demo03() throws Exception {
   String sql = "SELECT * FROM TB_USER WHERE id=?;";
   Map<String, Object> map = jdbcTemplate.queryForMap(sql, 6);
   System.out.println(map);
}

queryForList returns a List collection

Public List < Map < string, Object > > queryforlist (string SQL): execute a query statement and return a List set. The List stores Map type data.

public static void demo04() throws Exception {
   String sql = "SELECT * FROM TB_USER WHERE age > ?;";
   List<Map<String, Object>> list = jdbcTemplate.queryForList(sql, 18);
   for (Map<String, Object> map : list) {
      System.out.println(map);
   }
}

RowMapper returns a custom object

Public List query (string SQL, RowMapper, RowMapper): execute the query statement and return a List collection. The List stores the data of the type specified by RowMapper.

public static void demo05() throws Exception {
   String sql = "SELECT * FROM TB_USER WHERE age > ?;";
  List<User> list = jdbcTemplate.query(sql, new Object[]{18}, new RowMapper<User>(){
    @Override
    public User mapRow(ResultSet res, int i) throws SQLException {
      User user = new User();
      user.setId(res.getInt("ID"));
      user.setName(res.getString("NAME"));
      user.setAge(res.getInt("AGE"));
      return user;
    }
  });
   for (User user : list) {
      System.out.println(user);
   }
}

There are many overloaded methods in the methods mentioned above. The main reason is that the number of parameters is different, which can be used according to your own needs.

What's wrong? Welcome to make suggestions~