Following this tutorial about Vaadin 7 and SQLContainer I encountered the following problem.
I am coding inside Netbeans 8.0.2 and would like to use the embedded JavaDB/Derby database over JDBC in this early development stage.
I am trying to databind a table in my Vaadin application to show data. Inside init(VaadinRequest vaadinRequest) I call my database() method.
import com.vaadin.data.util.sqlcontainer.SQLContainer;
import com.vaadin.data.util.sqlcontainer.connection.JDBCConnectionPool;
import com.vaadin.data.util.sqlcontainer.connection.SimpleJDBCConnectionPool;
import com.vaadin.data.util.sqlcontainer.query.TableQuery;
private void database() {
System.out.println("database()");
JDBCConnectionPool pool = null;
try {
pool = new SimpleJDBCConnectionPool("org.apache.derby.jdbc.ClientDriver",
"jdbc:derby://localhost:1527/gr", "a", "a", 2, 5);
TableQuery tq = new TableQuery("GROCERY", pool);
tq.setVersionColumn("OPTLOCK");
SQLContainer container = new SQLContainer(tq); // <-- raises exception
//container.setAutoCommit(true);
Item i = container.addItem(1);
Object props = i.getItemPropertyIds();
} catch (SQLException ex) {
Logger.getLogger(MyUI.class.getName()).log(Level.SEVERE, null, ex);
}
}
Instead of the data I get this exception. What am I doing wrong or missing?
java.sql.SQLSyntaxErrorException: Syntax error: Encountered "LIMIT" at line 1, column 41.
at org.apache.derby.client.am.SQLExceptionFactory40.getSQLException(Unknown Source)
at org.apache.derby.client.am.SqlException.getSQLException(Unknown Source)
at org.apache.derby.client.am.Connection.prepareStatement(Unknown Source)
at com.vaadin.data.util.sqlcontainer.query.TableQuery.executeQuery(TableQuery.java:526)
at com.vaadin.data.util.sqlcontainer.query.TableQuery.getResults(TableQuery.java:252)
at com.vaadin.data.util.sqlcontainer.SQLContainer.getPropertyIds(SQLContainer.java:1200)
at com.vaadin.data.util.sqlcontainer.SQLContainer.<init>(SQLContainer.java:134)
at org.darugna.MyUI.database(MyUI.java:294)
at org.darugna.MyUI.init(MyUI.java:194)
at com.vaadin.ui.UI.doInit(UI.java:675)
at com.vaadin.server.communication.UIInitHandler.getBrowserDetailsUI(UIInitHandler.java:214)
at com.vaadin.server.communication.UIInitHandler.synchronizedHandleRequest(UIInitHandler.java:74)
at com.vaadin.server.SynchronizedRequestHandler.handleRequest(SynchronizedRequestHandler.java:41)
at com.vaadin.server.VaadinService.handleRequest(VaadinService.java:1408)
at com.vaadin.server.VaadinServlet.service(VaadinServlet.java:351)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
at org.apache.catalina.core.StandardWrapper.service(StandardWrapper.java:1682)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:344)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:214)
at org.glassfish.tyrus.servlet.TyrusServletFilter.doFilter(TyrusServletFilter.java:295)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:256)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:214)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:316)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:160)
at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:734)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:673)
at com.sun.enterprise.web.WebPipeline.invoke(WebPipeline.java:99)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:174)
at org.apache.catalina.connector.CoyoteAdapter.doService(CoyoteAdapter.java:415)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:282)
at com.sun.enterprise.v3.services.impl.ContainerMapper$HttpHandlerCallable.call(ContainerMapper.java:459)
at com.sun.enterprise.v3.services.impl.ContainerMapper.service(ContainerMapper.java:167)
at org.glassfish.grizzly.http.server.HttpHandler.runService(HttpHandler.java:201)
at org.glassfish.grizzly.http.server.HttpHandler.doHandle(HttpHandler.java:175)
at org.glassfish.grizzly.http.server.HttpServerFilter.handleRead(HttpServerFilter.java:235)
at org.glassfish.grizzly.filterchain.ExecutorResolver$9.execute(ExecutorResolver.java:119)
at org.glassfish.grizzly.filterchain.DefaultFilterChain.executeFilter(DefaultFilterChain.java:284)
at org.glassfish.grizzly.filterchain.DefaultFilterChain.executeChainPart(DefaultFilterChain.java:201)
at org.glassfish.grizzly.filterchain.DefaultFilterChain.execute(DefaultFilterChain.java:133)
at org.glassfish.grizzly.filterchain.DefaultFilterChain.process(DefaultFilterChain.java:112)
at org.glassfish.grizzly.ProcessorExecutor.execute(ProcessorExecutor.java:77)
at org.glassfish.grizzly.nio.transport.TCPNIOTransport.fireIOEvent(TCPNIOTransport.java:561)
at org.glassfish.grizzly.strategies.AbstractIOStrategy.fireIOEvent(AbstractIOStrategy.java:112)
at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy.run0(WorkerThreadIOStrategy.java:117)
at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy.access$100(WorkerThreadIOStrategy.java:56)
at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy$WorkerThreadRunnable.run(WorkerThreadIOStrategy.java:137)
at org.glassfish.grizzly.threadpool.AbstractThreadPool$Worker.doWork(AbstractThreadPool.java:565)
at org.glassfish.grizzly.threadpool.AbstractThreadPool$Worker.run(AbstractThreadPool.java:545)
at java.lang.Thread.run(Thread.java:745)
Caused by: org.apache.derby.client.am.SqlException: Syntax error: Encountered "LIMIT" at line 1, column 41.
at org.apache.derby.client.am.Statement.completeSqlca(Unknown Source)
at org.apache.derby.client.net.NetStatementReply.parsePrepareError(Unknown Source)
at org.apache.derby.client.net.NetStatementReply.parsePRPSQLSTTreply(Unknown Source)
at org.apache.derby.client.net.NetStatementReply.readPrepareDescribeOutput(Unknown Source)
at org.apache.derby.client.net.StatementReply.readPrepareDescribeOutput(Unknown Source)
at org.apache.derby.client.net.NetStatement.readPrepareDescribeOutput_(Unknown Source)
at org.apache.derby.client.am.Statement.readPrepareDescribeOutput(Unknown Source)
at org.apache.derby.client.am.PreparedStatement.readPrepareDescribeInputOutput(Unknown Source)
at org.apache.derby.client.am.PreparedStatement.flowPrepareDescribeInputOutput(Unknown Source)
at org.apache.derby.client.am.PreparedStatement.prepare(Unknown Source)
at org.apache.derby.client.am.Connection.prepareStatementX(Unknown Source)
... 47 more
This is my table's schema
CREATE TABLE grocery (
id INTEGER PRIMARY KEY,
OPTLOCK INTEGER,
name TEXT,
category TEXT,
price INTEGER
);
The problem lies in the fact that Derby does not support SQL LIMIT.
One must supply its own SQL generator that creates the query in some other way.
I'm posting here the solution by Janko Dimitroff that I found in Vaadin forums.
import com.vaadin.data.util.sqlcontainer.query.generator.DefaultSQLGenerator;
/**
* @author Janko Dimitroff
*/
@SuppressWarnings("serial")
public class DerbySQLGenerator extends DefaultSQLGenerator {
public DerbySQLGenerator() {
}
/** Construct a DerbySQLGenerator with the specified identifiers for start and end of quoted strings. The identifiers
* may be different depending on the database engine and it's settings.
*
* @param quoteStart the identifier (character) denoting the start of a quoted string
* @param quoteEnd the identifier (character) denoting the end of a quoted string */
public DerbySQLGenerator(String quoteStart, String quoteEnd) {
super(quoteStart, quoteEnd);
}
/** Generates the LIMIT and OFFSET clause.
*
* @param sb StringBuffer to which the clause is appended.
* @param offset Value for offset.
* @param pagelength Value for pagelength.
* @return StringBuffer with LIMIT and OFFSET clause added. */
protected StringBuffer generateLimits(StringBuffer sb, int offset, int pagelength) {
sb.append(" OFFSET ").append(offset).append(" ROWS").append(" FETCH NEXT ").append(pagelength).append(" ROWS ONLY");
return sb;
}
}