mysql url访问_8、SpringBoot 数据访问

01

JDBC

1. 使用默认数据源类型

• 在原来已经导入了 web 启动器(spring-boot-starter-web)和 JSP 依赖的基础上，再导入 JDBC 启动器(spring-boot-starter-jdbc)和 MySQL 的驱动依赖(mysql-connector-java)

       <dependency>             <groupId>org.springframework.bootgroupId>             <artifactId>spring-boot-starter-jdbcartifactId>       dependency>       <dependency>             <groupId>mysqlgroupId>             <artifactId>mysql-connector-javaartifactId>             <scope>runtimescope>       dependency>

在全局配置文件(application.properties)中添加数据库连接配置和JSP前后缀：

spring.mvc.view.prefix=/WEB-INF/jsp/spring.mvc.view.suffix=.jspspring.datasource.url=jdbc:mysql://localhost:3306/jdbc?serverTimezone=GMTspring.datasource.driver-class-name=com.mysql.jdbc.Driverspring.datasource.username=rootspring.datasource.password=root

测试代码：

@Controllerpublic class HelloController {    @Autowired    private JdbcTemplate jdbcTemplate;    @ResponseBody    @GetMapping("/query")    public Map<String, Object> map(){        ListString,         return  list.get(0);    }}

自定义数据源类型

