SpringBoot--整合JDBC实例

整合JDBC

JdbcTemplate是Spring提供的一套JDBC模板框架，利用AOP技术来解决直接使用JDBC时大量重复代码的问题。JdbcTemplate虽然没有MyBatis那么灵活，但是比直接使用JDBC要方便很多。SpringBoot中对JdbcTemplate的使用提供了自动化配置类JdbcTemplateAutoConfiguration，部分源码如下：

@Configuration
@ConditionalOnClass({DataSource.class, JdbcTemplate.class})
@ConditionalOnSingleCandidate(DataSource.class)
@AutoConfigureAfter({DataSourceAutoConfiguration.class})
public class JdbcTemplateAutoConfiguration {
    private final DataSource dataSource;

    public JdbcTemplateAutoConfiguration(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    @Bean
    @Primary
    @ConditionalOnMissingBean({JdbcOperations.class})
    public JdbcTemplate jdbcTemplate() {
        return new JdbcTemplate(this.dataSource);
    }

    @Bean
    @Primary
    @ConditionalOnMissingBean({NamedParameterJdbcOperations.class})
    public NamedParameterJdbcTemplate namedParameterJdbcTemplate() {
        return new NamedParameterJdbcTemplate(this.dataSource);
    }
}

可以看出，当classpath下存在DataSource和JdbcTemplate并且DataSource只有一个实例时，自动配置才会生效，若开发者没有提供JdbcOperations，则SpringBoot会自动向容器中注入一个JdbcTemplate（JdbcTemplate是JdbcOperations的子类）。由此可以看到，若想使用JdbcTemplate，只需要提供JdbcTemplate的依赖和DataSource依赖即可。

创建数据库和表

CREATE DATABASE `chapter05` DEFAULT CHARACTER SET utf8;
USE `chapter05`;

CREATE TABLE `book` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(128) DEFAULT NULL,
`author` VARCHAR(64) DEFAULT NULL,
PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;


INSERT into `book`(`id`, `name`, `author`) VALUES(1, '三国演义', '罗贯中'), (2, '水浒传', '施耐庵');

添加依赖

	<dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-thymeleaf</artifactId>
        </dependency>

        <!-- JDBC -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</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>
    </dependencies>

数据库配置：appliaction.properties

spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.url=jdbc:mysql:///chapter05
spring.datasource.username=root
spring.datasource.password=123456

创建实体类

public class Book {
    private Integer id;
    private String name;
    private String author;
	//省略getter和setter、toString()
}

Dao层

@Repository
public class BookDao {

    @Autowired
    JdbcTemplate jdbcTemplate;

    public int addBook(Book book){
        String sql = "insert into book(name, author) values(?,?)";
        return jdbcTemplate.update(sql,book.getName(), book.getAuthor());
    }

    public int updateBook(Book book){
        String sql = "update book set name=?, author=? where id=?";
        return jdbcTemplate.update(sql, book.getName(), book.getAuthor(), book.getId());
    }

    public int deleteBookById(Integer id){
        String sql = "delete from book where id=?";
        return jdbcTemplate.update(sql, id);
    }

    public Book getBookById(Integer id){
        String sql = "select * from book where id=?";
        return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(Book.class), id);
    }

    public List<Book> getAllBooks(){
        String sql = "select * from book";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Book.class));
    }

}

Service层

@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();
    }

}

Controller

@RestController
public class BookController {
    @Autowired
    BookService bookService;

    @GetMapping("/bookOps")
    public void bookOps(){
        Book b1 = new Book();
        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);
    }
}

测试：
在浏览器访问 http://localhost:8080/bookOps

控制台打印：

addBook>>>1
updateBook>>>1
getBookById>>>Book{id=1, name='朝花夕拾', author='鲁迅'}
deleteBookById>>>0
getAllBooks>>>[Book{id=1, name='朝花夕拾', author='鲁迅'}, Book{id=6, name='西厢记', author='王实甫'}, Book{id=7, name='西厢记', author='王实甫'}]