Follow the steps to setup a JDBC development environment with which you can compile and run JDBC MySQL example.
Install Java
Make sure you have Java SE installed in you computer. Refer the links to install Java inWindows, Ubuntu.
Download and install Eclipse IDE
You can code and run a Java program using a simple text editor (such as Notepad) and use command prompt to run the program. Alternatively, you can use any Integrated Development Environment (IDE) (such as Eclipse, NetBeans, etc). We use Eclipse IDE.
Refer this link to install Eclipse IDE.
Download and install MySQL database
This MySQL JDBC example requires MySQL database to be installed. Refer this link to install MySQL.
- This Java database application uses MySQL as sample database. Refer this tutorial on MySQL for creating database and tables, inserting data into tables, etc.
Java MySQL Connector
JDBC API mostly consists of interfaces which work independently of any database. A database specific driver is required for each database which implements the JDBC API.
The JDBC database Connector provides access to the database. To reach the database using JDBC we need a JDBC driver from the database provider in our case – MySQL. This connector is typically delivered with the product in a jar or zip file or available in the provider’s website. These files must be in our classpath (which is explained later underConfigure JDBC Driver in Eclipse) otherwise we will get some class-not-found-exceptions indicating that the driver was not found on the classpath.
- MySQL Connector/J is the official MySQL JDBC driver.
- The Java MySQL connector JAR can be downloaded from: http://dev.mysql.com/downloads/connector/j/. This tutorial uses JDBC MySQL connector 5.1 version. Unzip the connector to a safe location on your computer which contains MySQL Connector/J JAR.
JDBC MySQL Sample Database
This JDBC MySQL example uses a sample database “jdbcdb” which contains the following table;
- Create a database in MySQL and name it as “jdbcdb“.
- Create the following tables inside this database.
Department Table:
Field | Type | Key | Extra |
---|---|---|---|
dept_id | int | Primary Key | auto_increment |
dept_name | varchar(50) | ||
location | varchar(50) |
Employee Table:
Field | Type | Key | Extra |
---|---|---|---|
emp_id | int | Primary Key | auto_increment |
emp_name | varchar(50) | ||
dob | date | ||
salary | double | ||
dept_id | int | Foreign key references department(dept_id) |
Create a Java project in Eclipse IDE
- Open Eclipse IDE.
- Create a new Java Project and name it as “JDBCMySQLSample“. If you are a newbie,refer this link on getting started with Java and Eclipse.
JDBC MySQL Connection String URL
We write a class (JDBCMySQLConnection) defining database connection configuration statements and methods to make JDBC connect to MySQL database.
Following steps are involved in JDBC MySQL connection.
Use Interfaces from java.sql package
You need to import required classes/interfaces from java.sql.* package which acts as a bridge between Java application and database.
Load MySQL Java driver
The Java MySQL driver (com.mysql.jdbc.Driver) is available in the downloaded Java MySQL Connector JAR file.This connector JAR file needs to be included in the client project’s classpath which is explained later underConfigure JDBC Driver in Eclipse.
The statement Class.forName (“com.mysql.jdbc.driver”) loads the MySQL Java driver class in memory.
In the below code, we have created a static final String variable (constant) and passing it as parameter to class.forName as shown below.
public static final String DRIVER_CLASS = "com.mysql.jdbc.Driver";
. . .
Class.forName(DRIVER_CLASS);
Establish Java MySQL connection
We connect to MySQL from Java using DriverManager class by calling DriverManager.getConnection() method.This method requires a JDBC MySQL connection URL string, MySQL database username and password. In this example, we have created these as constant variables and passed it in getConnection() method.
public static final String URL = "jdbc:mysql://localhost/jdbcdb";
public static final String USER = "YOUR_DATABASE_USERNAME";
public static final String PASSWORD = "YOUR_DATABASE_PASSWORD";
. . .
. . .
Connection connection = DriverManager.getConnection(URL, USER, PASSWORD);
Java database connection string URL:
jdbc:<DBMS>://<HOSTNAME>:<PORT_NUMBER>/YOUR_DATABASE_NAME
Example, for Java MySQL connection string URL:
jdbc:mysql://localhost:3306/jdbcdb
where,
- “jdbc:” – Required for all databases
- “mysql” – Any Relational Database. In this case, it is mysql
- localhost - is the name of the server hosting your database
- 3306 is the default port number for MySQL, which can be omitted if not changed to any other number.
- YOUR_DATABASE_NAME is the MySQL database name, in this example it is “jdbcdb”
To complete the above steps, create a new class JDBCMySQLConnection in packagecom.theopentutorials.jdbc.db and copy the following code.
package com.theopentutorials.jdbc.db;
//Step 1: Use interfaces from java.sql package
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class JDBCMySQLConnection {
//static reference to itself
private static JDBCMySQLConnection instance = new JDBCMySQLConnection();
public static final String URL = "jdbc:mysql://localhost/jdbcdb";
public static final String USER = "YOUR_DATABASE_USERNAME";
public static final String PASSWORD = "YOUR_DATABASE_PASSWORD";
public static final String DRIVER_CLASS = "com.mysql.jdbc.Driver";
//private constructor
private JDBCMySQLConnection() {
try {
//Step 2: Load MySQL Java driver
Class.forName(DRIVER_CLASS);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
private Connection createConnection() {
Connection connection = null;
try {
//Step 3: Establish Java MySQL connection
connection = DriverManager.getConnection(URL, USER, PASSWORD);
} catch (SQLException e) {
System.out.println("ERROR: Unable to Connect to Database.");
}
return connection;
}
public static Connection getConnection() {
return instance.createConnection();
}
}
Employee class:
We write a class with properties defining the table attributes. For example, to query the employee table and retrieve employee details, we write a class with following code;
package com.theopentutorials.jdbc.to;
import java.util.Date;
public class Employee {
private int empId;
private String empName;
private Date dob;
private double salary;
private int deptId;
public int getEmpId() {
return empId;
}
public void setEmpId(int empId) {
this.empId = empId;
}
public String getEmpName() {
return empName;
}
public void setEmpName(String empName) {
this.empName = empName;
}
public Date getDob() {
return dob;
}
public void setDob(Date dob) {
this.dob = dob;
}
public double getSalary() {
return salary;
}
public void setSalary(double salary) {
this.salary = salary;
}
public void setDeptId(int deptId) {
this.deptId = deptId;
}
public int getDeptId() {
return deptId;
}
//toString()
@Override
public String toString() {
return "Employee [empId=" + empId + ", empName=" + empName + ", dob="
+ dob + ", salary=" + salary + ", deptId=" + deptId + "]";
}
}
Java Application Client (main())
- An application involving Java with database to process any SQL statement must follow these steps:
- Establish a connection. (This is done by JDBCMySQLConnection class mentioned above)
- Create a Statement object. (Line 41)
- Execute the query. (Line 42)
- Process the ResultSet object. This is required only for SELECT SQL query. (Line 44-51)
- Close the connection. (Line 57)
We write a class “JDBCMySQLDemo” in package “com.theopentutorials.jdbc.main” to test JDBC MySQL connection and execute a simple JDBCSELECT query.
package com.theopentutorials.jdbc.main;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import com.theopentutorials.jdbc.db.DbUtil;
import com.theopentutorials.jdbc.db.JDBCMySQLConnection;
import com.theopentutorials.jdbc.to.Employee;
public class JDBCMySQLDemo {
public static void main(String[] args) {
BufferedReader br = new BufferedReader(new InputStreamReader(System.in));
System.out.println("Enter the EmployeeID:");
int employeeId;
try {
employeeId = Integer.parseInt(br.readLine());
JDBCMySQLDemo demo = new JDBCMySQLDemo();
Employee employee = demo.getEmployee(employeeId);
System.out.println(employee);
} catch (NumberFormatException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
public Employee getEmployee(int employeeId) {
ResultSet rs = null;
Connection connection = null;
Statement statement = null;
Employee employee = null;
String query = "SELECT * FROM employee WHERE emp_id=" + employeeId;
try {
connection = JDBCMySQLConnection.getConnection();
statement = connection.createStatement();
rs = statement.executeQuery(query);
if (rs.next()) {
employee = new Employee();
employee.setEmpId(rs.getInt("emp_id"));
employee.setEmpName(rs.getString("emp_name"));
employee.setDob(rs.getDate("dob"));
employee.setSalary(rs.getDouble("salary"));
employee.setDeptId((rs.getInt("dept_id")));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return employee;
}
}
This JDBC MySQL example gets an employee ID from user, finds this employee in database and prints the details to standard output.
The programs steps are as follows,
- Line 21 gets employee ID as input from user.
- Line 23 calls a method getEmployee() passing the employee ID.
- Line 38 frames the SQL query.
- Line 40 makes Java MySQL connection.
- Line 41 creates statement object from connection.
- Line 42 executes the query and returns ResultSet object.
- Line 44-51 process the ResultSet object.
- Line 57 closes database connection.
Configure JDBC driver in Eclipse IDE
If you run JDBCMySQLDemo class you will get a runtime exception mentioning Driver class not found as shown below
java.lang.ClassNotFoundException: com.mysql.jdbc.Driver
You need to add the downloaded Java MySQL Connector JAR in client project’s classpath. To do this, right click on your Java Project (JDBCMySQLSample) -> Properties -> Buildpath -> Libraries -> Add External JAR and select “mysql-connector-java-5.1.14-bin.jar” JAR file.
Output
Run JDBCMySQLDemo class to get the output as shown below.
JDBC MySQL Application folder structure
The complete folder structure of this project is shown below.
Tags: class.forname com.mysql.jdbc.driver, configure jdbc driver in eclipse ide, java database application example, java mysql connector jar, jdbc connect to mysql, jdbc examples, jdbc mysql connection string, jdbc mysql example, jdbc mysql sample, jdbc mysql tutorial, jdbc select query, jdbc url for mysql