Kristian Lunde

www.klunde.net

My first introduction to java and databases

with 2 comments

The other day I did a complete reconfiguration of java on my system. Tonight I started to play around with java and postgresql. I installed PostgreSQL 8.2 (yes I know 8.3 is out there), and downloaded the jdbc driver for PosgreSQL.

PostgreSQL

After installing PostgreSQL, you have to add a user to the authorization file pg_hba.conf located in /etc/postgresql/8.x/main on a ubuntu system, remember to restart the postgreSQL server after editing pg_hba.conf. When that is done add the same user to the postgreSQL server either by using postgreSQL’s createuser function or by logging into the postgreSQL server and doing a SQL CREATE USER, I did the latter one.

  1. CREATE USER kristian WITH PASSWORD 'xxxxxxxx' CREATEDB;

This enables me to do a regular access of the database with:

  1. kristian@Saturn:~$ psql -U kristian crawler
  2. Welcome to psql 8.2.7, the PostgreSQL interactive terminal.</code>
  3.  
  4. Type:  \copyright for distribution terms
  5. \h for help with SQL commands
  6. \? for help with psql commands
  7. \g or terminate with semicolon to execute query
  8. \q to quit
  9.  
  10. crawler=#

My table:

  1. crawler=# \d document;
  2. TABLE "public.document"
  3. COLUMN     |            Type             |                       Modifiers
  4. —————+—————————–+————————
  5. id            | integer                     | NOT NULL DEFAULT NEXTVAL('document_id_seq'::regclass)
  6. url           | text                        | NOT NULL
  7. document      | text                        | NOT NULL
  8. ts_downloaded | timestamp without time zone | DEFAULT now()

Data in the table:

  1. crawler=# select * from document;
  2. id |          url          | document |       ts_downloaded
  3. —-+———————–+———-+—————————-
  4. 1 | http://www.klunde.net | jalla    | 2008-04-19 21:11:11.805482

Java

First thing to get the postgreSQL jdbc driver to work is to add it to the java classpath, and of course copy the actual jar file to its correct location, for instance /usr/lib/jvm/java-6-sun-1.6.0.03/jre/ext/lib/which is my java library path.
My classpath:

  1. /usr/lib/jvm/java-6-sun-1.6.0.03/lib:/usr/lib/jvm/java-6-sun-1.6.0.03/jre/ext/lib/postgresql-8.3-603.jdbc4.jar:.

Now its about time to write some actual code:

  1. import java.sql.Connection;
  2. import java.sql.DriverManager;
  3. import java.sql.PreparedStatement;
  4. import java.sql.ResultSet;
  5. import java.sql.SQLException;
  6. import java.sql.Statement; </code>
  7.  
  8. public class DatabaseTest
  9. {
  10. private Connection connection = null;
  11.  
  12. private static final String USERNAME = "XXXXX";
  13. private static final String PASSWORD = "XXXXX";
  14. private static final String URL = "jdbc:postgresql:";
  15. private static final String DATABASE = "crawler";
  16.  
  17. public DatabaseTest()
  18. {
  19. try
  20. {
  21. connection = DriverManager.getConnection(URL + DATABASE, USERNAME, PASSWORD);
  22. ResultSet rs = doQuery("SELECT * FROM document");
  23.  
  24. rs.next();
  25. System.out.println(rs.getString(2));
  26. }
  27. catch (SQLException e)
  28. {
  29. System.out.println("Database Exception!");
  30. System.out.println(e.toString());
  31. e.printStackTrace();
  32. }
  33.  
  34. }
  35.  
  36. public ResultSet doQuery(String sql)
  37. {
  38. ResultSet rs;
  39. try
  40. {
  41. Statement stmt = connection.createStatement();
  42. rs = stmt.executeQuery(sql);
  43. return rs;
  44. }
  45. catch (SQLException e) {
  46. e.printStackTrace();
  47. return null;
  48. }
  49. }
  50. }

Trying to execute my script:

  1. kristian@Saturn:~/workspace/crawler$  javac DatabaseTest.java
  2. kristian@Saturn:~/workspace/crawler$  java -Djdbc.drivers=org.postgresql.Driver DatabaseTest
  3. http://www.klunde.net

It works :D The installation and initial setup went pretty much by the book, but as a PHP developer I must say that I’m a little bit disappointed, JDBC cannot compare itself to the PHP PDO extension yet. But then again, maybe I just need to get to know the JDBC library better. Anyhow it was fun writing this little java app and refreshing some of my java skills.

Be Sociable, Share!

Written by Kristian Lunde

May 5th, 2008 at 10:14 pm

2 Responses to 'My first introduction to java and databases'

Subscribe to comments with RSS or TrackBack to 'My first introduction to java and databases'.

  1. Simple and straight to the point, I’d suggest installing the Highlight Source Pro plugin for WordPress, so you can simply enclose the code in -tags.

    Check it out at: http://blog.kno.at/tools/highlight-source-pro/

    Mats Lindh

    5 May 08 at 22:35:43

  2. Post updated with the Highlight Source Pro plugin.

    Kristian Lunde

    6 May 08 at 11:54:45

Leave a Reply

Get Adobe Flash player