spirng boot mysql_SpringBoot(六) SpirngBoot与Mysql关系型数据库

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