Spring Boot整合持久层
整合持久层就是和数据库打交道,这里以mysql为例。
1.准备工作
首先创建一个数据库和表,代码如下:
create database test1 default character set utf8; use test1; CREATE TABLE `book` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) DEFAULT NULL, `author` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8; insert into book values(null,'三国演义','罗贯中'); insert into book values(null,'水浒传','施耐庵');
然后创建springboot的项目。
2.整合JdbcTemplate
第一步:导入依赖
<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> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <version>1.18.8</version> </dependency>
其中lombok可以导入也可以不导入,这里是为了使用get和set方法方便。
第二步:配置数据源,这里使用的配置文件是application.yml
#数据源配置 spring: datasource: #使用阿里巴巴的druid type: com.alibaba.druid.pool.DruidDataSource #配置数据库的路径和用户名密码 url: jdbc:mysql://localhost:3306/test1?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC username: root password: root
第三步:创建类Book
package com.example.demo.entity; import lombok.Getter; import lombok.Setter; import lombok.ToString; @Getter @Setter @ToString public class Book { private Integer id; private String name; private String author; }
第四步:创建类BookController
package com.example.demo.controller; import com.example.demo.entity.Book; import com.example.demo.service.Bookservice; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController; import java.util.List; @RestController @RequestMapping("/book") public class BookController { @Autowired private BookService bookservice; @RequestMapping("/addBook") public String addBook(){ Book book=new Book(); book.setName("西游记"); book.setAuthor("张三"); bookservice.addBook(book); return "添加成功"; } @RequestMapping("/updateBook") public String updateBook(){ Book book=new Book(); book.setId(3); book.setName("西游记2"); book.setAuthor("张三2"); bookservice.updateBook(book); return "修改成功"; } @RequestMapping("/deleteBook") public String deleteBook(){ bookservice.deleteBook(3); return "添删成功"; } @RequestMapping("/getAllBook") public List<Book> getAllBook(){ return bookservice.getAllBook(); } }
第五步:创建类BookService
package com.example.demo.service; import com.example.demo.dao.BookDao; import com.example.demo.entity.Book; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import java.util.List; @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(Integer id){ return bookDao.deleteBook(id); } public List<Book> getAllBook(){ return bookDao.getAllBook(); } }
第六步:创建类BookDao
package com.example.demo.dao; import com.example.demo.entity.Book; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.jdbc.core.BeanPropertyRowMapper; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.stereotype.Repository; import java.util.List; @Repository public class BookDao { @Autowired private JdbcTemplate template; //添加图书 public int addBook(Book book){ String sql="insert into book values(null,?,?)"; return template.update(sql,book.getName(),book.getAuthor()); } //修改图书 public int updateBook(Book book){ String sql="update book set author=?,name=? where id=?"; return template.update(sql,book.getAuthor(),book.getName(),book.getId()); } //删除图书 public int deleteBook(Integer id){ String sql="delete from book where id=?"; return template.update(sql,id); } //查询图书 public List<Book> getAllBook(){ String sql="select * from book"; return template.query(sql,new BeanPropertyRowMapper<>(Book.class)); } }
第七步:测试。启动项目,在浏览器输入localhost:8080/book/abbBook即可向数据库添加设定的数据,同理其他的几个接口也可以使用。到这里SpringBoo整合JdbcTemolate已经完成了,至于从前端向controller的接口传递数据,这里暂时不讲。
3.整合MyBatis
第一步:导入依赖
<dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>1.3.2</version> </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>
第二步:类Book,类BookController同上,而BookService中只需把@Autowired中的BookDao改成BookMapper即可,其他同上。
第三步:创建接口BookMapper
package com.example.demo.dao; import com.example.demo.entity.Book; import org.apache.ibatis.annotations.Mapper; import java.util.List; @Mapper public interface BookMapper { int addBook(Book book); int updateBook(Book book); int deleteBook(Integer id); List<Book> getAllBook(); }
第四步:创建BookMapper.xml文件
<?xml version="1.0" encoding="uTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.example.demo.dao.BookMapper"> <insert id="addBook" parameterType="com.example.demo.entity.Book"> insert into book values(null,#{name},#{author}) </insert> <update id="updateBook" parameterType="com.example.demo.entity.Book"> update book set name=#{name},author=#{author} where id=#{id} </update> <delete id="deleteBook" parameterType="java.lang.Integer"> delete from book where id=#{id} </delete> <select id="getAllBook" resultType="com.example.demo.entity.Book"> select * from book </select> </mapper>
在资源目录下先创建mapper目录,在目录下创建一个名为BookMapper.xml的文件
第五步:修改yml文件
#数据源配置 spring: datasource: #使用阿里巴巴的druid type: com.alibaba.druid.pool.DruidDataSource #配置数据库的路径和用户名密码 url: jdbc:mysql://localhost:3306/test1?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC username: root password: 123456 #配置xml的位置 mybatis: mapperLocations: classpath*:mapper/*Mapper.xml
第六步:启动项目,进行测试,接口可以正常使用。
4.整合Spring Data JPA
JPA是一种ORM规范,Hibernate是一个ORM框架,因此JPA相当于Hibernate的一个子集。这里只需要有数据库即可,就是要test1数据库,不需要手动创建表,。
第一步:导入依赖
<dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-jpa</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: #数据源配置 datasource: #使用阿里巴巴的druid type: com.alibaba.druid.pool.DruidDataSource #配置数据库的路径和用户名密码 url: jdbc:mysql://localhost:3306/test1?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC username: root password: root #JPA相关配置 jpa: #指定数据库 database: mysql #在控制台打印JPA执行过程中生成的sql show-sql: true #项目启动时根据实体类更新数据库的表 hibernate: ddl-auto: update
第三步:创建实体类Book
package com.example.demo.entity; import lombok.Getter; import lombok.Setter; import lombok.ToString; import javax.persistence.*; @Getter @Setter @ToString //表示该类是一个实体类,name是表名,不写则默认是类名 @Entity(name="t_book") public class Book { //id表示是主键,然后GeneratedValue是自动生成,配置生成的策略 @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Integer id; //Column设置字段的名字,不写则是属性名 @Column(name = "book_name",nullable = false) private String name; private String author; private Float price; //Transient设置忽略的字段,创建表时不生成此字段 @Transient private String description; }
第四步:创建类BookController
package com.example.demo.controller; import com.example.demo.entity.Book; import com.example.demo.service.BookService; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController; import java.util.List; @RestController @RequestMapping("/book") public class BookController { @Autowired private BookService bookservice; @GetMapping("/add") public Book insert( Book book){ return bookservice.save(book); } @GetMapping("/findAll") public List<Book> findAll(){ return bookservice.findAll(); } }
第五步:创建类BookService
package com.example.demo.service; import com.example.demo.dao.BookDao; import com.example.demo.entity.Book; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import java.util.List; @Service public class BookService { @Autowired private BookDao bookDao; public Book save(Book book) { return bookDao.save(book); } public List<Book> findAll() { return bookDao.findAll(); } }
第六步:创建接口BookDao
package com.example.demo.dao; import com.example.demo.entity.Book; import org.springframework.data.jpa.repository.JpaRepository; import org.springframework.stereotype.Component; @Component public interface BookDao extends JpaRepository<Book,Integer>{ }
BookDao中没有写方法,原因是jpa中有一些常用的方法。
5.配置多数据源
多数据源就是javaEE项目中采用了不同数据库实例中的 多个库,或者同一个数据库实例中多个不同的库。
1)JdbcTemplate多数据源
第一步:创建两个数据库test1,test2,然后在两个数据库中分别创建一个book,并插入一条不同的内容,创建test1的脚本如下,test2同
create database test2 default character set utf8 use test2 CREATE TABLE `book` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) DEFAULT NULL, `author` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
第二步:添加依赖
<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-spring-boot-starter</artifactId> <version>1.1.10</version> </dependency>
第三步:配置数据库
#多数据源配置 #数据源1 spring: datasource: one: type: com.alibaba.druid.pool.DruidDataSource #配置数据库的路径和用户名密码 url: jdbc:mysql://localhost:3306/test1?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC username: root password: root #数据源2 two: type: com.alibaba.druid.pool.DruidDataSource #配置数据库的路径和用户名密码 url: jdbc:mysql://localhost:3306/test2?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC username: root password: root
第四步:配置多数据源
package com.example.demo.config; import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder; import org.springframework.boot.context.properties.ConfigurationProperties; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import javax.sql.DataSource; @Configuration public class DataSourceConfig { //根据不同前缀的配置文件来创建不同的DataSource实例 @Bean @ConfigurationProperties("spring.datasource.one") DataSource dsOne(){ return DruidDataSourceBuilder.create().build(); } @Bean @ConfigurationProperties("spring.datasource.two") DataSource dsTwo(){ return DruidDataSourceBuilder.create().build(); } }
第五步:配置JdbcTemplate
package com.example.demo.config; import org.springframework.beans.factory.annotation.Qualifier; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import org.springframework.jdbc.core.JdbcTemplate; import javax.sql.DataSource; @Configuration public class JdbcTemplateConfig { //根据不同的DataSource实例来创建jdbcTemplate的实例 @Bean JdbcTemplate jdbcTemplateOne(@Qualifier("dsOne")DataSource dataSource){ return new JdbcTemplate(dataSource); } @Bean JdbcTemplate jdbcTemplateTwo(@Qualifier("dsTwo")DataSource dataSource){ return new JdbcTemplate(dataSource); } }
第六步:创建类BookController
为了简单化,这里就直接使用controller来注入JdbcTemplate,在实际开发中需规范化。
package com.example.demo.controller; import com.example.demo.entity.Book; import com.example.demo.service.BookService; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.beans.factory.annotation.Qualifier; import org.springframework.boot.autoconfigure.quartz.QuartzProperties; import org.springframework.jdbc.core.BeanPropertyRowMapper; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController; import javax.annotation.Resource; import java.util.HashMap; import java.util.List; import java.util.Map; @RestController @RequestMapping("/book") public class BookController { //以下使用两种不同的方法注入JdbcTemplate @Resource(name = "jdbcTemplateOne") private JdbcTemplate jdbcTemplateOne; @Autowired @Qualifier("jdbcTemplateTwo") private JdbcTemplate jdbcTemplateTwo; @GetMapping("/find") public Map find(){ Map<String,Object> map=new HashMap<>(); String sql="select * from book"; List<Book> query1 = jdbcTemplateOne.query(sql, new BeanPropertyRowMapper<>(Book.class)); List<Book> query2 = jdbcTemplateTwo.query(sql, new BeanPropertyRowMapper<>(Book.class)); map.put("datasouce1",query1); map.put("datasouce2",query2); return map; } }
第七步:测试
启动项目,在浏览器输入localhost:8080/book/get即可看到查询的两个数据库的所有结果。
2)Mybatis多数据源
第一步:上面已经详细的介绍了一些配置信息,这里就不再赘述。两个数据库同上,数据库配置同上,多数据源配置同上。依赖也只是把Spring-boot-starter-jdbc替换成mybatis的依赖即可,mybatis的依赖如下:
<dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>1.3.2</version> </dependency>
第二步:配置Mybatis
第一个配置类
package com.example.demo.config; import org.apache.ibatis.session.SqlSessionFactory; import org.mybatis.spring.SqlSessionFactoryBean; import org.mybatis.spring.SqlSessionTemplate; import org.mybatis.spring.annotation.MapperScan; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import javax.annotation.Resource; import javax.sql.DataSource; @Configuration //指定接口所在的位置,它下面的接口将使用SqlSessionFactory实例 @MapperScan(value = "com.example.demo.dao",sqlSessionFactoryRef = "sqlSessionFactoryBean1") public class MybatisConfigOne { @Resource(name="dsOne") private DataSource dsOne; @Bean SqlSessionFactoryBean sqlSessionFactoryBean1() throws Exception { SqlSessionFactoryBean factoryBean=new SqlSessionFactoryBean(); factoryBean.setDataSource(dsOne); return (SqlSessionFactoryBean) factoryBean.getObject(); } @Bean SqlSessionTemplate sqlSessionTemplate1() throws Exception { return new SqlSessionTemplate((SqlSessionFactory) sqlSessionFactoryBean1()); } }
第二个配置类
package com.example.demo.config; import org.apache.ibatis.session.SqlSessionFactory; import org.mybatis.spring.SqlSessionFactoryBean; import org.mybatis.spring.SqlSessionTemplate; import org.mybatis.spring.annotation.MapperScan; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import javax.annotation.Resource; import javax.sql.DataSource; @Configuration //指定接口所在的位置,它下面的接口将使用SqlSessionFactory实例 @MapperScan(value = "com.example.demo.dao",sqlSessionFactoryRef = "sqlSessionFactoryBean2") public class MybatisConfigTwo { @Resource(name="dsTwo") private DataSource dsTwo; @Bean SqlSessionFactoryBean sqlSessionFactoryBean2() throws Exception { SqlSessionFactoryBean factoryBean=new SqlSessionFactoryBean(); factoryBean.setDataSource(dsTwo); return (SqlSessionFactoryBean) factoryBean.getObject(); } @Bean SqlSessionTemplate sqlSessionTemplate1() throws Exception { return new SqlSessionTemplate((SqlSessionFactory) sqlSessionFactoryBean2()); } }
第三步:在包com.example.demo.dao和com.example.demo.dao2分别创建接口BookMapper和BookMapper2
package com.example.demo.dao; import com.example.demo.entity.Book; import java.util.List; public interface BookMapper { List<Book> getAllBook(); }
package com.example.demo.dao2;
import com.example.demo.entity.Book;
import java.util.List;
public interface BookMapper2 {
List<Book> getAllBook();
}
第四步:在上面两个包中分别创建BookMapper.xml,BookMapper2.xml
<?xml version="1.0" encoding="uTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.example.demo.dao.BookMapper"> <select id="getAllBook" resultType="com.example.demo.entity.Book"> select * from book </select> </mapper>
<?xml version="1.0" encoding="uTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.example.demo.dao2.BookMapper2"> <select id="getAllBook" resultType="com.example.demo.entity.Book"> select * from book </select> </mapper>
第五步:创建BookController
package com.example.demo.controller; import com.example.demo.dao.BookMapper; import com.example.demo.dao2.BookMapper2; import com.example.demo.entity.Book; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController; import java.util.HashMap; import java.util.List; import java.util.Map; @RestController @RequestMapping("/book") public class BookController { @Autowired private BookMapper mapper; @Autowired private BookMapper2 mapper2; @GetMapping("/find") public Map find(){ Map<String,Object> map=new HashMap<>(); List<Book> query1 = mapper.getAllBook(); List<Book> query2 = mapper2.getAllBook(); map.put("datasouce1",query1); map.put("datasouce2",query2); return map; } }
第六步:启动项目,进行测试。
6.整合Mybatis出现null
当前端传入的数据给后台,一个对象中有的属性为null时mybatis是不能进行插入操作的,但是需求是这些为null的值得转换为空字符串存入到数据库,其中的一个解决办法如下:
第一步:创建一个类,用于转换类型是字符串,值为null的属性
package com.kanq.framework.util; import org.apache.ibatis.type.JdbcType; import org.apache.ibatis.type.TypeHandler; import org.springframework.context.annotation.Configuration; import java.sql.CallableStatement; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; /** * 当值为null时设置为"",用于mybatis的数据插入 */ @Configuration public class NullValueHandler implements TypeHandler<String> { @Override public void setParameter(PreparedStatement preparedStatement, int i, String s, JdbcType jdbcType) throws SQLException { if(s==null&&jdbcType==JdbcType.VARCHAR){//判断传入的参数值是否为null preparedStatement.setString(i,"");//设置当前参数的值为空字符串 }else{ preparedStatement.setString(i,s);//如果不为null,则直接设置参数的值为value } } @Override public String getResult(ResultSet resultSet, String s) throws SQLException { return resultSet.getString(s); } @Override public String getResult(ResultSet resultSet, int i) throws SQLException { return resultSet.getString(i); } @Override public String getResult(CallableStatement callableStatement, int i) throws SQLException { return callableStatement.getString(i); } }
第二步:在mybatis的BookMapper.xml中修改如下
insert into xcjhb values(null, #{xczrr,jdbcType=VARCHAR,typeHandler=com.kanq.framework.util.NullValueHandler},.....)
typeHandler的值是NullValueHandler所在的路径,这样当前台传入的值为null时后台就可以正常插入了。