The basic operation to do with any relational database is reading the database . The JDBC API is having all the required classes for reading database in Java.
Reading database in Java
Reading a data base table using JDBC consists of the following steps.
1)Load the JDBC driver
2)Establish connection with database
3)Do the transactions
4)Close the data base connection
The concept is explaining here with example.We are discussing with MySQL server 5.6 . If you are for the first time with MySQL download it from here.
We can query the database using either using the default command line client of MySQL server or any object browser like SQLyog. We can download the community edition of SQLyog for learning purposes from here.
Before starting our Java code , we should create the database and database table .These can be done by running the following queries either from the command line client or from the SQLyog.
1)Create a database with name mydatabase
CREATE DATABASE mydatabase;
2)Create table in the above created database
USE mydatabase;
CREATE TABLE mytable (
number INT PRIMARY KEY,
DATA VARCHAR(100)
);
This query creates a table with two columns . First column is number of type int . It is the primary key of table. Second column is data .It is of String type.
3)Now insert few data to the above created table.
USE mydatabase;
INSERT INTO mytable VALUES(‘1′,’cat’);
INSERT INTO mytable VALUES(‘2′,’dog’);
INSERT INTO mytable VALUES(‘3′,’cow’);
INSERT INTO mytable VALUES(‘4′,’sheep’);
(We can verify the contents of table by using the query :select * from mytable )
Now let us see the code to fetch the contents of database table mytable.For running the code the MySQL Connector jar should be there in class path. We can download it from here.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class DBRead {
private Connection connection = null;
private Statement statement = null;
private ResultSet resultset = null;
public void DBRead() {
}
public void fetchDetails() {
try {
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/mydatabase", "username",
"password");
statement = connection.createStatement();
String query = "select * from mytable";
resultset = statement.executeQuery(query);
while (resultset.next()) {
System.out.println("Number = " + resultset.getInt(1)
+ " ; Data = " + resultset.getString(2));
}
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
try {
if (resultset != null)
resultset.close();
if (statement != null)
statement.close();
if (connection != null)
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
public static void main(String[] args) {
DBRead dbRead = new DBRead();
dbRead.fetchDetails();
}
}
So our code establishes connection with database using the database login credentials first . Then creating a Statement object . And then executing the query. The results are storing to a ResultSet object. Then iterating the ResultSet object for printing the details in console.
Output
Number = 1 ; Data = cat
Number = 2 ; Data = dog
Number = 3 ; Data = cow
Number = 4 ; Data = sheep