Announcement Announcement Module
Collapse
No announcement yet.
HSQLDB issue: HSQL as backend for the existing MVC application using JDBC template Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

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

    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:

    Code:
        <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

    Code:
        	<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

    Code:
        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:
    Code:
        <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

    Code:
        <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:

    Code:
    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...

  • #2
    Implementation for Product DAO:

    Code:
        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

    Comment


    • #3
      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?!

      Comment


      • #4
        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.

        Comment


        • #5
          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...

          Comment


          • #6
            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.

            Comment


            • #7
              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.

              Comment


              • #8
                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.

                Comment


                • #9
                  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...

                  Comment


                  • #10
                    Issue SOLVED

                    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.
                    Last edited by aaziz; Mar 7th, 2012, 06:09 AM. Reason: Got my answer

                    Comment

                    Working...
                    X