YouTipJava Mysql Connect
# Java MySQL Connection
In this chapter, we will introduce how Java uses JDBC to connect to a MySQL database.
Connecting Java to MySQL requires a driver package. The latest version can be downloaded from: **[http://dev.mysql.com/downloads/connector/j/](http://dev.mysql.com/downloads/connector/j/)**. After unzipping, you will get the JAR library file, which you then import into your project.
!(#)
You can download the JAR package provided by this site: **[mysql-connector-java-5.1.39-bin.jar](
This example uses Eclipse. Import the JAR package:
!(#)
> **Note for MySQL versions above 8.0:**
>
>
> * 1. The driver package version for MySQL 8.0 and above is [mysql-connector-java-8.0.16.jar](https://static.jyshare.com/download/mysql-connector-java-8.0.16.jar).
>
> * 2. **com.mysql.jdbc.Driver** has been replaced with com.mysql.cj.jdbc.Driver.
>
> * 3. SSL connection is not required for MySQL 8.0 and above, but it needs to be explicitly disabled.
>
> * 4. `allowPublicKeyRetrieval=true` allows the client to retrieve the public key from the server.
>
> * 5. Finally, you also need to set the server timezone (e.g., `serverTimezone=UTC`).
>
>
>
> The way to load the driver and connect to the database is as follows:
>
> ```java
> Class.forName("com.mysql.cj.jdbc.Driver");
> conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test_demo?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC","root","password");
> ```
* * *
## Create Test Data
Next, we will create a database named `TUTORIAL` in MySQL and create a `websites` table with the following structure:
```sql
CREATE TABLE `websites` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` char(20) NOT NULL DEFAULT '' COMMENT 'Site Name',
`url` varchar(255) NOT NULL DEFAULT '',
`alexa` int(11) NOT NULL DEFAULT '0' COMMENT 'Alexa Ranking',
`country` char(10) NOT NULL DEFAULT '' COMMENT 'Country',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
Insert some data:
```sql
INSERT INTO `websites` VALUES
('1', 'Google', 'https://www.google.cm/', '1', 'USA'),
('2', 'Taobao', 'https://www.taobao.com/', '13', 'CN'),
('3', 'Tutorial', '', '5892', ''),
('4', 'Weibo', 'http://weibo.com/', '20', 'CN'),
('5', 'Facebook', 'https://www.facebook.com/', '3', 'USA');
The data table is displayed as follows:
!(#)
* * *
## Connect to the Database
The following example uses JDBC to connect to a MySQL database. Note that some data like username and password need to be configured according to your development environment.
## MySQLDemo.java File Code:
```java
package com.tutorial.test;
import java.sql.*;
public class MySQLDemo {
// For MySQL versions below 8.0 - JDBC driver name and database URL
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost:3306/TUTORIAL";
// For MySQL versions 8.0 and above - JDBC driver name and database URL
//static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
//static final String DB_URL = "jdbc:mysql://localhost:3306/TUTORIAL?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC";
// Database username and password, need to be set according to your configuration
static final String USER = "root";
static final String PASS = "123456";
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
try {
// Register JDBC driver
Class.forName(JDBC_DRIVER);
// Open connection
System.out.println("Connecting to database...");
conn = DriverManager.getConnection(DB_URL, USER, PASS);
// Execute query
System.out.println("Creating statement...");
stmt = conn.createStatement();
String sql;
sql = "SELECT id, name, url FROM websites";
ResultSet rs = stmt.executeQuery(sql);
// Expand result set database
while (rs.next()) {
// Retrieve by column name
int id = rs.getInt("id");
String name = rs.getString("name");
String url = rs.getString("url");
// Display values
System.out.print("ID: " + id);
System.out.print(", Site Name: " + name);
System.out.print(", Site URL: " + url);
System.out.print("n");
}
// Clean-up environment
rs.close();
stmt.close();
conn.close();
} catch (SQLException se) {
// Handle JDBC errors
se.printStackTrace();
} catch (Exception e) {
// Handle Class.forName errors
e.printStackTrace();
} finally {
// Close resources
try {
if (stmt != null)
stmt.close();
} catch (SQLException se2) {
} // nothing we can do
try {
if (conn != null)
conn.close();
} catch (SQLException se) {
se.printStackTrace();
}
}
System.out.println("Goodbye!");
}
}
The output of the above example is as follows:
!(#)