connect to remote mysql database by the java

ava Database Connectivity (JDBC) is a Java-based data access technology that defines how a client may access a database. It provides methods for querying and updating data in a database. The JDBC classes are contained in the Java package java.sql and javax.sql.

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.

You may also like:

1. Eclipse DTP: Configure MySQL Datasource using Data Source Explorer
2. Sample Databases used in this MySQL tutorial
3. Servlet JNDI DataSource in Tomcat
4. Servlet DataSource Resource Injection in Tomcat
5. Eclipse DTP: Configure H2 Datasource using Data Source Explorer

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