- Springboot数据层开发
- 数据源自动管理
引入jdbc的依赖和springboot的应用场景
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-dbcp2</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency> |
让我们使用yaml方式配置，创建application.yaml
spring:
datasource:
username: root
password: root
url: jdbc:mysql://localhost:3306/boot_demo
driver-class-name: com.mysql.jdbc.Driver
type: com.zaxxer.hikari.HikariDataSource |
在默认情况下， 数据库连接可以使用DataSource池进行自动配置
- 如果Hikari可用， Springboot将使用它。
- 如果Commons DBCP2可用， 我们将使用它。
我们可以自己指定数据源配置，通过type来选取使用哪种数据源
spring:
datasource:
username: root
password: root
url: jdbc:mysql://localhost:3306/boot_demo
driver-class-name: com.mysql.jdbc.Driver
type: com.zaxxer.hikari.HikariDataSource
# type: org.apache.commons.dbcp2.BasicDataSource |
-
- 配置druid数据源
引入druid的依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.0.9</version>
</dependency> <dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.15</version>
</dependency> |
修改spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
在application.yaml中加入
spring:
datasource:
username: root
password: root
url: jdbc:mysql://localhost:3306/boot_demo
driver-class-name: com.mysql.jdbc.Driver
type: com.alibaba.druid.pool.DruidDataSource
initialSize: 5
minIdle: 5
maxActive: 20
maxWait: 60000
timeBetweenEvictionRunsMillis: 60000
minEvictableIdleTimeMillis: 300000
validationQuery: SELECT 1 FROM DUAL
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
poolPreparedStatements: true
filters: stat,wall,log4j
maxPoolPreparedStatementPerConnectionSize: 20
useGlobalDataSourceStat: true
connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500 |
创建数据源注册类
@Configuration
public class DruidConfig {
@ConfigurationProperties(prefix = "spring.datasource")
@Bean
public DataSource dataSource(){
return new DruidDataSource();
}
} |
配置druid运行期监控
@Configuration
public class DruidConfig {
@ConfigurationProperties(prefix = "spring.datasource")
@Bean
public DataSource dataSource(){
return new DruidDataSource();
}
@Bean
public ServletRegistrationBean statViewServlet(){
ServletRegistrationBean bean = new ServletRegistrationBean(new StatViewServlet(),
"/druid/*");
Map<String,String> initParams = new HashMap<>();
initParams.put("loginUsername","root");
initParams.put("loginPassword","root");
initParams.put("allow","");//默认就是允许所有访问
initParams.put("deny","192.168.15.21");
bean.setInitParameters(initParams);
return bean;
}
//2、配置一个web监控的filter
@Bean
public FilterRegistrationBean webStatFilter(){
FilterRegistrationBean bean;
bean = new FilterRegistrationBean();
bean.setFilter(new WebStatFilter());
Map<String,String> initParams = new HashMap<>();
initParams.put("exclusions","*.js,*.css,/druid/*");
bean.setInitParameters(initParams);
bean.setUrlPatterns(Arrays.asList("/*"));
return bean;
}
} |
打开监控页面
http://localhost:8080/druid
-
- springboot整合jdbcTemplate
在数据源建表
| SET FOREIGN_KEY_CHECKS=0; -- ---------------------------- -- Table structure for tx_user -- ---------------------------- DROP TABLE IF EXISTS `tx_user`; CREATE TABLE `tx_user` ( `username` varchar(10) DEFAULT NULL, `userId` int(10) NOT NULL, `password` varchar(10) DEFAULT NULL, PRIMARY KEY (`userId`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
创建Controller
@Controller
public class TestController {
@Autowired
JdbcTemplate jdbcTemplate;
@ResponseBody
@RequestMapping("/query")
public List<Map<String, Object>> query(){
List<Map<String, Object>> maps = jdbcTemplate.queryForList("SELECT * FROM tx_user");
return maps;
}
} |
启动springboot访问
http://localhost:8080/query
Springboot中提供了JdbcTemplateAutoConfiguration的自动配置
org.springframework.boot.autoconfigure.jdbc.JdbcTemplateAutoConfiguration,\
JdbcTemplateAutoConfiguration源码：
-
- Springboot整合mybatis注解版
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.3.1</version>
</dependency> |
步骤：
1）、配置数据源相关属性（见上一节Druid）
2）、给数据库建表
3）、创建JavaBean
public class TxPerson {
private int pid;
private String pname;
private String addr;
private int gender;
private Date birth; |
4）创建Mapper
@Mapper
public interface TxPersonMapper {
@Select("select * from tx_person")
public List<TxPerson> getPersons();
@Select("select * from tx_person t where t.pid = #{id}")
public TxPerson getPersonById(int id);
@Options(useGeneratedKeys =true, keyProperty = "pid")
@Insert("insert into tx_person(pid, pname, addr,gender, birth)" +
" values(#{pid}, #{pname}, #{addr},#{gender}, #{birth})")
public void insert(TxPerson person);
@Delete("delete from tx_person where pid = #{id}")
public void update(int id);
} |
单元测试
解决驼峰模式和数据库中下划线不能映射的问题。
@Configuration
public class MybatisConfig {
@Bean
public ConfigurationCustomizer getCustomizer(){
return new ConfigurationCustomizer() {
@Override
public void customize(org.apache.ibatis.session.Configuration configuration) {
configuration.setMapUnderscoreToCamelCase(true);
}
};
}
} |
查询结果
TxPerson{pid=1, pname='张三', pAddr='北京', gender=1, birth=Thu Jun 14 00:00:00 CST 2018} |
我们同样可以在mybatis的接口上不加@Mapper注解，通过扫描器注解来扫描
Mapper接口存放在cn.tx.mapper下
-
- Springboot整合mybatis配置文件
创建sqlMapConfig.xml配置文件
<?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>
</configuration> |
创建映射文件PersonMapper.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="cn.tx.mapper.TxPersonMapper">
<select id="getPersons" resultType="TxPerson">
select * from tx_person
</select>
</mapper> |
在application.yaml中配置mybatis的信息
mybatis:
config-location: classpath:mybatis/sqlMapConfig.xml
mapper-locations: classpath:mybatis/mapper/*.xml
type-aliases-package: cn.tx.springboot.jdbc_demo1 |
