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:
forName(com.mysql.jdbc.xyz) ;
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:
Java
// Java Program to Retrieve Contents of a Table Using JDBC// connection// Showing linking of created database// Importing SQL libraries to create databaseimport java.sql.*;public class connection { Connection con = null; public static Connection connectDB() { try { // Importing and registering drivers Class.forName("com.mysql.jdbc.Driver"); Connection con = DriverManager.getConnection( "root", "1234"); // here,root is the username and 1234 is the // password,you can set your own username and // password. return con; } catch (SQLException e) { System.out.println(e); } }} |
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 given 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:
Java
// Java Program retrieving contents of// Table Using JDBC connection// Java code producing output which is based// on values stored inside the "cuslogin" table in DB// Importing SQL libraries to create databaseimport java.sql.*;public class GFG { // Step1: Main driver method public static void main(String[] args) { // Step 2: Making connection using // Connection type and inbuilt function on Connection con = null; PreparedStatement p = null; ResultSet rs = null; con = connection.connectDB(); // Try block to catch exception/s try { // SQL command data stored in String datatype String sql = "select * from cuslogin"; p = con.prepareStatement(sql); rs = p.executeQuery(); // Printing ID, name, email of customers // of the SQL command above System.out.println("id\t\tname\t\temail"); // Condition check while (rs.next()) { int id = rs.getInt("id"); String name = rs.getString("name"); String email = rs.getString("email"); System.out.println(id + "\t\t" + name + "\t\t" + email); } } // Catch block to handle exception catch (SQLException e) { // Print exception pop-up on screen System.out.println(e); } }} |
Output: The above output is completely based on the values stored inside the “cuslogin” table as shown in the above code
