Spring-Boot-Data

Spring Boot与数据访问


Docker

简介

Docker是一个开源的应用容器引擎;是一个轻量级容器技术;

Docker支持将软件编译成一个镜像;然后在镜像中各种软件做好配置,将镜像发布出去,其他使用者可以直接使用这个镜像;

运行中的这个镜像称为容器,容器启动是非常快速的。

核心概念

  • docker主机(Host):安装了Docker程序的机器(Docker直接安装在操作系统之上)
  • docker客户端(Client):连接docker主机进行操作
  • docker仓库(Registry):用来保存各种打包好的软件镜像;
  • docker镜像(Images):软件打包好的镜像,放在docker仓库中
  • docker容器(Container):镜像启动后的实例称为一个容器,容器是独立运行的一个或一组应用

安装Docker

1
sudo apt-get install docker.io

启动:

1
sudo systemctl start docker
1
sudo systemctl enable docker

测试:

1
sudo docker run hello-world

Docker常用命令&操作

镜像操作

操作 命令 说明
检索 docker search [keyword] 去docker hub上检索镜像的详细信息,如镜像的TAG。
拉取 docker pull [image-name]:[tag] :[tag]是可选的,多为软件的版本,默认是latest
列表 docker images 查看所有本地镜像
删除 docker rmi [image-id] 删除指定的本地镜像

容器操作

操作 命令 说明
运行 docker run –name [container-name] –d[image-name:tag] 根据镜像启动容器
停止 docker stop [container-id] 停止运行某个容器
查看 dokcer ps 查看在运行的容器
启动 docker start [container-id] 启动容器
删除 docker rm [container-id] 删除容器
日志 docker logs [container-name/container-id] 查看容器日志
进入 docker exec -it [container-name] bash 进入某个容器

在docker安装mysql

1
docker pull mysql

添加一个mysql映射到本机

1
docker run -p 3307:3306 --name ogic-mysql -e MYSQL_ROOT_PASSWORD=pass -d mysql

JDBC

Java数据库连接,Java Database Connectivity,简称JDBC

引入依赖

1
2
3
4
5
6
7
8
9
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>

配置application.yml

1
2
3
4
5
6
7
8
9
10
11
spring:
datasource:
username: root
password: [yourpassword]
url: jdbc:mysql://112.74.42.31:3307/jdbc
driver-class-name: com.mysql.cj.jdbc.Driver
# 调用类路径下的department.sql建表
schema:
- classpath:department.sql
# 强制初始化
initialization-mode: always

