Springboot整合持久层（一）整合jdbcTemplate

1. jdbcTemplate介绍

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

   （1）创建数据库和表

CREATE DATABASE 'chapter' DEFAULT CHARACTER SET utf8;
USE 'chapter';
DROP TABLE IF EXISTS `book`;
CREATE TABLE `book` (
  `id` int(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(128) DEFAULT NULL,
  `author` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
INSERT INTO `book` VALUES ('1', '三国演义', '罗贯中');
INSERT INTO `book` VALUES ('2', '水浒传', '施耐庵');
           

（2）导入相关的依赖

<!--web-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</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>
        <!--druid数据源-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.9</version>
        </dependency>
           

spring-boot-starter-jdbc中提供了 spring-jdbc,另外还加入了数据库驱动依赖和数据库连接池依赖。

（3）数据库配置

#数据库配置
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource 
spring.datasource.url=jdbc:mysql://localhost:3306/chapter?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8
spring.datasource.username=root
spring.datasource.password=root
           

（4）创建实体类

public class Book {
	private	Integer id;
	private	String name;
	private	String author;
	//省略 getter/setter
}
           

（5）创建Dao层

@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 deleteBookByld(Integer id){
		return jdbcTemplate.update("DELETE FROM book WHERE id=?",id);
	}
	public Book getBookByld(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));
	}
}
           

（6）创建Service和Controller

//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 deleteBookByld(Integer id) {
		return bookDao.deleteBookByld(id);
	}
	public Book getBookByld(Integer id) {
		return bookDao.getBookByld(id);
	}
	public List<Book> getAllBooks() {
		return bookDao.getAllBooks();
	}
}

//Controller
@RestController
public class BookController{

	@Autowired
	BookService bookService;

	@GetMapping("/bookOps")
	public void bookOps() {

		Book bl = new Book();
		bl.setName("西厢记");
		bl.setAuthor("王实甫");
		int i = bookService.addBook(bl);
		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.getBookByld(1);
		System.out.println("getBookById»>" + b3);

		int delete = bookService.deleteBookByld(2);
		System.out.println("deleteBookById»>" + delete);

		List<Book> allBooks = bookService.getAllBooks();
		System.out.println("getALLBooks>»" + allBooks);
	}

}
           

（7）运行查看结果