Geek Answers Handbook

How to migrate Hibernate from MySQL to SQL Server?

I need to migrate a Java EE application from JPA / Hibernate (5.1) from MySQL to SQL Server (2012). Since the abstraction was provided by JPA, my humble thought was that it was simple and straightforward.

I started downloading SQL Server JDBC 4.2 drivers on Wildfly 10, downloaded from the Microsoft website, and identifying a new data source leading to a successful connection test.

Then I reissued my application allowing Hibernate to restore database tables, but I was stuck with this:

13:27:29,268 WARN  [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (ServerService Thread Pool -- 61) SQL Error: 1038, SQLState: S0004
13:27:29,268 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (ServerService Thread Pool -- 61) An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.
13:27:29,270 ERROR [org.jboss.msc.service.fail] (ServerService Thread Pool -- 61) MSC000001: Failed to start service jboss.persistenceunit."best.war#best": org.jboss.msc.service.StartException in service jboss.persistenceunit."best.war#best": javax.persistence.PersistenceException: [PersistenceUnit: best] Unable to build Hibernate SessionFactory
    at org.jboss.as.jpa.service.PersistenceUnitServiceImpl$1$1.run(PersistenceUnitServiceImpl.java:172)
    at org.jboss.as.jpa.service.PersistenceUnitServiceImpl$1$1.run(PersistenceUnitServiceImpl.java:117)
    at org.wildfly.security.manager.WildFlySecurityManager.doChecked(WildFlySecurityManager.java:667)
    at org.jboss.as.jpa.service.PersistenceUnitServiceImpl$1.run(PersistenceUnitServiceImpl.java:182)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
    at java.lang.Thread.run(Thread.java:745)
    at org.jboss.threads.JBossThread.run(JBossThread.java:320)
Caused by: javax.persistence.PersistenceException: [PersistenceUnit: best] Unable to build Hibernate SessionFactory
    at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.persistenceException(EntityManagerFactoryBuilderImpl.java:954)
    at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:884)
    at org.jboss.as.jpa.hibernate5.TwoPhaseBootstrapImpl.build(TwoPhaseBootstrapImpl.java:44)
    at org.jboss.as.jpa.service.PersistenceUnitServiceImpl$1$1.run(PersistenceUnitServiceImpl.java:154)
    ... 7 more
Caused by: org.hibernate.exception.SQLGrammarException: Error accessing table metadata
    at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:106)
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:111)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:97)
    at org.hibernate.tool.schema.extract.internal.InformationExtractorJdbcDatabaseMetaDataImpl.convertSQLException(InformationExtractorJdbcDatabaseMetaDataImpl.java:99)
    at org.hibernate.tool.schema.extract.internal.InformationExtractorJdbcDatabaseMetaDataImpl.locateTableInNamespace(InformationExtractorJdbcDatabaseMetaDataImpl.java:354)
    at org.hibernate.tool.schema.extract.internal.InformationExtractorJdbcDatabaseMetaDataImpl.getTable(InformationExtractorJdbcDatabaseMetaDataImpl.java:228)
    at org.hibernate.tool.schema.internal.exec.ImprovedDatabaseInformationImpl.getTableInformation(ImprovedDatabaseInformationImpl.java:109)
    at org.hibernate.tool.schema.internal.SchemaMigratorImpl.performMigration(SchemaMigratorImpl.java:252)
    at org.hibernate.tool.schema.internal.SchemaMigratorImpl.doMigration(SchemaMigratorImpl.java:137)
    at org.hibernate.tool.schema.internal.SchemaMigratorImpl.doMigration(SchemaMigratorImpl.java:110)
    at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.performDatabaseAction(SchemaManagementToolCoordinator.java:176)
    at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.process(SchemaManagementToolCoordinator.java:64)
    at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:458)
    at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:465)
    at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:881)
    ... 9 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:217)
    at com.microsoft.sqlserver.jdbc.TDSTokenHandler.onEOF(tdsparser.java:251)
    at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:81)
    at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:36)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection$1ConnectionCommand.doExecute(SQLServerConnection.java:1834)
    at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:6276)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1793)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectionCommand(SQLServerConnection.java:1839)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.setCatalog(SQLServerConnection.java:2190)
    at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.switchCatalogs(SQLServerDatabaseMetaData.java:331)
    at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.getResultSetFromStoredProc(SQLServerDatabaseMetaData.java:282)
    at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.getResultSetWithProvidedColumnNames(SQLServerDatabaseMetaData.java:309)
    at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.getTables(SQLServerDatabaseMetaData.java:496)
    at org.hibernate.tool.schema.extract.internal.InformationExtractorJdbcDatabaseMetaDataImpl.locateTableInNamespace(InformationExtractorJdbcDatabaseMetaDataImpl.java:339)
    ... 19 more

13:27:29,278 ERROR [org.jboss.as.controller.management-operation] (Controller Boot Thread) WFLYCTL0013: Operation ("deploy") failed - address: ([("deployment" => "best.war")]) - failure description: {"WFLYCTL0080: Failed services" => {"jboss.persistenceunit.\"best.war#best\"" => "org.jboss.msc.service.StartException in service jboss.persistenceunit.\"best.war#best\": javax.persistence.PersistenceException: [PersistenceUnit: best] Unable to build Hibernate SessionFactory
    Caused by: javax.persistence.PersistenceException: [PersistenceUnit: best] Unable to build Hibernate SessionFactory
    Caused by: org.hibernate.exception.SQLGrammarException: Error accessing table metadata
    Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as \"\" or [] are not allowed. Change the alias to a valid name."}}
