we are going to implement the first JDBC Program example. Here are the different steps to connect and working with the database through Java code.
To implement the JDBC code in Java program, typically we have 6 different steps, are listed below.
To write a simple JDBC program, we should follow these 6 different steps. Lets understand indetail for each step.
Load a JDBC Driver : To load a class at runtime into JVM, we need to call a static method called forName() of java.lang.Class. By calling the forName() method we can load the JDBC Driver class into the JVM.
class.forName("sun.jdbc.odbc.JdbcOdbcDriver);
Establish a Connection : By using the DriverManager class, we can establish the connection to the database. By calling the getConnection(String url, String user,String password) static factory method in DriverManager class, we can obtain a Connection object. To get the connection object we need to proved the connection url as a first parameter and database username and password as second and third parameters. Like below.
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","123456");
Create a Statement : In order to send the SQL commands to database from our java program, we need Statement object. We can get the Statement object by calling the createStatement() method on connection.
Statement stmt = con.createStatement();
Execute Sql queries : Inorder to execute the SQL commands on database, Statement interface provides provides three different methods:
When we want to run the non-select operations then we can choose executeUpdate()
int count = stmt.executeUpdate("non-select command");
When we want to execute select operations then we can choose executeQuery()
ResultSet rs = stmt.executeQuery("select command");
When we want to run both select and non-select operations, then we can use execute()
boolean isTrue = stmt.executeQuery("select / non-select command");
Process the ResultSet : For the select operations, we use executeQuery() method. The executed query returns the data in the form of ResultSet object. To process the data we need to go through the ResultSet.
ResultSet rs = stmt.executeQuery("select * from emp");
while(rs.next()){
System.out.println(rs.getInt(1));
}
Close Connection : Last but not least, finally we need to close the connection object. It is very important step to close the connection. Else we may get JDBCConnectionException exception.
con.close();
Complete JDBC example:
package com.navabitsolutions.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class JdbcSelectExample {
public static void main(String[] args) throws Exception {
// Step - I
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
// Step - II
Connection con = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/jdbc", "root", "123456");
// Step -III
Statement stmt = con.createStatement();
// Step - IV
ResultSet rs = stmt.executeQuery("select * from student");
// Step - V
while (rs.next()) {
System.out.println(rs.getInt(1) + " " + rs.getString(2) + " "
+ rs.getString(3));
}
// Step - VI
rs.close();
stmt.close();
con.close();
}
}