Make writing a habit together! This is the 13th day of my participation in the “Gold Digging Day New Plan · April More Text Challenge”. Click here for more details.

Write at the front 👀

Today I’ll focus on using JDBC Template

What is Jdbc Template

JdbcTemplate is the Spring framework’s encapsulation of JDBC. The JdbcTemplate handles resource creation and release; He helps us avoid common mistakes, such as forgetting to always close connections; He runs core JDBC workflows, such as Statement creation and execution, while we only need to provide SQL statements and extract results.

Jdbc Template

1. Import the JAR package

  1. Commons logging – 1.2. The jar
  2. Spring beans – 5.0.0. RELEASE. The jar
  3. Spring – the core – 5.0.0. RELEASE. The jar
  4. Spring – JDBC – 5.0.0. RELEASE. The jar
  5. Spring – tx – 5.0.0. RELEASE. The jar

Create a JdbcTemplate object

  • Depends on the DataSource

3. Call the JdbcTemplate method to complete the CRUD operation

  • execute( ): Can execute all SQL statements, generally used to execute DDL statements.
  • update(String, Object...): Used to execute INSERT, UPDATE, and DELETE DML statements
  • batchUpdate(String, List<Object[]>): Batch add, delete and change
  • queryForMap( ): Queries a single row record, encapsulates the result set as a Map set, uses the column name as the key, and the value as the value to encapsulate the record as a Map set
    • Note: The queryForMap() method can only query a result set of 1 length
  • query(String, RowMapper<Department>, Object...): Queries multi-line records and encapsulates the query result as a JavaBean object
    • Parameter to query: RowMapper
    • Generally, BeanPropertyRowMapper implementation class is used to complete automatic encapsulation of data into JavaBean
    • New BeanPropertyRowMapper< type >(type.class)
  • queryForObject(String, Class, Object...): Queries a single value and encapsulates the query result as an object
    • Generally used for aggregate function queries

Three, for example

1 ️ ⃣ demand

  1. Changed Luffy’s fruit to rubber fruit
  2. Add an OP character record
  3. Delete the record you just added
  4. Query Query the record whose ID is 1 and encapsulate it as a Map set
  5. Query all records and encapsulate them as a List
  6. Query all records and encapsulate them as a List collection of OP objects
  7. Query the maximum bounty

2️ unit test on retail Junit

0. Obtain the JdbcTemplate object

//0. Get the JdbcTemplate object
private JdbcTemplate jdbcTemplate = new JdbcTemplate(DruidUtils.getDataSource());
Copy the code

1. Modify operations

/** * 1. Change lufei's fruit to rubber */
@Test
public void test1(a) {
    //1.1 Define SQL statements
    String sql = "Update op set fruit=' fruit 'where name=? ";
    jdbcTemplate.update(sql, "The road");
}
Copy the code
  • The demo results are as follows: 👇

2. Add operations

/** * 2. Add an op character record */
@Test
public void test2(a){
    2.1 Define SQL statements
    String sql = "insert into op(name,gender,fruit,reward,ity_id) values (? ,? ,? ,? ,?) ";
    / / 2.2 SQL execution
    jdbcTemplate.update(sql,"Marco".1."Birdy fruit - Mythical beast species - Not dead bird.".1374000000.1);
}
Copy the code
  • The demo results are as follows: 👇

3. Delete the vm

/** * 3. Delete the record */

@Test
public void test3(a){
    2.1 Define SQL statements
    String sql = "delete from op where name=?";
    / / 2.2 SQL execution
    int count =jdbcTemplate.update(sql,"Marco");
    System.out.println("Number of affected rows:"+count);
}

Copy the code
  • The demo results are as follows: 👇

4. Query single row records

/** * 4. Query Query the record whose ID is 1 and encapsulate it as a Map set */
@Test
public void test4(a) {
    4.1 Defining SQL statements
    String sql = "select * from op where id=?";
    / / 4.2 SQL execution
    Map<String, Object> map = jdbcTemplate.queryForMap(sql, 1);
    System.out.println(map);
}
Copy the code
  • The demo results are as follows: 👇

5. Query multi-line records

/** * 5. Query all records and encapsulate them as List */
@Test
public void test5(a){
    //5.1 Defining SQL statements
    String sql="select * from op";
    / / 5.2 SQL execution
    List<Map<String, Object>> mapList=jdbcTemplate.queryForList(sql);
    for(Map<String, Object> valueMap:mapList){ System.out.println(valueMap); }}Copy the code
  • The demo results are as follows: 👇

6. Encapsulate records as Javabeans

/** * 6. Query all records and encapsulate them as a List of op objects */
@Test
public void test6(a){
    //6.1 Defining SQL statements
    String sql = "select * from op";
    / / 6.2 SQL execution
    List<op> opList=jdbcTemplate.query(sql,new BeanPropertyRowMapper<op>(op.class));
    for(op opValue:opList ){ System.out.println(opValue); }}Copy the code
  • The demo results are as follows: 👇

7. Query a single value

/** * 7. Query the maximum bounty */

@Test
public void test7(a){
    //7.1 Defining SQL statements
    String sql = "select max(reward) from op";
    / / 7.2 SQL execution
    Long maxReward = jdbcTemplate.queryForObject(sql,Long.class);
    System.out.println("Highest reward:"+maxReward);
}
Copy the code
  • The demo results are as follows: 👇

Write it at the back 🍻

Thank you for watching ✨ have any deficiencies, welcome to point out oh 💖 nuggets operation students audit hard 💗