Java 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. Show
Follow the steps to setup a JDBC development environment with which you can compile and run JDBC MySQL example. Install JavaMake sure you have Java SE installed in you computer. Refer the links to install Java in Windows, Ubuntu. Download and install Eclipse IDEYou 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 databaseThis MySQL JDBC example requires MySQL database to be installed. Refer this link to install MySQL.
Java MySQL ConnectorJDBC 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 under Configure JDBC Driver in Eclipse) otherwise we will get some class-not-found-exceptions indicating that the driver was not found on the classpath.
JDBC MySQL Sample DatabaseThis JDBC MySQL example uses a sample database “jdbcdb” which contains the following table;
Department Table:
Employee Table:
Create a Java project in Eclipse IDE
JDBC MySQL Connection String URLWe 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 packageYou need to import required classes/interfaces from java.sql.* package which acts as a bridge between Java application and database. Load MySQL Java driverThe 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 under Configure JDBC Driver in Eclipse. The statement Class.forName (“com.mysql.jdbc.driver”) loads the MySQL Java driver class in
memory. public static final String DRIVER_CLASS = "com.mysql.jdbc.Driver"; . . . Class.forName(DRIVER_CLASS); Establish Java MySQL connectionWe 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:
Example, for Java MySQL connection string URL:
where,
To complete the above steps, create a new class JDBCMySQLConnection in package com.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())
We write a class “JDBCMySQLDemo” in package “com.theopentutorials.jdbc.main” to test JDBC MySQL connection and execute a simple JDBC SELECT 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.
Configure JDBC driver in Eclipse IDEIf 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. OutputRun JDBCMySQLDemo class to get the output as shown below. JDBC MySQL Application folder structureThe complete folder
structure of this project is shown below. Where do I put MySQL Connector in Eclipse?Configure JDBC driver in Eclipse IDE
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.
How can I download MySQL Connector and configure JDBC with Eclipse IDE?Create and Export MySQL JDBC driver bundle. In the file system extract the MySQL driver JAR from the downloaded MySQL ZIP file to the downloads folder:. In your Eclipse IDE open the New Project wizard and select Plug-in from existing JAR archives:. On the JAR selection page use Add External...:. How do I use MySQL in Eclipse?JDBC With MySQL Connector/J Using Eclipse. Create a new Java project in Eclipse.. Right click on the project folder, select Build Path, and from the submenu select Add External Archives.... In the JAR Selection panel that comes up, navigate to /Users/Shared/mysql-connector-java-5.1.34-bin.jar as shown here and click Open:. How do I install MySQL Java Connector?Downloading and installing MySQL Connector/J. Download the MySQL Connector/J drivers at dev.mysql.com.. Install the . jar file and note its location for future reference. For example, install the . jar file at C:\Program Files\MySQL\MySQL Connector J\mysql-connector-java-5.1. 32-bin. jar.. |