MySQL Java Tutorial Base
In this tutorial, we use the MySQL Connector/J driver. It is the official JDBC driver for MySQL. The examples were created and tested on Ubuntu Linux. You might also want to check Java tutorial, PostgreSQL Java tutorial, MySQL tutorial, or Spring JdbcTemplate tutorial on ZetCode.
JDBC
JDBC is an API for the Java programming language that defines how a client may access a database. It provides methods for querying and updating data in a database. JDBC is oriented towards relational databases. From a technical point of view, the API is as a set of classes in the java.sql package. To use JDBC with a particular database, we need a JDBC driver for that database.
JDBC is a cornerstone for database programming in Java. Today, it is considered to be very low-level and prone to errors. Solutions such as MyBatis or JdbcTemplate were created to ease the burden of JDBC programming. However, under the hood, these solutions still use JDBC. JDBC is part of the Java Standard Edition platform.
JDBC manages these three main programming activities:
- connecting to a database;
- sending queries and update statements to the database;
- retrieving and processing the results received from the database in answer to the query.
MySQL Connector/J
To connect to MySQL in Java, MySQL provides MySQL Connector/J, a driver that implements the JDBC API. MySQL Connector/J is a JDBC Type 4 driver. The Type 4 designation means that the driver is a pure Java implementation of the MySQL protocol and does not rely on the MySQL client libraries. In this tutorial, we use MySQL Connector/J 5.1.41, which is a maintenance release of the 5.1 production branch.
为了使用Java连接到MySQL，MySQL提供了MySQL Connector/J，它是实现JDBC API的驱动程序。MySQL Connector/J是JDBC Type 4驱动程序。Type 4表示驱动程序是MySQL协议的纯Java实现，并且不依赖MySQL客户端库。在本教程中，我们使用MySQL Connector/J 5.1.41，它是5.1生产分支的维护版本
Connection string
A database connection is defined with a connection string. It contains information such as database type, database name, server name, and port number. It also may contain additional key/value pairs for configuration. Each database has its own connection string format.
The following is a syntax of a MySQL connection string:
jdbc:mysql://[host1][:port1][,[host2][:port2]]...[/[database]]
[?propertyName1=propertyValue1[&propertyName2=propertyValue2]...]
It is possible to specify multiple hosts for a server failover setup. The items in square brackets are optional. If no host is specified, the host name defaults to localhost. If the port for a host is not specified, it defaults to 3306, the default port number for MySQL servers.
jdbc:mysql://localhost:3306/testdb?useSSL=false
This is an example of a MySQL connection string. The jdbc:mysql:// is known as a sub-protocol and is constant for MySQL. We connect to the localhost on MySQL standard port 3306. The database name is testdb. The additional key/value pairs follow the question mark character (?). The useSSL=false tells MySQL that there will be no secure connection.
这是一个MySQL连接字符串的示例。jdbc:mysql://被称为子协议，对于MySQL来说是常量。我们在MySQL标准端口3306上连接到本地主机。数据库名称为testdb。其他键/值对在问号字符（？）之后。useSSL = false告诉MySQL将没有安全连接
About MySQL database
MySQL is a leading open source database management system. It is a multi user, multithreaded database management system. MySQL is especially popular on the web. It is one part of the very popular LAMP platform consisting of Linux, Apache, MySQL, and PHP. Currently MySQL is owned by Oracle. MySQL database is available on most important OS platforms. It runs on BSD Unix, Linux, Windows, or Mac OS. Wikipedia and YouTube use MySQL. These sites manage millions of queries each day. MySQL comes in two versions: MySQL server system and MySQL embedded system.
MySQL是领先的开源数据库管理系统。它是一个多用户，多线程的数据库管理系统。MySQL有两个版本：MySQL服务器系统和MySQL嵌入式系统
Maven file
We use the following Maven file:
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>com.zetcode</groupId>
<artifactId>version</artifactId>
<version>1.0-SNAPSHOT</version>
<packaging>jar</packaging>
<name>version</name>
<description>Demo project for spring boot</description>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<maven.compiler.source>1.8</maven.compiler.source>
<maven.compiler.target>1.8</maven.compiler.target>
</properties>
<dependencies>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.20</version>
<scope>runtime</scope>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.codehaus.mojo</groupId>
<artifactId>exec-maven-plugin</artifactId>
<version>3.0.0</version>
<configuration>
<mainClass>com.zetcode.version</mainClass>
<cleanupDaemonThreads>false</cleanupDaemonThreads>
</configuration>
</plugin>
</plugins>
</build>
</project>
The POM file has a dependency for the MySQL driver. We also include the exec-maven-plugin for executing Java programs from Maven. Between the <mainClass></mainClass> tags we provide the full name of the application.
POM文件具有MySQL驱动程序的依赖关系。我们还包括exec-maven-plugin，用于从Maven执行Java程序。在
Java MySQL version
If the following program runs OK, then we have everything installed OK. We check the version of the MySQL server.
检查MySQL服务器的版本
JdbcMySQLVersion.java
package com.zetcode.version;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
public class JdbcMySQLVersion {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/app_messages?sslMode=DISABLED&serverTimezone=UTC&useLegacyDatetimeCode=false";
String username = "root";
String password = "MyNewPass4!";
String query = "SELECT VERSION()";
try (Connection con = DriverManager.getConnection(url, username, password);
Statement st = con.createStatement();
ResultSet rs = st.executeQuery(query)) {
if (rs.next()) {
System.out.println(rs.getString(1));
}
} catch (SQLException ex) {
Logger lgr = Logger.getLogger(JdbcMySQLVersion.class.getName());
lgr.log(Level.SEVERE, ex.getMessage(), ex);
}
}
}
We connect to the database and get some info about the MySQL server.
String url = "jdbc:mysql://localhost:3306/app_messages?sslMode=DISABLED&serverTimezone=UTC&useLegacyDatetimeCode=false";
This is the connection URL for the MySQL database. Each driver has a different syntax for the URL. In our case, we provide a host, a port, and a database name.
这是MySQL数据库的连接URL。每个驱动程序对于URL都有不同的语法。在例子中提供一个主机，一个端口和一个数据库名称
try (Connection con = DriverManager.getConnection(url, user, password);
Statement st = con.createStatement();
ResultSet rs = st.executeQuery(query)) {
We establish a connection to the database, using the connection URL, user name, and password. The connection is established with the getConnection() method.
使用连接URL，用户名和密码建立与数据库的连接。使用getConnection()方法建立连接
The createStatement() method of the connection object creates a Statement object for sending SQL statements to the database.
连接对象的createStatement()方法创建一个Statement对象，用于将SQL语句发送到数据库
The executeQuery() method of the connection object executes the given SQL statement, which returns a single ResultSet object. The ResultSet is a table of data returned by a specific SQL statement.
连接对象的executeQuery()方法执行给定的SQL语句，该语句返回单个ResultSet对象。ResultSet是特定SQL语句返回的数据表。
The try-with-resources syntax ensures that the resources are cleaned up in the end.
try-with-resources语法可确保最终清除资源
if (result.next()) {
System.out.println(result.getString(1));
}
A ResultSet object maintains a cursor pointing to its current row of data. Initially the cursor is positioned before the first row. The next() method moves the cursor to the next row. If there are no rows left, the method returns false. The getString() method retrieves the value of a specified column. The first column has index 1.
ResultSet对象维护一个游标，该游标指向其当前数据行。最初，光标位于第一行之前。next()方法将光标移动到下一行。如果没有剩余的行，则该方法返回false。getString()方法检索指定列的值。第一列的索引为1
} catch (SQLException ex) {
Logger lgr = Logger.getLogger(JdbcMySQLVersion.class.getName());
lgr.log(Level.SEVERE, ex.getMessage(), ex);
}
In case of an exception, we log the error message. For this console example, the message is displayed in the terminal.
如果发生异常，我们将记录错误消息。对于此控制台示例，该消息显示在终端中
mvn exec:java -q
We run the program from the command line. The Manen's -q option runs Maven in quiet mode; i.e. we only see error messages.
我们从命令行运行程序。Manen的-q选项在安静模式下运行Maven。即我们只看到错误消息