连接数据库测试

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
@RunWith(SpringRunner.class)
@SpringBootTest
public class SpringBootDataJdbcDemoApplicationTests {

@Autowired
DataSource dataSource;

@Test
public void contextLoads() throws SQLException {

System.out.println(dataSource.getClass());

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

}

建表文件department.sql

1
2
3
4
5
6
7
8
9
10
11
SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for department
-- ----------------------------
DROP TABLE IF EXISTS department;
CREATE TABLE department (
id int(11) NOT NULL AUTO_INCREMENT,
departmentName varchar(255) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

要留意分隔符问题(虽然这里没有出现),因为spring默认用;做分隔符,在sql中使用FUNCTION的话容易出错,这里要将分隔符改成$$,即在application.properties加入spring.datasource.separator=$$,也可以改application.yml,然后修改sql文件,例如:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 当存储过程`p1`存在时,删除。
drop procedure if exists p1;$$

-- 创建存储过程`p1`
create procedure p1()
begin
declare row_num int;
select count(*) into row_num from `t_user`;
if row_num = 0 then
INSERT INTO `t_user`(`username`, `password`) VALUES ('zhangsan', '123456');
end if;
end;$$

-- 调用存储过程`p1`
call p1();$$
drop procedure if exists p1;$$

使用一个Controller调用JdbcTemplate的方法来访问数据库:

1
2
3
4
5
6
7
8
9
10
11
12
@RestController
public class QueryController {

@Autowired
JdbcTemplate jdbcTemplate;

@GetMapping("/query")
public List<Map<String, Object>> allDepartment(){
List<Map<String, Object>> list = jdbcTemplate.queryForList("SELECT * FROM department");
return list;
}
}

Druid

Druid是由阿里巴巴数据库事业部出品,为监控而生的数据库连接池,我们可以使用它来监控SQL操作和Web请求,首先引入依赖:

1
2
3
4
5
6
<!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.16</version>
</dependency>

修改application.yml,得到:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
spring:
datasource:
username: root
password: yourpassword
url: jdbc:mysql://112.74.42.31:3308/demo
driver-class-name: com.mysql.cj.jdbc.Driver
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
filters: stat,wall
maxPoolPreparedStatementPerConnectionSize: 20
useGlobalDataSourceStat: true
connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500

之后在配置类中配置Druid:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
@Configuration
public class DruidConfig {

//按application.yml里的属性建立数据源
@ConfigurationProperties(prefix = "spring.datasource")
@Bean
public DataSource druid(){
return new DruidDataSource();
}

//配置一个管理后台的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","gogogo");
initParams.put("allow","");
initParams.put("deny","192.168.15.21");

bean.setInitParameters(initParams);
return bean;
}


//配置一个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/进入登录页

浏览器输入http://localhost:8080/query发送操作数据库的请求,该请求的实现在前面

MyBatis

引入依赖

1
2
3
4
5
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.0.1</version>
</dependency>

做一个Entity

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
public class Department {
private Integer id;
private String departmentName;

public Integer getId() {
return id;
}

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

public String getDepartmentName() {
return departmentName;
}

public void setDepartmentName(String departmentName) {
this.departmentName = departmentName;
}
}

使用注释配置

在接口中使用@Mapper来使用MyBatis,注意一定要有这个注释,不然MyBatis不指定你这是一个Mapper,也可以使用@MapperScan来扫描一个区域的Java文件并注释为Mapper,但不推荐,不过把项目给他人使用时会方便许多,当别人把Java文件拖进某个包里就会自动注释为Mapper。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
@Mapper
public interface DepartmentMapper {

@Select("SELECT * FROM department WHERE id = #{id}")
public Department getDeptById(Integer id);

@Delete("DELETE FROM department WHERE id = #{id}")
public Integer deleteDeptById(Integer id);

@Options(useGeneratedKeys = true, keyProperty = "id")
@Insert("INSERT INTO department(departmentName) VALUES(#{departmentName})")
public Integer insertDept(Department department);

@Update("UPDATE department SET departmentName=#{departmentName} WHERE id = #{id}")
public Integer updateDept(Department department);
}

其中@Options(useGeneratedKeys = true, keyProperty = "id")用来指明ID,MyBatis插入时会获取表格中最后一行的ID,并+1赋给新的一行

Controller中使用该接口:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
@RestController
public class DepartmentController {

@Autowired
DepartmentMapper departmentMapper;

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

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

上面的例子中查询可以在浏览器输入http://localhost:8080/dept/1来查询第一个department,插入可以输入http://localhost:8080/dept?departmentName=newDepartmentName来插入一个新的department

如果要修改MyBatis的配置规则,可以给容器中添加一个ConfigurationCustomizer,例如,下面启用驼峰命名法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
@org.springframework.context.annotation.Configuration
public class MyBatisConfig {

@Bean
public ConfigurationCustomizer configurationCustomizer(){
return new ConfigurationCustomizer(){

@Override
public void customize(Configuration configuration) {
configuration.setMapUnderscoreToCamelCase(true);
}
};
}
}

使用XML文件配置

若要使用XML文件进行配置,需要现在application.yml中指定XML文件的位置

1
2
3
mybatis:
config-location: classpath:mybatis/mybatis-config.xml 指定全局配置文件的位置
mapper-locations: classpath:mybatis/mapper/*.xml 指定sql映射文件的位置

全局配置文件:

1
2
3
4
5
6
7
8
9
<?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>

sql映射文件:

1
2
3
4
5
6
7
8
9
<?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.ogic.springbootmybatisdemo.mapper.EmployeeMapper">
<select id="getEmpById" resultType="com.ogic.springbootmybatisdemo.bean.Employee">
SELECT * FROM employee WHERE id=#{id}
</select>
</mapper>

这里要注意我们还是需要EmployeeMapper接口,并在其中声明好我们要使用的方法,只不过是吧注释部分的内容移到XML文件中而已。

1
2
3
4
@Mapper
public interface EmployeeMapper {
public Employee getEmpById(Integer id);
}

JPA

JPAJava Persistence API Java持久化API是指一个Java 应用程序接口规范

  1. 编写一个实体类与数据表映射

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    @Entity //告诉JPA这是一个实体类(和数据表映射的类)
    @Table(name = "demo") //@Table来指定和哪个数据表对应;如果省略默认表名就是类名小写;
    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接口来操作实体类对应的数据表

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

    其中User是实体类类名,Integer是主键类型

  3. application.yml里进行基本的配置,默认使用hibernate

    1
    2
    3
    4
    5
    6
    7
    spring:  
    jpa:
    hibernate:
    # 更新或者创建数据表结构
    ddl-auto: update
    # 控制台显示SQL
    show-sql: true