Java connect to postgres database

Working with PostgreSQL in Java — Using the JDBC Library

In this tutorial, we’ll learn how to connect to a Postgres database using JDBC, and run basic SQL queries in our Java application.

For our Java application, we’ll be using a maven project structure. You can see the complete source code for this example on Github

What Is JDBC?#

JDBC stands for Java Database Connectivity. It is a set of APIs that we can use to connect and execute queries on any compatible database, like PostgreSQL.

Most of the classes and methods that we’ll be using are a part of the java.sql and javax.sql packages, which come included as a part of Java SE.

1. We create a connection to the database
2. We send a query statement (something like SELECT * FROM mytable )
3. The database returns the results
4. We use the results in our application

The JDBC API contains classes that describe each of these steps:

1. The Connection class represents the connection that we make with our database.
2. PreparedStatement represents the query statement that will be executed.
3. The results are returned by the database as a ResultSet instance that contains our queried data.

In the next sections, we’ll look at some examples of how we can do this in practice.

Creating a Test Database#

Once you’ve installed Postgres you should have a Postgres instance running on our local machine ( localhost ) on port 5432 (which is the default for Postgres).

Now we can create the database and tables in the psql shell:

CREATE DATABASE bird_encyclopedia; \c bird_encyclopedia  CREATE TABLE birds (  id SERIAL PRIMARY KEY,  bird VARCHAR(256),  description VARCHAR(1024) ); 

We can also populate the birds table with some test data:

INSERT INTO birds (bird , description) VALUES ('pigeon', 'common in cities'), ('eagle', 'bird of prey'); 

Installing the Database Driver#

When using JDBC, we need to install the driver of the database we’re using. In this case, that would be the PostgreSQL JDBC driver, which we can add as a dependency to our pom.xml file:

   org.postgresql   postgresql   42.4.1  

The database driver is an implementation of the JDBC API interfaces, and allows our application to use JDBC classes to interact with the database:

Connecting to the Database — Creating a Data Source#

To execute queries, we need to create a connection to the database. However creating a connection is resource-intensive, and we normally use a DataSource interface, which acts as a factory that provides Connection instances.

We can create a new PGSimpleDataSource instance provided by the postgres driver library, which implements the DataSource interface:

import javax.sql.DataSource; import org.postgresql.ds.PGSimpleDataSource;  public class App   // We can create a method to create and return a DataSource for our Postgres DB  private static DataSource createDataSource()   // The url specifies the address of our database along with username and password credentials  // you should replace these with your own username and password  final String url =  "jdbc:postgresql://localhost:5432/bird_encyclopedia?user=dbadmin&password=my-secret-password";  final PGSimpleDataSource dataSource = new PGSimpleDataSource();  dataSource.setUrl(url);  return dataSource;  > > 

Now we can create Connection instances from our DataSource that we can use to run queries.

Reading Data#

Let’s look at some code that will execute a query to read all the entries present in the birds table:

// Use the method we defined earlier to create a datasource  DataSource dataSource = createDataSource();  // get a connection from the datasource  Connection conn = dataSource.getConnection();  // Create a new statement on the connection  PreparedStatement stmt = conn.prepareStatement("SELECT * FROM birds");  // Execute the query, and store the results in the ResultSet instance  ResultSet rs = stmt.executeQuery();  // We run a loop to process the results. // The rs.next() method moves the result pointer to the next result row, and returns // true if a row is present, and false otherwise // Note that initially the result pointer points before the first row, so we have to call // rs.next() the first time  while (rs.next())   // Now that `rs` points to a valid row (rs.next() is true), we can use the `getString`  // and `getLong` methods to return each column value of the row as a string and long  // respectively, and print it to the console  System.out.printf("id:%d bird:%s description:%s%n", rs.getLong("id"),  rs.getString("bird"), rs.getString("description")); > 

This will give the output:

id:1 bird:pigeon description:common in cities id:2 bird:eagle description:bird of prey 

When reading results, the ResultSet acts as a row-wise scanner. Initially, it points just before the first row, and returns false if no more rows exist.

In case your result doesn’t have any rows at all, it will return `false` the first time as well:

Adding Query Params#

In many cases, we need to pass in variables to our query. For example, if we want to filter a particular bird from our birds table, we can use the query:

SELECT * FROM birds WHERE bird = ? 

Here, we want to pass in the value of the bird filter from our Java code.

We can do this by setting parameters on our PreparedStatement instance:

// . // same as before // .  // Create a prepared statement with a query param denoted by "?"  PreparedStatement stmt = conn.prepareStatement("select * from birds where bird=?"); // Set the value of the param. Param indexes begin from 1 // Since we want to set the value as a string, we use `setString` // with the param index and param value as arguments  stmt.setString(1, "eagle");  // Execute the query and read the results same as before  ResultSet rs = stmt.executeQuery(); while (rs.next())   System.out.printf("id:%d bird:%s description:%s%n", rs.getLong("id"),  rs.getString("bird"), rs.getString("description")); > 

This will give us the output:

