整合JdbcTemplate

JdbcTemplate是Spring提供的一套JDBC模板框架，利用AOP技术来解决直接使用JDBC时大量重复代码的问题

• 创建数据库和表

mysql> CREATE DATABASE `chapter05` DEFAULT CHARACTER SET utf8;
mysql> use `chapter05`;
mysql> CREATE TABLE `book` (
    -> `id` int(11) NOT NULL AUTO_INCREMENT,
    -> `name` varchar(128) DEFAULT NULL,
    -> `author` varchar(64) DEFAULT NULL,
    -> PRIMARY KEY (`id`)
    -> );
mysql> insert into `book` values ('bookName1','author1');

• 创建项目 添加依赖

 <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>
            <version>8.0.18</version>
            <scope>runtime</scope>
        </dependency>
    </dependencies>

• 配置数据库 application.properties

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

• 创建实体类

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

• 创建BookDao

@Autowired
    private 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.getId());
    }

    public int deleteBook(Book book) {
        return jdbcTemplate.update("DELETE FROM book WHERE id=?", book.getId());
    }

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

• Service

@Service
public class BookService {
    @Autowired
    private BookDao bookDao;

    public int addBook(Book book) {
        return bookDao.addBook(book);
    }

    public int updateBook(Book book) {
        return bookDao.updateBook(book);
    }

    public int deleteBook(Book book) {
        return bookDao.deleteBook(book);
    }

    public Book getBookById(Integer id) {
        return bookDao.getBookById(id);
    }

    public List<Book> getAllBooks() {
        return bookDao.getAllBooks();
    }
}

• controller

@RestController
public class BookController {
    @Autowired
    private BookService bookService;

    @GetMapping("/bookOps")
    public void bookOps() {
        Book book1 = new Book();
        book1.setName("bookName3");
        book1.setAuthor("bookAuthor3");
        int result = bookService.addBook(book1);
        System.out.println("addBook>>" + result);
    }
}

整合Mybatis

• 创建项目添加依赖

 <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>3.0.3</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.10</version>
        </dependency>
        <dependency>
            <groupId>com.mysql</groupId>
            <artifactId>mysql-connector-j</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-tomcat</artifactId>
            <scope>provided</scope>
        </dependency>
    </dependencies>

• 创建数据库访问层

@Mapper
public interface BookMapper {
    int addBook(Book book);
    int deleteBook(Integer id)
    int updateBook(Book book);
    Book selectBookById(Integer id);
    List<Book> selectBooks();
}

• 在BookMapper上添加@Mapper注解，表明该接口是一个Mybatis中的Mapper,但是这种需要在每一个mapper中添加@Mapper，如果想要全局配置，需要在配置类上添加@MapperScan("要扫描的包名")

• 创建Mapper.xml

<mapper namespace="com.probuing.springbootmybatis.mapper">
    <insert id="addBook" parameterType="com.probuing.springbootmybatis.pojo.Book">
        INSERT INTO book(name,author) values (#{name},#{author})
    </insert>
    <delete id="deleteBookById"  parameterType="int">
        DELETE FROM book WHERE id=#{id}
    </delete>
    <select id="selectBooks" resultType="com.probuing.springbootmybatis.pojo.Book">
        SELECT *
        FROM book
    </select>
</mapper>

• Service和Controller 与上一个案例相同
• 配置pom.xml文件
  在Maven工程中,XML配置文件建议卸载resources目录下，如果将Mapper.xml文件写在包下，Maven在运行时会忽略包下的XML文件，因此需要在pom.xml文件中重新指明资源文件的位置

<build>
        <resources>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.xml</include>
                </includes>
            </resource>
            <resource>
                <directory>src/main/resources</directory>
            </resource>
        </resources>
</build>

整合Spring Data JPA

JPA是一种ORM规范，在以前的框架中 Hibernate常用的时候，JPA和Hibernate的关系就像JDBC与JDBC驱动的关系，即JPA制定了ORM规范，而Hibernate是这些规范的实现

Spring Data是Spring的一个子项目，致力于简化数据库访问，通过规范的名称来分析开发者的意图，减少代码量。SpringData不仅支持关系型数据库，也支持非关系型数据库

• 创建jpa数据库

mysql> CREATE DATABASE `jpa` DEFAULT CHARACTER SET utf8;

• 创建项目 添加依赖

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

• 数据库配置

spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.url=jdbc:mysql:///chapter05
spring.datasource.username=root
spring.datasource.password=xml123xml
mybatis.type-aliases-package=com.probuing.springbootmybatis.pojo
# jpa
spring.jpa.show-sql=true
spring.jpa.database=mysql
spring.jpa.hibernate.ddl-auto=update

• 创建实体

@Entity(name = "j_book")
public class BookJPA {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    @Column(name = "book_name",nullable = false)
    private String name;
    private String author;
    private Float price;
    @Transient
    private String description;
}

• @Entity 表示该类是一个实体类，在项目启动时会根据该类自动生成一张表，表的名称即@Entity注解中name的值。如果没有配置name，默认表名为类名
• @Id注解表示该属性时一个主键 @GeneratedValue注解表示主键自动生成，strategy则表示主键的生成策略
• 默认情况下，生成的表中字段的名称就是实体类中的属性名称，通过@Column注解可以定制生成的字段的属性，name表示该属性对应的数据表中字段的名称，nullable表示该字段非空
• @Transient注解表示在生成数据库的表时，该属性被忽略，即不生成对应的字段
• BookDao接口
  BookDao接口需要继承JpaRepository

public interface BookJPADao extends JpaRepository<BookJPA,Integer> {
    List<BookJPA> getBooksByAuthorStartingWith(String author);

    List<BookJPA> getBooksByPriceGreaterThan(Float price);
}

• 自定义的JPADao继承自JpaRepository，JpaRepository中提供了一些基本的数据操作方法，有基本的增删改查、分页查询、排序查询等
• 第2行定义的方法表示查询以某个字符开始的所有数据
• 第3行定义的方法表示查询单价大于某个值的所有数据

• 在SpringDataJPA中，只要方法的定义符合既定规范，SpringData就能分析出开发者的意图，从而避免开发者定义SQL。既定规范就是一定的方法命名规则，支持的命名规则如：

  
  
  
  
• BookService

@Service
public class BookService {
    @Autowired
    private BookJPADao bookJPADao;
    public void addBook(BookJPA bookJPA) {
        bookJPADao.save(bookJPA);
    }
    public void updateBook(BookJPA bookJPA) {
        bookJPADao.save(bookJPA);
    }
    public void deleteBook(BookJPA bookJPA) {
        bookJPADao.delete(bookJPA);
    }
    public BookJPA getBookById(Integer id) {
        Optional<BookJPA> optional = bookJPADao.findById(id);
        BookJPA bookJPA = optional.get();
        return bookJPA;
    }
    public List<BookJPA> getAllBooks() {
        return bookJPADao.findAll();
    }
}

• BookController

@RestController
public class BookController {
    @Autowired
    private BookService bookService;

    @GetMapping("/bookOps")
    public void bookOps() {
        BookJPA bookJPA = new BookJPA();
        bookJPA.setName("JPA的Book数据");
        bookJPA.setAuthor("JPA的Author");
        bookService.addBook(bookJPA);
    }
    @GetMapping("/getBooks")
    public List<BookJPA> getBooks() {
        return bookService.getAllBooks();
    }
}