SQLException: no such table

Question

now I got some trouble connecting to my database. I know the tables i am looking for exist because when I access them with the command line they can be queried.

Probably some minor oversight but I would love some help.

This is where I make my connection to my database package persistence;

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;


public class DBRegistry {

    private static DBRegistry db = null;
    private static Connection connection = null;

    private DBRegistry() {};

    public static synchronized DBRegistry getUniqueInstance() {
        if (db == null) {
                db = new DBRegistry();
                return db;
        }
        else return db;
    }

    public synchronized Connection getDBConnection() {
            try {
                Class.forName("org.sqlite.JDBC");
                connection = DriverManager.getConnection("jdbc:sqlite:src/database/taskMan.db");
                return connection;
            } 
            catch (SQLException e) {e.printStackTrace();} 
            catch (ClassNotFoundException e) {e.printStackTrace();}
            return null;
    }

    public synchronized void closeConnection() {
        try {
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Here is how I query it

public void create(UUID oid, Object obj) {
    Task t = (Task)obj;
    String statement = "INSERT INTO `complexTask` (`oid`,`description`,`status`) VALUES (?, ?, ?)";
    try {
        PreparedStatement dbStatement = db.prepareStatement(statement);
        dbStatement.setString(1, oid.toString());
        dbStatement.setString(2, t.getDescription());
        dbStatement.setBoolean(3, t.getStatus());
        dbStatement.executeUpdate();
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

and finally a stack trace:

java.sql.SQLException: no such table: complexTask
    at org.sqlite.DB.throwex(DB.java:288)
    at org.sqlite.NativeDB.prepare(Native Method)
    at org.sqlite.DB.prepare(DB.java:114)
    at org.sqlite.PrepStmt.<init>(PrepStmt.java:37)
    at org.sqlite.Conn.prepareStatement(Conn.java:231)
    at org.sqlite.Conn.prepareStatement(Conn.java:224)
    at org.sqlite.Conn.prepareStatement(Conn.java:213)
    at persistence.framework.ComplexTaskRDBMapper.create(ComplexTaskRDBMapper.java:23)
    at persistence.PersistanceFacade.create(PersistanceFacade.java:49)
    at persistence.persistanceStates.NewState.commit(NewState.java:10)
    at persistence.PersistentObject.commit(PersistentObject.java:23)
    at domain.objects.Task.commitToDB(Task.java:89)
    at domain.TaskRepository.commitToDB(TaskRepository.java:60)
    at domain.TaskController.persistanceCommit(TaskController.java:97)
    at presentation.TaskControlsJPanel$3.actionPerformed(TaskControlsJPanel.java:127)
    at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:2012)
    at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2335)
    at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:404)
    at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:259)
    at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:253)
    at java.awt.Component.processMouseEvent(Component.java:6175)
    at javax.swing.JComponent.processMouseEvent(JComponent.java:3267)
    at java.awt.Component.processEvent(Component.java:5940)
    at java.awt.Container.processEvent(Container.java:2105)
    at java.awt.Component.dispatchEventImpl(Component.java:4536)
    at java.awt.Container.dispatchEventImpl(Container.java:2163)
    at java.awt.Component.dispatchEvent(Component.java:4362)
    at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4461)
    at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4125)
    at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4055)
    at java.awt.Container.dispatchEventImpl(Container.java:2149)
    at java.awt.Window.dispatchEventImpl(Window.java:2478)
    at java.awt.Component.dispatchEvent(Component.java:4362)
    at java.awt.EventQueue.dispatchEvent(EventQueue.java:604)
    at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:275)
    at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:200)
    at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:190)
    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:185)
    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:177)
    at java.awt.EventDispatchThread.run(EventDispatchThread.java:138)

And some JUnit code for good measure, the first test passes and the second fails with a similar error to the one above

package test.persistence;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import persistence.DBRegistry;
import junit.framework.TestCase;

public class TestDBRegistry extends TestCase {

    public void testDBRegistryConnection() {
        Connection con =  DBRegistry.getUniqueInstance().getDBConnection();
        assertNotNull(con);
    }

    public void testTableQuery() throws SQLException {
        Connection con =  DBRegistry.getUniqueInstance().getDBConnection();
        PreparedStatement dbStatement = con.prepareStatement("SELECT COUNT(*) FROM `singleTask`");
        assertEquals("should be 1 for successful query", 1, dbStatement.executeQuery());
    }

Solution 1

I notice that in both your unit test and your other code you are using back-ticks around the table name. In the latest version of sqlite, this is fine but in older versions it wasn't handled as well I believe. Can you try removing the ticks around the table name or maybe changing them to regular quotes rather than back-ticks?

If that doesn't solve it I would check to be absolutely sure that you are pointing to the correct db file. If you specify a filename that doesn't exist you won't get an error, it will simply create a new database there. I'm not sure what the "current directory" is under the context of your app or unit test but be sure it is pointing to where you think it is. To test this, you could change the db file name to foo.db, run the unit test, then search your machine for foo.db to see where it got created. That will tell you where your app is working off of.

Solution 2

I'm not sure your JDBC connection string is quite right. The connection string you use ends with taskMan.db, but your comment above implies that the name of the database file is taskManDb.db (note the extra Db).

Solution 3

It might not fix your problem with the SQL INSERT, but I don't care at all for your DBRegistry implementation. I'd write it like this:

package persistence;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


public class DBRegistry
{
    public static Connection getConnection(String driver, String url, String username, String password) throws ClassNotFoundException, SQLException
    {
        Class.forName(driver);
        return DriverManager.getConnection(url, username, password);
    }

    public static void close(Connection connection)
    {
        try
        {
            if (connection != null)
            {
                connection.close();
            }
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
    }


    public static void close(Statement statement)
    {
        try
        {
            if (statement != null)
            {
                statement.close();
            }
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
    }

    public static void close(ResultSet resultSet)
    {
        try
        {
            if (resultSet != null)
            {
                resultSet.close();
            }
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
    }
}