SpringBoot--整合JDBC实例

素颜马尾好姑娘i 2021-08-13 12:56 382阅读 0赞

### 整合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>
    
            
            <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='王实甫'}]