Problema criando tabelas com hibernate

esdras_63 · Fevereiro 8, 2012, 2:27pm

Olá pessoal! Estou com um problema ao ler um arquivo sql e criar a tabela executando um sql query. Olha o strack trace:

Caused by: javax.faces.el.EvaluationException: org.hibernate.exception.SQLGrammarException: could not execute native bulk manipulation query
	at javax.faces.component.MethodBindingMethodExpressionAdapter.invoke(MethodBindingMethodExpressionAdapter.java:102)
	at com.sun.faces.application.ActionListenerImpl.processAction(ActionListenerImpl.java:102)
	... 26 more
Caused by: org.hibernate.exception.SQLGrammarException: could not execute native bulk manipulation query
	at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:92)
	at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
	at org.hibernate.engine.query.NativeSQLQueryPlan.performExecuteUpdate(NativeSQLQueryPlan.java:219)
	at org.hibernate.impl.SessionImpl.executeNativeUpdate(SessionImpl.java:1310)
	at org.hibernate.impl.SQLQueryImpl.executeUpdate(SQLQueryImpl.java:396)
	at com.brazcom.daos.BrzLoginDaoImpl.isAutentico(BrzLoginDaoImpl.java:53)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:597)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:318)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
	at $Proxy38.isAutentico(Unknown Source)
	at com.brazcom.beans.LoginBean.autentica(LoginBean.java:42)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:597)
	at org.apache.el.parser.AstValue.invoke(AstValue.java:262)
	at org.apache.el.MethodExpressionImpl.invoke(MethodExpressionImpl.java:278)
	at com.sun.faces.facelets.el.TagMethodExpression.invoke(TagMethodExpression.java:105)
	at javax.faces.component.MethodBindingMethodExpressionAdapter.invoke(MethodBindingMethodExpressionAdapter.java:88)
	... 27 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE TABLE IF NOT EXISTS `brz_nf21` (
  `AnoMes` varchar(4) NOT NULL DEFAULT '' at line 10
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
	at com.mysql.jdbc.Util.getInstance(Util.java:386)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3597)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3529)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1990)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2151)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2625)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2119)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2415)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2333)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2318)
	at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:105)
	at org.hibernate.engine.query.NativeSQLQueryPlan.performExecuteUpdate(NativeSQLQueryPlan.java:210)
	... 50 more
08/02/2012 16:21:28 org.apache.catalina.core.StandardWrapperValve invoke
SEVERE: Servlet.service() for servlet [Faces Servlet] in context with path [/brazcom] threw exception [org.hibernate.exception.SQLGrammarException: could not execute native bulk manipulation query] with root cause
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE TABLE IF NOT EXISTS `brz_nf21` (
  `AnoMes` varchar(4) NOT NULL DEFAULT '' at line 10
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
	at com.mysql.jdbc.Util.getInstance(Util.java:386)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3597)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3529)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1990)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2151)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2625)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2119)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2415)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2333)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2318)
	at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:105)
	at org.hibernate.engine.query.NativeSQLQueryPlan.performExecuteUpdate(NativeSQLQueryPlan.java:210)
	at org.hibernate.impl.SessionImpl.executeNativeUpdate(SessionImpl.java:1310)
	at org.hibernate.impl.SQLQueryImpl.executeUpdate(SQLQueryImpl.java:396)
	at com.brazcom.daos.BrzLoginDaoImpl.isAutentico(BrzLoginDaoImpl.java:53)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:597)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:318)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
	at $Proxy38.isAutentico(Unknown Source)
	at com.brazcom.beans.LoginBean.autentica(LoginBean.java:42)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:597)
	at org.apache.el.parser.AstValue.invoke(AstValue.java:262)
	at org.apache.el.MethodExpressionImpl.invoke(MethodExpressionImpl.java:278)
	at com.sun.faces.facelets.el.TagMethodExpression.invoke(TagMethodExpression.java:105)
	at javax.faces.component.MethodBindingMethodExpressionAdapter.invoke(MethodBindingMethodExpressionAdapter.java:88)
	at com.sun.faces.application.ActionListenerImpl.processAction(ActionListenerImpl.java:102)
	at javax.faces.component.UICommand.broadcast(UICommand.java:315)
	at javax.faces.component.UIViewRoot.broadcastEvents(UIViewRoot.java:794)
	at javax.faces.component.UIViewRoot.processApplication(UIViewRoot.java:1259)
	at com.sun.faces.lifecycle.InvokeApplicationPhase.execute(InvokeApplicationPhase.java:81)
	at com.sun.faces.lifecycle.Phase.doPhase(Phase.java:101)
	at com.sun.faces.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:118)
	at javax.faces.webapp.FacesServlet.service(FacesServlet.java:409)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:304)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
	at org.primefaces.webapp.filter.FileUploadFilter.doFilter(FileUploadFilter.java:79)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:224)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:185)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:151)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:100)
	at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:929)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:405)
	at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:269)
	at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:515)
	at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:300)
	at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
	at java.lang.Thread.run(Thread.java:619)

