pom.xml文件的配置
org.springframework.boot
spring-boot-starter-jdbc
mysql
mysql-connector-java
runtime
写配置文件
spring.datasource.url = jdbc:mysql://localhost:3306/spring?useUnicode=true&characterEncoding=utf-8
spring.datasource.username = root
spring.datasource.password = root
spring.datasource.driver-class-name = com.mysql.jdbc.Driver
spring:
datasource:
username: root
password: Welcome_1
url: jdbc:mysql://192.168.179.131:3306/jdbc
driver-class-name: com.mysql.jdbc.Driver
type: com.alibaba.druid.pool.DruidDataSource
# schema:
# - classpath:department.sql
server:
port: 9000
自定义数据源DRUID
spring-boot-starter-jdbc 默认使用tomcat-jdbc数据源,如果你想使用其他的数据源,比如这里使用了阿里巴巴的数据池管理,你应该额外添加以下依赖:
com.alibaba
druid
1.0.19
编写java测试链接代码
@SpringBootApplicationpublic classApplication {public static voidmain(String[] args) {
SpringApplication.run(Application.class, args);
}//destroy-method="close"的作用是当数据库连接不使用的时候,就把该连接重新放到数据池中,方便下次使用调用.
@Bean(destroyMethod = "close")publicDataSource dataSource() {
DruidDataSource dataSource= newDruidDataSource();
dataSource.setUrl(env.getProperty("spring.datasource.url"));
dataSource.setUsername(env.getProperty("spring.datasource.username"));//用户名
dataSource.setPassword(env.getProperty("spring.datasource.password"));//密码
dataSource.setDriverClassName(env.getProperty("spring.datasource.driver-class-name"));
dataSource.setInitialSize(2);//初始化时建立物理连接的个数
dataSource.setMaxActive(20);//最大连接池数量
dataSource.setMinIdle(0);//最小连接池数量
dataSource.setMaxWait(60000);//获取连接时最大等待时间,单位毫秒。
dataSource.setValidationQuery("SELECT 1");//用来检测连接是否有效的sql
dataSource.setTestOnBorrow(false);//申请连接时执行validationQuery检测连接是否有效
dataSource.setTestWhileIdle(true);//建议配置为true,不影响性能,并且保证安全性。
dataSource.setPoolPreparedStatements(false);//是否缓存preparedStatement,也就是PSCache
returndataSource;
}
}
spring:
datasource:
username: root
password: Welcome_1
url: jdbc:mysql://192.168.179.131:3306/jdbc
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,去掉监控界面sql无法统计,‘wall’用于防火墙
filters: stat,wall,log4j
maxPoolPreparedStatementPerConnectionSize: 20
userGlobalDataSourceStat: true
# 通过connectProperties属性来打开mergeSql功能;慢SQL记录
connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500
# schema:
# - classpath:department.sql
server:
port: 9000
编写测试代码
@Repositorypublic class LearnDaoImpl implementsLearnDao{
@AutowiredprivateJdbcTemplate jdbcTemplate;
@Overridepublic intadd(LearnResouce learnResouce) {return jdbcTemplate.update("insert into learn_resource(author, title,url) values(?, ?, ?)",learnResouce.getAuthor(),learnResouce.getTitle(),learnResouce.getUrl());
}
@Overridepublic Page queryLearnResouceList(Mapparams) {
StringBuffer sql=newStringBuffer();
sql.append("select * from learn_resource where 1=1");if(!StringUtil.isNull((String)params.get("author"))){
sql.append(" and author like '%").append((String)params.get("author")).append("%'");
}if(!StringUtil.isNull((String)params.get("title"))){
sql.append(" and title like '%").append((String)params.get("title")).append("%'");
}
Page page= new Page(sql.toString(), Integer.parseInt(params.get("page").toString()), Integer.parseInt(params.get("rows").toString()), jdbcTemplate);returnpage;
}
}
@Configuration
public class DruidConfig {
@ConfigurationProperties(prefix = "spring.datasource")
@Bean
public DataSource druid(){
return new DruidDataSource();
}
//配置Druid的监控
//1、配置一个管理后台
@Bean
public ServletRegistrationBean statViewServlet(){
ServletRegistrationBean bean = new ServletRegistrationBean(new StatViewServlet(),"/druid/*");
Map initParams =new HashMap<>();
initParams.put("loginUsername", "admin");
initParams.put("loginPassword", "123456");
bean.setInitParameters(initParams);
return bean;
}
//2、配置监控的filter
@Bean
public FilterRegistrationBean webstatFilter(){
FilterRegistrationBean bean = new FilterRegistrationBean();
bean.setFilter(new WebStatFilter());
Map initParams =new HashMap<>();
initParams.put("exclusions", "*.js,*.css,/druid/*");
bean.setInitParameters(initParams);
bean.setUrlPatterns(Arrays.asList("/*"));
return bean;
}
}
访问:localhost:8080/druid/login.html
以上是使用JDBCTemptlate模板,可以参考API文档 JdbcTemplate
SpringBoot整合Mybatis
1.使用注解的方式
导入依赖
org.mybatis.spring.boot
mybatis-spring-boot-starter
1.3.2
com.alibaba
druid
1.1.9
mysql
mysql-connector-java
runtime
org.springframework.boot
spring-boot-starter-jdbc
导入配置文件中关于Druid的配置
创建数据表
创建数据库对应的JavaBean,以及getter和setter方法
在配置文件中修改驼峰命名开启 ,不写配置文件就写配置类
mybatis:
configuration:
map-underscore-to-camel-case: true
数据库中以下划线分割,而javabean中以驼峰命名。解决办法
public class MyBatisConfig {
@Bean
public ConfigurationCustomizer configurationCustomizer(){
return new ConfigurationCustomizer() {
@Override
public void customize(Configuration configuration) {
configuration.setMapUnderscoreToCamelCase(true);
}
};
}
}
使用注解方式导入mapper
@MapperScan(value = "com.test.testMapper")
编写测试类(@component注解不添加也没事,只是不加service那边引入mapper的时候会有错误提示,也就是红线,但不影响程序的运行)
@Component
@Mapper
public interface DepartmentMapper {
@Insert("insert into department(dept_name) value(#{deptName})")
public int insertDept(Department department);
@Delete("delete from department where id=#{id}")
public int deleteDeptById(Integer id);
@Update("update department set dept_Name=#{deptName} where id=#{id}")
public int updateDept(Department department);
@Select("select * from department where id=#{id}")
public Department getDeptById(Integer id);
}
配置文件的方式整合Mybatis(xml方式)
新建mybatis的配置文件。
/p>
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
新建mapper接口及其方法。
public interface EmployeeMapper {
public Employee getEmpById(Integer id);
public void insetEmp(Employee employee);
}
新建Employee的mapper.xml的映射文件
/p>
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
select * from employee where id=#{id}
INSERT INTO employee(last_name,email,gender,d_id) VALUES (#{lastName},#{email},#{gender},#{dId})
修改application.yml配置文件
mybatis:
config-location: classpath:mybatis/mybatis-config.xml
mapper-locations: classpath:mybatis/mapper/*.xml
编写controller类进行测试。更多的mybatis使用查询官方文档。mybatis官方中文参考文档
PageHelper分页插件
导入pom.xml
com.github.pagehelper
pagehelper
x.x.x
例子
//2. use static method startPage
PageHelper.startPage(1, 10);
List list = countryMapper.selectIf(1);//3. use static method offsetPage
PageHelper.offsetPage(1, 10);
List list = countryMapper.selectIf(1);//4. method parameters
public interfaceCountryMapper {
ListselectByPageNumSize(
@Param("user") User user,
@Param("pageNum") intpageNum,
@Param("pageSize") intpageSize);
}//config supportMethodsArguments=true
List list = countryMapper.selectByPageNumSize(user, 1, 10);