2ndQuadrant is now part of EDB

Bringing together some of the world's top PostgreSQL experts.

2ndQuadrant | PostgreSQL
Mission Critical Databases
  • Contact us
  • EN
    • FR
    • IT
    • ES
    • DE
    • PT
  • Support & Services
  • Products
  • Downloads
    • Installers
      • Postgres Installer
      • 2UDA – Unified Data Analytics
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
  • Postgres Learning Center
    • Webinars
      • Upcoming Webinars
      • Webinar Library
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Blog
    • Training
      • Course Catalogue
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
    • Books
      • PostgreSQL 11 Administration Cookbook
      • PostgreSQL 10 Administration Cookbook
      • PostgreSQL High Availability Cookbook – 2nd Edition
      • PostgreSQL 9 Administration Cookbook – 3rd Edition
      • PostgreSQL Server Programming Cookbook – 2nd Edition
      • PostgreSQL 9 Cookbook – Chinese Edition
    • Videos
    • Events
    • PostgreSQL
      • PostgreSQL – History
      • Who uses PostgreSQL?
      • PostgreSQL FAQ
      • PostgreSQL vs MySQL
      • The Business Case for PostgreSQL
      • Security Information
      • Documentation
  • About Us
    • About 2ndQuadrant
    • 2ndQuadrant’s Passion for PostgreSQL
    • News
    • Careers
    • Team Profile
  • Blog
  • Menu Menu
You are here: Home1 / Blog2 / Greenplum3 / Setting JDBC with Greenplum
Carlo Ascani

Setting JDBC with Greenplum

January 19, 2012/1 Comment/in Greenplum /by Carlo Ascani

JDBC is the driver used to access a database with Java. Greenplum has a full working JDBC implementation.
In this short article we’ll see how to use it.


## Download and install
It is possible to download the JDBC for Greenplum directly from the Greenplum Community Edition site (http://www.greenplum.com/community/downloads/database-ce/).
Look for the *”Connectivity Tools”* file.
You will receive a link to download the archive file.
Extract the archive and run the binary extracted. Then follow the instructions on screen and in less than a minute you have installed JDBC.
## Prepare the Greenplum server
After a successful installation, make sure that the server accepts TCP connections from the desired hosts. Check that *listen_addresses* is properly set in postgresql.conf.
**Note:** by default, Greenplum listens to any address.
Another aspect you have to consider is the user authentication, which is delegated to the pg_hba.conf file (please refer to page 36 of Greenplum AdminGuide for more information).
After you have verified the user is able to connect to the database, you can go on and test JDBC.
Connecting to a Greenplum Database with JDBC is a three steps procedure:
* Import JDBC
* Load the driver
* Connect to the database
To import JDBC, add this line to the top of your Java source:

import java.sql.*;

To load the driver, use this line:

Class.forName("org.postgresql.Driver");

Remember that the *forName* function can throw a *ClassNotFoundException* if the driver is not available. We do not try to catch that exception in the simple example below. You should in your production environment.
To connect to a database using JDBC, you have to use a connection URL. It can be in one of these three forms:
* jdbc:postgresql:databasename
* jdbc:postgresql://host/databasename
* jdbc:postgresql://host:port/databasename
Build an URL that suits your needs and use it with the getConnection function. For example:

Connection db = DriverManager.getConnection(url, username, password);

## Issuing a query
To perform queries on the database, you have to use a Statement or a PreparedStatement instance.
You can create a Statement object using the createStatement method of class Connection.
The result of a query execution is a ResultSet object, containing th entire result.
A ResultSet object can be iterated with the usual next() function, as shown in the example below.
## A simple example

import java.sql.*;
public static void main(){
Class.forName("org.postgresql.Driver");
Connection db = DriverManager.getConnection("jdbc:postgresql://localhost/testdb","username", "passw0rd");
Statement st = db.createStatement();
ResultSet rs = st.executeQuery("SELECT * FROM mytable WHERE columnfoo = 500");
while (rs.next()) {
System.out.print("Column 1 returned ");
System.out.println(rs.getString(1));
}
rs.close();
st.close();
}

It is very important to understand how to bind values in queries, in order to prevent from SQL injection issues. The following snippet is an example for that:

int foovalue = 500;
PreparedStatement st = conn.prepareStatement("SELECT * FROM mytable WHERE columnfoo = ?");
st.setInt(1, foovalue);
ResultSet rs = st.executeQuery();
while (rs.next()) {
System.out.print("Column 1 returned ");
System.out.println(rs.getString(1));
}
rs.close();
st.close();

Tags: greenplum, java, JDBC, postgres
Share this entry
  • Share on Facebook
  • Share on Twitter
  • Share on WhatsApp
  • Share on LinkedIn
1 reply
  1. vikram
    vikram says:
    March 26, 2015 at 7:53 pm

    Thanks a lot!!! It was just what I was looking for.

    Reply

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Search

Get in touch with us!

Recent Posts

  • Random Data December 3, 2020
  • Webinar: COMMIT Without Fear – The Beauty of CAMO [Follow Up] November 13, 2020
  • Full-text search since PostgreSQL 8.3 November 5, 2020
  • Random numbers November 3, 2020
  • Webinar: Best Practices for Bulk Data Loading in PostgreSQL [Follow Up] November 2, 2020

Featured External Blogs

Tomas Vondra's Blog

Our Bloggers

  • Simon Riggs
  • Alvaro Herrera
  • Andrew Dunstan
  • Craig Ringer
  • Francesco Canovai
  • Gabriele Bartolini
  • Giulio Calacoci
  • Ian Barwick
  • Marco Nenciarini
  • Mark Wong
  • Pavan Deolasee
  • Petr Jelinek
  • Shaun Thomas
  • Tomas Vondra
  • Umair Shahid

PostgreSQL Cloud

2QLovesPG 2UDA 9.6 backup Barman BDR Business Continuity community conference database DBA development devops disaster recovery greenplum Hot Standby JSON JSONB logical replication monitoring OmniDB open source Orange performance PG12 pgbarman pglogical PG Phriday postgres Postgres-BDR postgres-xl PostgreSQL PostgreSQL 9.6 PostgreSQL10 PostgreSQL11 PostgreSQL 11 PostgreSQL 11 New Features postgresql repmgr Recovery replication security sql wal webinar webinars

Support & Services

24/7 Production Support

Developer Support

Remote DBA for PostgreSQL

PostgreSQL Database Monitoring

PostgreSQL Health Check

PostgreSQL Performance Tuning

Database Security Audit

Upgrade PostgreSQL

PostgreSQL Migration Assessment

Migrate from Oracle to PostgreSQL

Products

HA Postgres Clusters

Postgres-BDR®

2ndQPostgres

pglogical

repmgr

Barman

Postgres Cloud Manager

SQL Firewall

Postgres-XL

OmniDB

Postgres Installer

2UDA

Postgres Learning Center

Introducing Postgres

Blog

Webinars

Books

Videos

Training

Case Studies

Events

About Us

About 2ndQuadrant

What does 2ndQuadrant Mean?

News

Careers 

Team Profile

© 2ndQuadrant Ltd. All rights reserved. | Privacy Policy
  • Twitter
  • LinkedIn
  • Facebook
  • Youtube
  • Mail
Greenplum 4.2 is out! External web tables in Greenplum
Scroll to top
×