[Seasar-user:19064] [S2JDBC]SQLiteについて
宇佐美 大輔
[E-MAIL ADDRESS DELETED]
2009年 12月 25日 (金) 12:36:37 JST
お世話になります。宇佐美です。
S2JDBCでSQLiteを利用しようと考えています。
standardDialectを利用して、S2JDBC-Tutorialのexampleのテストを実行したところ、
以下のテストでエラーになりました(その他のテストは全て通りました)。
・InsertTest.java
・PagingTest.java
※エラー内容は下記
また、S2JDBC-GENのgen-entityで関連定義(@OneToManyなど)が生成されません。
gen-ddlはエラーになります。エラー内容は下記参照。
SQLiteでもFOREIGN KEYがサポートされた様なので、
正式に対応できるのでは？と思った次第です。
http://sqlite.org/foreignkeys.html
検討頂ければ幸いです。
環境
S2JDBC 2.4.40
DB:SQLite 3.6.20
DDL
------------------------------------------------------
create table department (
id integer PRIMARY KEY AUTOINCREMENT,
name varchar(255) not null,
version integer not null);
create table address (
id integer PRIMARY KEY AUTOINCREMENT,
name varchar(255) not null,
version integer not null);
create table employee (
id integer PRIMARY KEY AUTOINCREMENT,
name varchar(255) not null,
job_type integer not null,
salary integer,
department_id integer,
address_id integer,
version integer not null,
constraint EMPLOYEE_FK1 FOREIGN KEY(DEPARTMENT_ID) REFERENCES DEPARTMENT(ID),
constraint EMPLOYEE_FK2 FOREIGN KEY(ADDRESS_ID) REFERENCES ADDRESS(ID));
create table id_generator (
PK varchar(80) not null primary key,
VALUE integer);
INSERT INTO DEPARTMENT VALUES(1,'ACCOUNTING',1);
INSERT INTO DEPARTMENT VALUES(2,'RESEARCH',1);
INSERT INTO DEPARTMENT VALUES(3,'SALES',1);
INSERT INTO DEPARTMENT VALUES(4,'OPERATIONS',1);
INSERT INTO ADDRESS VALUES(1,'STREET 1',1);
INSERT INTO ADDRESS VALUES(2,'STREET 2',1);
INSERT INTO ADDRESS VALUES(3,'STREET 3',1);
INSERT INTO ADDRESS VALUES(4,'STREET 4',1);
INSERT INTO ADDRESS VALUES(5,'STREET 5',1);
INSERT INTO ADDRESS VALUES(6,'STREET 6',1);
INSERT INTO ADDRESS VALUES(7,'STREET 7',1);
INSERT INTO ADDRESS VALUES(8,'STREET 8',1);
INSERT INTO ADDRESS VALUES(9,'STREET 9',1);
INSERT INTO ADDRESS VALUES(10,'STREET 10',1);
INSERT INTO ADDRESS VALUES(11,'STREET 11',1);
INSERT INTO ADDRESS VALUES(12,'STREET 12',1);
INSERT INTO ADDRESS VALUES(13,'STREET 13',1);
INSERT INTO ADDRESS VALUES(14,'STREET 14',1);
INSERT INTO EMPLOYEE VALUES(1,'ALLEN',1,1600,3,1,1);
INSERT INTO EMPLOYEE VALUES(2,'WARD',1,1250,3,2,1);
INSERT INTO EMPLOYEE VALUES(3,'JONES',2,2975,2,3,1);
INSERT INTO EMPLOYEE VALUES(4,'MARTIN',1,1250,3,4,1);
INSERT INTO EMPLOYEE VALUES(5,'BLAKE',2,2850,3,5,1);
INSERT INTO EMPLOYEE VALUES(6,'CLARK',2,2450,1,6,1);
INSERT INTO EMPLOYEE VALUES(7,'SCOTT',3,3000.0,2,7,1);
INSERT INTO EMPLOYEE VALUES(8,'KING',4,5000,1,8,1);
INSERT INTO EMPLOYEE VALUES(9,'TURNER',1,1500,3,9,1);
INSERT INTO EMPLOYEE VALUES(10,'ADAMS',0,1100,2,10,1);
INSERT INTO EMPLOYEE VALUES(11,'JAMES',0,950,3,11,1);
INSERT INTO EMPLOYEE VALUES(12,'FORD',3,3000,2,12,1);
INSERT INTO EMPLOYEE VALUES(13,'MILLER',0,1300,1,13,1);
INSERT INTO EMPLOYEE VALUES(14,'SMITH',0,800,2,14,1);
insert into ID_GENERATOR (PK, VALUE) values ('employee_id', 15);
insert into ID_GENERATOR (PK, VALUE) values ('department_id', 15);
insert into ID_GENERATOR (PK, VALUE) values ('address_id', 15);
----------------------------------------------------------------------
＝InsertTest.javaのエラー内容(GenerationType.IDENTITY)＝
org.seasar.extension.jdbc.exception.IdentityGeneratorNotSupportedRuntimeException: [ESSR0741]エンティティ(Employee)のIDプロパティ(id)を自動生成する方
法にIDENTITYが指定されていますがDBMS(null)ではサポートされていません
at org.seasar.extension.jdbc.PropertyMeta.getIdGenerator(PropertyMeta.java:290)
at org.seasar.extension.jdbc.query.AutoInsertImpl.prepareTargetProperties(AutoInsertImpl.java:128)
at org.seasar.extension.jdbc.query.AutoInsertImpl.prepare(AutoInsertImpl.java:107)
at org.seasar.extension.jdbc.query.AbstractAutoUpdate.execute(AbstractAutoUpdate.java:88)
at examples.InsertTest.testInsertTx(InsertTest.java:44)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at junit.framework.TestCase.runTest(TestCase.java:164)
at org.seasar.framework.unit.S2FrameworkTestCase.doRunTest(S2FrameworkTestCase.java:519)
at org.seasar.extension.unit.S2TestCase.doRunTest(S2TestCase.java:103)
at org.seasar.framework.unit.S2FrameworkTestCase.runBare(S2FrameworkTestCase.java:308)
at junit.framework.TestResult$1.protect(TestResult.java:106)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.framework.TestResult.run(TestResult.java:109)
at junit.framework.TestCase.run(TestCase.java:120)
at junit.framework.TestSuite.runTest(TestSuite.java:230)
at junit.framework.TestSuite.run(TestSuite.java:225)
at org.eclipse.jdt.internal.junit.runner.junit3.JUnit3TestReference.run(JUnit3TestReference.java:130)
at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:460)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:673)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:386)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)
＝InsertTest.javaのエラー内容(GenerationType.SEQUENCE)＝
org.seasar.extension.jdbc.exception.SequenceGeneratorNotSupportedRuntimeException: [ESSR0742]エンティティ(Employee)のIDプロパティ(id)を自動生成する方
法にSEQUENCEが指定されていますがDBMS(null)ではサポートされていません
at org.seasar.extension.jdbc.PropertyMeta.getIdGenerator(PropertyMeta.java:296)
at org.seasar.extension.jdbc.query.AutoInsertImpl.prepareTargetProperties(AutoInsertImpl.java:128)
at org.seasar.extension.jdbc.query.AutoInsertImpl.prepare(AutoInsertImpl.java:107)
at org.seasar.extension.jdbc.query.AbstractAutoUpdate.execute(AbstractAutoUpdate.java:88)
at examples.InsertTest.testInsertTx(InsertTest.java:44)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at junit.framework.TestCase.runTest(TestCase.java:164)
at org.seasar.framework.unit.S2FrameworkTestCase.doRunTest(S2FrameworkTestCase.java:519)
at org.seasar.extension.unit.S2TestCase.doRunTest(S2TestCase.java:103)
at org.seasar.framework.unit.S2FrameworkTestCase.runBare(S2FrameworkTestCase.java:308)
at junit.framework.TestResult$1.protect(TestResult.java:106)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.framework.TestResult.run(TestResult.java:109)
at junit.framework.TestCase.run(TestCase.java:120)
at junit.framework.TestSuite.runTest(TestSuite.java:230)
at junit.framework.TestSuite.run(TestSuite.java:225)
at org.eclipse.jdt.internal.junit.runner.junit3.JUnit3TestReference.run(JUnit3TestReference.java:130)
at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:460)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:673)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:386)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)
＝InsertTest.javaのエラー内容(GenerationType.TABLE)＝
org.seasar.extension.jdbc.exception.IdGenerationFailedRuntimeException: [ESSR0738]エンティティ(Employee)のIDプロパティ(id)の自動生成に失敗しました。
at org.seasar.extension.jdbc.id.TableIdGenerator.updateIdTable(TableIdGenerator.java:142)
at org.seasar.extension.jdbc.id.TableIdGenerator$1.execute(TableIdGenerator.java:112)
at org.seasar.extension.tx.adapter.JTATransactionManagerAdapter.requiresNew(JTATransactionManagerAdapter.java:79)
at org.seasar.extension.jdbc.id.TableIdGenerator.getNewInitialValue(TableIdGenerator.java:106)
at org.seasar.extension.jdbc.id.AbstractPreAllocateIdGenerator$IdContext.getNextValue(AbstractPreAllocateIdGenerator.java:144)
at org.seasar.extension.jdbc.id.AbstractPreAllocateIdGenerator.preInsert(AbstractPreAllocateIdGenerator.java:76)
at org.seasar.extension.jdbc.query.AutoInsertImpl.getIdValue(AutoInsertImpl.java:215)
at org.seasar.extension.jdbc.query.AutoInsertImpl.prepareParams(AutoInsertImpl.java:185)
at org.seasar.extension.jdbc.query.AutoInsertImpl.prepare(AutoInsertImpl.java:110)
at org.seasar.extension.jdbc.query.AbstractAutoUpdate.execute(AbstractAutoUpdate.java:88)
at examples.InsertTest.testInsertTx(InsertTest.java:44)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at junit.framework.TestCase.runTest(TestCase.java:164)
at org.seasar.framework.unit.S2FrameworkTestCase.doRunTest(S2FrameworkTestCase.java:519)
at org.seasar.extension.unit.S2TestCase.doRunTest(S2TestCase.java:103)
at org.seasar.framework.unit.S2FrameworkTestCase.runBare(S2FrameworkTestCase.java:308)
at junit.framework.TestResult$1.protect(TestResult.java:106)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.framework.TestResult.run(TestResult.java:109)
at junit.framework.TestCase.run(TestCase.java:120)
at junit.framework.TestSuite.runTest(TestSuite.java:230)
at junit.framework.TestSuite.run(TestSuite.java:225)
at org.eclipse.jdt.internal.junit.runner.junit3.JUnit3TestReference.run(JUnit3TestReference.java:130)
at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:460)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:673)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:386)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)
＝PagingTest.javaのエラー内容＝
org.seasar.framework.exception.SQLRuntimeException: [ESSR0072]SQLで例外(SQL=[select T1_.ID as C1_, T1_.NAME as C2_, T1_.JOB_TYPE as C3_, T1_.SALARY
as C4_, T1_.DEPARTMENT_ID as C5_, T1_.ADDRESS_ID as C6_, T1_.VERSION as C7_ from EMPLOYEE T1_ order by C1_], Message=[[ESSR0072]SQLで例外(SQL=[select
T1_.ID as C1_, T1_.NAME as C2_, T1_.JOB_TYPE as C3_, T1_.SALARY as C4_, T1_.DEPARTMENT_ID as C5_, T1_.ADDRESS_ID as C6_, T1_.VERSION as C7_ from
EMPLOYEE T1_ order by C1_], Message=[SQLite only supports TYPE_FORWARD_ONLY cursors], ErrorCode=0, SQLState=null)が発生しました : [SQLで例外(Message=
[SQLite only supports TYPE_FORWARD_ONLY cursors], ErrorCode=0, SQLState=null)が発生しました。], [SQLite only supports TYPE_FORWARD_ONLY cursors],
ErrorCode=0, SQLState=null)が発生しました
at org.seasar.extension.jdbc.util.ConnectionUtil.prepareStatement(ConnectionUtil.java:145)
at org.seasar.extension.jdbc.manager.JdbcContextImpl.usingCursorPreparedStatement(JdbcContextImpl.java:203)
at org.seasar.extension.jdbc.query.AbstractSelect.processCursorPreparedStatement(AbstractSelect.java:316)
at org.seasar.extension.jdbc.query.AbstractSelect.processResultSet(AbstractSelect.java:396)
at org.seasar.extension.jdbc.query.AbstractSelect.getResultListInternal(AbstractSelect.java:225)
at org.seasar.extension.jdbc.query.AbstractSelect.getResultList(AbstractSelect.java:172)
at examples.PagingTest.testPaging(PagingTest.java:47)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at junit.framework.TestCase.runTest(TestCase.java:164)
at org.seasar.framework.unit.S2FrameworkTestCase.doRunTest(S2FrameworkTestCase.java:519)
at org.seasar.extension.unit.S2TestCase.doRunTest(S2TestCase.java:103)
at org.seasar.framework.unit.S2FrameworkTestCase.runBare(S2FrameworkTestCase.java:308)
at junit.framework.TestResult$1.protect(TestResult.java:106)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.framework.TestResult.run(TestResult.java:109)
at junit.framework.TestCase.run(TestCase.java:120)
at junit.framework.TestSuite.runTest(TestSuite.java:230)
at junit.framework.TestSuite.run(TestSuite.java:225)
at org.eclipse.jdt.internal.junit.runner.junit3.JUnit3TestReference.run(JUnit3TestReference.java:130)
at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:460)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:673)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:386)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)
Caused by: org.seasar.framework.exception.SSQLException: [ESSR0072]SQLで例外(SQL=[select T1_.ID as C1_, T1_.NAME as C2_, T1_.JOB_TYPE as C3_,
T1_.SALARY as C4_, T1_.DEPARTMENT_ID as C5_, T1_.ADDRESS_ID as C6_, T1_.VERSION as C7_ from EMPLOYEE T1_ order by C1_], Message=[SQLite only supports
TYPE_FORWARD_ONLY cursors], ErrorCode=0, SQLState=null)が発生しました
at org.seasar.extension.dbcp.impl.ConnectionWrapperImpl.wrapException(ConnectionWrapperImpl.java:581)
at org.seasar.extension.dbcp.impl.ConnectionWrapperImpl.prepareStatement(ConnectionWrapperImpl.java:416)
at org.seasar.extension.jdbc.util.ConnectionUtil.prepareStatement(ConnectionUtil.java:142)
... 26 more
Caused by: java.sql.SQLException: SQLite only supports TYPE_FORWARD_ONLY cursors
at org.sqlite.Conn.checkCursor(Conn.java:232)
at org.sqlite.Conn.prepareStatement(Conn.java:402)
at org.sqlite.Conn.prepareStatement(Conn.java:398)
at org.seasar.extension.dbcp.impl.ConnectionWrapperImpl.prepareStatement(ConnectionWrapperImpl.java:411)
... 27 more
＝gen-ddlのエラー内容＝
[gen-ddl] DEBUG 2009-12-24 13:33:30,074 [main] 物理的なコネクションを取得しました
[gen-ddl] DEBUG 2009-12-24 13:33:30,074 [main] 論理的なコネクションを取得しました。tx=null
[gen-ddl] DEBUG 2009-12-24 13:33:30,074 [main] 論理的なコネクションを閉じました。tx=null
[gen-ddl] DEBUG 2009-12-24 13:33:30,090 [main] RDBMS(SQLite)に対しS2JDBC-Gen用のデータベースの方言
(org.seasar.extension.jdbc.gen.internal.dialect.StandardGenDialect)を使用します。
[gen-ddl] INFO 2009-12-24 13:33:30,090 [main] DDL情報ファイル(<ワークスペースのパス>\S2JDBC-SQLite\db\ddl-info.txt)が存在しません。バージョン番号は
0 とみなされます。
[gen-ddl] DEBUG 2009-12-24 13:33:30,246 [main] 物理的なコネクションを閉じました
[gen-ddl] Java Result: 1
BUILD FAILED
<ワークスペースのパス>\S2JDBC-SQLite\s2jdbc-gen-build.xml:117: Exception in thread "main"
org.seasar.extension.jdbc.gen.exception.CommandFailedRuntimeException: [ES2JDBCGen0005]コマンド
(org.seasar.extension.jdbc.gen.internal.command.GenerateDdlCommand)の実行に失敗しました。バージョンは S2JDBC-Gen 2.4.40 です。理由は
org.seasar.extension.jdbc.gen.internal.exception.UnsupportedGenerationTypeRuntimeException: [ES2JDBCGen0020]このRDBMSではGenerationType.IDENTITYはサ
ポートされていません。エンティティ(Address)のプロパティ(id)。
at org.seasar.extension.jdbc.gen.internal.command.AbstractCommand.execute(AbstractCommand.java:164)
at org.seasar.extension.jdbc.gen.internal.command.CommandInvokerImpl.invoke(CommandInvokerImpl.java:29)
at org.seasar.extension.jdbc.gen.command.CommandAdapter.main(CommandAdapter.java:61)
Caused by: org.seasar.extension.jdbc.gen.internal.exception.UnsupportedGenerationTypeRuntimeException: [ES2JDBCGen0020]このRDBMSでは
GenerationType.IDENTITYはサポートされていません。エンティティ(Address)のプロパティ(id)。
at org.seasar.extension.jdbc.gen.internal.desc.ColumnDescFactoryImpl.doIdentity(ColumnDescFactoryImpl.java:144)
at org.seasar.extension.jdbc.gen.internal.desc.ColumnDescFactoryImpl.getColumnDesc(ColumnDescFactoryImpl.java:80)
at org.seasar.extension.jdbc.gen.internal.desc.TableDescFactoryImpl.doColumnDesc(TableDescFactoryImpl.java:230)
at org.seasar.extension.jdbc.gen.internal.desc.TableDescFactoryImpl.createTableDesc(TableDescFactoryImpl.java:160)
at org.seasar.extension.jdbc.gen.internal.desc.TableDescFactoryImpl.getTableDesc(TableDescFactoryImpl.java:142)
at org.seasar.extension.jdbc.gen.internal.desc.DatabaseDescFactoryImpl.getDatabaseDesc(DatabaseDescFactoryImpl.java:101)
at org.seasar.extension.jdbc.gen.internal.command.GenerateDdlCommand$DdlVersionIncrementerCallback.execute(GenerateDdlCommand.java:1369)
at org.seasar.extension.jdbc.gen.internal.version.DdlVersionIncrementerImpl.increment(DdlVersionIncrementerImpl.java:122)
at org.seasar.extension.jdbc.gen.internal.command.GenerateDdlCommand.doExecute(GenerateDdlCommand.java:1192)
at org.seasar.extension.jdbc.gen.internal.command.AbstractCommand.execute(AbstractCommand.java:162)
... 2 more
Total time: 1 second
Seasar-user メーリングリストの案内