<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
错误
java.sql.SQLException: The server time zone value '�й���ʱ��' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.
在url中加上serverTimezone=UTC
效果:
默认是用 org.apache.tomcat.jdbc.pool.DataSource 作为数据源;
数据源的相关配置都在 DataSourceProperties 里面;
自动配置原理:
org.springframework.boot.autoconfigure.jdbc:
org.apache.tomcat.jdbc.pool.DataSource、HikariDataSource、BasicDataSource、
/**
* Generic DataSource configuration.
*/
@ConditionalOnMissingBean(DataSource.class)
@ConditionalOnProperty(name = "spring.datasource.type")
static class Generic {
@Bean
public DataSource dataSource(DataSourceProperties properties) {
//使用DataSourceBuilder创建数据源,利用反射创建响应type的数据源,并且绑定相关属性
return properties.initializeDataSourceBuilder().build();
}
}
作用:
(1)runSchemaScripts();运行建表语句;
(2)runDataScripts();运行插入数据的 SQL 语句;
默认只需要将文件命名为:
schema-*.sql、data-*.sql
默认规则:schema.sql,schema-all.sql;
可以使用
schema:
- classpath:department.sql
指定位置
<!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.8</version>
</dependency>
spring:
datasource:
username: root
password: 123456
url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false
driver-class-name: com.mysql.jdbc.Driver
type: com.alibaba.druid.pool.DruidDataSource
# 下面为连接池的补充设置,应用到上面所有数据源中
# 初始化大小,最小,最大
initial-size: 5
min-idle: 5
max-active: 20
# 配置获取连接等待超时的时间
max-wait: 60000
# 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒
time-between-eviction-runs-millis: 60000
# 配置一个连接在池中最小生存的时间,单位是毫秒
min-evictable-idle-time-millis: 300000
validation-query: SELECT 1 FROM DUAL
test-while-idle: true
test-on-borrow: false
test-on-return: false
# 打开PSCache,并且指定每个连接上PSCache的大小
pool-prepared-statements: true
# 配置监控统计拦截的filters,去掉后监控界面sql无法统计,'wall'用于防火墙
max-pool-prepared-statement-per-connection-size: 20
filters: stat,wall
use-global-data-source-stat: true
# 通过connectProperties属性来打开mergeSql功能;慢SQL记录
connect-properties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
Druid的补充配置需要手动映射
导入druid数据源
@Configuration
public class DruidConfig {
//手动映射
@ConfigurationProperties(prefix = "spring.datasource")
@Bean
public DataSource druid() {
return new DruidDataSource();
}
//配置Druid的监控
//1.配置一个管理后台的servlet
@Bean
public ServletRegistrationBean statViewServlet() {
ServletRegistrationBean<StatViewServlet> bean = new ServletRegistrationBean<>(new StatViewServlet(), "/druid/*");
Map<String,String> initParams = new HashMap<>();
initParams.put("loginUsername","admin");
initParams.put("loginPassword","458974");
initParams.put("allow",""); //不填写值的话就是默认所有
initParams.put("deny","");
bean.setInitParameters(initParams);
return bean;
}
//2.配合一个web监控的filter
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;
}
}
<!-- 这不是spring官方提供的,由mybatis自己做的适配包 -->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.3.1</version>
</dependency>
//指定这是一个操作数据库的mapper
@Mapper
public interface SysUserMapper {
@Select("select * from sys_user where user_id = #{id} ")
public SysUser getUserById(Long id);
}
@Options(useGeneratedKeys = true,keyProperty = "id") //回传自增主键,keyProperty是对象中的属性
@Insert("insert into department(departmentName) values(#{departmentName})")
public int insertDept(Department department);
假定我们要使数据库字段 user_name 和实体的 userName 属性匹配,那么需要自定义配置规则,如下
@org.springframework.context.annotation.Configuration
public class MyBatisConfig {
@Bean
public ConfigurationCustomizer configurationCustomizer() {
return new ConfigurationCustomizer() {
@Override
public void customize(Configuration configuration) {
configuration.setMapUnderscoreToCamelCase(true); //开启驼峰命名规则
}
};
}
}
又或者可以在配置文件中配置开启驼峰映射
mybatis:
configuration:
map-underscore-to-camel-case: true
一个注解扫描所有包,不需要每个注解加@Mapper
使用MapperScan批量扫描所有的Mapper接口;
@MapperScan(value = "top.wu.springboot.mapper")
@SpringBootApplication
public class SpringBoot06DataMybatisApplication {
public static void main(String[] args) {
SpringApplication.run(SpringBoot06DataMybatisApplication.class, args);
}
}
mybatis:
config-location: classpath:mybatis/mybatis-config.xml 指定全局配置文件的位置
mapper-locations: classpath:mybatis/mapper/*.xml 指定sql映射文件的位置
上面配合的驼峰映射 configuration:
无法与 config-location:
一起使用,换在 XML 中配置
<configuration>
<settings>
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
</configuration>
更多使用参照
http://www.mybatis.org/spring-boot-starter/mybatis-spring-boot-autoconfigure/
JPA:ORM(Object Relational Mapping);
//使用JPA注解配置映射关系
@Entity //告诉JPA这是一个实体类(和数据表映射的类)
@JsonIgnoreProperties(value = {"hibernateLazyInitializer", "handler"}) //设置当有属性为null值时任可转为json,不写的话存在null值会报错,错误信息如下
@Table(name = "sys_user") //@Table来指定和哪个数据表对应;如果省略默认表名就是user;
public class SysUser implements Serializable {
@Id //这是一个主键
@GeneratedValue(strategy = GenerationType.IDENTITY)//自增主键
private long user_id;
@Column(name = "user_code",length = 50) //这是和数据表对应的一个列
private String user_code;
@Column //省略默认列名就是属性名
private String user_name;
@Column
private String user_password;
@Column
private Character user_state;
省略getter/setter
}
com.fasterxml.jackson.databind.exc.InvalidDefinitionException: No serializer found for class org.hibernate.proxy.pojo.bytebuddy.ByteBuddyInterceptor and no properties discovered to create BeanSerializer (to avoid exception, disable SerializationFeature.FAIL_ON_EMPTY_BEANS) (through reference chain: com.wu.srpingbootdata.pojo.SysUser$HibernateProxy$cGGP5atS["hibernateLazyInitializer"])
//继承JpaRepository来完成对数据库的操作
//泛型1是要操作的对象,2是主键的ID
public interface UserRepository extends JpaRepository<User,Integer> {
}
spring:
jpa:
hibernate:
# 更新或者创建数据表结构
ddl-auto: update
# 控制台显示SQL
show-sql: true
@RequestMapping("/hei")
@ResponseBody
public SysUser hei() {
SysUser sysUser = userRepository.getOne(3l);
return sysUser;
}