_edited_edited.png)
Prerequisite: Database connection String like Hostname, Servicename/SID, Username, Password, PortNumber.
In modern day Technology, Data can be categorized into 3 categories.
1. Structured Data: Data that is confined to a specific structure. This structure can be created as tables or views in Databases.
2. Semi-Structured Data: Data that is partial structured. Mostly they are stored as Key and Value pairs.
3. Unstructured Data: Data that do not follow any structure. Example Audio and video files.
As developers or testers, it is very evident that we need to work with all types of these data forms. Here we will talk about Structured data. Structured Data is usually stored in relational Database system. There are several Database systems available in the market Oracle, Postgres, Azure SQL database, MySQL etc. Through Java we can connect to any of Databases using a concept called as JDBC.
JDBC
Java database connectivity (JDBC) is the standard Java API for database-independent connectivity between the Java programming language and a wide range of databases. The JDBC API consists of a set of interfaces and classes written in the Java programming language. JDBC is a Java API to connect and execute the query with the database. Java can be used to write different types of executables and these different executables are able to use a JDBC driver to access a database, and take advantage of the stored data. Such Java executables can be:
-- Java Applications
-- Java Applets
-- Java Servlets
-- Java ServerPages (JSPs)
--Enterprise JavaBeans (EJBs).
The JDBC API provides the following classes and interfaces:
Driver Manager
Driver
Connection
Statements
ResultSet
SQLException
In order to JDBC to function correctly, you need to download JDBC drivers and associate them to your Java project. Every Database has its own set of JDBC drivers. You will need to download drivers specific to your database and project need. For this blog, I am working with Postgres SQL DB. To download Postgres DB JDBC driver visit
Depending on Java version of your machine, select appropriate JDBC driver version from below screenshot.
Once you download jar files you have to associate downloaded jar file path to your project. Open eclipse, right click on your project and click on properties.
Then select java build path and within libraries, add external jars. Select the jar file path, click on apply and apply &close.
Once the jar files are added successfully, we can now create java classes to connect to the database and run queries.
We follow below steps to connect to database and execute the queries through Java program.
--Establish database connection
--Create query or SQL statements
--Execute queries
--Validate results
For the purpose of this blog, I am using database called as Diabetes_DB, within this database I have table named ‘Gender’ there are only two records in this table at present. They are shown in screenshot below:
Let us try to insert a new record in Gender table through java program. The very first step is to establish connection with database:
Connection connect=DriverManager.getConnection("jdbc:postgresql://localhost:5432/Diabetes_DB","postgres","password");
In above syntax I have created ‘connect’ object which we will use throughout java program to connect to database. My database resides on host ‘localhost’, port’5432’. Database I am trying to connect is ‘Diabetes_DB’, username is ‘postgres’ and password is ‘password’.
Statement stmt=connect.createStatement();
String s=”insert into public.\”Gender\” values (‘G003’,’Unknown’)”;
Once the database connection is established we can now use ‘connect’ object to create SQL Statements. Please refer above Statement syntax. I used Connection object ‘connect’ to create a Statement object ‘stmt’. I am also using a simple SQL INSERT INTO query and storing the entire query into a variable names as ‘s’.
stmt.executeQuery(s);
Once you have SQL statement object ready, you can use it execute the said query on the database. We can use executeQuery() method to implement the same.
Your Java application program will look something like shown in below screenshot.
Lets now execute Java program. Upon successful execution, we should be able to validate if our program worked on not by simply logging to our database and querying our Gender table.
Please see below screenshot, I am able to see an additional record now in Gender table.
You could perform all the database actions using above mentioned steps. Lets now try another database option to Create a Table. If you see screenshot below, I have total 16 tables available in my DB. I am going to try to create a table named ‘Patient_Emergency_Contact’ in the same database.
Your java application code will look something like shown below to create a table. Since I am not specifying any schema name, the table will be created in “public” schema of the database.
Now execute the code and validate the outcome. In below screenshot, I see a new table is created with the same name as ‘Patient_emergency_contact’.
By using above mentioned steps you can run any database related operations using JDBC connection in a Java app program. JDBC makes it possible for the java class to connect to the Database, retrieve data from the database or for the matter of fact perform any of the CRUD operations, manipulate the resultant data and close the connection.