HSQLDB issue: HSQL as backend for the existing MVC application using JDBC template

[aaziz]

I done my MVC web application using spring security 2.0.4 and spring 2.5 and HSQLDB, where I made CRUD application. For products I already used HSQL as a database. And I integrated security by using roles which are hard coded in my applicationContext-security.xml like this:

    <authentication-provider>
                <user-service id="userDetailsService">
                        <user name="admin" password="admin" authorities="ROLE_USER, ROLE_ADMIN" />
                        <user name="username" password="password" authorities="ROLE_USER" />
                        <user name="test" password="test" authorities="ROLE_USER" />
                </user-service>
        </authentication-provider>

Now I have to use same HSQL databse which I am using for products for the roles(Their user names and passwords). So I done the following cofigurations in my application:

My dataAccessContext.xml

    	<bean id="dataSource"
    		class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    		<property name="driverClassName" value="org.hsqldb.jdbcDriver" />
    		<property name="url" value="jdbc:hsqldb:mem:test" />
    		<property name="username" value="sa" />
    		<property name="password" value="" />
    	</bean>
    
    	<bean id="dataSourcePopulator" class="springapp1.service.HsqldbSchemaAndDataPopulator">
    		<property name="dataSource" ref="dataSource" />
    	</bean>
    </beans>

This is my dataSourcePopulator file: HsqldbSchemaAndDataPopulator.java

    package springapp1.service;
    
        import javax.sql.DataSource;
        
        import org.springframework.beans.factory.InitializingBean;
        import org.springframework.jdbc.core.JdbcTemplate;
        import org.springframework.util.Assert;
        
        /**
         * I am responsible for populating the configured datasource
         */
        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);");
        
                // 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('test','test',TRUE);");
        
                template
                        .execute("INSERT INTO AUTHORITIES VALUES('admin','ROLE_USER');");
                template
                        .execute("INSERT INTO AUTHORITIES VALUES('admin','ROLE_ADMIN');");
        
                template
                        .execute("INSERT INTO AUTHORITIES VALUES('username','ROLE_USER');");
        
                template.execute("INSERT INTO AUTHORITIES VALUES('test','ROLE_USER');");
            }
        
            public void setDataSource(final DataSource dataSource) {
                this.template = new JdbcTemplate(dataSource);
            }
        }

My web.xml snippet:

    <context-param>
            	<param-name>contextConfigLocation</param-name>
            	<param-value>
                          /WEB-INF/applicationContext-security.xml
                          /WEB-INF/dataAccessContext.xml
                          /WEB-INF/applicationContext.xml
                    </param-value>
     </context-param>

I updated my applicationContext-security.xml file

    <authentication-provider>
                <jdbc-user-service id="userDetailsService" data-source-ref="dataSource" />
        </authentication-provider>

Now when i run the application it gives me following error in localhost log file:

exception

org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [select id, description, price from products]; nested exception is java.sql.SQLException: Table not found in statement [select id, description, price from products]
	org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:583)
	org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:501)
	javax.servlet.http.HttpServlet.service(HttpServlet.java:617)
	javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
	org.springframework.security.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:378)
	org.springframework.security.intercept.web.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:109)
	org.springframework.security.intercept.web.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:83)
....
root cause

org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [select id, description, price from products]; nested exception is java.sql.SQLException: Table not found in statement [select id, description, price from products]
	org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:220)
	org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
	org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:407)
	org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:458)
	org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:466)
	org.springframework.jdbc.core.simple.SimpleJdbcTemplate.query(SimpleJdbcTemplate.java:187)
	springapp1.repository.JdbcProductDao.getProductList(JdbcProductDao.java:58)
	springapp1.service.SimpleProductManager.getProducts(SimpleProductManager.java:20)
	sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
	sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
...

And its looking in the product list not in the users info list.

Continued...

[aaziz]

