Java Program to Output Query Results in Tabular Format in JDBC

0 0 4 minutes read

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

// 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(
                "jdbc:mysql://localhost:3306/hotelman",
                "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

// 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

Java

// 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);
        }
    }
}