整合DRUID資料源
實際的開發中,我們很少用上面那種方式的資料源。
引入DruidDataSource:
com.alibaba
druid
1.1.8
配置:
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
基本配置的這些屬性是和DataSourceProperties對應的,但這裡配置了還不能夠用到,還需要一個Configuration,目的是不使用反射的自動建立,自己建立并綁定屬性。
@Configuration
public class DruidConfig {
@ConfigurationProperties(prefix = "spring.datasource")
@Bean
public DataSource druid() {
return new DruidDataSource();
}
//配置Druid的監控
//1、配置一個管理背景的Servlet
@Bean
public ServletRegistrationBean statViewServlet() {
ServletRegistrationBean bean = new ServletRegistrationBean(new StatViewServlet(), "/druid
@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);
建立Controller
@RestController
public class DeptController {
@Autowired
DepartmentMapper departmentMapper;
@GetMapping("/dept/{id}")
public Department getDept(@PathVariable("id") Integer id) {
return departmentMapper.getDeptById(id);
}
@GetMapping("/dept")
public Department insert(Department department) {
departmentMapper.insertDept(department);
return department;
}
}
目前,我們的資料庫中表的字段名稱和javaBean中屬性名是一緻的,如果改動了,則不能夠比對,者就需要一個配置檔案或者配置類來解決這個問題。
建立MyBatisConfig類,使用自定義MyBatis的配置規則
@org.springframework.context.annotation.Configuration
public class MyBatisConfig {
@Bean
public ConfigurationCustomizer configurationCustomizer(){
return new ConfigurationCustomizer() {
@Override
public void customize(Configuration configuration) {
configuration.setMapUnderscoreToCamelCase(true);
}
};
}
}
可以使用 @MapperScan批量掃描。
配置檔案的方式
EmployeeMapper.xml
/p>
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
SELECT * FROM employee WHERE id=#{id}
INSERT INTO employee(lastName,email,gender,d_id) VALUES (#{lastName},#{email},#{gender},#{dId})
mybatis.config.xml
/p>
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
當然還是需要mapper的
@Mapper
public interface EmployeeMapper {
public Employee getEmpById(Integer id);
public void insertEmp(Employee employee);
}
重要的一點,需要在yml檔案中配置
mybatis:
mapper-locations: classpath:mybatis/mapper/*.xml 指定映射檔案配置位置
config-location: classpath:mybatis/mybatis-config.xml 指定全局檔案配置
運作測試的過程中,又出現了之前的異常
java.sql.SQLException: The server time zone value 'Öйú±ê׼ʱ¼ä' is unrecognized or represents more than one time
這次換用另一種方式解決,不使用指令 set global time_zone='+8:00' ;來解決,而是在資料庫驅動url後面添加serverTimezone=UTC,也能解決這個問題。
url: jdbc:mysql://36.1.52.145:3306/jdbc?serverTimezone=UTC