Java Program to Retrieve Contents of a Table Using JDBC connection
It can be of two types namely structural and non-structural database. The structural database is the one which can be stored in row and columns. A nonstructural database can not be stored in form of rows and columns for which new concepts are introduced which would not be discussing here. Most of the real-world data is non-structural like photos, videos, social media. As they are not having pre-defined data-types, so they are not present in the database. Hence, the structural database is stored in data warehouses and unstructured in data lakes. Java Database Connectivity is basically a standard API(application interface) between the java programming language and various databases like Oracle, SQL, PostgreSQL, MongoDB, etc. It connects the front end(for interacting with the users) with the backend for storing data
JDBC consists of 7 elements that are known as connection steps. They are listed below:
JDBC connection steps Sep/Connection Number 1 Import the package 2 Load and Register the drivers 3 Establish the connection 4 Create the statement 5 Execute the statement 6 Process Result 7 Close/terminate
The syntax for importing package to deal with JDBC operations:
import java.sql.* ;
The syntax for registering drivers after loading the driver class:
Steps: Below are 3 steps listed covering all 7 components and applying the same:
- The first database will be created from which the data is supposed to be fetched. If the data is structured SQL can be used to fetch the data. If the data is unstructured MongoDB to fetch data from da lakes. Here SQL database is created and so do all further executions.
- This step is optional as here need is simply too old and register drivers. In some IDE drivers are inbuilt for example NetBeans but they are missing in eclipse. Here the installation of drivers is needed. Jar files are needed to be downloaded hardly taking any space in memory.
- Retrieve Contents of a Table Using JDBC connection
Step 1: Creating a database using SQLyog: The user can create a database using ‘SQLyog’ and create some tables in it and fill data inside it.
Considering a random example of hotel database management systems. Now applying SQL commands over it naming this database as ‘hotelman’. Now suppose the user starts inserting tables inside it be named as “cuslogin” and “adminlogin“.
Step 2:Create Connection: Considering IDE as Netbeans for clear understanding because now work let in this package else if using any other IDEs install drivers first. Once cleared with
- Create a new package
- Open a new java file
- Write the below code for JDBC
- Save the filename with connection.java.
Here considering IDE as Netbeans for clear’ sake of understanding. Below is the illustration for the same:
Step 3: Retrieve Contents of a Table Using JDBC connection: Suppose “cuslogin” table has columns namely “id”,”name”,”email” and the user wants to see the contents of “cuslogin” table. It involves a series of steps give below with declaration and syntax for interpretation
3.1: Initialize a string with the SQL query as follows
String sql="select * from cuslogin";
3.2: Initialize the below objects of Connection class, PreparedStatement class, and ResultSet class(needed for JDBC ) and connect with the database as follows:
Connection con=null; PreparedStatement p=null; ResultSet rs=null; con=connection.connectDB();
3.3: Now, add the SQL query of 3.1 inside prepareStatement and execute it as follows
p =con.prepareStatement(sql); rs =p.executeQuery();
3.4 Run a loop till rs.next() is not equal to NULL , fetch values from the table based on the data types, for example we use getInt() for integer datatype values and getString() for string datatype values.
3.5 Open a new java file (here, its result.java) inside the same package and type the full code (shown below) for the retrieval of contents of table “cuslogin”.
3.6 Both the files viz ‘result.java’ and ‘connection.java’ should be inside the same package, else the program won’t give the desired output!
Implementation: Below is the java example illustrating the same:
Output: The above output is completely based on the values stored inside the “cuslogin” table as shown in the above code
Attention reader! Don’t stop learning now. Get hold of all the important Java Foundation and Collections concepts with the Fundamentals of Java and Java Collections Course at a student-friendly price and become industry ready. To complete your preparation from learning a language to DS Algo and many more, please refer Complete Interview Preparation Course.