In this tutorial, you are going to learn JDBC connection in Java with MySQL. Before that, lets check some basics of JDBC so that you will understand the code properly.
The Java JDBC API is an API used to connect and execute the query with the database.
But what’s API?
API is the acronym of Application Programming Interface which is mostly a document that describes all the features of the software.
In Java, JDBC refers to Java Database Connectivity, which is generally a typical Java API for establishing connectivity between the Java programming language and an extensive set of databases.
What is the use of JDBC API?
The JDBC API can perform the below-listed tasks:
Table of Contents
The most familiar and frequently used JDBC components include.
Software that facilitates java application to relate with the database is known as JDBC Driver. The varied forms of JDBC drivers are:
It utilizes an ODBC driver to establish a connection with the database. The JDBC-ODBC bridge driver transfers the JDBC method invocations into the ODBC function invocations.
It utilizes client-side libraries to establish a connection with the database. The driver transfers JDBC method invocations into the native function invocations
It utilizes middleware to convert JDBC calls to vendor-specific calls in whichever way possible.
It handles the direct conversion of JDBC method invocations to vendor-specific database protocols is.
After knowing the basic components of Java JDBC and varied kinds of JDBC drivers. Now let us dive into the process of establishing a connection between Java program and database.
There are 5 basic steps involved to connect any Java application with the database using JDBC. These steps are:
The forName() method is exploited to load the driver class and register it. The syntax to register a class using forName() method is :
public static void forName(String <class_name>) throws ClassNotFoundException.
For instance, to load the MySQL driver we write,
Class.forName("com.mysql.jdbc.Driver");
To set up the connection, the getConnection() method of DriverManager class is exploited to connect with the database.
The getConnection() method defines 2 varied syntax, they are:
public static Connection getConnection(String url)throws SQLException
public static Connection getConnection(String url,String name,String password) throws SQLException
For instance, to connect with the MySQL database we write:
Connection c = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","root");
To create a statement, we make use of createStament() method of the Connection interface. The createStatement() is defined as:
public Statement createStatement() throws SQLException
For instance, to create a statement object we write:
Statement s = c.createStatement();
To execute the queries to the database, the executeQuery() method of Statement interface is made use of. This method gives back an object of ResultSet which is used to fetch all the records of a relational table.
The syntax of writing executeQuery() method is:
public ResultSet executeQuery(String sql)throws SQLException
For instance, to execute a query using the executeQuery() method, we write:
ResultSet rs=s.executeQuery("select * from test");
The close() method of the Connection interface is made use of to close the entire connection with database.
The syntax of writing close() method is:
public void close()throws SQLException
For instance, the way to close a particular connection of database is:
c.close();
After getting to know the basic course of connecting a Java program with database, let us know check out a quick example to make concepts more clear and easy to understand.
In the below example, the name of the database is ‘test’, and the username and password that connects to the database is ‘root’. The driver class used for connecting the MySQL database is “com.mysql.jdbc.Driver”.
Before we write a program to establish database connectivity, let us create a database first.
create database test; use test; create table student (rollno int(10), name varchar(50), age int(10));
This is basic MySQL commands to create create the relational database and tables in it.
Below is the program that connects Java application with MySQL database that you created.
import java.sql.*;
class ExampleConnection {
public static void main(String args[]) {
try {
Class.forName("com.mysql.jdbc.Driver");
Connection c=DriverManager.getConnection(
"jdbc:mysql://localhost:3306/test","root","root");
Statement s=c.createStatement();
ResultSet rs=s.executeQuery("select * from student");
while(rs.next())
System.out.println(rs.getInt(1)+" "+rs.getString(2)+" "+rs.getInt(3));
c.close();
}
catch(Exception e)
{
System.out.println(e);}
}
}
}
And that’s a wrap to Java JDBC tutorial. To sum it up, in this tutorial we explored the basics of Java JDBC, it’s components, different types of JDBC drivers. You also learned the fundamental process to make the JDBC connection in Java with MySQL.
Try running the code given in this tutorial to make the JDBC connection. And then person some MySQL select queries to read the data from MySQL table. Let me know in the comment if you find any difficulty.
Happy Learning!
I am a graduate in computer science with a creative bent of mind for writing content. With great gusto, I enjoy learning new things. I trail in database management system, and object-oriented programming languages like Java, C/C++.