Saturday, January 26, 2008

» Loading a JDBC Driver at runtime

In the context of a nice mini-framework (just a few classes actually) for integration tests for Java using JUnit, DBUnit (for database initialisation and comparison against expected data) and Spring, I found myself confronted with somewhat less elegant requirement of having to specify the JDBC Driver jar filename in the CLASSPATH and having to change it depending on the target database. Whereas changing the JDBC database URL, username and password is easy (just using Spring's PropertyPlaceholderConfigurer and a .properties file), changing the CLASSPATH is annoying, because it has to be changed in the Eclipse build path (e.g. using a classpath variable) as well as in the build configuration (be it Ant or Maven). I wanted to specify the filename of the JDBC Driver jar in the same .properties file as the JDBC URL, username and password. While this may sound trivial to some, it isn't, because you cannot load jars at runtime using the default ClassLoader. This code snippet shows how one can (ab)use the URLClassLoader to load jars at runtime. But the problem is that I didn't want to set a new default ClassLoader nor pass JVM parameters at startup, i.e. use the pristine Eclipse and Ant/Maven environment and do it purely through Java code at runtime. The trick is quite simple, actually: 1) write a delegate implementation of JDBC's java.sql.Driver class, that passes each method call to a static (singleton) Driver 2) use the name of the delegate class above as the name of the JDBC Driver class 3) set the static Driver in the delegate Driver class above to an instance of the real JDBC Driver class Let's start with the delegate:
package sample; import java.sql.Connection; import java.sql.Driver; import java.sql.DriverManager; import java.sql.DriverPropertyInfo; import java.sql.SQLException; import java.util.Properties; public class DelegateDriver implements Driver {          static {         try {             DriverManager.registerDriver(new DelegateDriver());         catch (SQLException e) {             throw new RuntimeException(new StringBuffer()             .append("failed to register ").append(DelegateDriver.class.getName())             .append(" with the JDBC ").append(DriverManager.class.getName())             .append(": ").append(e.getMessage()).toString(), e);         }     }          public static Driver DELEGATE = null;     private static Driver getDelegate() {         if (DELEGATE == null) {             throw new IllegalStateException("delegate driver not set");         }         return DELEGATE;     }          public boolean acceptsURL(String urlthrows SQLException {         return getDelegate().acceptsURL(url);     }     public Connection connect(String url, Properties infothrows SQLException {         return getDelegate().connect(url, info);     }     public int getMajorVersion() {         return getDelegate().getMajorVersion();     }     public int getMinorVersion() {         return getDelegate().getMinorVersion();     }     public DriverPropertyInfo[] getPropertyInfo(String url, Properties info)             throws SQLException {         return getDelegate().getPropertyInfo(url, info);     }     public boolean jdbcCompliant() {         return getDelegate().jdbcCompliant();     } }
And here is the class to use to configure the actual JDBC Driver class as well as the JDBC driver jar file, shaped as a Spring-ready singleton bean:
package sample; import java.io.File; import java.net.URL; import java.net.URLClassLoader; import java.sql.Driver; public class JDBCDriverLoader {          private String jdbcDriverClass;     private File jdbcDriverFile;          /** Configure using Spring or Java code: */     public void setJdbcDriverFile(File jdbcDriverFile) {         this.jdbcDriverFile = jdbcDriverFile;     }          /** Configure using Spring or Java code: */     public void setJdbcDriverClass(String jdbcDriverClass) {         this.jdbcDriverClass = jdbcDriverClass;     }     public void initialize() throws Exception {         // TODO throw IllegalStateException if jdbcDriverFile or jdbcDriverClass is null         DelegateDriver.DELEGATE = (Drivernew URLClassLoader(new URL[]{}this.getClass().getClassLoader()) {{             // Have to use a subclass because addURL() is protected.             // See http://snippets.dzone.com/posts/show/3574             addURL(new URL("jar:file://" + jdbcDriverFile.getPath() "!/"));         }}.loadClass(jdbcDriverClass).newInstance();     }      }
All you need to do now is to use sample.DelegateDriver as the name of the JDBC Driver class (e.g. in your Apache Commons DBCP connection pool). Jumping through those hoops is needed because it's a different ClassLoader. I'll leave the rest of the glue as an exercise to the reader ;)

Labels:

» Aggregate join results with MySQL using GROUP_CONCAT

An ever recurring task with relational databases is to select data from a table and a list of values coming from another table (an 1-n navigation on an n-m relationship). Say you have a table person and a table role, defined as follows:
CREATE TABLE person (
  id INTEGER NOT NULL PRIMARY KEY,
  name VARCHAR(255) NOT NULL
);

CREATE TABLE role (
  id INTEGER NOT NULL PRIMARY KEY,
  role VARCHAR(255) NOT NULL
);
For the sake of simplicity, I'll omit real-world fields in the person table, as well as UNIQUE constraints. There, you'd typically want to have a relationship table to bind person rows to role rows, as you can't directly model n-m relationships. Let's name it person_role, aggregating 1-n relationships to person and role:
CREATE TABLE person_role (
  person_id INTEGER NOT NULL,
  role_id INTEGER NOT NULL,
  PRIMARY KEY (person_id, role_id),
  FOREIGN KEY (person_id) REFERENCES person.id,
  FOREIGN KEY (role_id) REFERENCES role.id
);
Let's insert some data for the same of the example:
INSERT INTO role VALUES(1, 'admin');
INSERT INTO role VALUES(2, 'developer');
INSERT INTO role VALUES(3, 'user');
INSERT INTO person VALUES(1, 'John Doe');
INSERT INTO person VALUES(2, 'Scott Tiger');
INSERT INTO person_role VALUES(1, 1);
INSERT INTO person_role VALUES(1, 2);
INSERT INTO person_role VALUES(1, 3);
The content of the tables is now:
mysql> select * from person;
+----+-------------+
| id | name        |
+----+-------------+
|  1 | John Doe    | 
|  2 | Scott Tiger |
+----+-------------+
mysql> select * from person_role;
+-----------+---------+
| person_id | role_id |
+-----------+---------+
|         1 |       1 | 
|         1 |       2 | 
|         1 |       3 | 
+-----------+---------+
mysql> select * from role;
+----+-----------+
| id | role      |
+----+-----------+
|  1 | admin     | 
|  2 | developer | 
|  3 | user      | 
+----+-----------+
What we want to do now is to retrieve every person with its associated roles. You basically have two options: 1) Only retrieve person rows and complete them with data from role one by one (needs to be done in the invoking programming language, cannot be done with SQL), here using some sort of python-ish pseudocode:
persons = SELECT id, name FROM person ORDER BY id;
for p in persons:
   p.roles = [ SELECT role FROM person_role
                 INNER JOIN role ON person_role.role_id=role.id
                 WHERE person_id=p.id ]
2) Select everything at once, and reduce duplicates in your invoking code:
SELECT person.id AS id, name, role FROM person
  LEFT OUTER JOIN person_role ON person.id=person_role.person_id
  LEFT OUTER JOIN role ON person_role.role_id=role.id
  ORDER BY person.id; 
That'll give you lots of duplicates and you'll have to sort it out yourself in your Java/PHP/Python/Ruby/C++/C/C# code. The result from the query above would look something like this:
+----+-------------+-----------+
| id | name        | role      |
+----+-------------+-----------+
|  1 | John Doe    | admin     | 
|  1 | John Doe    | developer | 
|  1 | John Doe    | user      | 
|  2 | Scott Tiger | NULL      | 
+----+-------------+-----------+
Note that you need to use LEFT OUTER JOIN to also get results from person rows that don't have any roles assigned through person_role. Neither of these solutions are all that satisfactory. Well, with MySQL's GROUP_CONCAT function, there's a third option:
SELECT person.id AS id, name, GROUP_CONCAT(role.role SEPARATOR ',') AS roles
  FROM person
  LEFT OUTER JOIN person_role ON person.id=person_role.person_id
  LEFT OUTER JOIN role ON person_role.role_id=role.id
  GROUP BY id
  ORDER BY id;
The result of the query above is:
+----+-------------+----------------------+
| id | name        | roles                |
+----+-------------+----------------------+
|  1 | John Doe    | admin,developer,user | 
|  2 | Scott Tiger | NULL                 | 
+----+-------------+----------------------+
Neat ;) When you use EXPLAIN to compare the query optimizer strategy for option 2 and 3, it does exactly the same operations. It's just that you can reduce it into a single statement, and avoid having to reduce the result rows into single objects (you just need to split the roles result column using the separator you specified in the query). Here's what EXPLAIN on solution 2 and 3 gives us:
+----+-------------+-------------+--------+---------------+---------+---------+--------------------------+------+----------------+
| id | select_type | table       | type   | possible_keys | key     | key_len | ref                      | rows | Extra          |
+----+-------------+-------------+--------+---------------+---------+---------+--------------------------+------+----------------+
|  1 | SIMPLE      | person      | ALL    | NULL          | NULL    | NULL    | NULL                     |    2 | Using filesort | 
|  1 | SIMPLE      | person_role | ref    | PRIMARY       | PRIMARY | 4       | play.person.id           |    1 | Using index    | 
|  1 | SIMPLE      | role        | eq_ref | PRIMARY       | PRIMARY | 4       | play.person_role.role_id |    1 |                | 
+----+-------------+-------------+--------+---------------+---------+---------+--------------------------+------+----------------+

Labels:

Sunday, January 20, 2008

» FOSDEM 2008: first batch of speaker interviews

Tuesday, January 15, 2008

» Hardware issues on opensuse.org

Adrian announced hardware failures on a large server hosting part of opensuse.org today. Affected resources are: Yep, the latter means the redirector is down too. Read Adrian's email (see link above) for the details. In the mean time, you can still use a mirror:

Labels:

Monday, January 14, 2008

» webpin CLI 0.9.0, and faster server

We have a shiny new server for Benjamin Weber's "webpin" (search engine for openSUSE packages that indexes pretty much all repositories, including the openSUSE Build Service repos). It's a lot faster than the previous one. Therefore, I warmly recommend upgrading to webpin-0.9.0 that uses the new server for package search queries. The webpin CLI package is available from the openSUSE:Tools repository. For the web interface, make sure to use the following URL: http://packages.opensuse-community.org I have also updated my openSearch configuration files to use the new URL.

Labels: ,

Sunday, January 06, 2008

» Conspiracy Theorist

Interesting post by Bruce on Conspiracy Theorists and Free Software. I agree with most if not all he wrote in that article. But, while I was reading it, it immediately made me think of one person. Bet you'll have the same experience ? Have a read. OK. Found ? Yes, him ! Oh well, agreed, that was quite obvious.

Labels: