Java Wildfly-Swarm: How to use JDBC with SQL Server (T-SQL)

Question

I'm using Wildfly-Swarm to build up a java web application on microsoft azure.

My Configuration:

JDK 8.0
Wildfly-Swarm 1.0.0.Beta8
Maven 3.2
JDBC 4.2 Microsoft SQL Server

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">

  <modelVersion>4.0.0</modelVersion>
  <groupId>com.example.azure.swarm</groupId>
  <artifactId>AzureSwarmTest</artifactId>
  <name>Wildfly Swarm Example</name>
  <version>1.0.0-SNAPSHOT</version>
  <packaging>jar</packaging>

  <properties>
    <version.sqlserver>4.0</version.sqlserver>
    <version.wildfly.swarm>1.0.0.Beta8</version.wildfly.swarm>
    <maven.compiler.source>1.8</maven.compiler.source>
    <maven.compiler.target>1.8</maven.compiler.target>
    <failOnMissingWebXml>false</failOnMissingWebXml>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
  </properties>

  <dependencyManagement>
    <dependencies>
      <dependency>
        <groupId>org.wildfly.swarm</groupId>
        <artifactId>bom</artifactId>
        <version>${version.wildfly.swarm}</version>
        <scope>import</scope>
        <type>pom</type>
      </dependency>
    </dependencies>
  </dependencyManagement>

  <build>
    <finalName>AzureSwarmTest</finalName>
    <plugins>
        <plugin>
            <groupId>org.apache.maven.plugins</groupId>
            <artifactId>maven-compiler-plugin</artifactId>
            <version>3.2</version>
            <configuration>
                <source>1.8</source>
                <target>1.8</target>
            </configuration>
        </plugin>
      <plugin>
        <groupId>org.wildfly.swarm</groupId>
        <artifactId>wildfly-swarm-plugin</artifactId>
        <version>${version.wildfly.swarm}</version>
        <configuration>
          <mainClass>com.example.Main</mainClass>
        </configuration>
        <executions>
          <execution>
            <goals>
              <goal>package</goal>
            </goals>
          </execution>
        </executions>
      </plugin>
    </plugins>
  </build>

  <dependencies>
    <!-- Java EE 7 dependency -->
    <dependency>
      <groupId>javax</groupId>
      <artifactId>javaee-api</artifactId>
      <version>7.0</version>
      <scope>provided</scope>
    </dependency>
    <!-- Wildfly Swarm Fractions -->
    <dependency>
      <groupId>org.wildfly.swarm</groupId>
      <artifactId>ejb</artifactId>
    </dependency>
    <dependency>
      <groupId>org.wildfly.swarm</groupId>
      <artifactId>jpa</artifactId>
    </dependency>
    <dependency>
      <groupId>org.wildfly.swarm</groupId>
      <artifactId>jaxrs-cdi</artifactId>
    </dependency>
    <dependency>
      <groupId>org.wildfly.swarm</groupId>
      <artifactId>swagger-webapp</artifactId>
    </dependency>
    <dependency>
        <groupId>org.wildfly.swarm</groupId>
        <artifactId>datasources</artifactId>
    </dependency>

    <dependency>
        <groupId>com.microsoft.sqlserver</groupId>
        <artifactId>sqljdbc42</artifactId>
        <version>4.2</version>
        <scope>compile</scope>
    </dependency>
  </dependencies>
</project>

persistence.xml

<?xml version="1.0" encoding="UTF-8"?>
<persistence
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        version="2.1"
        xmlns="http://xmlns.jcp.org/xml/ns/persistence"
        xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd">
    <persistence-unit name="MyPU" transaction-type="JTA">

        <class>com.example.model.User</class>

        <properties>
            <property name="hibernate.default_schema" value="MY_SCHEMA"/>
            <property name="hibernate.dialect" value="org.hibernate.dialect.SQLServer2008Dialect"/>
            <property name="hibernate.show_sql" value="true"/>
            <property name="hibernate.format_sql" value="true"/>
        </properties>
    </persistence-unit>
