Databases
StackOverflow2013
Postsdbo
POSQL JDBC template drop table issue: drop table with FK and Unique index (JDBC template)
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL JDBC template drop table issue: drop table with FK and Unique index (JDBC template)
    primarykey
    data
    text
    <p>I am using JDBC template for my web+MVC+Security (using Spring 2.5, Spring security 2.0.4) for the user authentication with roles in it. When I am trying to execute it, its giving me the following error:</p> <pre><code>Mar 06, 2012 10:10:42 AM org.apache.catalina.core.ApplicationContext log INFO: Closing Spring root WebApplicationContext Mar 06, 2012 10:11:42 AM org.apache.catalina.core.ApplicationContext log INFO: Initializing Spring root WebApplicationContext Mar 06, 2012 10:11:43 AM org.apache.catalina.core.StandardContext listenerStart SEVERE: Exception sending context initialized event to listener instance of class org.springframework.web.context.ContextLoaderListener org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'dataSourcePopulator' defined in ServletContext resource [/WEB-INF/dataAccessContext.xml]: Invocation of init method failed; nested exception is org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [CREATE TABLE USERS(USERNAME VARCHAR_IGNORECASE(50) NOT NULL PRIMARY KEY,PASSWORD VARCHAR_IGNORECASE(50) NOT NULL,ENABLED BOOLEAN NOT NULL);]; nested exception is java.sql.SQLException: Table already exists: USERS in statement [CREATE TABLE USERS] at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1338) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:473) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory$1.run(AbstractAutowireCapableBeanFactory.java:409) at java.security.AccessController.doPrivileged(Native Method) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:380) at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:264) at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222) at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:261) at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:185) at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:164) at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:429) at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:728) at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:380) at org.springframework.web.context.ContextLoader.createWebApplicationContext(ContextLoader.java:255) at org.springframework.web.context.ContextLoader.initWebApplicationContext(ContextLoader.java:199) at org.springframework.web.context.ContextLoaderListener.contextInitialized(ContextLoaderListener.java:45) at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:4135) at org.apache.catalina.core.StandardContext.start(StandardContext.java:4630) at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:791) at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:771) at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:546) at org.apache.catalina.startup.HostConfig.deployWAR(HostConfig.java:905) at org.apache.catalina.startup.HostConfig.deployWARs(HostConfig.java:740) at org.apache.catalina.startup.HostConfig.deployApps(HostConfig.java:500) at org.apache.catalina.startup.HostConfig.check(HostConfig.java:1345) at org.apache.catalina.startup.HostConfig.lifecycleEvent(HostConfig.java:303) at org.apache.catalina.util.LifecycleSupport.fireLifecycleEvent(LifecycleSupport.java:119) at org.apache.catalina.core.ContainerBase.backgroundProcess(ContainerBase.java:1337) at org.apache.catalina.core.ContainerBase$ContainerBackgroundProcessor.processChildren(ContainerBase.java:1601) at org.apache.catalina.core.ContainerBase$ContainerBackgroundProcessor.processChildren(ContainerBase.java:1610) at org.apache.catalina.core.ContainerBase$ContainerBackgroundProcessor.run(ContainerBase.java:1590) at java.lang.Thread.run(Unknown Source) Caused by: org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [CREATE TABLE USERS(USERNAME VARCHAR_IGNORECASE(50) NOT NULL PRIMARY KEY,PASSWORD VARCHAR_IGNORECASE(50) NOT NULL,ENABLED BOOLEAN NOT NULL);]; nested exception is java.sql.SQLException: Table already exists: USERS in statement [CREATE TABLE USERS] at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:97) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:407) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:429) at springapp1.service.HsqldbSchemaAndDataPopulator.afterPropertiesSet(HsqldbSchemaAndDataPopulator.java:23) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1369) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1335) ... 31 more Caused by: java.sql.SQLException: Table already exists: USERS in statement [CREATE TABLE USERS] at org.hsqldb.jdbc.Util.sqlException(Unknown Source) at org.hsqldb.jdbc.jdbcStatement.fetchResult(Unknown Source) at org.hsqldb.jdbc.jdbcStatement.execute(Unknown Source) at org.springframework.jdbc.core.JdbcTemplate$1ExecuteStatementCallback.doInStatement(JdbcTemplate.java:422) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:396) ... 35 more </code></pre> <p>HsqldbSchemaAndDataPopulator.java (JDBC template)</p> <pre><code>public class HsqldbSchemaAndDataPopulator implements InitializingBean { private JdbcTemplate template; public void afterPropertiesSet() throws Exception { Assert.notNull(template, "dataSource required"); // add tables to represent admin core-domain instances. template .execute("CREATE TABLE USERS(USERNAME VARCHAR_IGNORECASE(50) NOT NULL PRIMARY KEY," + "PASSWORD VARCHAR_IGNORECASE(50) NOT NULL," + "ENABLED BOOLEAN NOT NULL);"); template .execute("CREATE TABLE AUTHORITIES(USERNAME VARCHAR_IGNORECASE(50) NOT NULL,AUTHORITY VARCHAR_IGNORECASE(50) NOT NULL,CONSTRAINT FK_AUTHORITIES_USERS FOREIGN KEY(USERNAME) REFERENCES USERS(USERNAME));"); template .execute("CREATE UNIQUE INDEX IX_AUTH_USERNAME ON AUTHORITIES(USERNAME,AUTHORITY);"); // add tables to represent bug tracking domain instances. template .execute("CREATE TABLE PROJECTS(ID BIGINT NOT NULL PRIMARY KEY, NAME VARCHAR_IGNORECASE(50) NOT NULL, DESCRIPTION VARCHAR_IGNORECASE(200) NOT NULL);"); // insert data here template .execute("INSERT INTO USERS VALUES('disabled','disabled',FALSE);"); template.execute("INSERT INTO USERS VALUES('admin','admin',TRUE);"); template .execute("INSERT INTO USERS VALUES('username','password',TRUE);"); template.execute("INSERT INTO USERS VALUES('user','pass',TRUE);"); //ADMIN-ROLES template .execute("INSERT INTO AUTHORITIES VALUES('admin','ROLE_USER');"); template .execute("INSERT INTO AUTHORITIES VALUES('admin','ROLE_ADMIN');"); //USER-ROLES template .execute("INSERT INTO AUTHORITIES VALUES('username','ROLE_USER');"); } public void setDataSource(final DataSource dataSource) { this.template = new JdbcTemplate(dataSource); } } </code></pre> <p>So, I add the following code to the HsqldbSchemaAndDataPopulator class:</p> <pre><code>//Delete tables if exist template .execute("ALTER TABLE AUTHORITIES DROP CONSTRAINT FK_AUTHORITIES_USERS;"); template .execute("ALTER TABLE AUTHORITIES DROP INDEX IX_AUTH_USERNAME;"); template .execute("DROP TABLE PROJECTS IF EXISTS;"); template .execute("DROP TABLE USERS IF EXISTS;"); template .execute("DROP TABLE USERS IF AUTHORITIES;"); </code></pre> <p>Now it gives me the following error:</p> <pre><code>Mar 06, 2012 10:11:43 AM org.apache.catalina.core.ApplicationContext log INFO: Closing Spring root WebApplicationContext Mar 06, 2012 10:16:33 AM org.apache.catalina.core.ApplicationContext log INFO: Initializing Spring root WebApplicationContext Mar 06, 2012 10:16:34 AM org.apache.catalina.core.StandardContext listenerStart SEVERE: Exception sending context initialized event to listener instance of class org.springframework.web.context.ContextLoaderListener org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'dataSourcePopulator' defined in ServletContext resource [/WEB-INF/dataAccessContext.xml]: Invocation of init method failed; nested exception is org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [ALTER TABLE AUTHORITIES DROP CONSTRAINT FK_AUTHORITIES_USERS;]; nested exception is java.sql.SQLException: Constraint not found FK_AUTHORITIES_USERS in table: AUTHORITIES in statement [ALTER TABLE AUTHORITIES DROP CONSTRAINT FK_AUTHORITIES_USERS] at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1338) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:473) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory$1.run(AbstractAutowireCapableBeanFactory.java:409) at java.security.AccessController.doPrivileged(Native Method) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:380) at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:264) at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222) at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:261) at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:185) at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:164) at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:429) at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:728) at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:380) at org.springframework.web.context.ContextLoader.createWebApplicationContext(ContextLoader.java:255) at org.springframework.web.context.ContextLoader.initWebApplicationContext(ContextLoader.java:199) at org.springframework.web.context.ContextLoaderListener.contextInitialized(ContextLoaderListener.java:45) at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:4135) at org.apache.catalina.core.StandardContext.start(StandardContext.java:4630) at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:791) at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:771) at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:546) at org.apache.catalina.startup.HostConfig.deployDirectory(HostConfig.java:1041) at org.apache.catalina.startup.HostConfig.deployDirectories(HostConfig.java:964) at org.apache.catalina.startup.HostConfig.deployApps(HostConfig.java:502) at org.apache.catalina.startup.HostConfig.check(HostConfig.java:1345) at org.apache.catalina.startup.HostConfig.lifecycleEvent(HostConfig.java:303) at org.apache.catalina.util.LifecycleSupport.fireLifecycleEvent(LifecycleSupport.java:119) at org.apache.catalina.core.ContainerBase.backgroundProcess(ContainerBase.java:1337) at org.apache.catalina.core.ContainerBase$ContainerBackgroundProcessor.processChildren(ContainerBase.java:1601) at org.apache.catalina.core.ContainerBase$ContainerBackgroundProcessor.processChildren(ContainerBase.java:1610) at org.apache.catalina.core.ContainerBase$ContainerBackgroundProcessor.run(ContainerBase.java:1590) at java.lang.Thread.run(Unknown Source) Caused by: org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [ALTER TABLE AUTHORITIES DROP CONSTRAINT FK_AUTHORITIES_USERS;]; nested exception is java.sql.SQLException: Constraint not found FK_AUTHORITIES_USERS in table: AUTHORITIES in statement [ALTER TABLE AUTHORITIES DROP CONSTRAINT FK_AUTHORITIES_USERS] at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:97) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:407) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:429) at springapp1.service.HsqldbSchemaAndDataPopulator.afterPropertiesSet(HsqldbSchemaAndDataPopulator.java:23) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1369) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1335) ... 31 more Caused by: java.sql.SQLException: Constraint not found FK_AUTHORITIES_USERS in table: AUTHORITIES in statement [ALTER TABLE AUTHORITIES DROP CONSTRAINT FK_AUTHORITIES_USERS] at org.hsqldb.jdbc.Util.sqlException(Unknown Source) at org.hsqldb.jdbc.jdbcStatement.fetchResult(Unknown Source) at org.hsqldb.jdbc.jdbcStatement.execute(Unknown Source) at org.springframework.jdbc.core.JdbcTemplate$1ExecuteStatementCallback.doInStatement(JdbcTemplate.java:422) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:396) ... 35 more </code></pre> <p>And this is my dataAccessContext.xml</p> <pre><code>&lt;?xml version="1.0" encoding="UTF-8"?&gt; &lt;beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd"&gt; &lt;!-- business stuff below --&gt; &lt;bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource"&gt; &lt;property name="driverClassName" value="org.hsqldb.jdbcDriver" /&gt; &lt;property name="url" value="jdbc:hsqldb:hsql://localhost" /&gt; &lt;property name="username" value="sa" /&gt; &lt;property name="password" value="" /&gt; &lt;/bean&gt; &lt;bean id="dataSourcePopulator" class="springapp1.service.HsqldbSchemaAndDataPopulator"&gt; &lt;property name="dataSource" ref="dataSource" /&gt; &lt;/bean&gt; &lt;/beans&gt; </code></pre> <p>I think I am doing something wrong with the SQL statements, but not exactly sure what it is? Can someone help me with this one? thank you.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USAbdulAziz
    1. USAbdulAziz
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload