MySQL Java Connector
Java Connector
MySQL provides connectivity for Java client applications with MySQL Connector/J, a driver that implements the Java Database Connectivity (JDBC) API. The API is the industry standard for database-independent connectivity between the Java programming language and a wide range of SQL databases, spreadsheets etc. The JDBC API can do the following things :
- Establish a connection with a database or access any tabular data source.
- Send SQL statements.
- Retrieve and process the results received from the database.
In the following section, we have discussed how to install, configure, and develop database applications using MySQL Connector/J (JDBC driver).
MySQL Connector/J version :
Connector/J version |
JDBC version |
MySQL Server version |
Status |
---|---|---|---|
5.1 | 3.0, 4.0 | 4.1, 5.0, 5.1, 5.5, 5.6, 5.7 | Recommended version |
5.0 | 3.0 | 4.1, 5.0 | Released version |
3.1 | 3.0 | 4.1, 5.0 | Obsolete |
3.0 | 3.0 | 3.x, 4.1 | Obsolete |
Download Connector/J :
MySQL Connector/J is the official JDBC driver for MySQL. You can download the latest version of MySQL Connector/J binary or source distribution from the following web site -
http://dev.MySQL.com/downloads/connector/j/
For Platform Independent select any one from the following :
For Microsoft Windows :
Installation
You can install the Connector/J package drivers using either the binary, binary installation or source installation. The binary method is easy which is a bundle of necessary libraries and other files pre-built, with an installer program. The source installation method is important where you want to customize or modifies the installation process or for those platforms where a binary installation package is not available. Apart from that solution, you manually add the Connector/J location to your Java classpath.
MySQL Connector/J is distributed as a .zip or .tar.gz archive containing the sources, the class files. After extracting the distribution archive, you can install the driver by placing MySQL-connector-java-version-bin.jar in your classpath, either by adding the full path to it to your classpath environment variable or by directly specifying it with the command line switch -cp when starting the JVM.
You can set the classpath environment variable under Unix, Linux or Mac OS X either locally for a user within their .profile, .login or another login file. You can also set it globally by editing the global /etc/profile file.
For example add the Connector/J driver to your classpath using one of the following forms, depending on your command shell :
# Bourne-compatible shell (sh, ksh, bash, zsh): shell> export CLASSPATH=/path/MySQL-connector-java-ver-bin.jar:$CLASSPATH # C shell(csh, tcsh): shell> setenv CLASSPATH /path/MySQL-connector-java-ver-bin.jar:$CLASSPATH
In Windows 2000, Windows XP, Windows Server 2003 and Windows Vista, you can set the environment variable through the System Control Panel.
Install Java Connector on Microsoft Windows
Select and download the MSI installer packages from http://dev.MySQL.com/downloads/connector/j/ as per your requirement.
Now follow the following steps :
Step -1 :
Double click the installer (here it is "MySQL-connector-java-gpl-5.1.31.msi")
Step -2 :
Click on 'Run' and complete the process.
Connecting to MySQL using MySQL Connector/J
The following example shows how to connect/terminate and handle errors. (Java version 7 Update 25 (build 1.7.0_25-b16))
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class test
{
public static void main (String[] args)
{
System.out.println("\n\n***** MySQL JDBC Connection Testing *****");
Connection conn = null;
try
{
Class.forName ("com.MySQL.jdbc.Driver").newInstance ();
String userName = "root";
String password = "pqrs123";
String url = "jdbc:MySQL://localhost/sakila";
conn = DriverManager.getConnection (url, userName, password);
System.out.println ("\nDatabase Connection Established...");
}
catch (Exception ex)
{
System.err.println ("Cannot connect to database server");
ex.printStackTrace();
}
finally
{
if (conn != null)
{
try
{
System.out.println("\n***** Let terminate the Connection *****");
conn.close ();
System.out.println ("\nDatabase connection terminated...");
}
catch (Exception ex)
{
System.out.println ("Error in connection termination!");
}
}
}
}
}
Explanation:
To create a java jdbc connection to the database, you must import the following java.sql package.
- import java.sql.Connection;
- import java.sql.DriverManager;
- import java.sql.SQLException;
Now we will make a class named 'test' and then the main method.
To create a connection to a database, the code is :
conn = DriverManager.getConnection (url, userName, password);
The JDBC DriverManager class defines objects which can connect Java applications to a JDBC driver. DriverManager is the backbone of the JDBC architecture. The DriverManager has a method called getConnection(). The method uses a jdbc url, username and a password to establish a connection to the database and returns a connection object. We have used the following url, username and password in the above code.
- The url string is "jdbc:MySQL://localhost/sakila" where the first part "jdbc:MySQL://localhost" is the database type (here it is MySQL) and server (here it is localhost). Rest part is the database name (here it is 'sakila').
- The user name for MySQL is defined inside the variable 'userName'.
- The password for MySQL is defined inside the variable 'password'.
The DriverManager attempts to connect to the database, if the connection is successful, a Connection object is created, (here it is called 'conn') and the program will display a message "Database Connection Established..."
If the connection fails (e.g. supplying wong password, host address etc.) then you need to handle the situation. We are trapping the error in catch part of the try … catch statement with appropriate messages and finally close the connection.
Compile and Run it
Assume that 'test.java' is stored in E:\ and 'MySQL-connector-java-5.1.31-bin.jar' is stored in "C:\Program Files\MySQL\MySQL Connector J\".
Note : The class path is the path that the Java Runtime Environment (JRE) searches for classes and other resource files. You can change the class path by using the -classpath or -cp option of some Java commands when you call the JVM or other JDK tools or by using the classpath environment variable.
Querying data using MySQL Connector/J
Suppose we want to get the names (first_name, last_name), salary of the employees who earn more than the average salary and who works in any of the IT departments.
Structure of 'hr' database :
Sample table : employees
SQL Code :
SELECT first_name, last_name, salary
FROM employees
WHERE department_id IN
(SELECT department_id FROM departments WHERE department_name LIKE 'IT%')
AND salary > (SELECT avg(salary) FROM employees);
Here is the Java Code (version 7 Update 25 (build 1.7.0_25-b16))
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;
public class testsql
{
public static void main (String[] args)
{
Connection conn = null;
try
{
Class.forName ("com.MySQL.jdbc.Driver").newInstance ();
String userName = "root";
String password = "datasoft123";
String url = "jdbc:MySQL://localhost/hr";
conn = DriverManager.getConnection (url, userName, password);
// Run SQL -> start from here
Statement stmt = null;
ResultSet rs = null;
try {
stmt = conn.createStatement();
rs = stmt.executeQuery("SELECT first_name, last_name, salary FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE department_name LIKE 'IT%') AND salary > (SELECT avg(salary) FROM employees)");
// Extract data from result set
System.out.println ("\n-------------SQL DATA-------------\n");
while(rs.next()){
//Retrieve by column name
String fname = rs.getString("first_name");
String lname = rs.getString("last_name");
int salary = rs.getInt("Salary");
//Display values
System.out.print("Name " + fname+' '+lname);
System.out.print(",Salary: " + salary);
}
System.out.println ("\n\n-------------END-------------\n");
}
catch (SQLException ex){
// handle any errors
System.out.println("SQLException: " + ex.getMessage());
System.out.println("SQLState: " + ex.getSQLState());
System.out.println("VendorError: " + ex.getErrorCode());
}
finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException sqlEx) { } // ignore
rs = null;
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException sqlEx) { } // ignore
stmt = null;
}
}
// SQL end at here
}
catch (Exception ex)
{
System.err.println ("Cannot connect to database server");
ex.printStackTrace();
}
finally
{
if (conn != null)
{
try
{
///System.out.println("\n***** Let terminate the Connection *****");
conn.close ();
// System.out.println ("\n\nDatabase connection terminated...");
}
catch (Exception ex)
{
System.out.println ("Error in connection termination!");
}
}
}
}
}
Compile and Run it
Assume that 'testsql.java' is stored in E:\ and 'MySQL-connector-java-5.1.31-bin.jar' is stored in "C:\Program Files\MySQL\MySQL Connector J\".
Note : The class path is the path that the Java Runtime Environment (JRE) searches for classes and other resource files. You can change the class path by using the -classpath or -cp option of some Java commands when you call the JVM or other JDK tools or by using the classpath environment variable.
Previous: MySQL Python Connector
Next: MySQL Storage Engines (table types)
It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.
https://www.w3resource.com/mysql/mysql-java-connection.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics