How to query a database table via JDBC

In this blog post, I will be explaining how you can query a database table via JDBC. JDBC as you might probably know stands for Java Database Connectivity .It is an API which can be used to access a relational database from Java code. Using JDBC, you can execute SQL queries directly from Java code. JDBC has been around for a very long time now.  Although nowadays ORM frameworks like Hibernate, SpringDAO, JPA etc are used to access a database, there is still a lot of legacy code around that uses JDBC. So in this blog post, I will be covering how you use JDBC to query a table.

 

Database Setup

Step 1  – Install MySQL.

This step is optional, you need to follow this step only if you want to use a MySQL database. You can download it from here. You can download the version that is appropriate for your operating system. This will download the MySQL Installer. Once the download is complete, you can follow the onscreen instructions to proceed with the installation.I recommend going with the developer default that installs MySQL, MySQLWorkbench and other utilities required for development.

Step 2 – Create Sample database with data

We will first need to create a database table. We will also need to insert some test data into the table. The code that we are going to write will then query this via JDBC. You can execute the following SQL code to create the sample database and insert some data into it.

drop database if exists PersonDB;create database PersonDB;use persondb;create table person (id int auto_increment,first_name varchar(20),last_name varchar(20),age int ,primary key (id));insert into person (first_name,last_name,age) values ('mickey','mouse',10);

If you are using MySQL, then open MySQLWorkBench, Open a new query tab and just copy and paste the SQL code given above.

Code Setup

Step 3 – Create a new Maven project in Eclipse

In this example, I will be using Eclipse and Maven.  So you will need to create a new Maven project in Eclipse. You can refer this post in order to understand how to create a Maven project in Eclipse.

The advantage of using a dependency management tool like Maven is that it automatically downloads the necessary jar files for you, you just need to add the appropriate dependency in the Maven pom file.

Step 4 – Add Database Driver dependency

As you might know, JDBC requires a database specific driver to communicate with the database. A driver is nothing but a Jar file. Each database, be it MySQL, Oracle or PostgreSQL, has its own specific drivers which you need to use if you want to connect to that database via JDBC. Since we are using Maven, you need to add the dependency for the driver in your Maven pom file. If you are using MySQL, you can add the following to your pom file:

JDBC For this double click on the pom.xml and add the following:

	<dependency>  		<groupId>mysql</groupId>  		<artifactId>mysql-connector-java</artifactId>  		<version>8.0.12</version>  	</dependency>

Your final pom file should look like this:

So this will automatically download the MySQL driver and add it to the classpath. If you are using some other database, then you need to add the appropriate dependency for that database.

Step 5 – Create Java class.

Create a new package called demo and create a new Class in this package called SelectDemo.

Write and Run Code

Step 7 – Write Java Code.

Add the following code to the SelectDemo class written earlier:

package demo;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;public class SelectDemo {public static void main(String[] args) {Connection conn = null;Statement stmt = null;try {// Step 1 - Register JDBC driverClass.forName("com.mysql.jdbc.Driver");// Step 2 - Open a connectionconn = DriverManager.getConnection("URL", "USERNAME", "PASSWORD");//Step 3 - Create statementstmt = conn.createStatement();//Step 4 - Execute queryString query = "select * from person";ResultSet rs = stmt.executeQuery(query);// Step 5 - Iterate through the resultset and print  valueswhile (rs.next()) {// Retrieve by column nameint id = rs.getInt("id");String firstName = rs.getString("first_name");String lastName = rs.getString("last_name");int age = rs.getInt("age");// Print valuesSystem.out.print("ID: " + id);System.out.print(", first_name: " + firstName);System.out.print(", last_name: " + lastName);System.out.println(", age: " + age);}//Step 6 - Close all resourcesrs.close();stmt.close();conn.close();} catch (SQLException e) {System.out.println("SQL Exception occured:");e.printStackTrace();} catch (Exception e) {System.out.println("Exception occured:");e.printStackTrace();}}// end main}

The above code can be broken down into the following steps:

  • Step 1 – Register the JDBC Driver – This step initializes the JDBC driver
  • Step 2  – Open a connection – This opens a Connection to the database. Here, you need to specify the database URL, username and password.  This returns a Connection object.
  • Step 3 – Create a statement. There is a connection.createStatement method that returns a Statement object. A Statement object is used for executing an SQL query
  • Step 4 – Execute the query. There is a statement.executeQuery method. This executes the SQL query. It returns a ResultSet object.
  • Step 5 – Iterate through the results. A while loop is used to iterate through the ResultSet object. There a several getter methods available on the ResultSet object. Depending on the data type being retrieved, the appropriate getter method is used. So since the id is an integer value, the rs.getInt method is used.
  • Step 5 – Close resources – All the resoures, that is the ResultSet, Statement and Connection objects are closed.

 

Step 8 – Run the code.

This will print the following output:

ID: 1, first_name: mickey, last_name: mouse, age: 10

 

 

 

Comments

Post a Comment

Popular posts from this blog

How to use logging in SpringBoot with code samples

Image
In this article, we will learn how to use logging in SpringBoot. Introduction There are several Java logging frameworks like logback, log4j, and so on. Spring Boot supports out-of-the-box support for logback. Let us learn how to use logging in SpringBoot with logback. Step 1 – Create a New Maven Project Create a new Maven project. (Refer to  this  blog post). This will create a project in Eclipse as follows:   Step 2 – Add the Spring Boot Dependencies Add the spring-boot-starter-web dependency to the pom.xml . So, the pom.xml should look as follows: <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>com.learnjava</groupId> <artifactId>learnjava-springboot-logging-example</artifactId> <version>0.0.1-SNAPSHOT&
Read more

Python While Loop with code samples

Much like a Java while loop, you can use a Python while loop to repeatedly execute a block of code as long as a condition is true. In this article, we will learn more about the Python while loop. Python While loop Syntax A while loop in Python has the following syntax: while <condition>: loop body The while keyword is followed by a condition. If the condition evaluates to true, the body of the loop is executed. Once the body of the loop is completed, the condition is checked again and this is repeated until the condition evaluates to false at which point the loop is exited. While loop Sample Code The following code demonstrates how you can use the while loop: num=10counter=1total=0while counter<=num: total += counter counter+=1print("sum of first 10 numbers is",total) This code adds the numbers from 1 to 10 and prints the sum. It uses a while loop to iterate from 1 to 10. The while loop checks the condition counter <= num and if so adds the current value of count
Read more

How to convert a List to a Set

In this blog post ,I will be showing you how you can convert a List to a Set. Consider the following code snippet: public class ListToSetDemo {public static void main(String[] args) {List<Integer> list = Arrays.asList(5,3,11,15,9);//Method 1Set<Integer> set = new HashSet<Integer>(list);System.out.println("Set is "+set);//method 2Set<Integer> set2 = new HashSet<Integer>();set2.addAll(list);System.out.println("Set is "+set2);}} This code demonstrates two ways to convert a List to a Set. Method 1 Here, we are creating a new HashSet . We are invoking the constructor that accepts a Collection object. Here, we are passing in the input list to this constructor.   Method 2 Here again, we are creating a new HashSet . However, we are using the default constructor. We are then invoking the addAll method and passing in the input list.   When you run this code, it will print the following output: Set is [3, 5, 9, 11, 15]Set is [3, 5, 9, 11, 15] No
Read more