In this article I will discuss about PostgreSQL database and how to use it through Java code. PostgreSQL, or simply Postgres, is an Object-Relational Database Management System (ORDBMS). Its primary function is to store data, securely and supporting best practices, and retrieve it later, as requested by other software applications, be it those on the same computer or those running on another computer across a network (including the Internet).
Postgres comes with plenty of drivers for the developer to use it through the most used languages, such as Java, C++, PHP, Python, .NET languages, and even Common LISP.
In this article, I will show how to use use Postgres through Java, i.e. connecting to the database, creating tables through Java code, execute search (select) queries and inserting/modifying the records on the table.
These are the steps I will follow in this article:
- Download and install PostgreSQL and pgAdmin 3
- Use pgAdmin 3 to create databases and tables
- Connect to the PostgreSQL database using Java code
- Create a simple Helper class to communicate with the database
Obtaining and installing PostgreSQL
You can obtain Postgres for free from its website together with the JDBC drivers. You can install it on Windows, Mac OS X, Linux, Solaris and BSD Unix.
Run the installer and you will see the above ‘Welcome’ screen to the installer. Click Next and you will see the next screen, where you would have to choose the directory where PostgreSQL will be installed.
On the next screen you will choose the directory where to store the data. Normally (and by default) this would be %POSTGRESQL_HOME%/data
After choosing the data directory, specify the password for the database superuser, whose username is “postgres” by default. I’m setting it just “password”.
After defining your password, you should select the port where to access PostgreSQL. By default, it is 5432 but you can choose any free port on your machine.
Next, you have to define the locale that will be used by the database cluster. PostgreSQL uses the standard ISO C and POSIX locale facilities provided by the server operating system, but you can choose from a list of locales. It will contain information regarding alphabets, sorting, number formatting, etc.
After you specified the locale, stand back while the installer does its work.
In the end of the process, you will have PostgreSQL and pgAdmin III installed on your machine.
Using pgAdmin III to create databases and tables
pgAdmin is a tool that helps working with PostgreSQL. It allows you to create databases, tables, manipulate data, etc. with a simple user interface.
Before using pgAdmin, you must connect to the database, by right-clicking PostgreSQL node and then clicking Connect, and entering the password when prompted.
To add a new database you right-click at the Databases and click New Database. Now, create a database called try_postgre:
You will see that a new node is added, representing the new database. To add a table, you expand the try_postgre node, then Schemas, then public and right-click the Tables node and then click New Table.
On the Properties tab specify the name of the table, which will be people. Then, switch to the Columns tab and add a new column by clicking the Add button.
You specify the name of the column and the data type (in some cases even the length). In the above case, serial is an auto-incrementable integer. Now, add columns until you get this:
Now you click OK to create the table. I would like to make ID primary key. You can do this by right-clicking Constraints on people table and then New Object > New Primary Key. On the dialog, switch to Columns tab, select column id, click Add, and then OK. This would set the id column as the primary key.
This whole process, which is kinda long, is the same as executing this query:
CREATE TABLE people ( id serial NOT NULL, name character(20), surname character(20), age integer, CONSTRAINT people_pkey PRIMARY KEY (id) ) WITH ( OIDS=FALSE ); ALTER TABLE people OWNER TO postgres;
To execute SQL queries, you can use the Query tool by clicking Tools > Query tool or simply CTRL+E.
You can use this Query tool to add data into the table, by executing this:
insert into people (name, surname, age) values ('Bruce','Scott',65);
insert into people (name, surname, age) values ('John','Doe',40);
insert into people (name, surname, age) values ('Jane','Doe',35);
Connecting to Postgres
Create an interface called DbContract in a .db package, and put this code into it:
DbContract.java
package com.javacodegeeks.examples.db;
public interface DbContract {
public static final String HOST = "jdbc:postgresql://localhost:5432/";
public static final String DB_NAME = "try_postgre";
public static final String USERNAME = "postgres";
public static final String PASSWORD = "password";
}
You may want to put your username and password there. We define these “global constants” in order to keep the code DRY (Don’t Repeat Yourself), not WET (Write Everything Twice).
Now you can go on creating the class TestConnection which will test the connection with the database:
TestConnection.java
package com.javacodegeeks.examples;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import com.javacodegeeks.examples.db.DbContract;
public class TestConnection {
public static void main(String[] args) {
try {
Class.forName("org.postgresql.Driver");
Connection c = DriverManager.getConnection(
DbContract.HOST+DbContract.DB_NAME,
DbContract.USERNAME,
DbContract.PASSWORD);
System.out.println("DB connected");
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
}
}
Don’t forget to add the required drivers in the path, otherwise the connection won’t success and the code won’t work. If the host, database name, username and password are correct, and the drivers are added, then the output would be:
DB connected
Otherwise, a PSQLException will throw.
Now, we are going to simplify a bit our work with Postres by creating a helper class called PostgresHelper. Put it in the db package and put this code into it:
PostgresHelper.java
package com.javacodegeeks.examples.db;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class PostgresHelper {
private Connection conn;
private String host;
private String dbName;
private String user;
private String pass;
//we don't like this constructor
protected PostgresHelper() {}
public PostgresHelper(String host, String dbName, String user, String pass) {
this.host = host;
this.dbName = dbName;
this.user = user;
this.pass = pass;
}
public boolean connect() throws SQLException, ClassNotFoundException {
if (host.isEmpty() || dbName.isEmpty() || user.isEmpty() || pass.isEmpty()) {
throw new SQLException("Database credentials missing");
}
Class.forName("org.postgresql.Driver");
this.conn = DriverManager.getConnection(
this.host + this.dbName,
this.user, this.pass);
return true;
}
}
I put the protected constructor on line 16 in order to not allow the creation of an empty/default helper instance. So, the user is forced to use the second constructor.
The connect() method is the one that creates the connection with the database. It returns true if the connection is done successfully, otherwise it throws an SQLException.
To use it, create a class called Main and put this code into it:
Main.java
package com.javacodegeeks.examples;
import java.sql.SQLException;
import com.javacodegeeks.examples.db.DbContract;
import com.javacodegeeks.examples.db.PostgresHelper;
public class Main {
public static void main(String[] args) {
PostgresHelper client = new PostgresHelper(
DbContract.HOST,
DbContract.DB_NAME,
DbContract.USERNAME,
DbContract.PASSWORD);
try {
if (client.connect()) {
System.out.println("DB connected");
}
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
}
}
You can see on lines 12-16 how the instance is created, and how the connection is done on line 19. Since the credentials are OK, it will show the same output as the first class.
Selecting data in PostgreSQL
To execute SQL queries, we will create a method called execQuery() in our PostgreHelper class:
public ResultSet execQuery(String query) throws SQLException {
return this.conn.createStatement().executeQuery(query);
}
Add this to the class, and after doing that, add this piece of code after the connection is made at Main class:
ResultSet rs = client.execQuery("SELECT * FROM people");
while(rs.next()) {
System.out.printf("%d\t%s\t%s\t%d\n",
rs.getInt(1),
rs.getString(2),
rs.getString(3),
rs.getInt(4));
}
The method does only execute the SQL statement that is passed as parameter, and may throw an SQLException if the parameter is not a valid SQL statement.
The output of that will be:
1 Bruce Scott 65 2 John Doe 40 3 Jane Doe 35
The same would happen if you pass a query like "SELECT * FROM people WHERE surname = 'Doe'":
2 John Doe 40 3 Jane Doe 35
Inserting into the PostgreSQL
Now we will write a method to insert data into the Postgres database. This method will be insert(), so add it to the PostgresHelper class:
public int insert(String table, Map values) throws SQLException {
StringBuilder columns = new StringBuilder();
StringBuilder vals = new StringBuilder();
for (String col : values.keySet()) {
columns.append(col).append(",");
if (values.get(col) instanceof String) {
vals.append("'").append(values.get(col)).append("', ");
}
else vals.append(values.get(col)).append(",");
}
columns.setLength(columns.length()-1);
vals.setLength(vals.length()-1);
String query = String.format("INSERT INTO %s (%s) VALUES (%s)", table,
columns.toString(), vals.toString());
return this.conn.createStatement().executeUpdate(query);
}
This method takes two parameters. The first one is a string, the table where we will insert the data. The other one is a Map instance which maps column names (Strings) to values (Objects) that will be added to the table.
By using StringBuilders I create a string for all the column names, and another one for values, putting every String value between single quotation marks (‘ and ‘).
After that, I make sure to delete the last comma from both strings, by decreasing the length of both StringBuilders by 1. Then, I execute the query formed by calling executeUpdate().
To test this, write this code after the connection is made in the Main class:
Map vals = new HashMap();
vals.put("id", 4);
vals.put("name", "Aldo");
vals.put("surname", "Ziflaj");
vals.put("age", 19);
if (client.insert("people", vals) == 1) {
System.out.println("Record added");
}
You can put your data instead of mine at the HashMap. If the ID of 4 is not duplicated, you will see Record added. Otherwise, an SQLException will be thrown.
This method would add only one record in the specified table. If you want to add a list of them, you may create a similar method, e.g. insertList(), to call the insert() method at every item of the list/array.
MySQL VS PostgreSQL
MySQL vs PostgreSQL is a decision many must make when approaching open-source relational database management systems. Both are time-proven solutions that compete strongly with proprietary database software.
MySQL has long been assumed to be the faster but less full-featured of the two database systems, while PostgreSQL was assumed to be a more densely featured database system often described as an open-source version of Oracle. MySQL has been popular among various software projects because of its speed and ease of use, while PostgreSQL has had a close following from developers who come from an Oracle or SQL Server background.
Though, nowadays the two Relational Database Management Systems are less different. MySQL is updated to more features and functionality, and PostgreSQL has improved its speed.
When you have to choose between MySQL and PostgreSQL, you must keep in mind that:
- MySQL doesn’t try to be compatible with SQL standards. If you are going to switch database during development, or try to integrate with any other RDBMS, you may face issues.
- Concurrency of MySQL is not the best you can find. It is fast with read operations, but it has some issues with concurrent read-writes.
- Speed is not the greatest asset of PostgreSQL. If you decided to use PostgreSQL, considering this, better invest some on the machine that runs the database.
- PostgreSQL might be an overkill for simple things. While it is good with data integrity and ACID (Atomicity, Consistency, Isolation, Durability), it can be obsolete using it for simple, small applications
Download Code
You can download the full source code of this example here : JavaPostgreExample
can you provide CRUD operation using spring mvc and hibernate by using postgresql