springboot整合各种数据访问方式
1.使用jdbc
新建一个工程,选web、jdbc、mysql;
配置数据库连接,在yml文件中配置
spring: datasource: url: jdbc:mysql://localhost:3306/jdbc?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf-8 driver-class-name: com.mysql.cj.jdbc.Driver username: root password: root
测试数据库jdbc:
部门表:
DROP TABLE IF EXISTS `department`; CREATE TABLE `department` ( `id` int(11) NOT NULL AUTO_INCREMENT, `departmentName` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
员工表:
DROP TABLE IF EXISTS `employee`; CREATE TABLE `employee` ( `id` int(11) NOT NULL AUTO_INCREMENT, `lastName` varchar(255) DEFAULT NULL, `email` varchar(255) DEFAULT NULL, `gender` int(2) DEFAULT NULL, `d_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
controller:使用jdbc访问部门表
@RestController public class controller{ @Autowired private JdbcTemplate template; @RequestMapping("/go") public Map<String,Object> go(){ return template.queryForList("select * from department").get(0); } }
运行工程,浏览器访问:localhost:8080/go
2.使用druid数据源
<dependency> <groupId>com.alibaba</groupId> <artifactId>druid-spring-boot-starter</artifactId> <version>1.1.22</version> </dependency>
配置数据源:
spring: datasource: url: jdbc:mysql://localhost:3306/jdbc?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf-8 driver-class-name: com.mysql.cj.jdbc.Driver username: root password: root type: com.alibaba.druid.pool.DruidDataSource druid: initial-size: 8 min-idle: 1 max-active: 20 max-wait: 60000 time-between-eviction-runsMillis: 60000 min-evictable-idle-timeMillis: 300000 validation-query: select 'x' FROM DUAL test-while-idle: true test-on-borrow: false test-on-return: false pool-prepared-statements: true max-open-prepared-statements: 20 max-pool-prepared-statement-per-connection-size: 20 filters: stat connection-properties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000 use-global-data-source-stat: true
3.使用mybatis
pom依赖:使用mybatis的启动器替换jdbc的启动器
<dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>2.1.2</version> </dependency>
该启动器是mybatis提供的,而不是spring官方提供的;
spring官方启动器命名方式为spring-boot-xxx,而这个启动器命名方式为mybatis-xxx;
1)使用注解方式
映射接口:
用来操作数据库;映射接口要以注解@Mapper标记
@Mapper public interface DepartmentMapper { @Select("select * from department where id=#{id}") public Department get(Integer id); @Delete("delete from department where id=#{id}") public int delete(Integer id); @Insert("insert into department(departmentName) values(#{departmentName}) ") public int insert(Department department); @Update("update department set departmentName=#{departmentName} where id=#{id}") public int update(Department department); }
controller:
因为DepartmentMapper是一个接口,使用@Autowired会idea报红,实际上没什么问题
@RestController public class go{ @Autowired private DepartmentMapper mapper; @RequestMapping("/get/{id}") public Department get(@PathVariable("id") Integer id){ return mapper.get(id); } }
测试:
其它:
使用注解方式时不需要做配置,因为mybatis提供的jar包中包含了mybatis的自动配置类;
如果需要自动配置,可以在yml中配置mybatis属性,mybatis提供的properties类会从yml中取值来覆盖默认值;
简化:
@Mapper用来指定数据库映射接口;
如果接口很多时,每个接口都加@Mapper注解很麻烦;
可以在工程启动类加一个注解,指定映射接口的包路径,这样包中所有接口都会被当做映射接口;
@MapperScan(value = "com.example.demo.mapper")
驼峰命名法的问题:
通常数据库字段命名方式为:“department_name”;
而java实体类属性命名方式为驼峰命名:“departmentName”;
二者名称不一致时,mybatis的sql语句查到的结果无法映射到实体类上;
为了解决这一问题,可以定义一个配置类,然后用@Bean注解添加一个定制器到spring容器;
在定制器中配置mybatis,设置驼峰命名和数据库命名之间的映射;
@Configuration public class MapperConfig { @Bean public ConfigurationCustomizer configurationCustomizer(){ return new ConfigurationCustomizer() { @Override public void customize(org.apache.ibatis.session.Configuration configuration) { configuration.setMapUnderscoreToCamelCase(true); } }; } }
2)配置方式
在yml中配置mybatis属性,指定sql映射xml文件的路径
mybatis: config-location: classpath:mybatis/mybatis-config.xml #指定全局配置文件的位置 mapper-locations: classpath:mybatis/mapper/*.xml #指定sql映射文件的位置
在xml中写sql操作数据库即可;
帮助文档:
在github上搜索mybatis;
进入mybatis3;
点查看文档;
映射接口:
public interface EmployeeMapper { public Employee get(Integer id); }
全局配置文件:
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<settings>
<!--配置驼峰命名法与数据库命名法的映射-->
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
</configuration>
sql映射文件:
<?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.mapper.EmployeeMapper"> <!--通过id查询--> <select id="get" resultType="com.example.demo.entity.Employee"> select * from employee where id = #{id} </select> </mapper> <mapper>的namespace属性用做映射接口和映射xml的绑定; namespace的值为映射接口的全类名; <select>的id属性值为映射接口中的方法名; <select>的resultType属性值为方法返回值类型的全类名;
controller:
@RestController public class to{ @Autowired private EmployeeMapper empMapper; @RequestMapping("/getEmp/{id}") public Employee get(@PathVariable("id") Integer id){ return empMapper.get(id); } }
测试:
4.使用jpa
需要的依赖:web、jpa、mysql驱动
实体类:
@JsonIgnoreProperties(value = { "hibernateLazyInitializer"}) //使用JPA注解配置映射关系 @Entity //告诉JPA这是一个实体类(和数据表映射的类) @Table(name = "tbl_user") //@Table来指定和哪个数据表对应;如果省略默认表名就是user; public class User implements Serializable{ @Id //这是一个主键 @GeneratedValue(strategy = GenerationType.IDENTITY)//自增主键 private Integer id; @Column(name = "last_name", length = 50) //这是和数据表对应的一个列 private String lastName; @Column //省略默认列名就是属性名 private String email; public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getLastName() { return lastName; } public void setLastName(String lastName) { this.lastName = lastName; } public String getEmail() { return email; } public void setEmail(String email) { this.email = email; } }
yml配置:
要配置数据源;
配置jpa:自动根据实体类创建表、控制台打印sql语句;
spring: datasource: url: jdbc:mysql://localhost:3306/jdbc?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf-8 driver-class-name: com.mysql.cj.jdbc.Driver username: root password: root jpa: hibernate: # 更新或者创建数据表结构 ddl-auto: update # 控制台显示SQL show-sql: true
仓库接口:
使用springData通用的仓库api,可以简化crud操作;
底层默认由Hibernate实现;
public interface UserRepository extends JpaRepository<User,Integer>{}
controller:
@RestController public class UserController { @Autowired private UserRepository rep; @RequestMapping("get/{id}") public User get(@PathVariable("id")Integer id){ return rep.getOne(id); } }
踩坑:
com.fasterxml.jackson.databind.exc.InvalidDefinitionException: No serializer found for class org.hibernate.proxy.pojo.bytebuddy.ByteBuddyInterceptor and no properties...
原因:
因为jsonplugin用的是java的内审机制.被管理的pojo会加入一个hibernateLazyInitializer属性,jsonplugin会对hibernateLazyInitializer拿出来操作,并读取里面一个不能被反射操作的属性就产生了异常
解决办法:
在实体类上类上加上一个注解,@JsonIgnoreProperties(value = { "hibernateLazyInitializer"})即可解决此错误
测试: