Java Spring：HSQL-无法执行数据库脚本
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18541876/
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
Spring: HSQL- Failed to execute database script
提问by ashur
I'm trying to run HSQL with following two scripts and then test it, but each time I get this error which I cannot handle.
我正在尝试使用以下两个脚本运行 HSQL，然后对其进行测试，但每次我都遇到无法处理的错误。
Failed to execute database script; nested exception is org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement at line 4 of resource class path resource [data.sql]: insert into spittle (spitter_id, spittleText, postedTime) values (2, 'Trying out Spring''s new expression language.', '2010-06-11')
执行数据库脚本失败；嵌套异常是 org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement at line 4 of resource class path resource [data.sql]: insert into spittle (spitter_id, spittleText,postedTime) values (2, '试用 Spring 的新表达式语言。', '2010-06-11')
Update - additional exception that has been thrown:
更新 - 已抛出的其他异常：
integrity constraint violation: foreign key no parent; SYS_FK_10108 table: SPITTLE
完整性约束违规：外键没有父级；SYS_FK_10108 表：SPITTLE
These are my scripts:
这些是我的脚本：
schema.sql
模式.sql
drop table if exists spittle;
drop table if exists spitter;
create table spitter (
id identity,
username varchar(25) not null,
password varchar(25) not null,
fullname varchar(100) not null,
email varchar(50) not null,
update_by_email boolean not null
);
create table spittle (
id integer identity primary key,
spitter_id integer not null,
spittleText varchar(2000) not null,
postedTime date not null,
foreign key (spitter_id) references spitter(id)
);
data.sql
数据.sql
insert into spitter (username, password, fullname, email, update_by_email) values ('habuma', 'password', 'Craig Walls', '[email protected]', false);
insert into spitter (username, password, fullname, email, update_by_email) values ('artnames', 'password', 'Art Names', '[email protected]', false);
insert into spittle (spitter_id, spittleText, postedTime) values (1, 'Have you read Spring in Action 3? I hear it is awesome!', '2010-06-09');
insert into spittle (spitter_id, spittleText, postedTime) values (2, 'Trying out Spring''s new expression language.', '2010-06-11');
insert into spittle (spitter_id, spittleText, postedTime) values (1, 'Who''s going to SpringOne/2GX this year?', '2010-06-19');
appContext.xml
应用上下文.xml
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:jdbc="http://www.springframework.org/schema/jdbc"
xsi:schemaLocation="http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-3.0.xsd
http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd">
<jdbc:embedded-database id="dataSource" type="H2">
<jdbc:script location="classpath:schema.sql" />
<jdbc:script location="classpath:data.sql" />
</jdbc:embedded-database>
</beans>
UnitTest
单元测试
package com.habuma.spitter.persistence;
import org.springframework.jdbc.datasource.embedded.EmbeddedDatabase;
import org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseBuilder;
public class DataAccessUnitTestTemplate {
private static EmbeddedDatabase db;
@Before
public void setUp() {
// creates a HSQL in-memory db populated from default scripts classpath:schema.sql and classpath:test-data.sql
// obviously, this is the 'in-code' method, but the xml should work for Spring managed tests.
db = new EmbeddedDatabaseBuilder().addDefaultScripts().build();
}
@Test
public void testDataAccess() {
JdbcSpitterDao jdbc = new JdbcSpitterDao(db);
System.out.println(jdbc.getSpitterById(1L));
}
@After
public void tearDown() {
db.shutdown();
}
}
采纳答案by fredt
Your script starts the ID column value with 0. You can specify the start value.
您的脚本以 0 开始 ID 列值。您可以指定开始值。
create table spitter (
id int generated by default as identity (start with 1) primary key,
username varchar(25) not null,
password varchar(25) not null,
fullname varchar(100) not null,
email varchar(50) not null,
update_by_email boolean not null
);
Alternatively you can insert the ids explicitly:
或者，您可以显式插入 ID：
insert into spitter (id, username, password, fullname, email, update_by_email) values (1, 'habuma', 'password', 'Craig Walls', '[email protected]', false);
