Java 将数据插入到 H2 数据库表中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18308038/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Inserting data into a H2 Database Table
提问by Oliver Muchai
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)
采纳答案by Mohsin Shaikh
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 ---^--------^
回答by JB Nizet
You forgot to quote "database":
你忘了引用“数据库”：
String sql = "INSERT INTO MYLOVELYSTUDENTS "
+ "VALUES (123456789, 'Zara', 'Ali', 18, 'database')";
quotes needed here ---^--------^
回答by MadProgrammer
The last column is a VARCHARcolumn, yet you seem to be passing an unquoted value to the insertstatement
最后一列是一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')";
回答by Multithreader
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?
也许你的意思是'database'而不是database？
回答by Jesse van Bekkum
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".
我只看到一个：我认为你需要在“数据库”这个词周围加上引号。