</persistence>

... void main(String ... args)

public static void main(String[]args) throws Exception{

    Container container = new Container();

    container.fraction(datasourceWithSqlServer());

    container.fraction(new JPAFraction()
        .inhibitDefaultDatasource()
        .defaultDatasource("jboss/datasources/MyDS"));

    container.start();

    JAXRSArchive appDeployment = ShrinkWrap.create(JAXRSArchive.class);
    appDeployment.addClasses(User.class);
    appDeployment.addAsLibrary(container.createDefaultDeployment());
    appDeployment.addAllDependencies();

    container.deploy(appDeployment);

}

private static DatasourcesFraction datasourceWithSqlServer(){
    return new DatasourcesFraction()
            .jdbcDriver("sqlserver", (d) -> {
                d.driverClassName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
                d.xaDatasourceClass("com.microsoft.sqlserver.jdbc.SQLServerXADataSource");
                d.driverModuleName("com.microsoft");
            })
            .dataSource("myDS", (ds) -> {
                ds.driverName("sqlserver");
                ds.connectionUrl("jdbc:sqlserver://myDB.database.windows.net:1433;" +
                        "database=myDB;" +
                        "user=user@myDB;" +
                        "password=password;" +
                        "encrypt=true;" +
                        "trustServerCertificate=true;" +
                        "hostNameInCertificate=*.database.windows.net;" +
                        "loginTimeout=30;"
                );
            });


}

... Simple Model

@Entity
@Table(name = "USER")
@NamedQueries({
        @NamedQuery(name = User.GET_ALL, query = "SELECT u FROM User u")
})
@XmlRootElement
public class User  implements Serializable {

    /** The Constant serialVersionUID. */
    private static final long serialVersionUID = -5895315599733726081L;

    public static final String GET_ALL = "User.GET_ALL_USER";

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    @Column(name = "FIRST_NAME")
    private String firstName;

    @Column(name = "LAST_NAME")
    private String lastName;

    protected User(){}

    public String getFirstName() {
        return firstName;
    }

    public String getLastName() {
        return lastName;
    }

    public Long getId() {
        return id;
    }

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

    @Override
    public String toString() {
        return "User{" +
                "firstName='" + firstName + '\'' +
                ", lastName='" + lastName + '\'' +
                '}';
    }

    public void update(User user){
        this.firstName = user.getFirstName();
        this.lastName = user.getLastName();
    }

}

... Service

@Stateless
public class UserService {

    @PersistenceContext
    EntityManager em;

    public List<User> getAll(){
        return em.createNamedQuery(User.GET_ALL, User.class).getResultList();
    }
}

... REST-API

> @ApplicationScoped
@Path("/user")
public class HelloWorldEndpoint {

  @EJB
  protected UserService service;

  @GET
  @Produces(MediaType.APPLICATION_JSON)
  public List<User> doGet() {
      return service.getAll();

  }
}

When I run Maven and execute the *-swarm.jar -file everything runs properly, but if I try to call the Rest API I get the following error:

com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'USER'

I get the following SQL sysout:

Hibernate:
    select
        user0_.id as id1_0_,
        user0_.FIRST_NAME as FIRST_NA2_0_,
        user0_.LAST_NAME as LAST_NAM3_0_
    from
        MY_SCHEMA.USER user0

This error can be solved by using NamedNativeQuery, but I want to use NamedQuery. It seems, that hibernate.dialect can't "translate" JP-QL to T-SQL ... Does anyone have a clue how to solve the issue?

score 0 · Accepted Answer · edited May 23 '17 at 12:06

0

It seems that you're using a reserved keyword, "USER" for your table as described in this T-SQL Reserved Keywords documentation.

So the easiest solution would probably be to rename your USER table to something else like USERS for example, even more as reserved keywords use is not recommended.

Java Wildfly-Swarm: How to use JDBC with SQL Server (T-SQL)

1 Answers1