Monday, January 12, 2015

GlassFish: Connection Pool Setup for PostgreSQL


In this post we are gonna explain how to make a JDBC data source registered over JNDI to access it from a web app.  After a DataSource object is registered within JNDI, an application can use the JNDI API to access it and connect to the datasource it represents.

You need install PostgreSQL and Glassfish, in this example I installed:  

glassfish 4.0
psql (PostgreSQL) 9.1.14



We will explain the required settings step-by-step:
    • Copy the previous driver into [glassfish_home]/glassfish/domains/domain1/lib/. In this example /opt/glassfish4/glassfish/domains/domain1/lib/
    • Access to the administration panel in Glassfish (http://localhost:4848). Go to the tab Resources/JDBC/JDBC Connection Pools:
      • Create a new connection pool and select the resource type javax.sql.DataSource, database vendor PostgreSQL and click next.
      • As datasource classname enter org.postgresql.ds.PGSimpleDataSource and insert the next additional properties:
                      User
                      DatabaseName
                      Password
                      ServerName (127.0.0.1)
                      Url  (something similar to this one: jdbc:postgresql://127.0.0.1:5432/preferences)
                      PortNumber (5432 is the default port)

      • You can test the connection using the button "Ping".
    • In order to be able to use this connection pool in java applications, it needs a JNDI registration. Go to the tab Resources/JDBC/JDBC Resources and configure the new resource. Set the JNDI name jdbc/[database_name] and select the connection pool previously created. This JNDI name will be used by applications to access the PostgreSQL database.



    Thursday, January 08, 2015

    Importing data from a CSV file into a Postgres table


    You have a CSV file called “data.csv”. The values of the different columns are delimited using “,” You want to import this data into Postgres, in a table called 'example'

    In postgres execute this command:

    COPY example FROM '/path/to/data.csv' WITH DELIMITER ',' CSV HEADER;

    If you have in the table the columns user_id, item_id and rating and in your csv you have:

    2,5,3.0
    2,123, 4.0

    Then in your table you will have:

    user_id   item_id    rating
    2               5           3.0
    2              123        4.0