How to Connect to MySQL via JDBC
MySQL provides a driver that enable users to make JDBC connections to MySQL databases. This driver is called the MySQL Connector/J JDBC driver. The MariaDB fork of the MySQL database also provides a MySQL compatible driver called the MariaDB Connector / J JDBC driver. Note that as of this writing, the MariaDB driver does not support the new default authorization mechanism in MySQL version 8. If using MySQL version 8 or newer, the MySQL Connector/J driver is recommended over the MariaDB driver.
Both the MySQL Connector/J and MariaDB Connector/J drivers require no software other than the driver jar file. These drivers connect to MySQL databases via TCP/IP. Because of that, the MySQL database server needs to be configured to allow TCP/IP connections. This is true even if the MySQL database is running on the same local server as the client.
Both the MySQL and MariaDB JDBC drivers can be downloaded from their respective web sites. Once the driver jar file is downloaded and included the the classpath of the Java program, a connection can be made to the database. In order for the Java program to load the driver, the program needs to know the main entry class of the driver. Listed below are the classes to use for the MySQL and MariaDB drivers:
MySQL Connector/J Java Class:
For newer MySQL drivers:
com.mysql.cj.jdbc.Driver
For older MySQL drivers:
com.mysql.jdbc.Driver
MariaDB Connector/J Java Class:
org.mariadb.jdbc.Driver
The next step in creating the connection is to construct the JDBC URL. Listed below are some example JDBC URL formats. Both the MariaDB and MySQL drivers use the same base JDBC URL formats.
MySQL Connector/J JDBC Driver Formats
Example JDBC URL with host, port, and database name:
jdbc:mysql://localhost:3306/stocks
The MySQL JDBC driver provides properties that can be appended to the JDBC URL to force certain behavior. Listed below are some of the most common properties, and information on how to format the JDBC URL to include the properties.
MySQL Connector/J Common JDBC Properties
- autoReconnect: Set to true to tell the driver to re-establish dead or stale connections
- useSSL: Set to true to connect using SSL. Requires an SSL enabled MySQL database
- verifyServerCertificate: When connecting via SSL, set to false to skip certificate verification. Useful if connecting to development environments via self-signed certificates.
- useCompression: Set to true to use zlib compression when communicating with the MySQL server
Example JDBC URL with host, port, database name, and connection properties
jdbc:mysql://localhost:3306/sample?useSSL=true&autoReconnect=true
MariaDB Connector/J JDBC Driver Formats
Example JDBC URL with host, port, and database name:
jdbc:mysql://localhost:3306/sample
The MariaDB JDBC driver provides properties that can be appended to the JDBC URL to force certain behavior. Listed below are some of the most common properties, and information on how to format the JDBC URL to include the properties.
MariaDB Connector/J Common JDBC Properties
- autoReconnect: Set to true to tell the driver to re-establish dead or stale connections
- useSSL: Set to true to connect using SSL. Requires an SSL enabled MariaDB database
- trustServerCertificate: When connecting via SSL, set to true to skip certificate verification. Useful if connecting to development environments via self-signed certificates.
- useCompression: Set to true to use zlib compression when communicating with the MariaDB server
Example JDBC URL with host, port, database name, and connection properties
jdbc:mysql://localhost:3306/sample?useSSL=true&autoReconnect=true
Sample Code for Making a JDBC Connection
Below is sample Java code using JDBC to access a MySQL database.
Class dbDriver = Class.forName("com.mysql.jdbc.Driver");
String jdbcURL = "jdbc:mysql://localhost:3306/sample?useSSL=true&autoReconnect=true";
Connection connection = DriverManager.getConnection(jdbcURL, "user", "password");
Statement statement = connection.createStatement();
ResultSet rs = statement.executeQuery("select * from employee");
while(rs.next())
{
System.out.println("name = " + rs.getString("name"));
System.out.println("id = " + rs.getInt("id"));
}
.
.
.
Microsoft SQL Server: How to Connect to MS SQL Server via JDBC
Oracle: How to Connect to Oracle via JDBC
PostgreSQL: How to Connect to PostgreSQL via JDBC
Redshift: How to Connect to Redshift via JDBC
SQLite: How to Connect to SQLite via JDBC
