Announcement Announcement Module
Collapse
No announcement yet.
Spring JDBC and sql table alias don't seem to work on SQL Server Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Spring JDBC and sql table alias don't seem to work on SQL Server

    I've got a simple dao that I've written as follows and am trying to run against sql server 2008 R2.

    Code:
    ...
    
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.util.List;
    
    import org.springframework.dao.EmptyResultDataAccessException;
    import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
    import org.springframework.jdbc.core.namedparam.NamedParameterJdbcDaoSupport;
    import org.springframework.jdbc.core.RowMapper;
    import org.springframework.stereotype.Repository;
    
    ...
    
    @Repository
    public class JdbcDepartmentDao extends NamedParameterJdbcDaoSupport implements IDepartmentDao
    {
    	private static final String FIND_ALL_DEPARTMENTS = "select d.* from department d order by d.name";
    	
    	public List<Department> findAll() {
    		return this.getJdbcTemplate().query(FIND_ALL_DEPARTMENTS, new DepartmentMapper());
    	}
    	
    	private static class DepartmentMapper implements RowMapper<Department> {
            public Department mapRow(ResultSet rs, int rowNum) throws SQLException {
            	Department department = new Department();
            	department.setId(rs.getLong("d.departmentId"));
            	department.setName(rs.getString("d.name"));
                return department;
            }
        }
    }
    When I run the findAll() method, it dumps out in the RowMapper with the following error

    Code:
     INFO [main] Aug/21 08:10:05,321 support.ClassPathXmlApplicationContext.[] - Refreshing org.springframework.context.support.ClassPathXmlApplicationContext@578088c0: startup date [Sun Aug 21 08:10:05 CDT 2011]; root of context hierarchy
     INFO [main] Aug/21 08:10:05,541 xml.XmlBeanDefinitionReader.[] - Loading XML bean definitions from class path resource [beans.xml]
     INFO [main] Aug/21 08:10:07,159 config.PropertyPlaceholderConfigurer.[] - Loading properties file from class path resource [datasource.properties]
     INFO [main] Aug/21 08:10:07,254 support.DefaultListableBeanFactory.[] - Pre-instantiating singletons in org.springframework.beans.factory.support.DefaultListableBeanFactory@7c2e1f1f: defining beans [propertyConfigurer,jobs,org.springframework.aop.config.internalAutoProxyCreator,org.springframework.transaction.annotation.AnnotationTransactionAttributeSource#0,org.springframework.transaction.interceptor.TransactionInterceptor#0,org.springframework.transaction.config.internalTransactionAdvisor,transactionManager,departmentDao,jobDao,locationDao]; root of factory hierarchy
     INFO [main] Aug/21 08:10:07,305 datasource.DriverManagerDataSource.[] - Loaded JDBC driver: net.sourceforge.jtds.jdbc.Driver
    DEBUG [main] Aug/21 08:10:07,662 core.JdbcTemplate.[] - Executing SQL query [select d.* from department d order by d.name]
     INFO [main] Aug/21 08:10:07,928 xml.XmlBeanDefinitionReader.[] - Loading XML bean definitions from class path resource [org/springframework/jdbc/support/sql-error-codes.xml]
     INFO [main] Aug/21 08:10:08,060 support.SQLErrorCodesFactory.[] - SQLErrorCodes loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase]
    Exception in thread "main" org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [select d.* from department d order by d.name]; nested exception is java.sql.SQLException: Invalid column name d.departmentId.
    	at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:98)
    	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:406)
    	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:455)
    	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:463)
    	at dao.jdbc.JdbcDepartmentDao.findAll(JdbcDepartmentDao.java:27)
    	at driver.DepartmentDaoDriver.main(DepartmentDaoDriver.java:16)
    Caused by: java.sql.SQLException: Invalid column name d.departmentId.
    	at net.sourceforge.jtds.jdbc.JtdsResultSet.findColumn(JtdsResultSet.java:979)
    	at net.sourceforge.jtds.jdbc.JtdsResultSet.getLong(JtdsResultSet.java:987)
    	at dao.jdbc.JdbcDepartmentDao$DepartmentMapper.mapRow(JdbcDepartmentDao.java:33)
    	at dao.jdbc.JdbcDepartmentDao$DepartmentMapper.mapRow(JdbcDepartmentDao.java:1)
    	at org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:92)
    	at org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:1)
    	at org.springframework.jdbc.core.JdbcTemplate$1QueryStatementCallback.doInStatement(JdbcTemplate.java:445)
    	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:395)
    	... 4 more
    For some reason it looks like it doesn't know how how to interpret the table alias "d." but I can run the exact same thing against mysql and it works fine. It works just fine if I alias each field name individually but for large tables, I would rather not clutter my sql statement and instead use something like "select d.* from department ...". Does anyone know how I can get this to work? I've tried both the jtds driver and the Microsoft drive and both do the same thing. I'm using java 1.5 and spring 3.0.5.RELEASE.

  • #2
    Hello

    For some reason it looks like it doesn't know how how to interpret the table alias "d." but I can run the exact same thing against mysql and it works fine.
    Some Database providers has its own tricks

    Just curious, try for your MS SQL Server with the follow, Microsoft has its own history

    About your error stack trace
    Code:
    Exception in thread "main" 
    org.springframework.jdbc.BadSqlGrammarException: 
    StatementCallback; bad SQL grammar 
    [select d.* from department d order by d.name]; 
    nested exception is java.sql.SQLException: 
    Invalid column name d.departmentId.
    One (only add the follow blue part)
    Code:
    private static final String FIND_ALL_DEPARTMENTS = 
    "select d.* from department as d order by d.name";
    Two (SQL remains without changes, but in DepartmentMapper we delete the d.*)

    Code:
    private static final String FIND_ALL_DEPARTMENTS = 
    "select d.* from department  d order by d.name";
    ...
    private static class DepartmentMapper implements RowMapper<Department> {
            public Department mapRow(ResultSet rs, int rowNum) throws SQLException {
            	Department department = new Department();
            	department.setId(rs.getLong("departmentId"));
            	department.setName(rs.getString("name"));
                return department;
            }
    ...
    Let me know each result for each suggestion

    HTH

    Comment


    • #3
      Well,

      Option 1: didn't work...same error.
      Option 2: is what I'm currently doing...the problem here is that I need to do a join on a couple of tables where each table has a column with the same name so I need to be able to specify the table via the alias (otherwise, I'm thinking that I have to explicitly name each column in the select portion of the statement...which I'm trying to avoid).

      Any other thoughts?

      Comment


      • #4
        Hello

        Option 1: didn't work...same error.
        This is weird

        I have a question for you. Did you check the Tables generated to MySQL and SQL Server? Are you totally sure both has the same fields names?

        How you are creating each Table for each Database? if is by hand perhaps you wrote wrong some field

        the problem here is that I need to do a join on a couple of tables where each table has a column with the same name so I need to be able to specify the table via the alias (otherwise, I'm thinking that I have to explicitly name each column in the select portion of the statement...which I'm trying to avoid).
        I had the same situation when I worked with JasperReports, and the alias work fine.

        Check my question 1, perhaps just there, is the problem

        This work on MySQL?
        Code:
        department.setId(rs.getLong("d.departmentId"));

        Comment

        Working...
        X