Tag: postgresl

Below are the steps to setup a connection to a Postgres DB and some other options that you can use.

Pom.xml:

<dependency>
	<groupId>org.postgresql</groupId>
	<artifactId>postgresql</artifactId>
	<version>9.4.1208.jre7</version>
</dependency>

Import:

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

Build The Connection:

Class.forName("org.postgresql.Driver");
Connection connection = DriverManager.getConnection(##URL##, ##USER##, ##PASS##);

Preparing the Query:
We utilise “PreparedStatement” to setup the connection. Which will allow us to use parameters in the query.

PreparedStatement ps = connection.prepareStatement("select id from table where column=?")

If your query had parameters (IE: ?) then you will need to pass in the value for each parameter. If you notice there is ##POSITION## and ##VALUE##. Position is the location of where the parameter appears in the query. You can set various types of data for example integer, json, etc.

ps.setString(##POSITION##, ##VALUE##);

After we perform a query we need to retrieve the data. We use “ResultSet” for that. Depending on how we return the data depends on how we get the data after the query succeeds. Below are examples of one line returned vs multiple rows returned.

ResultSet rs = ps.executeQuery();

if (rs.next()) {
	int variable = rs.getInt("id");
}

while (rs.next()) {
	//Do Something
}

Insert:
If you want to perform an insert you don’t need to do “executeQuery” you can call just “execute”.

ps = connection.prepareStatement("insert into mytable (column) values (?)");
ps.setInt(##POSITION##, ##VALUE##);
ps.execute();

Batching:
Sometimes we have a lot of updates or inserts to perform. We should batch that.

//You setup the preparedStatement and then add to the batch.
ps.addBatch();

//You can set a max batch size to send the batch once it hits that amount
if (++count % batchSize == 0) {
	ps.executeBatch();
}

Passing Json as a Parameter:
We create the postgres object. Set the type to json and the value as a string in json format. Next we set the preparedStatement parameter as the postgres object that we just created.

final PGobject jsonObject = new PGobject();
jsonObject.setType("json");
jsonObject.setValue(value.toString());

//Now set the json object into the preparedStatement
ps.setObject(##POSITION##, jsonObject);