13:27:29,300 INFO  [org.jboss.as.server] (ServerService Thread Pool -- 34) WFLYSRV0010: Deployed "best.war" (runtime-name : "best.war")
13:27:29,301 INFO  [org.jboss.as.controller] (Controller Boot Thread) WFLYCTL0183: Service status report
WFLYCTL0186:   Services which failed to start:      service jboss.persistenceunit."best.war#best": org.jboss.msc.service.StartException in service jboss.persistenceunit."best.war#best": javax.persistence.PersistenceException: [PersistenceUnit: best] Unable to build Hibernate SessionFactory

What's happening? This is mine persistence.xml:

<persistence version="2.1" xmlns="http://xmlns.jcp.org/xml/ns/persistence"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd">
    <persistence-unit name="best" transaction-type="JTA">
        <jta-data-source>java:/datasource/mssql/best</jta-data-source>
        <properties>
            <property name="hibernate.hbm2ddl.auto" value="update" />
        </properties>
    </persistence-unit>
  </persistence-units>
</persistence>

I also tried adding this since I am on SQL Server 2012, but nothing has changed:

<property name="hibernate.dialect" value="org.hibernate.dialect.SQLServer2012Dialect" />

Any idea or suggestion?

Update

, , hibernate.hbm2ddl.auto = update, create . update , MySQL, , .

+4
5

SQL Server .

persistence.xml:

<property name="hibernate.dialect" value="org.hibernate.dialect.SQLServer2012Dialect" />
<property name="hibernate.default_catalog" value="[database name]" />
<property name="hibernate.default_schema" value="dbo" />
<property name="hibernate.id.new_generator_mappings" value="false" />

:

+4

, @Table , , hansernate.default_catalog persistence.xml.

pov. 4.3 . Hibernate SQL Server, @Table ! : https://hibernate.atlassian.net/browse/HHH-10978

+1

MySQL SQL Server .

, .

0

hibernate 5.1.0.Final SQL Server 2012. hibernate 4.3.11.Final .

Hibernate EntityManager 5.1.0.Final

INFO  Version:37 - HHH000412: Hibernate Core {5.1.0.Final}
INFO  Environment:213 - HHH000206: hibernate.properties not found
INFO  Environment:317 - HHH000021: Bytecode provider name : javassist
INFO  Version:66 - HCANN000001: Hibernate Commons Annotations {5.0.1.Final}
INFO  Dialect:156 - HHH000400: Using dialect: org.hibernate.dialect.SQLServer2012Dialect
INFO  Version:30 - HV000001: Hibernate Validator 5.2.4.Final
WARN  SqlExceptionHelper:129 - SQL Error: 1038, SQLState: S0004
ERROR SqlExceptionHelper:131 - Falta o está vacío un nombre de objeto o columna. Compruebe si todas las columnas de las instrucciones SELECT INTO tienen un nombre. Para otras instrucciones, busque si hay nombres de alias vacíos. No se permiten los alias definidos como "" o []. Cambie el alias por un nombre válido.

Hibernate EntityManager 4.3.11.Final

Version:54 - HHH000412: Hibernate Core {4.3.11.Final}
Environment:239 - HHH000206: hibernate.properties not found
Environment:346 - HHH000021: Bytecode provider name : javassist
Version:66 - HCANN000001: Hibernate Commons Annotations {4.0.5.Final}
Dialect:145 - HHH000400: Using dialect: org.hibernate.dialect.SQLServer2012Dialect
ASTQueryTranslatorFactory:47 - HHH000397: Using ASTQueryTranslatorFactory
Version:30 - HV000001: Hibernate Validator 5.2.4.Final
SchemaValidator:157 - HHH000229: Running schema validator
SchemaValidator:165 - HHH000102: Fetching database metadata
TableMetadata:65 - HHH000261: Table found: rino.rino.tipo_ticket

, !

0

, , Spring Boot JPA, MS SQL Server, , MySQL Postgres.

Spring Boot 1.5.9.RELEASE, Hibernate 5.0.12.Final.

, Spring Boot application.propeties , Spring Boot.

, Spring Boot with Microsoft SQL Server,

spring.datasource.url=jdbc:sqlserver://127.0.0.1:1433;jdbc.databaseName=mydb
spring.datasource.username=admin
spring.datasource.password=aadmin
spring.datasource.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.jpa.database=sql-server
spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=update

:

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(schema="dbo", name="MyUser",catalog="mydatabase")
public class MyUser {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Integer id;
    private String name;
    private int age;

    public MyUser() {
    }

    public MyUser(String name, int age) {
        this.name = name;
        this.age = age;
    }

    public Integer getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    @Override
    public String toString() {
        return "User{" + ", name='" + name + '\'' + ", Age=" + age + '}';
    }
}

. catalog @Table . Microsoft SQL Server Spring Boot Hibernate, .

0

More articles:


All Articles