How Spring Boot integrates JdbcTemplate

Integrate JdbcTemplate

JdbcTemplate is a set of JDBC template framework provided by Spring, which uses AOP technology to solve the problem of a large number of repeated codes when using JDBC directly. Although JdbcTemplate is not as flexible as Mybatis, it is much more convenient than using JDBC directly. The use of JdbcTemplate in Spring Boot provides the automated configuration class JdbcTemplateAutoConfiguration. Part of the source code is as follows:

@Configuration
@ConditionalOnClass({DataSource.class, JdbcTemplate.class})
@ConditionalOnSingleCandidate(DataSource.class)
@AutoConfigureAfter({DataSourceAutoConfiguration.class})
@EnableConfigurationProperties({JdbcProperties.class})
public class JdbcTemplateAutoConfiguration {
    public JdbcTemplateAutoConfiguration() {
    }
    @Configuration
    @Import({JdbcTemplateAutoConfiguration.JdbcTemplateConfiguration.class})
    static class NamedParameterJdbcTemplateConfiguration {
        NamedParameterJdbcTemplateConfiguration() {
        }
        @Bean
        @Primary
        @ConditionalOnSingleCandidate(JdbcTemplate.class)
        @ConditionalOnMissingBean({NamedParameterJdbcOperations.class})
        public NamedParameterJdbcTemplate namedParameterJdbcTemplate(JdbcTemplate jdbcTemplate) {
            return new NamedParameterJdbcTemplate(jdbcTemplate);
        }
    }
    @Configuration
    static class JdbcTemplateConfiguration {
        private final DataSource dataSource;
        private final JdbcProperties properties;

        JdbcTemplateConfiguration(DataSource dataSource, JdbcProperties properties) {
            this.dataSource = dataSource;
            this.properties = properties;
        }
        @Bean
        @Primary
        @ConditionalOnMissingBean({JdbcOperations.class})
        public JdbcTemplate jdbcTemplate() {
            JdbcTemplate jdbcTemplate = new JdbcTemplate(this.dataSource);
            Template template = this.properties.getTemplate();
            jdbcTemplate.setFetchSize(template.getFetchSize());
            jdbcTemplate.setMaxRows(template.getMaxRows());
            if (template.getQueryTimeout() != null) {
                jdbcTemplate.setQueryTimeout((int)template.getQueryTimeout().getSeconds());
            }
            return jdbcTemplate;
        }
    }
}

It can be seen from the source code that automatic configuration will take effect when DataSource and JdbcTemplate exist under the classpath and there is only one instance of DataSource. , if the developer does not provide JdbcOperations, Spring Boot will automatically inject a JdbcTemplate into the container (JdbcTemplate is a subclass of JdbcOperations). Therefore, developers who want to use JdbcTemplate only need to provide JdbcTemplate dependencies and DataSource dependencies.

Create databases and tables

Create tables in the database, as follows:

CREATE TABLE `book` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(128) DEFAULT NULL,
  `author` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
INSERT INTO `chapter05`(`id`, `name`, `author`) VALUES (1, '斗罗大陆Ⅰ', '唐家三少');
INSERT INTO `chapter05`(`id`, `name`, `author`) VALUES (2, '斗罗大陆Ⅱ', '唐家三少');

Create Project

Create a Spring Boot project and add dependencies

<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <scope>runtime</scope>
</dependency>
<dependency>
  <groupId>com.alibaba</groupId>
  <artifactId>druid</artifactId>
  <version>1.1.9</version>
</dependency>

spring-boot-starter-jdbc provides spring-jdbc, and also adds database driver dependencies and database connection pool dependencies

Database configuration

Configure basic database connection information in application.properties

##spring.datasource.type=com.alibaba.druid.pool.DruidDataSource

spring. datasource.url=jdbc:mysql://localhost:3306/weirdo
spring.datasource.username=root
spring.datasource.password=root

Create entity class

Create the Book entity class, the code is as follows:

