pom.xml文件的配置

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

写配置文件

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数据源，如果你想使用其他的数据源，比如这里使用了阿里巴巴的数据池管理,你应该额外添加以下依赖：

<dependency>

    <groupId>com.alibaba</groupId>

    <artifactId>druid</artifactId>

    <version>1.0.19</version>

</dependency>

编写java测试链接代码

@SpringBootApplication

public class Application {

    public static void main(String[] args) {

        SpringApplication.run(Application.class, args);

    }

    //destroy-method="close"的作用是当数据库连接不使用的时候,就把该连接重新放到数据池中,方便下次使用调用.

    @Bean(destroyMethod =  "close")

    public DataSource dataSource() {

        DruidDataSource dataSource = new DruidDataSource();

        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

        return dataSource;

    }

}

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

编写测试代码

@Repository

public class LearnDaoImpl  implements LearnDao{

    @Autowired

    private JdbcTemplate jdbcTemplate;

    @Override

    public int add(LearnResouce learnResouce) {

        return jdbcTemplate.update("insert into learn_resource(author, title,url) values(?, ?, ?)",learnResouce.getAuthor(),learnResouce.getTitle(),learnResouce.getUrl());

    }

    @Override

    public Page queryLearnResouceList(Map<String,Object> params) {

        StringBuffer sql =new StringBuffer();

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

        return page;

    }

}

@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<String,String> 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<String,String> 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.使用注解的方式

　导入依赖

<dependency>

    <groupId>org.mybatis.spring.boot</groupId>

    <artifactId>mybatis-spring-boot-starter</artifactId>

    <version>1.3.2</version>

</dependency>

<!-- https://mvnrepository.com/artifact/com.alibaba/druid -->

<dependency>

    <groupId>com.alibaba</groupId>

    <artifactId>druid</artifactId>

    <version>1.1.9</version>

</dependency>

<dependency>

    <groupId>mysql</groupId>

    <artifactId>mysql-connector-java</artifactId>

    <scope>runtime</scope>

</dependency>

<dependency>

    <groupId>org.springframework.boot</groupId>

    <artifactId>spring-boot-starter-jdbc</artifactId>

</dependency>

导入配置文件中关于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的配置文件。

<?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>

新建mapper接口及其方法。

public interface EmployeeMapper {

    public Employee getEmpById(Integer id);

    public void insetEmp(Employee employee);

}

新建Employee的mapper.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.wdjr.springboot.mapper.EmployeeMapper">

    <select id="getEmpById" resultType="com.wdjr.springboot.bean.Employee">

      select * from employee where id=#{id}

   </select>

    <insert id="insetEmp">

        INSERT  INTO employee(last_name,email,gender,d_id) VALUES (#{lastName},#{email},#{gender},#{dId})

    </insert>

</mapper>

修改application.yml配置文件

mybatis:

  config-location: classpath:mybatis/mybatis-config.xml

  mapper-locations: classpath:mybatis/mapper/*.xml

编写controller类进行测试。更多的mybatis使用查询官方文档。mybatis官方中文参考文档

PageHelper分页插件

导入pom.xml

<dependency>

    <groupId>com.github.pagehelper</groupId>

    <artifactId>pagehelper</artifactId>

    <version>x.x.x</version>

</dependency>

例子

//2. use static method startPage

PageHelper.startPage(1, 10);

List<Country> list = countryMapper.selectIf(1);

//3. use static method offsetPage

PageHelper.offsetPage(1, 10);

List<Country> list = countryMapper.selectIf(1);

//4. method parameters

public interface CountryMapper {

    List<Country> selectByPageNumSize(

            @Param("user") User user,

            @Param("pageNum") int pageNum,

            @Param("pageSize") int pageSize);

}

//config supportMethodsArguments=true

List<Country> list = countryMapper.selectByPageNumSize(user, 1, 10);

分页插件PageHelper项目地址： https://github.com/pagehelper/Mybatis-PageHelper

具体使用实例：https://github.com/pagehelper/Mybatis-PageHelper/blob/master/wikis/en/HowToUse.md

巴特西

SpringBoot(六) SpirngBoot与Mysql关系型数据库

pom.xml文件的配置

写配置文件

自定义数据源DRUID

编写java测试链接代码

编写测试代码

SpringBoot整合Mybatis

1.使用注解的方式

配置文件的方式整合Mybatis（xml方式）

PageHelper分页插件

最新文章

热门文章