}
+ @Test
+ public void concatTest()
+ {
+
+ Connection connection = null;
+ try
+ {
+ // create a database connection
+ connection = DriverManager.getConnection("jdbc:sqlite::memory:");
+ Statement statement = connection.createStatement();
+ statement.setQueryTimeout(30); // set timeout to 30 sec.
+
+ statement.executeUpdate("drop table if exists person");
+ statement.executeUpdate("create table person (id integer, name string, shortname string)");
+ statement.executeUpdate("insert into person values(1, 'leo','L')");
+ statement.executeUpdate("insert into person values(2, 'yui','Y')");
+ statement.executeUpdate("insert into person values(3, 'abc', null)");
+
+ statement.executeUpdate("drop table if exists message");
+ statement.executeUpdate("create table message (id integer, subject string)");
+ statement.executeUpdate("insert into message values(1, 'Hello')");
+ statement.executeUpdate("insert into message values(2, 'World')");
+
+ statement.executeUpdate("drop table if exists mxp");
+ statement.executeUpdate("create table mxp (pid integer, mid integer, type string)");
+ statement.executeUpdate("insert into mxp values(1,1, 'F')");
+ statement.executeUpdate("insert into mxp values(2,1,'T')");
+ statement.executeUpdate("insert into mxp values(1,2, 'F')");
+ statement.executeUpdate("insert into mxp values(2,2,'T')");
+ statement.executeUpdate("insert into mxp values(3,2,'T')");
+
+ ResultSet rs = statement
+ .executeQuery("select group_concat(ifnull(shortname, name)) from mxp, person where mxp.mid=2 and mxp.pid=person.id and mxp.type='T'");
+ while (rs.next())
+ {
+ // read the result set
+ assertEquals("Y,abc", rs.getString(1));
+ }
+ rs = statement
+ .executeQuery("select group_concat(ifnull(shortname, name)) from mxp, person where mxp.mid=1 and mxp.pid=person.id and mxp.type='T'");
+ while (rs.next())
+ {
+ // read the result set
+ assertEquals("Y", rs.getString(1));
+ }
+
+ PreparedStatement ps = connection
+ .prepareStatement("select group_concat(ifnull(shortname, name)) from mxp, person where mxp.mid=? and mxp.pid=person.id and mxp.type='T'");
+ ps.clearParameters();
+ ps.setInt(1, new Integer(2));
+ rs = ps.executeQuery();
+ while (rs.next())
+ {
+ // read the result set
+ assertEquals("Y,abc", rs.getString(1));
+ }
+ ps.clearParameters();
+ ps.setInt(1, new Integer(2));
+ rs = ps.executeQuery();
+ while (rs.next())
+ {
+ // read the result set
+ assertEquals("Y,abc", rs.getString(1));
+ }
+
+ }
+ catch (SQLException e)
+ {
+ // if the error message is "out of memory",
+ // it probably means no database file is found
+ System.err.println(e.getMessage());
+ }
+ finally
+ {
+ try
+ {
+ if (connection != null)
+ connection.close();
+ }
+ catch (SQLException e)
+ {
+ // connection close failed.
+ System.err.println(e);
+ }
+ }
+
+ }
+
}