JDBC (Java Database Connectivity) is a standard API(application interface) between the java programming language and various databases like oracle,SQL, etc.it connects the front end for interacting with the users with the backend for storing data.
Procedure:
- Creating a database
- Connection class
- Output results in tabular format.
- Inside the same package, right-click on it and open a JFrame form and give a name
- Add a table swing control(drag and drop) and a ‘VIEW’ button
- Click on the Source tab and import the below libraries
- Download a rs2xml.JAR file additionally import DbUtils if alone jar file does not work.
- Go to the design tab and double-click on the ‘view’ button to write the program for jdbc connection and for obtaining the result.
- Writing the code by double-clicking on the “view” button keeping a note not to write in the main method.
- Display the output
Implementation:
Step 1: Creating a database using sqlyog and create some tables in it and fill data inside it in order to output the contents of a table.
For example, here the database is named as “hotelman” in which table names are “cuslogin” and “adminlogin”. Pictorial Representation when the database is just created is given below
Step 2: Connection class
- Open IDE. Netbeans is used to illustrate for ease generally as it already contains the required jar files pre-installed. For any other IDE, first, import the required 4 jar files before creating a connection class.
- Create a package inside in order to establish a connection between the database and the program.
- Inside the package, open a new java file and create a connection class so that its object can be accessed in main(App class) for JDBC connectivity
- Lastly, save the filename with connection.java.
Example
// Java Program to Output Query Results in Tabular Format in // JDBC // Importing database classes for // handling sql exception and for jdbc connectivity // name of database here is mysql import java.sql.*;
// Connection class public class connection {
// Initially setting object to NULL in order to
// avoid any garbage value isse
Connection con = null ;
public static Connection connectDB()
{
// Try block to check all exceptions
try {
// Loading driver using forName() method
Class.forName( "com.mysql.jdbc.Driver" );
// Registering driver using DriverManager
// root is the username
// 1234 is the password
// Can set your own username and password
Connection con = DriverManager.getConnection(
"root" , "1234" );
// returning connection object which later on
// to be used in Main/App class
return con;
}
// Catch block to handle DB exceptions
catch (SQLException e) {
// Print the exception occurred
System.out.println(e);
}
}
} |
Step 3: Output results in tabular format.
Considering above table “cuslogin” has columns namely “id”, “name”, “email”, “password” and we want to display it in table form.
3.1 Inside the same package, right-click on it and open a JFrame form and give a name of your choice(as shown below)
3.2 Add a table swing control(drag and drop) and a ‘VIEW’ button(which when clicked would show the result in the table)
3.3 Click on the Source tab and import the below libraries
import java.sql.*; //for handling jdbc related syntax import javax.swing.JOptionPane; //for showing message import net.proteanit.sql.DbUtils; //for displaying the result of query to table form
3.4 Download a rs2xml.JAR file additionally import DbUtils if alone jar file does not work. Now, unzip it and upload the jar file only by following the below steps:
3.4.1 Right–click on libraries and click on add jar/folder
3.4.2 Upload the unzipped jar file and click on open.
3.5 Go to the design tab and double-click on the ‘view’ button to write the corresponding code for which inside the function, the program is as follows:
// Java Program for jdbc connection // and for obtaining the result Connection con = null ;
PreparedStatement ps = null ;
ResultSet rs = null ;
the above code is con = connection.connectDB(); // Query to display details of all customers String sql = "select * from cuslogin" ;
// Try block to check for exceptions try {
ps = con.prepareStatement(sql);
// result is stored in rs
rs = ps.executeQuery();
// send the result to the table
// Here, jTable1 is the name of the tabular format
jTable1.setModel(DbUtils.resultSetToTableModel(rs));
} // Catch block to handle exceptions catch (Exception e) {
// Display exception message as in dialog box
JOptionPane.showMessageDialog( null , e);
} |
3.5 Writing the code by double-clicking on the “view” button keeping a note not to write in the main method.
Example
// Importing input output java files import java.io.*;
// Class class GFG {
// Main driver method
public static void main(String[] args)
{
// Assigning NULL to connection object
Connection con = null ;
PreparedStatement ps = null ;
ResultSet rs = null ;
// For jdbc connection
con = connection.connectDB();
// Query to display details of all customers
String sql = "select * from cuslogin" ;
try {
ps = con.prepareStatement(sql);
// Result is stored in rs
rs = ps.executeQuery();
// Send the result to the table
jTable1.setModel(
DbUtils.resultSetToTableModel(rs));
// Here, jTable1 is name of the tabular format
}
// Catch block to handle if exception occurred
catch (Exception e) {
// Display exception message in dialog block
JOptionPane.showMessageDialog( null , e);
}
}
} |
Output:
- After running the java file
- On clicking the view button, the output is as follows