HomeCore Java

JDBC : Accessing MySQL database using JDBC

Like Tweet Pin it Share Share Email

In this post I will explain the steps required to connect to MySQL database from Java using JDBC. We will do one JDBC example, connect mysql database using JDBC.

Introduction : What is JDBC?

Java Database Connectivity (JDBC) is an API that lets you access any tabular data sources from a Java application. These data sources can include 1. SQL-based databases (Oracle, MySql etc.), 2. data stored in spreadsheets or 3. flat files. Most commercial databases now support JDBC by means of a JDBC driver. A Java application can use different JDBC drivers, all of which expose the same interface to the developers. This enables developers to plug different databases into their Java applications.

Using JDBC API developers can connect to a database, execute SQL statements on the database, and manipulate any data that is returned as a result of the execution of a database statement. The JDBC driver manages all of this for a specific database and also supports the pooling of database resources such as the connections.

In the below given example we will see how can we connect a database using Java programming.

Prerequisites for doing the JDBC examples in this article

Before starting examples in this tutorial. Please make sure you have these things ready:

1. Install MySql : Please ensure you have MySQL installed and running either in your system or a system to which you are connected. In case you want to install MySql from scratch. Please visit Installing MySQL on Windows 7.  

I liked this article available on about.com website. They have mentioned step by step with images.

In case you are not able to install please drop a comment in comment section. I will help you.

2. Obtain JDBC Driver:

In order to connect to MySQL from Java we need to use JDBC Driver for MySQL. Connector/J is the JDBC driver for MySQL. Please download Connector/j from here. You can select your platform and can download it. Once download is complete, unzip the download to a folder. The folder into which you extracted .zip file contains a folder called mysql-connector-java-5.1.15.  It contains mysql-connector-java-5.1.15.bin.jar (JDBC driver) and docs folder that provides information regarding how to use Connector/j.

You can also download Connector/j from google coders website.

3. Install MySQL Workbench:

MySQL Workbench is a GUI tool that allows you to work with MySQL. In the step 1 when you have downloaded MSI Installer version to of MySql installer, please select MySql Workbench option.  So at the time of installing MySql it will also be installed.

You need to download it from here.

Please choose your platform and the kind of download you want. Prefer MSI Installer version. After download is complete, install it and connect to database using it.

JDBC Program to Connect to MySQL


package com.sitenol.testDB;
import java.sql.Connection;
import java.sql.DriverManager;
public class JDBCConnection {
public static void main(String[] args) {
try {
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager
.getConnection("jdbc:mysql://localhost/testDB?user=root&password=mysql");
System.out.println("Connection Object Created : " + con);
con.close();
} catch (Exception ex) {
ex.printStackTrace();
}
}
}

Output : JDBC Program to Connect to MySQL

Connection Object Created : com.mysql.jdbc.JDBC4Connection@578088c0

In the above example I have shown you – how to connect MySql database using JDBC from Java. So you are able to connect to MySql database. Now I am going to explain you – how to retrieve records from a database using JDBC.

JDBC Program to fetch and show records from a table

Once you connected to MySql or any database you are operating. You can use JDBC API to do manipulation in the data tables.
Let us see this example to fetch records from the MySql data table using JDBC.

Sql queries for this example :


mysql> create table student (student_id int(10) primary key, student_name varchar(80));
Query OK, 0 rows affected (0.44 sec)
mysql> INSERT INTO student (student_id, student_name)
-> VALUES
-> (100, 'Peter Brayant'),
-> (101, 'Ravi Shankar'),
-> (104, 'Rajesh'),
-> (105, 'Abhishek');
Query OK, 4 rows affected (0.03 sec)
Records: 4 Duplicates: 0 Warnings: 0

JDBC Example to fetch and show records from Table:


package com.sitenol.testDB;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class StuedentList {
public static void main(String[] args) {
try {
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager
.getConnection("jdbc:mysql://localhost/testDB?user=root&password=mysql");
Statement st = con.createStatement();
ResultSet rs = st.executeQuery("select * from student");
while (rs.next()) {
System.out.println(rs.getString("student_name"));
}
st.close();
rs.close();
con.close();
} catch (Exception ex) {
ex.printStackTrace();
}
}
}

Output : JDBC Program to fetch and show records from a table


Peter Brayant
Ravi Shankar
Rajesh
Abhishek

In the next step I am explaining the code line by line.
Workspace Image for this example
Please see the workspace image for this example.

learn jdbc java, jdbc java mysql example
jdbc java mysql example

Understanding above examples together  1. Connect to MySql Data Base using JDBC and 2. Fetching records from Datatable :
This section will explain you “How will  the above given JDBC examples work?”.

In the next tutorial I will write one post to show “Using JDBC in JSP and Servlets with example?”. Link will be provided here once I will complete the article.

Using JDBC you can connect to a database. To do this, you would create a new JDBC connection using the following statements:

Class.forName(“com.mysql.jdbc.Driver”);
Connection con = DriverManager.getConnection(“jdbc:mysql://localhost/testDB?user=root&password=mysql”);

In the first statement, you are loading the appropriate driver class. In the second, you create the connection to the database by passing in the connection URL, which includes the driver name (jdbc), the database name (testDB), and the username and password for the database. For me user name is “root” and password is “mysql”.

The next step is to create a statement that will contain the SQL that you want to execute on the database. This command looks like

Statement stmt = con.createStatement() ;

As soon as you have created the statement, you can execute the statement in different ways using appropriate SQL statements. You would create a string with this SQL statement:

String sqlString = “select * from customer”;

Now you can execute the SQL statement by using either of the following commands as appropriate:

You would use the executeUpdate database in conjunction with an insert or update SQL statement.

stmt.executeUpdate(sqlString);

You would use the executeQuery statement in conjunction with a select SQL statement.

ResultSet rs = stmt.executeQuery(sqlString);

After using the results of your query, the final step is to release the connection:

con.close();

Five JDBC classes used in the above example

java.sql.DriverManager: This class serves as a factory for creating Connection objects.
java.sql.Connection: Connection fosters the database connection and serves as a factory for creating Statement objects.
java.sql.Statement: It represents the SQL statement to be executed by the database.
java.sql.ResultSet: It represents the rows and fields obtained through a SQL select statement.
java.sql.SQLException: all JDBC operations require this exception to be caught.

Code compilation errors, Problems while working on JDBC :

ClassNotFoundException : This is one of the common problem students, professionals faced.


java.lang.ClassNotFoundException: com.mysql.jdbc.Driver
at java.net.URLClassLoader$1.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at sun.misc.Launcher$AppClassLoader.loadClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at java.lang.Class.forName0(Native Method)
at java.lang.Class.forName(Unknown Source)
at com.sitenol.testDB.StuedentList.main(StuedentList.java:12)

Solutions for ClassNotFoundException: mysql-connector-java-5.1.15-bin.jar is missing. Please read Obtain JDBC Driver  in this article.

Thanks for reading how to access MySQL using JDBC from Java. Please give your comments for any suggestion and doubts, programming erros, compilation problems.

Comments (0)

Leave a Reply

Your email address will not be published. Required fields are marked *