Código para executar o sql:

try {
        // Abre o leitor do sql
	InputStream is = new FileInputStream(file);
	InputStreamReader ist = new InputStreamReader(is);
	BufferedReader br = new BufferedReader(ist);
	String sql = "";
	String s;
	
	while ((s = br.readLine()) != null) {
		sql += s + "\n";
	}
		
	SQLQuery query = getSession().createSQLQuery(sql);
	query.executeUpdate();
	
	br.close();
        ist.close();
	is.close();
} catch (IOException ex) {
	throw new RuntimeException(e);
}

SQL:

-- Tabela de login
CREATE TABLE IF NOT EXISTS `brz_login` (
  `LoginID` int(255) NOT NULL AUTO_INCREMENT,
  `NomeUsuario` varchar(50) DEFAULT NULL,
  `senha` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`LoginID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Tabela de notas fiscais
CREATE TABLE IF NOT EXISTS `brz_nf21` (
  `AnoMes` varchar(4) NOT NULL DEFAULT '0000',
  `Modelo` varchar(3) NOT NULL DEFAULT '21',
  `Serie` varchar(3) NOT NULL DEFAULT '001',
  `NumeroNF21` int(9) NOT NULL,
  `ClienteID` int(11) DEFAULT NULL,
  `DataEmissao` datetime DEFAULT NULL,
  `Valor` decimal(12,2) DEFAULT NULL,
  `BCICMS` decimal(12,2) DEFAULT NULL,
  `VICMS` decimal(12,2) DEFAULT NULL,
  `Isentas` decimal(12,2) DEFAULT NULL,
  `Outras` decimal(12,2) DEFAULT NULL,
  `Status` enum('S','R','N') DEFAULT 'N',
  `Impresso` enum('S','N') DEFAULT 'N',
  `Obs` varchar(50) DEFAULT NULL,
  `DataImpressao` datetime DEFAULT NULL,
  `QtdVias` int(11) DEFAULT NULL,
  `AutenticacaoFiscal` varchar(32) DEFAULT NULL,
  `AutenticacaoRegistro` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`AnoMes`,`Modelo`,`Serie`,`NumeroNF21`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Tabela de relacionamento com planos do mk-auth
CREATE TABLE IF NOT EXISTS `brz_relplanos` (
  `nome` varchar(255) NOT NULL,
  `Descricao` varchar(50) NOT NULL,
  `AliquotaICMS` decimal(5,2) NOT NULL,
  `TipoAssinante` smallint(6) NOT NULL,
  `TipoUtilizacao` smallint(6) NOT NULL,
  `GeraNF21` enum('S','N') NOT NULL DEFAULT 'S',
  `CFOP` varchar(5) DEFAULT NULL,
  PRIMARY KEY (`nome`),
  KEY `fk_brz_RelPlanos_sis_plano` (`nome`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Alterar tabela de lançamentos do mk-auth
ALTER TABLE `sis_lanc` ADD `NumeroNF21` INT(9) NULL DEFAULT 0;

-- Tabelas temporárias

-- Tabela de cadastro temporário
CREATE TABLE IF NOT EXISTS `brz_cadastro_tmp` (
  `ClienteID` int(11) NOT NULL DEFAULT '0',
  `cpf_cnpj` varchar(14) NOT NULL DEFAULT '',
  `IE` varchar(14) DEFAULT NULL,
  `RazaoSocial` varchar(35) DEFAULT NULL,
  `Logradouro` varchar(45) DEFAULT NULL,
  `Numero` varchar(5) DEFAULT NULL,
  `Complemento` varchar(15) DEFAULT NULL,
  `CEP` varchar(8) DEFAULT NULL,
  `Bairro` varchar(15) DEFAULT NULL,
  `Municipio` varchar(30) DEFAULT NULL,
  `UF` varchar(2) DEFAULT NULL,
  `Telefone` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`ClienteID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Tabela de notas fiscais temporária
CREATE TABLE IF NOT EXISTS `brz_nf21_tmp` (
  `AnoMes` varchar(4) NOT NULL DEFAULT '0000',
  `Modelo` varchar(3) NOT NULL DEFAULT '21',
  `Serie` varchar(3) NOT NULL DEFAULT '001',
  `NumeroNF21` int(9) NOT NULL,
  `ClienteID` int(11) NOT NULL,
  `DataEmissao` datetime DEFAULT NULL,
  `Valor` decimal(12,2) DEFAULT NULL,
  `BCICMS` decimal(12,2) DEFAULT NULL,
  `VICMS` decimal(12,2) DEFAULT NULL,
  `Isentas` decimal(12,2) DEFAULT NULL,
  `Outras` decimal(12,2) DEFAULT NULL,
  `Status` enum('S','R','N') DEFAULT 'N',
  `Impresso` enum('S','N') DEFAULT 'N',
  `Obs` varchar(50) DEFAULT NULL,
  `DataImpressao` datetime DEFAULT NULL,
  `Autenticacao` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`AnoMes`,`Modelo`,`Serie`,`NumeroNF21`),
  KEY `FK_Cadastro` (`ClienteID`),
  CONSTRAINT `FK_Cadastro` FOREIGN KEY (`ClienteID`) REFERENCES `brz_cadastro_tmp` (`ClienteID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Tabela de itens temporário
CREATE TABLE IF NOT EXISTS `brz_itens_tmp` (
  `AnoMes` varchar(4) NOT NULL DEFAULT '0000',
  `Modelo` varchar(3) NOT NULL DEFAULT '21',
  `Serie` varchar(3) NOT NULL DEFAULT '001',
  `NumeroNF21` int(9) NOT NULL,
  `Item` int(3) NOT NULL,
  `Descricao` varchar(40) DEFAULT NULL,
  `Valor` double DEFAULT NULL,
  `BCICMS` double DEFAULT NULL,
  `AliquotaICMS` double DEFAULT NULL,
  PRIMARY KEY (`NumeroNF21`,`AnoMes`,`Modelo`,`Serie`,`Item`),
  KEY `FK_NF` (`AnoMes`,`Modelo`,`Serie`,`NumeroNF21`),
  CONSTRAINT `FK_NF` FOREIGN KEY (`AnoMes`, `Modelo`, `Serie`, `NumeroNF21`) REFERENCES `brz_nf21_tmp` (`AnoMes`, `Modelo`, `Serie`, `NumeroNF21`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Debuguei e a variável sql está exatamente igual o arquivo.
Alguma idéia?? O sql executa perfeitamente direto no banco

Minduin · Fevereiro 8, 2012, 3:18pm

Não saberia lhe dizer o que pode estar errado, mas eu mudaria esse trecho de código

[code] String sql = “”;
String s;

while ((s = br.readLine()) != null) {  
    sql += s + "\n";  
}  
      
SQLQuery query = getSession().createSQLQuery(sql);  [/code]

Para

    StringBuilder sql = new StringBuilder();  
    String s;  
      
    while ((s = br.readLine()) != null) {  
        sql.append(s + " ");  
    }  
          
    SQLQuery query = getSession().createSQLQuery(sql.toString());

SQL costuma ser muito sensivel com caracteres estranhos.

Abs

esdras_63 · Fevereiro 8, 2012, 4:22pm

[quote=Minduin]Não saberia lhe dizer o que pode estar errado, mas eu mudaria esse trecho de código

[code] String sql = “”;
String s;

while ((s = br.readLine()) != null) {  
    sql += s + "\n";  
}  
      
SQLQuery query = getSession().createSQLQuery(sql);  [/code]

Para

    StringBuilder sql = new StringBuilder();  
    String s;  
      
    while ((s = br.readLine()) != null) {  
        sql.append(s + " ");  
    }  
          
    SQLQuery query = getSession().createSQLQuery(sql.toString());

SQL costuma ser muito sensivel com caracteres estranhos.

Abs[/quote]
A sim… Mas eu preciso de uma quebra de linha pois uso alguns comentários de uma linha no sql “–”. Mas parece que o sql está funcionando na primeira tabela, pois o erro acontece no segundo create se não estou enganado.
Mas alguma idéia??

faeldix · Fevereiro 8, 2012, 6:25pm

esse e um erro de sintaxe sql… experimente fazer direto no sgbd se nao funcionar… concerte primeiro. rs

esdras_63 · Fevereiro 8, 2012, 6:49pm

a sintaxe está correta. Já tentei fazendo direto e cria normalmente ^^

system · Janeiro 6, 2016, 1:31am

Problema criando tabelas com hibernate

Cursos de Mobile

Cursos de Programação

Cursos de Front-end

Cursos de DevOps

Cursos de Design & UX

Cursos de Business

Cursos de Data & BI