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

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

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

f70c8558b03db88b9f171038a9cfba39.png

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

a00a6c12dd427f09026318940142e356.png

03

SpringBoot 整合 Mybatis(注解版)

  1. 建表:

96a1d3987519671e34f1042dfb4c8a5a.png

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

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

创建JavaBean:Department.java

440d073a16892be09d68edd840abaed2.png

使用“二”中《整合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. 建表:

96a1d3987519671e34f1042dfb4c8a5a.png

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

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

创建JavaBean:Department.java

440d073a16892be09d68edd840abaed2.png

使用“二”中《整合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);       }}
fc5b3c4b512472c6129c432e4293cd40.png

扫码关注我

微信号|fancheng1995

7409a4b55e0a9204a701373515c9a5cd.gif

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

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
Spring Boot 内嵌的 H2 数据库是一款基于 Java 的内存数据库,可以很方便地在应用程序中使用。使用 H2 数据库有很多好处,比如它非常轻量级,可以快速启动和停止,适合于开发和测试环境等。 下面是在 Spring Boot 中使用 H2 数据库的步骤: 1. 在 pom.xml 中添加 H2 数据库的依赖: ```xml <dependency> <groupId>com.h2database</groupId> <artifactId>h2</artifactId> <scope>runtime</scope> </dependency> ``` 2. 在 application.properties 文件中配置 H2 数据库相关信息: ```properties # H2 Database spring.datasource.url=jdbc:h2:mem:testdb spring.datasource.driver-class-name=org.h2.Driver spring.datasource.username=sa spring.datasource.password= ``` 这里的配置将 H2 数据库的 URL 设置为 `jdbc:h2:mem:testdb`,表示使用内存数据库,用户名和密码分别设置为 `sa` 和空字符串。 3. 创建实体类和数据访问层: ```java @Entity @Table(name = "users") public class User { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Long id; private String username; private String password; // getters and setters } @Repository public interface UserRepository extends JpaRepository<User, Long> { } ``` 这里我们创建了一个名为 `User` 的实体类,并使用 `JpaRepository` 接口来访问数据库。 4. 创建控制器和服务层: ```java @RestController public class UserController { @Autowired private UserService userService; @GetMapping("/users") public List<User> getUsers() { return userService.getUsers(); } } @Service public class UserService { @Autowired private UserRepository userRepository; public List<User> getUsers() { return userRepository.findAll(); } } ``` 这里我们创建了一个名为 `UserController` 的控制器,并使用 `UserService` 来获取用户列表。 5. 运行应用程序,并访问 http://localhost:8080/users 即可查看用户列表。 总之,使用 H2 数据库内嵌到 Spring Boot 应用程序中非常简单,可以方便地进行开发和测试。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值