Inserting data into a H2 Database Table

Question

I'm a newbie in SQL. I'm trying to teach myself how to work with H2. I've managed to create a Table, but when I try to insert data using another class the data just doesn't get inserted.

Here's my code:

The Class that creates the "MYLOVELYSTUDENTS" Table:

// STEP 1. Import required packages
import java.sql.*;

public class JDBCExampleNewTable {
    // JDBC driver name and database URL
    static final String JDBC_DRIVER = "org.h2.Driver"; //org.h2.Driver
    static final String DB_URL = "jdbc:h2:~/mytest";

    //  Database credentials
    static final String USER = "sa";
    static final String PASS = "";

    public static void main (String[] args) {
        Connection conn = null;
        Statement stmt = null;

        try {
            //STEP 2: Register JDBC driver
            Class.forName("org.h2.Driver");

            //STEP 3: Open a connection
            System.out.println("Connecting to a selected database...");
            conn = DriverManager.getConnection(DB_URL, USER, PASS);
            System.out.println("Connected database successfully...");

            //STEP 4: Execute a query
            System.out.println("Creating table in given database...");
            stmt = conn.createStatement();

            String sql = "CREATE TABLE MYLOVELYSTUDENTS " +
                    "(studentid INTEGER not NULL, " +
                    " first VARCHAR(255), " +
                    " last VARCHAR(255), " +
                    " age INTEGER, " +
                    " course VARCHAR(255))";

            stmt.executeUpdate(sql);
            System.out.println("Created table in given database...");
        } catch (SQLException se) {
            //Handle errors for JDBC
            se.printStackTrace();
        } catch (Exception e) {
            //Handle errors for Class.forName
            e.printStackTrace();
        } finally {
            //finally block used to close resources
            try {
                if (stmt!=null)
                    conn.close();
            } catch (SQLException se) {
            } // do nothing
            try {
                if (conn!= null)
                    conn.close();
            } catch (SQLException se) {
                se.printStackTrace();
            } // end finally try
        } // end try
        System.out.println("Goodbye!");
    } // end main
} // end JDBCExample

The Class that inserts into the "MYLOVELYSTUDENTS" Table:

//STEP 1. Import required packages
import java.sql.*;

public class JDBCExampleInsertRecordsExample {
    // JDBC driver name and database URL
    static final String JDBC_DRIVER = "org.h2.Driver";
    static final String DB_URL = "jdbc:h2:~/mytest";

    //  Database credentials
    static final String USER = "sa";
    static final String PASS = "";

    public static void main (String[] args) {
        Connection conn = null;
        Statement stmt = null;

        try {
            //STEP 2: Register JDBC driver
            Class.forName(JDBC_DRIVER);

            //STEP 3: Open a connection
            System.out.println("Connecting to a selected database...");
            conn = DriverManager.getConnection(DB_URL, USER, PASS);
            System.out.println("Connected database successfully...");

            //STEP 4: Execute a query
            System.out.println("Inserting records into the table...");
            stmt = conn.createStatement();

            String sql = "INSERT INTO MYLOVELYSTUDENTS " + "VALUES (123456789, 'Zara', 'Ali', 18, database)";

            stmt.executeUpdate(sql);

            System.out.println("Inserted records into the table...");

        } catch (SQLException se) {
            //Handle errors for JDBC
            se.printStackTrace();
        } catch (Exception e) {
            //Handle errors for Class.forName
            e.printStackTrace();
        } finally {
            //finally block used to close resources
            try {
                if (stmt!=null)
                    conn.close();
            } catch (SQLException se) {
            } // do nothing
            try {
                if (conn!=null)
                    conn.close();
            } catch (SQLException se) {
                se.printStackTrace();
            } //end finally try
        } //end try

        System.out.println("Goodbye!");

    } //end main
} //end JDBCExample

The error message:

run:
Connecting to a selected database...
Connected database successfully...
Inserting records into the table...
org.h2.jdbc.JdbcSQLException: Column "DATABASE" not found; SQL statement:
INSERT INTO MYLOVELYSTUDENTS VALUES (123456789, 'Zara', 'Ali', 18, database) [42122-173]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:331)
    at org.h2.message.DbException.get(DbException.java:171)
    at org.h2.message.DbException.get(DbException.java:148)
    at org.h2.expression.ExpressionColumn.optimize(ExpressionColumn.java:144)
    at org.h2.command.dml.Insert.prepare(Insert.java:238)
    at org.h2.command.Parser.prepareCommand(Parser.java:219)
    at org.h2.engine.Session.prepareLocal(Session.java:428)
    at org.h2.engine.Session.prepareCommand(Session.java:377)
    at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1138)
    at org.h2.jdbc.JdbcStatement.executeUpdateInternal(JdbcStatement.java:124)
    at org.h2.jdbc.JdbcStatement.executeUpdate(JdbcStatement.java:113)
    at Database.H2Samples.JDBCExampleInsertRecordsExample.main(JDBCExampleInsertRecordsExample.java:34)
Goodbye!
BUILD SUCCESSFUL (total time: 4 seconds)

Answer 1

The last course column has the varchar2 datatype so you must keep the values in the Single quotes (' ')

       String sql = "INSERT INTO MYLOVELYSTUDENTS " 
         + "VALUES (123456789, 'Zara', 'Ali', 18, 'database')";
                            quotes needed here ---^--------^

Answer 2

You forgot to quote "database":

String sql = "INSERT INTO MYLOVELYSTUDENTS " 
             + "VALUES (123456789, 'Zara', 'Ali', 18, 'database')";
                                quotes needed here ---^--------^