SpringBoot之数据访问

最新推荐文章于 2023-08-02 14:40:00 发布

C8WATER

最新推荐文章于 2023-08-02 14:40:00 发布

阅读量335

点赞数 1

分类专栏： SpringBoot 文章标签： Java SpringBoot 数据访问

本文链接：https://blog.csdn.net/ip_jl/article/details/84947135

版权

SpringBoot 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

回顾:

整合JDBC

新建SpringBoot项目:

默认配置:

spring:
  datasource:
    username: root
    password: root
    url: jdbc:mysql://192.168.5.133:3306/jdbc
    driver-class-name: com.mysql.jdbc.Driver

测试代码:

@Test
public void contextLoads() throws SQLException{

    System.out.println(dataSource);
    Connection connection = dataSource.getConnection();
    System.out.println(connection);
    connection.close();
}

说明默认使用的是org.apache.tomcat.jdbc.pool.DataSource的数据源

默认支持:

org.springframework.boot.autoconfigure.jdbc

org.springframework.boot.autoconfigure.jdbc.DataSourceConfiguration

org.apache.tomcat.jdbc.pool.DataSource

org.apache.commons.dbcp.BasicDataSource

com.zaxxer.hikari.HikariDataSource

org.apache.commons.dbcp2.BasicDataSource

自定义数据类型(如: c3p0)

static class Generic {
    Generic() {
    }

    @Bean
    public DataSource dataSource(DataSourceProperties properties) {
    //使用builder创建数据源(自行看源码即可)
        return properties.initializeDataSourceBuilder().build();
    }
}

org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration

DataSourceInitializer --> ApplicationListener

runSchemaScripts(); //运行建表语句

runDataScripts(); //运行插入数据语句

源码里面说明了只需要将文件命名为schema-*.sql(默认是schema.sql)并放在classpath路径下即可被识别

也可以使用配置的方式: spring.datasource.schema=classpath:department.sql

配置自定义数据源:

<!--引入阿里的druid数据源
-->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.1.8</version>
</dependency>

配置文件: spring.datasource.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
useGlobalDataSourceStat: true
connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500

新建配置类DruidConfig:

@Configuration
public class DruidConfig {

    @ConfigurationProperties("spring.datasource")
    @Bean
    public DruidDataSource druid(){
        return new DruidDataSource();
    }

    /**
     * 配置druid的监控
     */
    //1.配置一个管理后台的servlet
    @Bean
    public ServletRegistrationBean statViewServlet(){
        ServletRegistrationBean bean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
        Map<String, String> initParams = new HashMap<>();
        initParams.put("loginUsername", "admin");
        initParams.put("loginPassword", "root");
        initParams.put("allow", "");  //默认允许所有
        initParams.put("deny", "173.0.0.88");  //拒绝本机访问
        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;
    }
}

访问 http://localhost:8080/druid/login.html

整合MyBatis

1) 注解版方式

新建SpringBoot项目:

同JDBC一样需要配置文件和druid的配置类

新建操作数据库的Mapper接口:

@Mapper//指定这是一个操作数据库的mapper
public interface DepartmentMapper {

    //将sql语句直接写在方法上
    @Select("select * from department where id=#{id}")
    public Department getDeptById(Integer id);

    @Delete("delete from department where id=#{id}")
    public int delDept(Integer id);

    @Options(useGeneratedKeys = true, keyProperty = "id") //使用自增的id, 并指定对象中的id是主键
    @Insert("insert into department(departmentName) value(#{departmentName})")
    public int insertDept(Department department);

    @Update("update department d set d.departmentName=#{departmentName} where id=#{id}")
    public int updateDept(Department department);
}

ps: 此时@Mapper的注解是必须要在接口上使用的, 但有很多个mapper接口时, 则每个都要去写上该注解则比较麻烦, 可以使用@MapperScan("<包路径>") 注解放在启动类上即可扫描该包路径下的所有mapper类.

新建Controller类:

@RestController
public class DeptController {

    @Autowired
    DepartmentMapper departmentMapper;

    @GetMapping("dept/{id}")
    public Department getDeptById(@PathVariable("id") Integer id){
        return departmentMapper.getDeptById(id);
    }

    @GetMapping("/insertDept")
    public Department insertDept(Department department){
        departmentMapper.insertDept(department);
        return department;
    }
}

访问http://localhost:8080/insertDept/departmentName=赵六

修改驼峰匹配模式: 新建自定义配置类 MyBatisConfig

@org.springframework.context.annotation.Configuration
public class MyBatisConfig {

    @Bean
    public ConfigurationCustomizer configurationCustomizer(){
        return new ConfigurationCustomizer() {
            @Override
            public void customize(Configuration configuration) {
                configuration.setMapUnderscoreToCamelCase(true);
            }
        };
    }
}

当数据库时xxx_xxx, 而实体类是xxxXxx时, 依旧能进行匹配.

2) 配置文件方式

1) 目录结构:

① EmployeeMapper.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="org.mybatis.example.BlogMapper">
    <select id="selectBlog" resultType="Blog">
select * from Blog where id = #{id}
</select>
</mapper>

修改之后:

<?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.idea.springboot.mapper.EmployeeMapper">
    <select id="getEmpById" resultType="com.idea.springboot.bean.Employee">
        select * from employee where id=#{id}
    </select>

    <insert id="insertEmp">
        insert into employee(lastName,email,gender,d_id) values (#{lastName},#{email},#{gender},#{dId})
    </insert>
</mapper>

② mybatis-config.xml

官网的配置文件框架:

<?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>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="org/mybatis/example/BlogMapper.xml"/>
    </mappers>
</configuration>

修改之后:

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

controller测试:

@RestController
public class EmpController {

    @Autowired
    EmployeeMapper employeeMapper;

    @GetMapping("/emp/{id}")
    public Employee getEmpById(@PathVariable("id") Integer id){
        return employeeMapper.getEmpById(id);
    }
}

整合JPA

SpringData: 统一数据访问的API(对其他JPA的再封装)

步骤:

① 编写一个实体类, 配置好和数据库表的映射关系.

@Entity//表示JPA这是一个实体类(和数据表映射的类)
@Table(name = "tbl_user")//和指定的数据库表对应(如果省略, 默认表名就是类名小写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;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }
}

② 编写dao接口, 操作实体类对应的数据库表, 该接口需要继承JpaRepository接口.

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

③ 基础的配置(其他都是自动配置好)

spring:
  jpa:
    hibernate:
#      update: 更新或创建数据库表(没有则创建, 有则更新)
      ddl-auto: update
#      true: 控制台展示sql
    show-sql: true

④ controller测试

@RestController
public class UserController {

    @Autowired
    UserRepository userRepository;

    @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 user1 = userRepository.save(user);
        return user1;
    }
}

C8WATER

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
SpringBoot之数据访问

回顾:SpringBoot之基础SpringBoot之配置SpringBoot之日志SpringBoot之Web开发基础SpringBoot之Web开发实验SpringBoot之Web开发后续处理SpringBoot之Docker准备整合JDBC新建SpringBoot项目:默认配置:spring: datasource: userna...
复制链接

扫一扫