关于SpringBoot与数据访问的笔记

JDBC

关于配置文件

这是关于application.yml文件的内容
注入的druid数据源

spring:
datasource:
username: root
password: 123456
url: jdbc:mysql://127.0.0.1:3306/jdbc?serverTimezone=UTC
driver-class-name: com.mysql.cj.jdbc.Driver
schema: classpath:department.sql
initialization-mode: always
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

整合druid数据源

@Configuration
public class DruidConfig {
@ConfigurationProperties(prefix = “spring.datasource”)
@Bean
public DataSource DruidConfig(){
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”,“123456”);
initParams.put(“allow”,"");
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;
}
}

controller代码

public class HelloController {
@Autowired
JdbcTemplate template;
@ResponseBody
@GetMapping("/query")
public Map<String,Object> map(){
    List<Map<String, Object>> list = template.queryForList("select * FROM student");
    return list.get(0);
}
}

整合mybatis

1）、配置数据源相关属性（见上一节Druid）
2）、给数据库建表
3）、创建JavaBean

注解版

mapper

//@Mapper
public 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);
}

自定义MyBatis的配置规则；给容器中添加一个ConfigurationCustomizer；
@Configuration
public class MyBatisConfig {

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

还可以在启动类中扫描所有的mapper，等同于给每个mapper类中加入了@mapper注解

@MapperScan(value = "com.snbc.mapper")
@SpringBootApplication
public class SpringbootDataMybatisApplication {

public static void main(String[] args) {
    SpringApplication.run(SpringbootDataMybatisApplication.class, args);
}
}

配置文件版（非注解版）

mapper就只是一个单纯的接口，定义xml文件实现具体数据访问

public interface EmployeeMapper {

public Employee getEmpById(Integer id);

public void insertEmp(Employee employee);

}

mybatis的相关配置写到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>
<settings>
    <setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
</configuration>

mapper接口的具体实现要写在对应的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.snbc.mapper.EmployeeMapper">
 <!--    public Employee getEmpById(Integer id);
public void insertEmp(Employee employee);-->
<select id="getEmpById" resultType="com.snbc.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>

而application.yml文件里需要扫描到所有的配置文件

mybatis:
	config-location: classpath:mybatis/mybatis-config.xml 指定全局配置文件的位置
    mapper-locations: classpath:mybatis/mapper/EmployeeMapper.xml 指定全局配置文件的位置

整合SpringData JPA

1）、编写一个实体类（bean）和数据表进行映射，并且配置好映射关系

//使用JPA注解配置映射关系
@Entity //告诉JPA这是一个实体类（和数据表映射的类）
@Table(name = "tbl_user") //@Table来指定和哪个数据表对应;如果省略默认表名就是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;

2）、编写一个Dao接口来操作实体类对应的数据表（Repository）
JpaRespository的两个参数，第一个问实体类，第二个位主键的类型

 //继承JpaRepository来完成对数据库的操作
public interface UserRepository extends JpaRepository<User,Integer> {
}

3）、基本的配置JpaProperties

spring: 
 jpa:
hibernate:
#     更新或者创建数据表结构
  ddl‐auto: update
#    控制台显示SQL
show‐sql: true