全民学后端快餐教程(2) - 连接数据库
上一节我们介绍了如何像写一个普通Java程序一样去写Web应用，现在我们已经可以通过@Controller注解来获取路由，并且返回字符串给浏览器显示。
跟客户端打通了之后，下面最重要的任务就是能够访问数据库。我们就以MySQL数据库被Oracle收购后fork出来的Mariadb为例，说明连接数据库的方法。
安装配置Mariadb
看了下阿里云ECS最新的Ubuntu镜像已经升级到了18.04 LTS，我们就以此版本为基础说明。
安装Mariadb
安装Mariadb很简单，用下面命令就可以了：
apt install mariadb-server
连接到Mariadb
通过mysql连接Mariadb服务：
mysql -u root
然后我们执行下show databases看看都有些什么库：
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
创建使用数据库
连接正常，于是我们开始干活，建立我们要用的数据库。
使用create database命令可以创建数据库，命令如下：
MariaDB [(none)]> create database prefix;
Query OK, 1 row affected (0.00 sec)
然后我们切换到prefix数据库，通过use命令：
MariaDB [(none)]> use prefix;
Database changed
建表
下面我们创建一张表。这张表叫做issue表，用于保存做代码扫描时发现的问题，主要字段是代码所在的文件名，行号，还有发现问题的内容字符串。
SQL语句如下：
CREATE TABLE issue(
id integer(16) not null auto_increment,
filename varchar(256) not null,
linenum integer(16) not null,
issuestring varchar(256) not null,
primary key(id)
);
运行结果如下：
MariaDB [prefix]> CREATE TABLE issue(
-> id integer(16) not null auto_increment,
-> filename varchar(256) not null,
-> linenum integer(16) not null,
-> issuestring varchar(256) not null,
-> primary key(id)
-> );
Query OK, 0 rows affected (0.02 sec)
我们插入一条记录测试下：
MariaDB [prefix]> insert into issue (filename,linenum, issuestring) values ('test.java',1,'No @author');
Query OK, 1 row affected (0.00 sec)
再select一下看看刚才插入的结果：
MariaDB [prefix]> select * from issue;
+----+-----------+---------+-------------+
| id | filename | linenum | issuestring |
+----+-----------+---------+-------------+
| 1 | test.java | 1 | No @author |
+----+-----------+---------+-------------+
1 row in set (0.00 sec)
创建用户
直接使用root用户访问数据库是件风险很高的事情。我们创建有只读权限的用户来访问数据库就好了。
我们通过create user命令来创建用户，例如就叫prefix:
create user 'prefix'@'localhost' identified by 'AliOS123';
下面我们给这个用户授予select, insert, update, delete的权限：
grant select,update,delete on *.* to 'prefix'@'localhost';
创建是否成功，我们测试一下，使用prefix用户来登录mysql：
mysql -u prefix -p
密码使用刚才设置的AliOS123。
我们试验下是否可以使用prefix数据库，并且可以选择issue表的内容：
MariaDB [(none)]> use prefix;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [prefix]> select * from issue;
+----+-----------+---------+-------------+
| id | filename | linenum | issuestring |
+----+-----------+---------+-------------+
| 1 | test.java | 1 | No @author |
+----+-----------+---------+-------------+
1 row in set (0.00 sec)
使用JDBC Template访问数据库
数据库配置
访问数据库，首先我们需要进行一些配置。配置文件我们放在src/main/resources目录下，名字叫application.properties，写库名，用户名，密码这三项就好：
spring.datasource.url=jdbc:mysql://localhost:3306/prefix
spring.datasource.username=prefix
spring.datasource.password=AliOS123
引入JDBC和MySQL的库依赖
我们还是修改pom.xml引入对JDBC和MySQL的库的依赖：
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
现在的完整pom.xml是这样的：
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>cn.alios.system.service.prefix</groupId>
<artifactId>Prefix</artifactId>
<version>1.0.0-SNAPSHOT</version>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.1.2.RELEASE</version>
</parent>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-aop</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
POJO类
下面我们开始写Java代码。首先为了保存数据库中读来的值，我们需要定义一个Java对象。这个对象不继承任何复杂对象，不实现任何接口，所以一般称为POJO(Plain Ordinary Java Object)对象。
我们的Issue类，只要id, filename, linenum, issuestring四个属性就好：
private Long id;
private String filename;
private Long lineNum;
private String issueString;
完整的类是再加上自动生成的getter和setter方法：
package cn.alios.system.service.prefix.pojo;
public class Issue {
private Long id;
private String filename;
private Long lineNum;
private String issueString;
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getFilename() {
return filename;
}
public void setFilename(String filename) {
this.filename = filename;
}
public Long getLineNum() {
return lineNum;
}
public void setLineNum(Long lineNum) {
this.lineNum = lineNum;
}
public String getIssueString() {
return issueString;
}
public void setIssueString(String issueString) {
this.issueString = issueString;
}
}
服务类
有个POJO类保存结果之后，我们来写一个根据id来查询的简单功能。这个名字可以叫做getIssueById，我们就简称getIssue吧：
package cn.alios.system.service.prefix.service;
import cn.alios.system.service.prefix.pojo.Issue;
public interface JdbcTemplateIssueService {
public Issue getIssue(Long id);
}
如何实现这个功能呢？这时候JDBCTemplate最省事，直接写SQL语句，调用JDBCTemplate的queryForObject函数，如下：
@Override
public Issue getIssue(Long id) {
String sql = "select id, filename, linenum, issuestring from issue where id = ?;";
Object[] params = new Object[]{id};
Issue issue = jdbcTemplate.queryForObject(sql, params, getIssueMapper());
return issue;
}
getIssueMapper负责将字段跟Issue对象的各个字段关联起来：
//映射关系
private RowMapper<Issue> getIssueMapper() {
RowMapper<Issue> issueRowMapper = (ResultSet rs, int rownum) -> {
Issue issue = new Issue();
issue.setId(rs.getLong("id"));
issue.setFilename(rs.getString("filename"));
issue.setLineNum(rs.getLong("linenum"));
issue.setIssueString(rs.getString("issuestring"));
return issue;
};
return issueRowMapper;
}
下面是完整的Service类。其中的@Autowired注解是Spring Boot会为我们自动注入JdbcTemplate对象。
package cn.alios.system.service.prefix.service;
import cn.alios.system.service.prefix.pojo.Issue;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;
import java.sql.ResultSet;
@Service
public class JdbcTemplateIssueServiceImpl implements JdbcTemplateIssueService {
@Autowired
private JdbcTemplate jdbcTemplate = null;
/*
表结构：
+----+-----------+---------+-------------+
| id | filename | linenum | issuestring |
+----+-----------+---------+-------------+
*/
//映射关系
private RowMapper<Issue> getIssueMapper() {
RowMapper<Issue> issueRowMapper = (ResultSet rs, int rownum) -> {
Issue issue = new Issue();
issue.setId(rs.getLong("id"));
issue.setFilename(rs.getString("filename"));
issue.setLineNum(rs.getLong("linenum"));
issue.setIssueString(rs.getString("issuestring"));
return issue;
};
return issueRowMapper;
}
@Override
public Issue getIssue(Long id) {
String sql = "select id, filename, linenum, issuestring from issue where id = ?;";
Object[] params = new Object[]{id};
Issue issue = jdbcTemplate.queryForObject(sql, params, getIssueMapper());
return issue;
}
}
将Service注入给Controller
JdbcTemplate是Spring容器帮我们注入的，现在我们再用@Autowired把Service注入给Controller，然后Controller就可以调用Service来查询数据库了：
package cn.alios.system.service.prefix.controller;
import cn.alios.system.service.prefix.pojo.Issue;
import cn.alios.system.service.prefix.service.JdbcTemplateIssueServiceImpl;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
@Controller
@RequestMapping("/test")
public class TestController {
@Autowired
JdbcTemplateIssueServiceImpl jdbcTemplateIssueService = null;
@RequestMapping("/")
@ResponseBody
public String test() {
Issue issue = jdbcTemplateIssueService.getIssue((long) 1);
if (issue != null) {
return issue.getFilename() + "," + issue.getIssueString();
} else {
return "Test Controller!";
}
}
}
到这里，一次对于数据库的完整访问流程就走通了。
我们试验一下效果：
mvn package
java -jar target/Prefix-1.0.0-SNAPSHOT.jar
然后在浏览器中查看：http://127.0.01:8080/test/
输出值为：test.java,No @author
小结
使用JdbcTemplate编程，主要靠直接写SQL语句，然后调用JdbcTemplate的接口去执行SQL语句，并处理返回值。
