Using H2 with Java maven swing application. Using flyway-core 10.17.0 for database migration. I have the following database tables:
CREATE TABLE IF NOT EXISTS quantity_unit (
id INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(10) NOT NULL,
PRIMARY KEY (id),
UNIQUE (name)
);
CREATE TABLE IF NOT EXISTS items (
id INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) NOT NULL,
`specification` VARCHAR(200) NULL,
`unit_id` INT NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (unit_id)
REFERENCES quantity_unit(id)
ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS images (
id INT NOT NULL AUTO_INCREMENT,
`item_id` INT NOT NULL,
`name` VARCHAR(41) NOT NULL,
`order` INT NOT NULL,
`default_image` BOOL NOT NULL,
`scale` DECIMAL(3,2) NOT NULL DEFAULT 0.6,
PRIMARY KEY (id),
FOREIGN KEY (item_id)
REFERENCES items(id)
ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS recipients (
id INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255) NOT NULL,
PRIMARY KEY (id),
UNIQUE (name)
);
CREATE TABLE IF NOT EXISTS source (
id INT NOT NULL AUTO_INCREMENT,
`information` VARCHAR(255) NOT NULL,
PRIMARY KEY (id),
UNIQUE (information)
);
CREATE TABLE IF NOT EXISTS inwards (
`id` INT NOT NULL AUTO_INCREMENT,
`item_id` INT NOT NULL,
`quantity` DECIMAL(8,2) NOT NULL,
`source_id` INT NULL,
`date` DATE NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (item_id)
REFERENCES items(id)
ON DELETE CASCADE,
FOREIGN KEY (source_id)
REFERENCES source(id)
ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS outwards (
`id` INT NOT NULL AUTO_INCREMENT,
`item_id` INT NOT NULL,
`quantity` DECIMAL(8,2) NOT NULL,
`recipient_id` INT NOT NULL,
`for` VARCHAR(255) NOT NULL,
`date` DATE NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (item_id)
REFERENCES items(id)
ON DELETE CASCADE,
FOREIGN KEY (recipient_id)
REFERENCES recipients(id)
ON DELETE CASCADE
);
Migrating the database, connecting to the database via Java code and inserting records are all going fine.
The following SQL query does not raise any error after running it through the H2 browser console. But the same exact query raises an error when running it from Java code!!.
Actually, I was using MariaDB with the same tables and queries and I had no errors at all.
SELECT inwards.item_id AS item_id, inwards.id AS inward_id, inwards.quantity, u.id AS unit_id ,u.name AS unit_name, s.id AS source_id, s.information AS source_information, inwards.date, i.name AS item_name, i.specification AS item_specs
FROM inwards JOIN items AS i JOIN quantity_unit AS u JOIN source AS s
ON (inwards.item_id = i.id) AND (i.unit_id = u.id) AND (s.id = inwards.source_id)
ORDER BY inwards.date ASC, inwards.id ASC
LIMIT 10 OFFSET 0
The error:
org.h2.jdbc.JdbcSQLSyntaxErrorException: Column "INWARDS.ITEM_ID" not found; SQL statement:
SELECT inwards.item_id AS item_id, inwards.id AS inward_id, inwards.quantity, u.id AS unit_id ,u.name AS unit_name, s.id AS source_id, s.information AS source_information, inwards.date, i.name AS item_name, i.specification AS item_specs FROM inwards JOIN items AS i JOIN quantity_unit AS u JOIN source AS s ON (inwards.item_id = i.id) AND (i.unit_id = u.id) AND (s.id = inwards.source_id) ORDER BY inwards.date ASC, inwards.id ASC LIMIT ? OFFSET ?; [42122-230]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:514)
at org.h2.message.DbException.getJdbcSQLException(DbException.java:489)
at org.h2.message.DbException.get(DbException.java:223)
at org.h2.message.DbException.get(DbException.java:199)
at org.h2.expression.ExpressionColumn.getColumnException(ExpressionColumn.java:244)
at org.h2.expression.ExpressionColumn.optimizeOther(ExpressionColumn.java:226)
at org.h2.expression.ExpressionColumn.optimize(ExpressionColumn.java:213)
at org.h2.expression.condition.Comparison.optimize(Comparison.java:147)
at org.h2.expression.condition.ConditionAndOrN.optimize(ConditionAndOrN.java:168)
at org.h2.expression.Expression.optimizeCondition(Expression.java:148)
at org.h2.table.TableFilter.createIndexConditions(TableFilter.java:687)
at org.h2.table.TableFilter.createIndexConditions(TableFilter.java:696)
at org.h2.table.TableFilter.createIndexConditions(TableFilter.java:699)
at org.h2.table.TableFilter.createIndexConditions(TableFilter.java:696)
at org.h2.table.TableFilter.createIndexConditions(TableFilter.java:699)
at org.h2.command.query.Select.preparePlan(Select.java:1379)
at org.h2.command.query.Select.preparePlan(Select.java:1260)
at org.h2.command.query.Query.prepare(Query.java:233)
at org.h2.command.Parser.prepareCommand(Parser.java:489)
at org.h2.engine.SessionLocal.prepareLocal(SessionLocal.java:644)
at org.h2.engine.SessionLocal.prepareCommand(SessionLocal.java:560)
at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1164)
at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:93)
at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:315)
at warehouse.db.CRUDInwards.search(CRUDInwards.java:168)
at warehouse.panel.inwards.ItemsSearchLogic$SearchHandler.actionPerformed(ItemsSearchLogic.java:268)
at java.desktop/javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1972)
at java.desktop/javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2314)
at java.desktop/javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:407)
at java.desktop/javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:262)
at java.desktop/javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:279)
at java.desktop/java.awt.Component.processMouseEvent(Component.java:6620)
at java.desktop/javax.swing.JComponent.processMouseEvent(JComponent.java:3398)
at java.desktop/java.awt.Component.processEvent(Component.java:6385)
at java.desktop/java.awt.Container.processEvent(Container.java:2266)
at java.desktop/java.awt.Component.dispatchEventImpl(Component.java:4995)
at java.desktop/java.awt.Container.dispatchEventImpl(Container.java:2324)
at java.desktop/java.awt.Component.dispatchEvent(Component.java:4827)
at java.desktop/java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4948)
at java.desktop/java.awt.LightweightDispatcher.processMouseEvent(Container.java:4575)
at java.desktop/java.awt.LightweightDispatcher.dispatchEvent(Container.java:4516)
at java.desktop/java.awt.Container.dispatchEventImpl(Container.java:2310)
at java.desktop/java.awt.Window.dispatchEventImpl(Window.java:2780)
at java.desktop/java.awt.Component.dispatchEvent(Component.java:4827)
at java.desktop/java.awt.EventQueue.dispatchEventImpl(EventQueue.java:775)
at java.desktop/java.awt.EventQueue$4.run(EventQueue.java:720)
at java.desktop/java.awt.EventQueue$4.run(EventQueue.java:714)
at java.base/java.security.AccessController.doPrivileged(AccessController.java:400)
at java.base/java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:87)
at java.base/java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:98)
at java.desktop/java.awt.EventQueue$5.run(EventQueue.java:747)
at java.desktop/java.awt.EventQueue$5.run(EventQueue.java:745)
at java.base/java.security.AccessController.doPrivileged(AccessController.java:400)
at java.base/java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:87)
at java.desktop/java.awt.EventQueue.dispatchEvent(EventQueue.java:744)
at java.desktop/java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:203)
at java.desktop/java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:124)
at java.desktop/java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:113)
at java.desktop/java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:109)
at java.desktop/java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:101)
at java.desktop/java.awt.EventDispatchThread.run(EventDispatchThread.java:90)
I did a lot of search before I asked, but I did not find related solution. I also tried to capitalize the code of the tables schema, I also tried to capitalize the query; but the same error happens with ununderstood reason.
This bug was fixed in H2 2.3.232, you need to upgrade.
You can also re-write your joins correctly, missing join conditions aren't valid in SQL, but some DBMS don't complaint about them:
SELECT inwards.item_id AS item_id, inwards.id AS inward_id, inwards.quantity, u.id AS unit_id ,u.name AS unit_name, s.id AS source_id, s.information AS source_information, inwards.date, i.name AS item_name, i.specification AS item_specs
FROM inwards
JOIN items AS i ON inwards.item_id = i.id
JOIN quantity_unit AS u ON i.unit_id = u.id
JOIN source AS s ON s.id = inwards.source_id
ORDER BY inwards.date ASC, inwards.id ASC
LIMIT 10 OFFSET 0
Corrected query will work in older versions of H2 too.
But don't use H2 2.3.230 anywhere, this version has significant problems.