How to SELECT data from in-memory Derby database table?

Question

I have created an in-memory database table using JavaDB/Derby. I can INSERT data into the table. But it doesn't work when I try to SELECT data from the table.

I create the table with:

DECLARE GLOBAL TEMPORARY TABLE SESSION.memtable (id int, name varchar(10))
NOT LOGGED

insert data with:

INSERT INTO SESSION.memtable (id, name) VALUES (?,?)

and it returns 1 for rows affected. I select data with:

SELECT name FROM SESSION.memtable WHERE id = ?

but it return an empty ResultSet (rs.next() is false).

What am I doing wrong when using SELECT?

I use the derby.jar that is included in JDK7.

Here is my code:

public class DBTest {
    public static void main(String[] args) {
        final int userId = 4;
        final String sql = "DECLARE GLOBAL TEMPORARY TABLE SESSION.memtable "+
                            "(id int, name varchar(10)) NOT LOGGED";
        final String inSQL = "INSERT INTO SESSION.memtable (id, name) "+
                             "VALUES (?,?)";
        final String selSQL = "SELECT name FROM SESSION.memtable WHERE id = ?"
        final String connURL = "jdbc:derby:memory:memdatabase;create=true";
        try(Connection conn = DriverManager.getConnection(connURL);) {
            try (PreparedStatement ps = conn.prepareStatement(sql);) {
                ps.execute();
            }
            try (PreparedStatement ps = conn.prepareStatement(inSQL);) {
                ps.setInt(1, userId);
                ps.setString(2, "Jonas");
                int rows = ps.executeUpdate();
                System.out.println(rows + " rows inserted.");
            }
            try (PreparedStatement ps = conn.prepareStatement(selSQL);) {
                ps.setInt(1, userId);
                try (ResultSet rs = ps.executeQuery();) {
                    String name;
                    if(rs.next()) {
                        name = rs.getString("name");
                    } else {
                        name = null;
                    }
                    System.out.println("Name: " + name);
                }
            }
        } catch (SQLException e) {e.printStackTrace();}}}

UPDATE

If I change the SELECT-query to:

SELECT t1.name AS name FROM SESSION.memtable t1 WHERE id = ?

I get this error message:

java.sql.SQLException: Operation 'DROP TABLE' cannot be performed on object 'MEMTABLE(ID, NAME)' because there is an open ResultSet dependent on that object.
    at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
    at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
    at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
    at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedConnection.commitIfAutoCommit(Unknown Source)
    at org.apache.derby.impl.jdbc.ConnectionChild.commitIfAutoCommit(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedStatement.resultSetClosing(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedResultSet.movePosition(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedResultSet.next(Unknown Source)
    at DBTest.main(DBTest.java:29)
Caused by: java.sql.SQLException: Operation 'DROP TABLE' cannot be performed on object 'MEMTABLE(ID, NAME)' because there is an open ResultSet dependent on that object.
    at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
    at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
    ... 11 more
Caused by: ERROR X0X95: Operation 'DROP TABLE' cannot be performed on object 'MEMTABLE(ID, NAME)' because there is an open ResultSet dependent on that object.
    at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
    at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.verifyNoOpenResultSets(Unknown Source)
    at org.apache.derby.impl.sql.GenericPreparedStatement.prepareToInvalidate(Unknown Source)
    at org.apache.derby.impl.sql.depend.BasicDependencyManager.coreInvalidateFor(Unknown Source)
    at org.apache.derby.impl.sql.depend.BasicDependencyManager.invalidateFor(Unknown Source)
    at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.tempTablesAndCommit(Unknown Source)
    at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.doCommit(Unknown Source)
    at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.userCommit(Unknown Source)
    at org.apache.derby.impl.jdbc.TransactionResourceImpl.commit(Unknown Source)
    ... 6 more

But Name: is not printed, so it still doesn't work.

score 1 · Accepted Answer · answered Nov 11 '11 at 14:39

The question was discussed on the derby-user mailing list, where the additional requirement to include on commit preserve rows was noted.

With autocommit on, a temporary table by default is cleared at commit, so the extra clause ensures that the rows are kept available beyond transaction commit.

score 0 · Answer 2 · answered Aug 10 '14 at 23:25

You use two concepts here, one is Derby with the In-Memors storage option and the other is the temporary table. If you want a In-Memors database you can still use regular defined tables ("CREATE TABLE ..."). This will make it easier as you don't have to deal with the session semantic of those temporary tables.

score -2 · Answer 3 · edited Jul 30 '15 at 19:52

-2

You should really look into Hibernate. Not only does it map your classes, but it manages the nasty JDBC stuff for you so you can concentrate on what you want to get done. It's far from perfect, but on top of this, it gives you database portability.

I use hibernate through Spring, so my code would look something like:

TempStorage temp = new TempStorage();
temp.setName("Bob");
getHibernateTemplate.insert(temp);
temp = getHibernateTemplate.find("SELECT name FROM TempStorage");

edited Jul 30 '15 at 19:52

Joshua Taylor

84,998
9
154
353

answered Nov 11 '11 at 12:35

Thom

14,013
25
105
185

Hibernate is not something I'm looking for and I don't understand how that solve my problem. – Jonas Nov 11 '11 at 12:37
Using hibernate will help keep you from getting bogged down in idiosyncrasies of a database language. Although it adds a couple of it's own. – Thom Nov 11 '11 at 12:48
Please, explain how Hibernate would solve my problem instead, and post some code. How do I write a select query from an in-memory derby database that works in Hibernate? – Jonas Nov 11 '11 at 12:53
I suspect that this isn't even possible to do with Hibernate + Derby. – Jonas Nov 11 '11 at 12:53

How to SELECT data from in-memory Derby database table?

3 Answers3