StackStalk
  • Home
  • Java
    • Java Collection
    • Spring Boot Collection
  • Python
    • Python Collection
  • C++
    • C++ Collection
    • Progamming Problems
    • Algorithms
    • Data Structures
    • Design Patterns
  • General
    • Tips and Tricks

Wednesday, June 4, 2014

JDBC Quick Start

 June 04, 2014     Java     No comments   

This article provides a quick introduction to JDBC (Java Database Connectivity) API. The JDBC API enables a Java application to connect to a database, send queries to the database and process the results received in a database query.

For the purpose of this tutorial let us use PostgreSQL database. PostgreSQL is an open source object-relational database management system (ORDBMS) and is free to use for commercial application.

PostgreSQL Installation

  • Download PostgreSQL from this link http://www.enterprisedb.com/downloads/postgres-postgresql-downloads and run it.
  • Installation is straight forward. Just follow the wizard.
To connect with a database JDBC API requires the drivers for each database. The JDBC driver gives out the connection to the database and implements the protocol for transferring the query and result between client and database.

PostgreSQL JDBC Driver

  • Download JDBC driver from this link http://jdbc.postgresql.org/download.html and add the jar to your CLASSPATH of the project. If you are using Java 7 use JDBC41.

PostgreSQL setup a database

  • Invoke the "pgAdmin" application which comes with the PostgreSQL installation.
  • Create a new database “mytestdb”. Right click "Databases à Create New Database".
Create Database
  • Expand Schemas under mytestdb. Go to public and right click on Tables to add “New tables”.
Create Table
  • Add the required columns.
Create Columns
  • Create the table.
  • Insert values using the SQL editor. 
Insert values using SQL Editor

Now we are ready to go.

JDBC Basics Example

Let us try a simple example to connect to the PostgreSQL database we created and select the records inserted from a Java application.

The DriverManager attempts to select an appropriate driver from the set of registered JDBC drivers and attempts to establish a connection with the give database URL, username and password.

The Statement object is used for executing SQL statements and to get the results.

The ResultSet object maintains a cursor to the current row of data and provides the getter methods to access the column values.

package com.sourcetricks.jdbcexamples;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JdbcTest {

  public static void main (String[] args) {
    final String dbUser = "postgres";
    final String dbPass = "test123";
    final String dbUrl = "jdbc:postgresql://localhost:5432/mytestdb";

    try ( Connection connection = DriverManager.getConnection(dbUrl, dbUser, dbPass); 
          Statement stmt = connection.createStatement(); ) 
    {   
      // Select query
      ResultSet rs = stmt.executeQuery("SELECT * from employee");
      while ( rs.next() ) {
        System.out.println(rs.getInt(1)); // Note starts from 1
        System.out.println(rs.getString(2));
      }
    } catch (SQLException e) {
      e.printStackTrace();
    } 
  }
}
In the next chapter on JDBC JDBC Database Access Examples we would see more specific examples on using Statements, PreparedStatement, ResultSet etc.
  • Share This:  
Newer Post Older Post Home

0 comments:

Post a Comment

Follow @StackStalk
Get new posts by email:
Powered by follow.it

Popular Posts

  • Avro Producer and Consumer with Python using Confluent Kafka
    In this article, we will understand Avro a popular data serialization format in streaming data applications and develop a simple Avro Produc...
  • Monitor Spring Boot App with Micrometer and Prometheus
    Modern distributed applications typically have multiple microservices working together. Ability to monitor and manage aspects like health, m...
  • Server-Sent Events with Spring WebFlux
    In this article we will review the concepts of server-sent events and work on an example using WebFlux. Before getting into this article it ...
  • Implement caching in a Spring Boot microservice using Redis
    In this article we will explore how to use Redis as a data cache for a Spring Boot microservice using PostgreSQL as the database. Idea is to...
  • Python FastAPI microservice with Okta and OPA
    Authentication (AuthN) and Authorization (AuthZ) is a common challenge when developing microservices. In this article, we will explore how t...
  • Spring Boot with Okta and OPA
    Authentication (AuthN) and Authorization (AuthZ) is a common challenge when developing microservices. In this article, we will explore how t...
  • Getting started with Kafka in Python
    This article will provide an overview of Kafka and how to get started with Kafka in Python with a simple example. What is Kafka? ...
  • Getting started in GraphQL with Spring Boot
    In this article we will explore basic concepts on GraphQL and look at how to develop a microservice in Spring Boot with GraphQL support. ...

Copyright © StackStalk