Reading from database postgresql in java and record of result in ArrayList

Question

I decided to remove the intermediate link and as a whole to simplify the decision, having transferred everything to the java program. There is a code on С++, reading of database postgresql

#include <iostream>

//using namespace std;

#include <stdio.h>
#include "libpq-fe.h"
#include <string>
#include <cstdio>
#include <stdlib.h>


int     main() {
    PGconn          *conn;
    PGresult        *res;
    int             rec_count;
    int             row;
    int             col;
    FILE            *stream;

    conn = PQconnectdb("hostaddr=192.168.143.93 port=5432 connect_timeout=10 dbname=NexentaSearch user=postgres password=postgres");
    if (PQstatus(conn) == CONNECTION_BAD) {
        fprintf(stderr, "Connection to database failed: %s\n",PQerrorMessage(conn));
     puts("No connection");
        exit(0);
    }

    res = PQexec(conn, "select path from tasks order by id");

    if (PQresultStatus(res) != PGRES_TUPLES_OK) {
        printf("We didn't get the data");
        exit(0);
    }

    rec_count = PQntuples(res);

I want to execute the same request to postgresql to a database on Java and to write results in ArrayList . Help to make it please.

Read the [JDBC tutorial](http://docs.oracle.com/javase/tutorial/jdbc/basics/), the try something and come back here if you have a concrete problem. — JB Nizet, Apr 21 '13 at 15:08

score 0 · Accepted Answer · answered Apr 21 '13 at 15:13

0

Look this posts:

answered Apr 21 '13 at 15:13

Aliaksei Bulhak

6,078
8
45
75

score 0 · Answer 2 · edited May 23 '17 at 11:50

You have two problems:

The first one is not hard. Start with an interface for a DAO:

public interface PathDao {
    List<String> findAllPaths(); 
}

The create an implementation:

public class PathDaoImpl implements PathDao {
    private static final String FIND_ALL_PATHS_QUERY = "select path from tasks order by id"; 
    private DataSource dataSource;

    public PathDaoImpl(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    public List<String> findAllPaths() {
        List<String> paths = new ArrayList<String>();
        Statement st = null;
        ResultSet rs = null;
        try {
            st = this.dataSource.getConnection().createStatement(FIND_ALL_PATHS_QUERY);
            rs = st.executeQuery();
            paths = mapPathQuery(rs);
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            DatabaseUtils.close(rs);
            DatabaseUtils.close(st);
        }    
        return paths;
    }

    private List<String> mapPathQuery(ResultSet rs) throws SQLException {
        List<String> paths = new ArrayList<String>();
        while (rs.next()) {
            paths.add(rs.getString("path");
        }
        return paths; 
    }
}

The second one is pretty easy, because you're asking for a single String column value.

public class DatabaseUtils {
    private DatabaseUtils() {}

    public static void close(Statement st) {
        try {
            if (st != null) {
                st.close();
            }
        } catch (Exception e) {
            e.printStackTrace(); // logging is a better solution
        }
    }

    public static void close(ResultSet rs) {
        try {
            if (rs != null) {
                rs.close();
            }
        } catch (Exception e) {
            e.printStackTrace(); // logging is a better solution
        }
    }
}

Reading from database postgresql in java and record of result in ArrayList

2 Answers2