[RESOLVIDO] org.hibernate.exception.SQLGrammarException

Assuntos Gerais
#1

Pessoal,

Já vi em vários outros lugares erros iguais ou semelhantes a este apontado por mim. Entretanto, venho pedir ajuda de vocês, pois não consegui sanar o meu problema especificamente.

Uso o Eclipse e o Hibernate 3.5.6 Final.

Seguem a saída do console (erro) e o código da (simples) aplicação:

CONSOLE:

5:02:25  INFO [Version             ] Hibernate Annotations 3.5.6-Final
15:02:25  INFO [Environment         ] Hibernate 3.5.6-Final
15:02:25  INFO [Environment         ] hibernate.properties not found
15:02:25  INFO [Environment         ] Bytecode provider name : javassist
15:02:25  INFO [Environment         ] using JDK 1.4 java.sql.Timestamp handling
15:02:25  INFO [Version             ] Hibernate Commons Annotations 3.2.0.Final
15:02:25  INFO [Configuration       ] configuring from resource: /hibernate.cfg.xml
15:02:25  INFO [Configuration       ] Configuration resource: /hibernate.cfg.xml
15:02:25  INFO [Configuration       ] Configured SessionFactory: null
15:02:25  INFO [HibernateSearchEventListenerRegister] Unable to find org.hibernate.search.event.FullTextIndexEventListener on the classpath. Hibernate Search is not enabled.
15:02:25  INFO [AnnotationBinder    ] Binding entity from annotated class: hello.Message
15:02:26  INFO [EntityBinder        ] Bind entity hello.Message on table MESSAGES
15:02:26  INFO [AnnotationConfiguration] Hibernate Validator not found: ignoring
15:02:26  INFO [DriverManagerConnectionProvider] Using Hibernate built-in connection pool (not for production use!)
15:02:26  INFO [DriverManagerConnectionProvider] Hibernate connection pool size: 20
15:02:26  INFO [DriverManagerConnectionProvider] autocommit mode: false
15:02:26  INFO [DriverManagerConnectionProvider] using driver: org.hsqldb.jdbcDriver at URL: jdbc:hsqldb:hsql//localhost
15:02:26  INFO [DriverManagerConnectionProvider] connection properties: {user=sa, password=****}
15:02:26  INFO [SettingsFactory     ] RDBMS: HSQL Database Engine, version: 1.8.1
15:02:26  INFO [SettingsFactory     ] JDBC driver: HSQL Database Engine Driver, version: 1.8.1
15:02:26  INFO [Dialect             ] Using dialect: org.hibernate.dialect.MySQLInnoDBDialect
15:02:26  INFO [JdbcSupportLoader   ] Disabling contextual LOB creation as JDBC driver reported JDBC version [3] less than 4
15:02:26  INFO [TransactionFactoryFactory] Using default transaction strategy (direct JDBC transactions)
15:02:26  INFO [TransactionManagerLookupFactory] No TransactionManagerLookup configured (in JTA environment, use of read-write or transactional second-level cache is not recommended)
15:02:26  INFO [SettingsFactory     ] Automatic flush during beforeCompletion(): disabled
15:02:26  INFO [SettingsFactory     ] Automatic session close at end of transaction: disabled
15:02:26  INFO [SettingsFactory     ] JDBC batch size: 15
15:02:26  INFO [SettingsFactory     ] JDBC batch updates for versioned data: disabled
15:02:26  INFO [SettingsFactory     ] Scrollable result sets: enabled
15:02:26  INFO [SettingsFactory     ] JDBC3 getGeneratedKeys(): disabled
15:02:26  INFO [SettingsFactory     ] Connection release mode: auto
15:02:26  INFO [SettingsFactory     ] Maximum outer join fetch depth: 2
15:02:26  INFO [SettingsFactory     ] Default batch fetch size: 1
15:02:26  INFO [SettingsFactory     ] Generate SQL with comments: disabled
15:02:26  INFO [SettingsFactory     ] Order SQL updates by primary key: disabled
15:02:26  INFO [SettingsFactory     ] Order SQL inserts for batching: disabled
15:02:26  INFO [SettingsFactory     ] Query translator: org.hibernate.hql.ast.ASTQueryTranslatorFactory
15:02:26  INFO [ASTQueryTranslatorFactory] Using ASTQueryTranslatorFactory
15:02:26  INFO [SettingsFactory     ] Query language substitutions: {}
15:02:26  INFO [SettingsFactory     ] JPA-QL strict compliance: disabled
15:02:26  INFO [SettingsFactory     ] Second-level cache: enabled
15:02:26  INFO [SettingsFactory     ] Query cache: disabled
15:02:26  INFO [SettingsFactory     ] Cache region factory : org.hibernate.cache.impl.NoCachingRegionFactory
15:02:26  INFO [SettingsFactory     ] Optimize cache for minimal puts: disabled
15:02:26  INFO [SettingsFactory     ] Structured second-level cache entries: disabled
15:02:26  INFO [SettingsFactory     ] Echoing all SQL to stdout
15:02:26  INFO [SettingsFactory     ] Statistics: disabled
15:02:26  INFO [SettingsFactory     ] Deleted entity synthetic identifier rollback: disabled
15:02:26  INFO [SettingsFactory     ] Default entity-mode: pojo
15:02:26  INFO [SettingsFactory     ] Named query checking : enabled
15:02:26  INFO [SettingsFactory     ] Check Nullability in Core (should be disabled when Bean Validation is on): enabled
15:02:27  INFO [SessionFactoryImpl  ] building session factory
15:02:27  INFO [SessionFactoryObjectFactory] Not binding factory to JNDI, no JNDI name configured
Hibernate: 
    insert 
    into
        MESSAGES
        (NEXT_MESSAGE_ID, MESSAGE_TEXT) 
    values
        (?, ?)
