Kristian Lunde

www.klunde.net

Archive for the ‘Databases’ Category

Hello Orange Bus

without comments

Last week I got a job in Newcastle :D

I will step into the position as “Head of development” at Orange Bus in November.

I really look forward to join the talented team at Orange Bus. Have a look at http://www.orangebus.co.uk where you can see some of their work.

Written by Kristian Lunde

September 1st, 2008 at 3:51 pm

Posted in PostgreSQL, Real Life

Tagged with ,

Cheat sheets

without comments

When talking about cheat sheets, www.ilovejackdaniels.com has a list of a few good cheat sheets. I’ve actually printed some of them at work and taped them to the wall next to my computer.

Thanks to Dave Child for doing the work of putting together these sheets.

URL: http://www.ilovejackdaniels.com/cheat-sheets/

Written by Kristian Lunde

May 12th, 2008 at 9:19 pm

Posted in Databases, Misc, PHP, web development

Tagged with , , ,

PostgreSQL cheat sheet

without comments

I often have a blank hole or two when it comes to the basic PostgreSQL commands, and have to do a look up in the documentation, Kitt Hodsden has posted a nice little cheat sheet with the most common basic postgreSQL commands.

URL: http://kitt.hodsden.org/postgresql

Written by Kristian Lunde

May 12th, 2008 at 9:11 pm

Posted in Databases, PostgreSQL

Tagged with

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.

Written by Kristian Lunde

May 5th, 2008 at 10:14 pm

Posted in Java, Linux, PostgreSQL, Programming

Tagged with , ,