Connecting Program to SQLite Database

Question

Hey guys I am making an inventory search program for a friend and am having issues with SQLite as I am new to this and this is my first time making something with a data base. I don't have anyone I can really show my code and I have just been putting this together following you tube tutorials and what not. If you guys could check this out and point out any obvious mistakes I may be making that would be great. I have included code that I got from a tutorial and the first method that I am trying to input data into the database with called "addVehicles".

    private static Connection con;
    private static boolean hasData = false;

    public ResultSet displayVehicles() throws ClassNotFoundException, SQLException {
        if(con == null) {
            getConnection();
        }

        Statement state = con.createStatement();
        ResultSet res = state.executeQuery("SELECT sim, wo, description, oilN, oilB, "
                + "airN, airB, hydN, hydB, fuelN, fuelB, misc FROM vehicles");
        return res;
    }

    private void getConnection() throws ClassNotFoundException, SQLException {
        // TODO Auto-generated method stub
        Class.forName("org.sqlite.JDBC");
        con = DriverManager.getConnection("jdbc:sqlite:SQLiteVehicles.db");
        initialise();


    }

    private void initialise() throws SQLException {
        // TODO Auto-generated method stub
        if(!hasData) {
            hasData = true;

            Statement state = con.createStatement();
            ResultSet res = state.executeQuery("SELECT name from sqlite_master WHERE"
                    + " type=' table' AND name=' vehicles'");
            if(!res.next()) {
                System.out.println("Building the Vehicle table with prepopulated values.");
                //build table
                Statement state2 = con.createStatement();
                state2.execute("CREATE TABLE vehicles(sim varchar(100)," + "wo varchar(100)," + "description varchar(500)," +
                        "oilN varchar(100)," + "oilB varchar(100)," + "airN varchar(100)," + "airB varchar(100)," +
                        "hydN varchar(100)," + "hydB varchar(100)," + "fuelN varchar(100)," + "fuelB varchar(100),"
                        + "misc varchar(1000),");

                //insert sample data
                PreparedStatement prep = con.prepareStatement("INSERT INTO vehicles "
                        + "values(?,?,?,?,?,?,?,?,?,?,?,?);");
                prep.setString(1, "101");
                prep.setString(2, "00000");
                prep.setString(3, "VEHICLE NOT FOUND");
                prep.setString(4, "N/A");
                prep.setString(5, "N/A");
                prep.setString(6, "N/A");
                prep.setString(7, "N/A"); 
                prep.setString(8, "N/A");
                prep.setString(9, "N/A");
                prep.setString(10, "N/A");
                prep.setString(11, "N/A");
                prep.setString(12, "TEST WORKED");
                prep.execute();


            }

        }
    }

    public void addVehicle(String sim, String wo, String description,
            String oilN, String oilB, String airN, String airB, String hydN,
            String hydB, String fuelN, String fuelB, String misc) 
                    throws ClassNotFoundException, SQLException {
        if(con == null) {
            getConnection();
        }
        PreparedStatement prep = con.prepareStatement("INSERT INTO vehicles "
                + "values(?,?,?,?,?,?,?,?,?,?,?,?);");
        prep.setString(1, sim);
        prep.setString(2, wo);
        prep.setString(3, description);
        prep.setString(4, oilN);
        prep.setString(5, oilB);
        prep.setString(6, airN);
        prep.setString(7, airB); 
        prep.setString(8, hydN);
        prep.setString(9, hydB);
        prep.setString(10, fuelN);
        prep.setString(11, fuelB);
        prep.setString(12, misc);
        prep.execute();
    }

I am getting a ton of error messages such as

Exception in thread "``JavaFX Application Thread" java.lang.RuntimeException: java.lang.reflect.InvocationTargetException
at javafx.fxml.FXMLLoader$MethodHandler.invoke(FXMLLoader.java:1774)
at javafx.fxml.FXMLLoader$ControllerMethodEventHandler.handle(FXMLLoader.java:1657)
at com.sun.javafx.event.CompositeEventHandler.dispatchBubblingEvent(CompositeEventHandler.java:86)
at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:238)

Just got some new errors

java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (near ",": syntax error) at org.sqlite.core.DB.newSQLException(DB.java:890) at org.sqlite.core.DB.newSQLException(DB.java:901) at org.sqlite.core.DB.throwex(DB.java:868) at org.sqlite.core.NativeDB.prepare(Native Method) at org.sqlite.core.DB.prepare(DB.java:211) at org.sqlite.jdbc3.JDBC3Statement.execute(JDBC3Statement.java:60) at application.SQLite.initialise(SQLite.java:47) at application.SQLite.getConnection(SQLite.java:30) at application.SQLite.displayVehicles(SQLite.java:17)

Answer 1

Take you statement and remove all the "code" elements and have a look at it. Go so far as even drop into the database console (or other DB app) and trying to execute the statement.

CREATE TABLE vehicles(sim varchar(100),wo varchar(100),description varchar(500), oilN varchar(100),oilB varchar(100),airN varchar(100),airB varchar(100),hydN varchar(100),hydB varchar(100),fuelN varchar(100),fuelB varchar(100),misc varchar(1000),

Immediately, I'm drawn to the last statement, which has a trailing ,, but there's something else which is wrong. If we remove all the "additional fluff" and have a closer look...

CREATE TABLE vehicles(...,misc varchar(1000),

You will notice you're missing the enclosing bracket, ) which should be at the end of the statement