15:02:27  WARN [JDBCExceptionReporter] SQL Error: -22, SQLState: S0002
15:02:27 ERROR [JDBCExceptionReporter] Table not found in statement [insert into MESSAGES (NEXT_MESSAGE_ID, MESSAGE_TEXT) values (?, ?)]
Exception in thread "main" org.hibernate.exception.SQLGrammarException: could not insert: [hello.Message]
	at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:92)
	at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
	at org.hibernate.id.insert.AbstractSelectingDelegate.performInsert(AbstractSelectingDelegate.java:64)
	at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2329)
	at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2836)
	at org.hibernate.action.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:71)
	at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:267)
	at org.hibernate.event.def.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:321)
	at org.hibernate.event.def.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:204)
	at org.hibernate.event.def.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:130)
	at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:210)
	at org.hibernate.event.def.DefaultSaveEventListener.saveWithGeneratedOrRequestedId(DefaultSaveEventListener.java:56)
	at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:195)
	at org.hibernate.event.def.DefaultSaveEventListener.performSaveOrUpdate(DefaultSaveEventListener.java:50)
	at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:93)
	at org.hibernate.impl.SessionImpl.fireSave(SessionImpl.java:705)
	at org.hibernate.impl.SessionImpl.save(SessionImpl.java:693)
	at org.hibernate.impl.SessionImpl.save(SessionImpl.java:689)
	at hello.HelloWorld.main(HelloWorld.java:57)
Caused by: java.sql.SQLException: Table not found in statement [insert into MESSAGES (NEXT_MESSAGE_ID, MESSAGE_TEXT) values (?, ?)]
	at org.hsqldb.jdbc.Util.throwError(Unknown Source)
	at org.hsqldb.jdbc.jdbcPreparedStatement.<init>(Unknown Source)
	at org.hsqldb.jdbc.jdbcConnection.prepareStatement(Unknown Source)
	at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:534)
	at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:116)
	at org.hibernate.id.insert.AbstractSelectingDelegate.performInsert(AbstractSelectingDelegate.java:54)
	... 16 more

HIBERNATE.CFG.XML

<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">

<hibernate-configuration>
	<session-factory>

		<property name="hibernate.connection.driver_class">org.hsqldb.jdbcDriver</property>
		<property name="hibernate.connection.url">jdbc:hsqldb:hsql//localhost</property>
		<property name="hibernate.connection.username">sa</property>
		<property name="hibernate.connection.password"></property>
		<property name="hibernate.show_sql">true</property>
		<property name="hibernate.format_sql">true</property>
		<property name="hibernate.dialect">org.hibernate.dialect.MySQLInnoDBDialect</property>
		
		<property name="hibernate.c3po.min_size">5</property>
		<property name="hibernate.c3po.max_size">20</property>
		<property name="hibernate.c3po.timeout">300</property>
		<property name="hibernate.c3po.max_statements">50</property>
		<property name="hibernate.c3po.idle_test_period">3000</property>
		
		<property name="show_sql">true</property>
		<property name="format_sql">true</property>	
		
		<mapping class="hello.Message"/>
		<mapping class="hello.HelloWorld"/>
		<mapping class="hibernate.HibernateUtil"/>
	
	</session-factory>
</hibernate-configuration>

PERSISTENCE.XML

<?xml version="1.0" encoding="UTF-8"?>
<persistence xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd" version="1.0">
  <persistence-unit name="HelloWorld">
    <provider>org.hibernate.ejb.HibernatePersistence</provider>
    <properties>
        <property name="hibernate.ejb.cfgfile" value="hibernate.cfg.xml"/>
        <property name="hibernate.hbm2ddl.auto" value="create"/>
    </properties>
  </persistence-unit>
</persistence>

MESSAGE.JAVA

package hello;

import javax.persistence.*;

@Entity                         
@Table(name="MESSAGES")
public class Message {

    @Id@GeneratedValue
    @Column(name="MESSAGE_ID")
    private Long id;
    
    @Column(name="MESSAGE_TEXT")
    private String text;
    
    @ManyToOne(cascade=CascadeType.ALL)
    @JoinColumn(name="NEXT_MESSAGE_ID")
    private Message nextMessage;
	
	
	public Message() {
		
	}
	
	public Message(String text) {
		this.text = text;
	}