• 引入自定义数据源依赖(关于数据源依赖可以查看博客：https://blog.csdn.net/fancheng614/article/details/85543816)

       <dependency>             <groupId>c3p0groupId>             <artifactId>c3p0artifactId>             <version>0.9.1.2version>       dependency>

自定义数据源

@Configurationpublic class MyDataSource {    @Value("${spring.datasource.url}")    String url;    @Value("${spring.datasource.username}")    String username;    @Value("${spring.datasource.password}")    String password;    @Value("${spring.datasource.driver-class-name}")    String driverClassName;    @Bean("dataSource")    public DataSource druidDataSource(StandardEnvironment env) throws  Exception {        Properties properties = new Properties();        ComboPooledDataSource dataSource = new ComboPooledDataSource();        dataSource.setDriverClass(driverClassName);        dataSource.setJdbcUrl(url);        dataSource.setUser(username);        dataSource.setPassword(password);        return dataSource;    }}

在全局配置文件(application.properties)中配置数据源相关信息

spring.mvc.view.prefix=/WEB-INF/jsp/spring.mvc.view.suffix=.jspspring.datasource.url=jdbc:mysql://localhost:3306/jdbc?serverTimezone=GMTspring.datasource.driver-class-name=com.mysql.jdbc.Driverspring.datasource.username=rootspring.datasource.password=root#指定数据源类型spring.datasource.type=com.mchange.v2.c3p0.ComboPooledDataSource

测试代码同《使用默认数据源类型》

自动执行建表语句和插入数据语句

02

整合Druid数据源

1. 使用Druid数据源可以监控数据库访问性能，Druid内置提供了一个功能强大的StatFilter插件，能够详细统计SQL的执行性能，这对于线上分析数据库访问性能有帮助。(更多Druid特性自行百度)

2. 导入Druid数据源依赖

                        <dependency>            <groupId>com.alibabagroupId>            <artifactId>druidartifactId>            <version>1.1.8version>        dependency>

自定义数据源

@Configurationpublic class MyDataSource {              // 注入 Druid 数据源       @ConfigurationProperties(prefix = "spring.datasource")       @Bean       public DataSource druid() {             return new DruidDataSource();       }              //配置Druid的监控    //1、配置一个管理后台的Servlet，    //Druid后台监控平台：http://localhost:8080/druid/index.html       @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");        initParams.put("allow","");//默认就是允许所有访问        initParams.put("deny","127.0.0.1"); // 设置不允许访问的IP        bean.setInitParameters(initParams);        return bean;    }              //2、配置一个web监控的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;    }}

在全局配置文件(application.properties)中配置数据源相关信息(还可以配置更多信息)

spring.mvc.view.prefix=/WEB-INF/jsp/spring.mvc.view.suffix=.jspspring.datasource.url=jdbc:mysql://localhost:3306/jdbc?serverTimezone=GMTspring.datasource.driver-class-name=com.mysql.jdbc.Driverspring.datasource.username=rootspring.datasource.password=rootspring.datasource.type=com.alibaba.druid.pool.DruidDataSourcespring.datasource.initialSize=5spring.datasource.minIdle=5spring.datasource.maxActive=20spring.datasource.maxWait=60000spring.datasource.timeBetweenEvictionRunsMillis=60000spring.datasource.minEvictableIdleTimeMillis=300000spring.datasource.validationQuery=SELECT 1 FROM DUALspring.datasource.testWhileIdle=truespring.datasource.testOnBorrow=falsespring.datasource.testOnReturn=falsespring.datasource.poolPreparedStatements=true#spring.datasource.filters=stat,wall,log4jspring.datasource.maxPoolPreparedStatementPerConnectionSize=20spring.datasource.useGlobalDataSourceStat=truespring.datasource.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500

Druid数据源监控访问：http://localhost:8080/druid/index.html

03

SpringBoot 整合 Mybatis(注解版)

1. 建表：

在上面《整合Druid数据源》基础上添加整合mybatis的依赖：

       <dependency>           <groupId>org.mybatis.spring.bootgroupId>           <artifactId>mybatis-spring-boot-starterartifactId>           <version>2.1.3version>       dependency>

创建JavaBean：Department.java

使用“二”中《整合Druid数据源》

编写操作数据库的Mapper：

@Mapperpublic interface DepartmentMapper {       @Select("select * from department where id=#{id}")       public Department getDeptById(Integer id);       @Delete("delete from department where id=#{id}")       public int deleteDeptById(Integer id);       @Options(useGeneratedKeys = true,keyProperty = "id")       @Insert("insert into department(departmentName)  values(#{departmentName})")       public int insertDept(Department department);       @Update("update department set departmentName=#{departmentName} where  id=#{id}")       public int updateDept(Department department);}

编写测试Controller：

@Controllerpublic class DepartmentCtrl {              @Autowired       private DepartmentMapper departmentMapper;              @ResponseBody       @RequestMapping("/getDepartment/{id}")       public Department getDepartment(@PathVariable("id")Integer id) {             Department department = departmentMapper.getDeptById(id);             return department;       }       @ResponseBody       @GetMapping("/insertDept")    public Department insertDept(Department department){        departmentMapper.insertDept(department);        return department;    }}

测试:

• http://localhost:8080/getDepartment/1

• http://localhost:8080/insertDept?departmentName=mengfancheng

(可选)自定义Mybatis的配置规则；给容器中添加一个ConfigurationCustomizer(这里举例启用驼峰命名规则)

@Configurationpublic class MyBatisConfig {    @Bean    public ConfigurationCustomizer configurationCustomizer(){        return new ConfigurationCustomizer() {            @Override            public void customize(org.apache.ibatis.session.Configuration configuration) {                configuration.setMapUnderscoreToCamelCase(true);            }        };    }}

如果不想在每个Mapper接口上面添加 @Mapper 注解，可以在主配置类上加注解：

//使用MapperScan批量扫描所有的Mapper接口；@MapperScan(value = "com.mfc.mapper")@SpringBootApplicationpublic class SpringBootMybatisApplication {    public static void main(String[] args) {        SpringApplication.run(SpringBoot4Application.class, args);    }}

04

SpringBoot 整合 Mybatis(配置文件版)

1. 建表：

在上面《整合Druid数据源》基础上添加整合mybatis的依赖：

       <dependency>           <groupId>org.mybatis.spring.bootgroupId>           <artifactId>mybatis-spring-boot-starterartifactId>           <version>2.1.3version>       dependency>

创建JavaBean：Department.java

使用“二”中《整合Druid数据源》

编写操作数据库的Mapper：

@Mapperpublic interface DepartmentMapper {       public Department getDeptById(Integer id);       public int insertDept(Department department);}

在resource/mybatis/mapper文件夹下面放了一个Department.xml文件映射Department.java：

<?xml version="1.0" encoding="UTF-8" ?>/span>        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"        "http://mybatis.org/dtd/mybatis-3-mapper.dtd"><mapper namespace="com.mfc.mapper.DepartmentMapper">       <select id="getDeptById" resultType="com.mfc.entity.Department">        SELECT * FROM Department WHERE id=#{id}    select>    <insert id="insertDept">        INSERT INTO Department(departmentName) VALUES (#{departmentName})    insert>mapper>

在resource/mybatis/文件夹下放了mybatis-config.xml全局配置文件

<?xml version="1.0" encoding="UTF-8" ?>/span>        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>

使用配置文件时注意在全局配置文件中配置上这些配置文件的路径

# 指定全局配置文件位置mybatis.config-location=classpath:mybatis/mybatis-config.xml# 指定sql映射文件位置mybatis.mapper-locations=classpath:mybatis/mapper/*.xml

编写测试Controller：

@Controllerpublic class DepartmentCtrl {              @Autowired       private DepartmentMapper departmentMapper;              @ResponseBody       @RequestMapping("/getDepartment/{id}")       public Department getDepartment(@PathVariable("id")Integer id) {             Department department = departmentMapper.getDeptById(id);             return department;       }       @ResponseBody       @GetMapping("/insertDept")    public Department insertDept(Department department){        departmentMapper.insertDept(department);        return department;    }}

测试:

1. http://localhost:8080/getDepartment/1

2. http://localhost:8080/insertDept?departmentName=mengfancheng

如果不想在每个Mapper接口上面添加 @Mapper 注解，可以在主配置类上加注解：

//使用MapperScan批量扫描所有的Mapper接口；@MapperScan(value = "com.mfc.mapper")@SpringBootApplicationpublic class SpringBootMybatisApplication {    public static void main(String[] args) {        SpringApplication.run(SpringBoot4Application.class, args);    }}

05

整合SpringData JPA

1. 使用上述的Druid数据源，配置数据源

2. 导入JPA的starter启动器

3. 创建实体类User.java

package com.example.springbootjpa.entity;import javax.persistence.*;@Entity@Table(name = "tbl_user")public class User {    // 主键    @Id    @GeneratedValue(strategy = GenerationType.IDENTITY) // 自增主键    private Integer id;    @Column(name = "last_name", length = 50)    private String lastName;    // /省略默认列名就是属性名    @Column    private String email;    // set && get}

编写一个Dao接口来操作实体类对应的数据表(Repository)

public interface UserRepository extends JpaRepository<User, Integer> {}

测试Controller

@Controllerpublic class UserController {    @Autowired    private UserRepository userRepository;    @ResponseBody    @GetMapping("/user/{id}")    public User getUser(@PathVariable("id") Integer id){        User user = userRepository.findOne(id);        return user;    }    @GetMapping("/user")    public User insertUser(User user){        User save = userRepository.save(user);        return save;    }}

06

SpringBoot整合Hibernate(重要)

1. 使用上面的 Druid数据源，配置数据源

2. 添加JPA依赖

    <dependency>        <groupId>org.springframework.bootgroupId>        <artifactId>spring-boot-starter-data-jpaartifactId>    dependency>

编写全局配置文件 application.properties

spring.datasource.url=jdbc:mysql://localhost:3306/jdbc?serverTimezone=GMTspring.datasource.driver-class-name=com.mysql.jdbc.Driverspring.datasource.username=rootspring.datasource.password=rootspring.datasource.type=com.alibaba.druid.pool.DruidDataSourcespring.jpa.properties.hibernate.dialect:  org.hibernate.dialect.MySQL5Dialectspring.jpa.hibernate.ddl-auto=updatespring.jpa.show-sql=true#SpringBoot默认的配置是会将sql句子中的命名由驼峰 accessToken转换为下划线 access_token，添加一下配置使用驼峰命名，不加下划线spring.jpa.hibernate.naming.physical-strategy =  org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl

编写JavaBean：Department.java

@Entity@Table(name = "Department")public class Department {       @Id    @GeneratedValue //主键，自动递 增       private Integer id;       @Column(name = "departmentName")       private String departmentName;}

编写数据访问接口DepartmentDao.java：

public interface DepartmentDao {       public Department getById(Integer id);       public void insert(Department department);}

编写数据访问接口的实现类 DepartmentDaoImpl.java：

• 注意：学习SpringBoot时使用的是SpringBoot 2.1.12.RELEASE版本，此版本SpringBoot集成的是Hibernate5.3.5.Final：

<hibernate.version>5.3.15.Finalhibernate.version>

所以不能再直接注入SessionFactory。 需要注入EntityManagerFactory，再获取Session

       @Autowired       private EntityManagerFactory entityManagerFactory;       public Session getSession() {           return  entityManagerFactory.unwrap(SessionFactory.class).openSession();       }

@Repositorypublic class DepartmentDaoImpl implements DepartmentDao {       @Autowired       private EntityManagerFactory entityManagerFactory;       public Session getSession() {           return  entityManagerFactory.unwrap(SessionFactory.class).openSession();       }       @Override       public Department getById(Integer id) {             Department department = getSession().get(Department.class, id);             System.out.println(department.getDepartmentName());             return department;       }       @Override       public void insert(Department department) {             getSession().save(department);       }}

编写Controller：DepartmentCtrl.java：

@Controllerpublic class DepartmentCtrl {       @Autowired       private DepartmentDao departmentDao;       @ResponseBody       @RequestMapping("/getDepartment/{id}")       public Department getDepartment(@PathVariable("id")Integer id) {             Department department = departmentDao.getById(id);             return department;       }       @ResponseBody       @GetMapping("/insertDept")    public Department insertDept(Department department){             departmentDao.insert(department);        return department;    }}

编写主启动类：

@EnableJpaRepositories("com.mfc.dao") // JPA扫描该包路径下的Repositorie@EntityScan("com.mfc.entity") // 扫描实体类@SpringBootApplicationpublic class SpringBoot5Application {       public static void main(String[] args) {             SpringApplication.run(SpringBoot5Application.class, args);       }}

扫码关注我

微信号｜fancheng1995

你们点点“分享”，给我充点儿电吧~