使用JavaDB/Derby的Vaadin SQLSyntaxErrorException：在第1行第41列遇到&Quot；Limit&Quot； [英] Vaadin SQLSyntaxErrorException using JavaDB/Derby: Encountered "LIMIT" at line 1, column 41
本文介绍了使用JavaDB/Derby的Vaadin SQLSyntaxErrorException：在第1行第41列遇到&Quot；Limit&Quot；的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
按照这个tutorial about Vaadin 7 and SQLContainer，我遇到了以下问题。
我在NetBeans 8.0.2中编码，并希望在此早期开发阶段使用JDBC上的嵌入式JavaDB/Derby数据库。
我正尝试在我的Vaadin应用程序中用数据绑定一个表来显示数据。在init(VaadinRequest vaadinRequest)内部，我调用我的database()方法。
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);
}
}
我得到的不是数据，而是这个异常。我做错了什么或遗漏了什么？
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
这是我的表的架构
CREATE TABLE grocery (
id INTEGER PRIMARY KEY,
OPTLOCK INTEGER,
name TEXT,
category TEXT,
price INTEGER
);
推荐答案
问题出在Derby不支持sql限制。
必须提供其自己的SQL生成器，该生成器以某种其他方式创建查询。
我在此发布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;
}
}
这篇关于使用JavaDB/Derby的Vaadin SQLSyntaxErrorException：在第1行第41列遇到&Quot；Limit&Quot；的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
