将数据插入H2数据库表

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.

我是SQL的新手。我正在努力教自己如何使用H2。我设法创建了一个表，但是当我尝试使用另一个类插入数据时，数据就不会被插入。

Here's my code:

这是我的代码：

The Class that creates the "MYLOVELYSTUDENTS" Table:

创建“MYLOVELYSTUDENTS”表的类：

// 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:

插入“MYLOVELYSTUDENTS”表的类：

//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)

5 个解决方案

#1

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

最后一个课程列具有varchar2数据类型，因此您必须将值保留在单引号（''）中

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

#2

You forgot to quote "database":

你忘了引用“数据库”：

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

#3

A good way to debug queries is to test them manually first. H2 has a h2 console (http://localhost:8082, by default). There you can enter queries, and get feedback on errors.

调试查询的好方法是首先手动测试它们。 H2有一个h2控制台（http：// localhost：8082，默认情况下）。在那里，您可以输入查询，并获得有关错误的反馈。

I saw only one: I think you need quotes around the word "database".

我只看到一个：我认为你需要引用“数据库”这个词。

#4

The last column is a VARCHAR column, yet you seem to be passing an unquoted value to the insert statement

最后一列是VARCHAR列，但您似乎将不带引号的值传递给insert语句

Instead of...

代替...

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

Try using

尝试使用

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

#5

The problem is in this line as the error shows:

问题在于此行，因为错误显示：

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

maybe you meant 'database' instead of database?

也许你的意思是'数据库'而不是数据库？

#1