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 mysqlimport java.sql.*;// Connection classpublic 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
// Java Program for jdbc connection// and for obtaining the resultConnection con = null;PreparedStatement ps = null;ResultSet rs = null;the above code is con = connection.connectDB();// Query to display details of all customersString sql = "select * from cuslogin";// Try block to check for exceptionstry { 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 exceptionscatch (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 filesimport java.io.*;// Classclass 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