id:2 bird:eagle description:bird of prey 

Inserting Data#

Write operations, like inserting data, work a bit differently than SELECT queries. In this case, we don’t get any results back. Instead, Postgres returns the number of rows that are modified by the write operation.

Instead of using stmt.executeQuery() like before, we can use the stmt.executeUpdate() method to insert data. This method returns an int that denotes the number of rows that were modified.

Let’s look at an example where we insert a new bird into our table:

// Create a new insert statement with the bird and description values as query params  PreparedStatement insertStmt =  conn.prepareStatement("INSERT INTO birds(bird, description) VALUES (?, ?)");  // Set the query params  insertStmt.setString(1, "rooster"); insertStmt.setString(2, "wakes you up in the morning");  // Run the insert query using the `executeUpdate` method. // This returns the number of inserted rows  int insertedRows = insertStmt.executeUpdate(); // Print out the number of inserted rows  System.out.printf("inserted %s bird(s)%n", insertedRows); 

Now we will get the output:

And, if we check the table again, we can see that a new row was inserted:

> psql dbadmin=# \c bird_encyclopedia You are now connected to database "bird_encyclopedia" as user "dbadmin". bird_encyclopedia=# select * from birds; id | bird | description ----+---------+----------------------------- 1 | pigeon | common in cities 2 | eagle | bird of prey 3 | rooster | wakes you up in the morning 

Updating Data#

We can use the executeUpdate method along with query params to execute an UPDATE query statement:

// Similar to the previous example, we can use query params to fill in the condition // as well as the value to update  PreparedStatement updateStmt =  conn.prepareStatement("UPDATE birds SET description = ? WHERE bird = ?"); updateStmt.setString(1, "has a red crown"); updateStmt.setString(2, "rooster"); int updatedRows = updateStmt.executeUpdate(); System.out.printf("updated %s bird(s)%n", updatedRows); 

In this case, we will get the number of updated rows as a return value from executeUpdate .

Now we will get the output:

Now if we check the table again, we can see the updated data for the rooster entry:

bird_encyclopedia=# select * from birds; id | bird | description ----+---------+------------------ 1 | pigeon | common in cities 2 | eagle | bird of prey 3 | rooster | has a red crown 

Deleting Data#

Since DELETE queries also modify data, we should use executeUpdate for this as well:

// Similar to the previous example, we can use query params to fill in the delete condition  PreparedStatement deleteStmt = conn.prepareStatement("DELETE FROM birds WHERE bird = ?"); deleteStmt.setString(1, "rooster"); int deletedRows = deleteStmt.executeUpdate(); System.out.printf("deleted %s bird(s)%n", deletedRows); 

Now we will get the output:

Our table will now only have the original two entries with the rooster entry removed:

bird_encyclopedia=# select * from birds; id | bird | description ----+---------+------------------ 1 | pigeon | common in cities 2 | eagle | bird of prey 

Handling Sql Exceptions#

The java.sql package specifies a SQLException class that is thrown if the database faces an error.

This error could be anything from a syntax error to a connection failure. We can determine the type of error in our Java program by handling the SQLException .

We can use the getSQLState method to determine the error code. We can decide what action to perform based on the error code. For example, you may want to retry the query if there is a temporary connection error, but report the exception if there is a syntax error:

public class App   public static void main(String[] args) throws Exception    DataSource dataSource = createDataSource();  Connection conn = dataSource.getConnection();   try   insertBird(conn);  > catch (SQLException e)   String errorCode = e.getSQLState();  // 08000 - connection_exception  if (errorCode == "08000")   // retry query after re-establishing connection  >  // 42601 - syntax error  else if (errorCode == "42601")   // throw error so that we can see the failure  throw e;  > else   // log a warning, or do some other action based on the error code  System.out.printf("SQL failed with error code: %s%n", errorCode);  >   >   >   private static DataSource createDataSource()   final String url =  "jdbc:postgresql://localhost:5432/bird_encyclopedia?user=dbadmin&password=my-secret-password";  final PGSimpleDataSource dataSource = new PGSimpleDataSource();  dataSource.setUrl(url);  return dataSource;  >   // add a new bird to the table - this should throw an SQLException because there is a  // spelling error for the "description" column  private static void insertBird(Connection conn) throws SQLException   PreparedStatement insertStmt =  // ⬇ typo  conn.prepareStatement("INSERT INTO birds(bird, descsription) VALUES (?, ?)");  insertStmt.setString(1, "rooster");  insertStmt.setString(2, "wakes you up in the morning");  int insertedRows = insertStmt.executeUpdate();  System.out.printf("inserted %s bird(s)%n", insertedRows);  >  > 

Conclusion#

In this tutorial, we covered the basics on how to connect and execute queries on a PostgreSQL database. In practical applications, we’d probably integrate our database connection with a web application or data processing job.

You can view and run the complete example code on Github.

If you want to know more about the different classes available within JDBC, you can look at the java.sql module documentation.

You can also view the documentation for the PostgreSQL JDBC driver to see postgres-specific classes.

Источник