全民學後端快餐教程(2)-連線資料庫

lusing發表於2019-02-01
後端資料庫

全民學後端快餐教程(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語句，並處理返回值。


相關文章