Implementation for Product DAO:

    package springapp1.repository;
    
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.util.List;
    
    import org.apache.commons.logging.Log;
    import org.apache.commons.logging.LogFactory;
    import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
    import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
    import org.springframework.jdbc.core.simple.SimpleJdbcDaoSupport;
    
    import springapp1.domain.Product;
    
    public class JdbcProductDao extends SimpleJdbcDaoSupport implements ProductDao {
    
    	public void deleteProduct(Product prod) {
    		logger.info("Deleting product with id: " + prod.getId());
    		int count = getSimpleJdbcTemplate().update(
    				"DELETE FROM products WHERE id = :id",
    				new MapSqlParameterSource().addValue("id", prod.getId()));
    		logger.info(count + " rows were deleted");
    	}
    
    	public List<Product> retrieveProduct(int id) {
    		List<Product> products = getSimpleJdbcTemplate().query(
    				"select id, description, price from products where id = :id",
    				new ProductMapper(),
    				new MapSqlParameterSource().addValue("id", id));
    		if (products.size() == 0)
    			return null;
    		else
    			products.get(id);
    		return products;
    	}
    
    	public Product createProduct(Product p) {
    		logger.info("Creating product: " + p.getDescription() + " Price:"
    				+ p.getPrice());
    
    		int count = getSimpleJdbcTemplate()
    				.update("INSERT INTO products (description,price,id) VALUES (:description, :price,:id)",
    						new MapSqlParameterSource()
    								.addValue("description", p.getDescription())
    								.addValue("price", p.getPrice())
    								.addValue("id", p.getId()));
    
    		logger.info("Rows inserted: " + count);
    
    		return p;
    	}
    
    	/** Logger for this class and subclasses */
    	protected final Log logger = LogFactory.getLog(getClass());
    
    	public List<Product> getProductList() {
    		logger.info("Getting products!");
    		List<Product> products = getSimpleJdbcTemplate().query(
    				"select id, description, price from products",
    				new ProductMapper());
    		return products;
    	}
    
    	public void saveProduct(Product prod) {
    		logger.info("Saving product: " + prod.getDescription());
    		int count = getSimpleJdbcTemplate()
    				.update("update products set description = :description, price = :price where id = :id",
    						new MapSqlParameterSource()
    								.addValue("description", prod.getDescription())
    								.addValue("price", prod.getPrice())
    								.addValue("id", prod.getId()));
    		logger.info("Rows affected: " + count);
    	}
    
    	private static class ProductMapper implements
    			ParameterizedRowMapper<Product> {
    
    		public Product mapRow(ResultSet rs, int rowNum) throws SQLException {
    			Product prod = new Product();
    			prod.setId(rs.getInt("id"));
    			prod.setDescription(rs.getString("description"));
    			prod.setPrice(new Double(rs.getDouble("price")));
    			return prod;
    		}
    
    	}
    
    }

I am very confused and tried all things but of no use. Can any one help? Thank you

[Marten Deinum]

Why is it strange?! You have a products dao which judging by the queries operates on the products table so why on earth do you expect these queries to magically work on a user table?!

[aaziz]

Thank you for your kind answer and quick reply. You are right with this that

so why on earth do you expect these queries to magically work on a user table?!

It is ok that the products are populating well. But the problem is how to configure the HSQL to store and then select credentials from the database, here I am confused. I will really grateful for your time.

[Marten Deinum]

Your problem description is vague and not concrete imho. What is it you want?! You tell something about spring security then move to a product dao that isn't working which you probably expect to operate on the users table?! So you see I'm a bit off...

Also your in-memory database doesn't do anything with products so that your application is giving this erorr isn't strange the table isn't there...

[aaziz]

I am trying to access (read, insert etc) from same/single data base for both product display (and some other queries) and to store and read credentials info to authenticate users from the database as well.

[Marten Deinum]

As stated there are no product tables in your database only user tables... If you don't create the tables nothing is going to be there.

[aaziz]

I am sorry, actually I created .sql file which contains (sample products):

CREATE TABLE products (
id INTEGER NOT NULL PRIMARY KEY,
description varchar(255),
price decimal(15,2)
);
CREATE INDEX products_description ON products(description);

and

INSERT INTO products (id, description, price) values(1, 'Lamp', 5.78);
INSERT INTO products (id, description, price) values(2, 'Table', 75.29);
INSERT INTO products (id, description, price) values(3, 'Chair', 22.81);

As you told me that the user table is created. But how can I select the user info from database.

[Marten Deinum]

The fact that you have a sql file doesn't mean the table is created as I mentioned before there is no product table!!!

Also what is your problem with the user table there is no problem! If you want to have a userdao write one which operates/selects data from the user table simply write a query...

So in short I still don't understand the problem you are having... IMHO you are trying to fit a square object into a round hole...

[aaziz]

I want to thank you for your kind reply, which enforce me to search for the main problem in my application. so now I have managed to work both (MVC application with security integration using database) using Spring 2.5, Spring security 2.0.4 and HSQLDB. What I have done here is that I have added in my dataSourcePopulator file: HsqldbSchemaAndDataPopulator.java following lines for the products to be populated:

template
.execute("CREATE TABLE USERS(USERNAME VARCHAR_IGNORECASE(50) NOT NULL PRIMARY KEY,"
+ "PASSWORD VARCHAR_IGNORECASE(50) NOT NULL,"
+ "ENABLED BOOLEAN NOT NULL);");
//some dummy items
template
.execute("INSERT INTO products (id, description, price) values(1, 'Lamp', 5.78);");
template
.execute("INSERT INTO products (id, description, price) values(2, 'Table', 75.29);");
template
.execute("INSERT INTO products (id, description, price) values(3, 'Chair', 22.81);");

And its working.