public class Book {
    private int id;
    private String name;
    private String author;
    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 String getAuthor() {
        return author;
    }
    public void setAuthor(String author) {
        this.author = author;
    }
}

Create the database access layer

Create BookDao, the code is as follows:

@Repository
public class BookDao {
    @Autowired
    JdbcTemplate jdbcTemplate;
    public int addBook(Book book) {
        return jdbcTemplate.update("INSERT INTO book(name,author) VALUES (?,?)",
                book.getName(), book.getAuthor());
    }
    public int updateBook(Book book) {
        return jdbcTemplate.update("UPDATE book SET name=?,author=? WHERE id=?",
                book.getName(), book.getAuthor(), book.getId());
    }
    public int deleteBookById(Integer id) {
        return jdbcTemplate.update("DELETE FROM book WHERE id=?", id);
    }
    public Book getBookById(Integer id) {
        return jdbcTemplate.queryForObject("select * from book where id=?",
                new BeanPropertyRowMapper<>(Book.class), id);
    }
    public List<Book> getAllBooks() {
        return jdbcTemplate.query("select * from book",
                new BeanPropertyRowMapper<>(Book.class));
    }
}

Code explanation:

• Create BookDao and inject jdbcTemplate. Since spring-jdbc related dependencies have been added, JdbcTemplate will be automatically registered in the Spring container, so JdbcTemplate can be directly injected here. Use

• to add, delete, and modify three types of operations in JdbcTemplate. Mainly used to complete the update and batchUpdate methods, query and queryForObject methods are mainly used to complete the query function. In addition, there is also the execute method that can be used to execute arbitrary sql, the call method that is used to call stored procedures, etc.

• When executing a query operation, a RowMapper is required to combine the queried columns and There is one-to-one correspondence between attributes in entity classes. If the column name and attribute name are the same, you can use BeanPropertyRowMapper directly; if the column name and attribute name are different, the developer needs to implement the RowMapper interface by himself to map the columns and entity class attributes one-to-one

Create Service and Controller

Create BookService and BooKController

@Service
public class BookService {
    @Autowired
    BookDao bookDao;
    public int addBook(Book book) {
        return bookDao.addBook(book);
    }
    public int updateBook(Book book) {
        return bookDao.updateBook(book);
    }
    public int deleteBookById(Integer id) {
        return bookDao.deleteBookById(id);
    }
    public Book getBookById(Integer id) {
        return bookDao.getBookById(id);
    }
    public List<Book> getAllBooks() {
        return bookDao.getAllBooks();
    }
}

@RestController
public class BookController {
    @Autowired
    BookService bookService;
    @GetMapping("/bookOps")
    public void bookOps() {
        Book b1 = new Book();
        b1.setId(99);
        b1.setName("西厢记");
        b1.setAuthor("王实甫");
        int i = bookService.addBook(b1);
        System.out.println("addBook>>>" + i);
        Book b2 = new Book();
        b2.setId(1);
        b2.setName("朝花夕拾");
        b2.setAuthor("鲁迅");
        int updateBook = bookService.updateBook(b2);
        System.out.println("updateBook>>>"+updateBook);
        Book b3 = bookService.getBookById(1);
        System.out.println("getBookById>>>"+b3);
        int delete = bookService.deleteBookById(2);
        System.out.println("deleteBookById>>>"+delete);
        List<Book> allBooks = bookService.getAllBooks();
        System.out.println("getAllBooks>>>"+allBooks);
    }
}

Finally access the http://localhost:8081/bookOps address in the browser, and the console prints the log as follows:

addBook>>>1

updateBook>>>1
getBookById>>>com.sang.Book@35e33288
deleteBookById>>>1
getAllBooks>>>[com.sang.Book@2f7c2d6d, com.sang.Book@32db4b36]

The data in the database is as follows:

The above is the detailed content of How Spring Boot integrates JdbcTemplate. For more information, please follow other related articles on the PHP Chinese website!