This is the sixth day of my participation in the First Challenge 2022

Today we will use JDBC to access the database. We will use SpringBoot JDBC to operate the database.

The appearance of connection pooling

Let’s think about the problem with not having a connection pool:

  1. Multiple applications may use the same connection, and if one application runs out and is not returned, other applications may freeze.
  2. The overhead of connection resource application and destruction is very high, which makes the management of connection technology extremely urgent
  3. Database connection resources are limited, if everyone to apply, it is easy to be squeezed dry

We recognize the importance of connection pools, so how do we choose one? I briefly summarize the following points:

  1. Reliability: this is certainly not too much to say, to have automatic detection of problems and a certain problem recovery ability.
  2. Performance: As little overhead as possible
  3. Features: Add more features without compromising performance
  4. Security: for example, encrypt database passwords
  5. Extensibility: trace comments, intercept day processing, etc

Well, with the above knowledge we can start today’s content, SpeingBoot JDBC learning

Native JDBC operation database

Using native JDBC to manipulate a database is a bit more complicated, but that’s not the point, just look at it:

  1. Registration drive
  2. Get a connection
  3. Create the Statement
  4. Database query
  5. Result set parsing
  6. Close the connection

Just doing a database query requires a lot of code, no transactions, and the code is not easy to maintain. If you use SpringBoot, as described earlier, it helps you configure data sources, transaction managers, and JDBC templates according to the configuration, simply out of the box, as shown below.

SpringBoot operates the database

A simple query

1. Print the queried data to the console using JDBC

jdbcTemplate.queryForList("select * from Foo").stream().forEach(s->log.info(s.toString()));
Copy the code

2. Insert multiple inserts

public void insertData(){ Arrays.asList("1","2").forEach(x->template.update( "INSERT INTO Foo (BAR) VALUES (?) ",x)); Map<String,String> row =new HashMap<>(); row.put("BAR","c"); Number n = insert.executeAndReturnKey(row); log.info("n:{}",n.longValue()); } Note: I inserted the last entry using the executeAndReturnKey of the SimpleJdbcInsert insert to get its primary keyCopy the code
@Bean
public SimpleJdbcInsert insert(JdbcTemplate jdbcTemplate) {
    return new SimpleJdbcInsert(jdbcTemplate).withTableName("Foo").usingGeneratedKeyColumns("ID");
}
Copy the code

3, query quantity queryForObj

log.info("Count:{}",template.queryForObject("select count(*) from Foo ",List.class));
Copy the code

4. Query the object content

List<String> barList = template.queryForList("select BAR from Foo", String.class);
barList.forEach(b->log.info("BAR:{}",b));
Copy the code

5. Query objects

List<Foo> list = template.query("select * from Foo", new RowMapper<Foo>() { @Override public Foo mapRow(ResultSet rs, int rowNum) throws SQLException { return Foo.builder() .id(rs.getLong(1)) .bar(rs.getString(2)) .build(); }}); list.forEach(f->log.info("Foo is{}",f));Copy the code

All the results are as follows: