GORM Tip – Table or view does not exist

Consider the following domain class where the class name and property names matched the database table name and field names.  Because this was true, I was hoping to rely on GORM “convention” and not configure any O/R mapping in the grails domain class or a Hibernate mapping file.

class PlayerTeam{

    static constraints = {
	}

  Long playerTeamId
  Long playerId
  Long teamId
}

In my controller, I called…

PlayerTeam.findByTeamId(teamId)

… and I receive this error

java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

This left me confused, as I thought using GORM convention would work.  I decided I needed to see the SQL that was actually being executed.  So I updated my DataSource.groovy.  (WARNING:  This will log a lot of SQL!)

environments {
	development {
		dataSource {
              url = "jdbc:oracle:thin:@dbserver:1521:dev"
              username = "stevewall123"
              password = "stevewall123"
              //***I CHANGED THIS LINE TO true***
              loggingSql = true
		}
        //Other environments
}

And this is what showed up in the console:

Hibernate:
    select
        *
    from
        ( select
            this_.id as id0_0_,
            this_.version as version0_0_,
            this_.team_id as team3_0_0_,
            this_.player_id as player4_0_0_,
            this_.player_team_id as player5_0_0_
        from
            player_team this_
        where
            this_.team_id=? )
    where
        rownum <= ?

java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

Lessons Learned about GORM convention when used with Java naming convention of classes and fields:

  • Look at the table name GORM generated:  player_team (the table in the database does not have underscores)
  • Look at the fields it is selecting:  player_id, team_id and player_team_id  (the fields in the database table do not have underscores)

To solve this, I added a static mapping block to my PlayerTeam domain class to map to the table and fields.

class PlayerTeam {

    static constraints = {
	}

  static mapping = {
    table 'ASSETONAGREEMENT'
    id column: 'playerTeamId'
    playerId column: 'playerId'
    teamId column: 'teamId'
  }

  Long id
  Long teamId
  Long playerId
}
PlayerTeam
Advertisements

About stevewall123

I am a Lead Software Engineer in Minneapolis working for Thomson Reuters. I am currently working on projects using Java, JavaScript, Spring, Drools, Hazelcast, Liquibase and Tomcat. Previously, I used C#, GWT, Grails, Groovy, JMS and JBoss Drools Guvnor. In the past I have worked on projects using J2EE, Swing, Webwork, Hibernate, Spring, Spring-WS, JMS, JUnit and Ant.
This entry was posted in Grails. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s