	public Long getId() {
		return id;
	}

	public void setId(Long id) {
		this.id = id;
	}

	public String getText() {
		return text;
	}

	public void setText(String text) {
		this.text = text;
	}

	public Message getNextMessage() {
		return nextMessage;
	}

	public void setNextMessage(Message nextMessage) {
		this.nextMessage = nextMessage;
	}

}

HIBERNATEUTIL.JAVA

package hibernate;

import org.hibernate.SessionFactory;
import org.hibernate.cfg.AnnotationConfiguration;

public class HibernateUtil {
	private static final SessionFactory sessionFactory = buildSessionFactory();
	
	private static SessionFactory buildSessionFactory(){
		return new AnnotationConfiguration().configure().buildSessionFactory();
	}
	
	public static SessionFactory getSessionFactory(){
		return sessionFactory;
	}
}

HELLOWORLD.JAVA

package hello;

import hibernate.HibernateUtil;

import java.util.*;

import org.hibernate.Session;

public class HelloWorld {

	/**
	 * @param args
	 */
	public static void main(String[] args) {
				
		Session session = HibernateUtil.getSessionFactory().openSession();
		org.hibernate.Transaction tx = session.beginTransaction();
		
		Message message = new Message("Hello World");
		Long msgId = (Long) session.save(message);
		
		try {
			tx.commit();
		} catch (SecurityException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		session.close();
		
		///NOVA SESSÃO
		Session newSession = HibernateUtil.getSessionFactory().openSession();
		org.hibernate.Transaction newTransaction = newSession.beginTransaction();
		
		List<?> messages = newSession.createQuery("from Message m order by m.text asc").list();
		System.out.println(messages.size() + "message(s) found:");
		
		for(Iterator<?> iter = messages.iterator(); iter.hasNext();){
			Message loadedMsg = (Message) iter.next();
			System.out.println(loadedMsg.getText());
		}
		
		try {
			newTransaction.commit();
		} catch (SecurityException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		newSession.close();

		HibernateUtil.getSessionFactory().close();
	}
}

Pelo o que aponta o erro, não estou conseguindo sequer criar a tabela em meu BD (hsqldb)…Agora o porque disto e a sua consequente solução é que não sei!

Alguém, por gentileza, poderia me ajudar?

Grato!

#2

Ora, porque você usa HSQL e o dialeto MYSQL:

[quote]jdbc:hsqldb:hsql//localhost
org.hibernate.dialect.MySQLInnoDBDialect[/quote]

Use “org.hibernate.dialect.HSQLDialect”.

Outra coisa, você não precisa mapear a classe HibernateUtil.

#3

Ops…Obrigado, fiz as alterações conforme recomendado; entretanto o mesmo erro continua sendo gerado!

Alguma outra dica/idéia pessoal?

[]'s

#4

Ele não está achando a tabela MESSAGES:

# Hibernate:   
#     insert   
#     into  
#         MESSAGES  
#         (NEXT_MESSAGE_ID, MESSAGE_TEXT)   
#     values  
#         (?, ?)  
# 15:02:27  WARN [JDBCExceptionReporter] SQL Error: -22, SQLState: S0002  
# 15:02:27 ERROR [JDBCExceptionReporter] Table not found in statement [insert into MESSAGES (NEXT_MESSAGE_ID, MESSAGE_TEXT) values (?, ?)]  
# Exception in thread "main" org.hibernate.exception.SQLGrammarException: could not insert: [hello.Message]

Deixe seu HibernateUtil assim:

public class HibernateUtil {

	private static SessionFactory factory;

	static {
		AnnotationConfiguration cfg = new AnnotationConfiguration();
		cfg.addAnnotatedClass(Message.class);
		cfg.addAnnotatedClass(OutraClasse.class);
                cfg.addAnnotatedClass(MaisOutraClasse.class);
		factory = cfg.buildSessionFactory();
	}

	public Session getSession() {
		return factory.openSession();
	}
}

Veja se resolve.
Confira tb se existe uma tabela MESSAGES no banco, pois vc anotou com @Table(name=“MESSAGES”).

[]s

#5

Guevara,

Não deu certo as alterações propostas…

Só um comentário: a tabela Message não está presente no BD porque o hibernate não esta conseguindo criá-la, é justamente este o problema ao qual estou pretendendo resolver!

O que preciso modificar?

Grato pela ajuda!!!

#6

Nesse caso vc altera o seu hibernate.cfg.xml para:

<property name="hibernate.hbm2ddl.auto">create</property>

ou tenta:

<property name="hibernate.hbm2ddl.auto">create/update</property>

ou apenas desta forma, que é a que eu uso:

<property name="hibernate.hbm2ddl.auto">update</property>

Lá no seu persistence.xml deixa:

<property name="hibernate.hbm2ddl.auto" value="update" />

Veja se ele cria as tabelas.
[]s

#7

Guevara,

Usando:

no arquivo “hibernate,cfg.xml”; e:

no “persistence.xml”, o exemplo funciona perfeitamente…

Muito obrigado pela ajuda!

Abraço!

#8