ABC’s of JDBC
Java Database Connectivity (JDBC) is an application programming interface (API) for the programming language Java, which defines how a client may access a database. It is a Java-based data access technology used for Java database connectivity.
Why JDBC?
We could’ve chosen Open Database Connectivity (ODBC) over JDBC, but JDBC ensures to give natural Java interface as ODBC would be inappropriate over such interface. The JDBC API was modeled after ODBC to include SQL in Java interface. JDBC is needed to provide a pure Java solution for application development.
Architecture of JDBC
JDBC is generally two layer model as listed below:
JDBC API: This layer supports the connection between the application and the JDBC Manager. It also uses driver manager to access database drivers so one can connect with databases that are heterogeneous.
JDBC Driver API: This layer supports the connection between JDBC Manager and Driver. This driver manager makes sure that correct driver is accessing the required the data resources.
JDBC Component Classes
JDBC API consists of six classes as follows:
- Driver Manager: Driver Manager class as name suggests is responsible for managing the list of drivers of the database. It also matches the connection requests coming from the java application along with database driver with help of sub-protocol of communication.
- Driver: The interface that handles the communications happening between the application and the database server is called the Driver.
- Connection: The connection class is used to make a connection with database. The object of connection class represents the context of communication.
- Statement: The statement class object makes it possible to submit SQL statements to the database.
- ResultSet: ResultSet class objects are used to execute the database query made by statement class objects and also acts as an iterator to move between the data.
- SQL Exception: SQL Exception class is used in handling any of the errors which occur in the database application.
Advantages of JDBC
- It is capable of reading any database. The only requirement for it to do so is the proper installation of all the drivers.
- It automatically creates the XML format of data from the database.
- It does not require the content to be converted.
- It provides full support to query and stored procedure.
- It supports modules.
Disadvantages of JDBC
- It is very sensitive when it comes to the driver. Hence, it is very important to install correct drivers and to deploy them for each type of database in order to make use of it.
- It does not allow a single sequence to update or insert multiple tables.
How to use JDBC?
So let’s see this with an example, over here I have used JavaFx and JDBC concepts. To know more about JavaFx.
NOTE: Make sure to download the respective SQL driver.
import javafx.application.Application;
import javafx.event.ActionEvent;
import javafx.geometry.Pos;
import javafx.scene.Scene;
import javafx.scene.control.Button;
import javafx.scene.control.TextField;
import javafx.scene.layout.GridPane;
import javafx.scene.text.Text;
import javafx.stage.Stage;
import javafx.event.EventHandler;
import java.sql.*;
public class Forms extends Application {
// Using Postgres SQL to use JDBC
static String url = "jdbc:postgresql://localhost/postgres?user=postgres&password=root";
static Connection con = null;
static Statement st = null;
public static void main(String[] args) throws SQLException{
try{
con = DriverManager.getConnection(url);
st = con.createStatement();
// Creating a table in SQL
String sql = "CREATE TABLE IF NOT EXISTS FORM(name varchar(30),"+
"e_mail varchar(50)," +
"mobile numeric(10));";
ResultSet r = st.executeQuery(sql);
con.close(); //always remember to close the connection
}catch (SQLException e){
e.printStackTrace();
}
launch(args);
}
@Override
// JavaFx
public void start(Stage primaryStage) {
// Software widget declarations
GridPane gridPane = new GridPane();
Text t1 = new Text("Name: ");
Text t2 = new Text("E-Mail ID: ");
Text t3 = new Text("Mobile Number: ");
TextField tf1 = new TextField();
TextField tf2 = new TextField();
TextField tf3 = new TextField();
Button b = new Button("Submit");
//Positioning the widgets
gridPane.add(t1,0,1);
gridPane.add(tf1,2,1);
gridPane.add(t2,0,3);
gridPane.add(tf2,2,3);
gridPane.add(t3,0,5);
gridPane.add(tf3,2,5);
gridPane.add(b,2,10);
gridPane.setAlignment(Pos.CENTER);
//Whats should happen when submit button is clicked.
EventHandler<ActionEvent> eventHandler = new EventHandler<ActionEvent>() {
@Override
public void handle(ActionEvent actionEvent) {
String name = tf1.getText();
String email = tf2.getText();
Long mobile = Long.parseLong(tf3.getText());
String insert = "'" + name + "'" + "," + "'" + email + "'" + "," + mobile;
try{
//Inserting values in table created above
String query1 = "INSERT INTO FORM VALUES(" + insert + ")";
ResultSet r1 = st.executeQuery(query1);
con.close();
}catch (SQLException e){
e.printStackTrace();
}
try{
//Viewing all the values in table
String query2 = "SELECT * FROM FORM";
ResultSet r2 = st.executeQuery(query2);
while (r2.next()){ //next is used to iterate between table values
String n = r2.getString("name");
String e = r2.getString("e_mail");
String m = r2.getString("mobile");
System.out.println(n + ", " + e + ", " + m);
}
}catch (SQLException e){
e.printStackTrace();
}
}
};
b.setOnAction(eventHandler);
Scene scene = new Scene(gridPane,800,500);
primaryStage.setScene(scene);
primaryStage.setTitle("Forms");
primaryStage.show();
}
}Output:
NOTE: One might get warnings in Terminal which can be ignored.
Conclusion
Thus, JDBC helps to connect to database and thereby executing SQL statements against the database and making data management easier.
