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

Question

我正在使用 Wildfly-Swarm 在 Microsoft Azure 上构建 Java Web 应用程序。

我的配置:

• 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>

持久性.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;"
                );
            });


}

...简单模型

@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();
    }

}

...服务

@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();

  }
}

当我运行 Maven 并执行 *-swarm.jar 文件时，一切正常运行，但如果我尝试调用 Rest API，则会收到以下错误:

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

我得到以下 SQL 系统输出:

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

这个错误可以通过使用NamedNativeQuery来解决，但我想使用NamedQuery。看来 hibernate.dialect 无法将 JP-QL“翻译”为 T-SQL ...有人知道如何解决这个问题吗？

Answer 1

您似乎正在为您的表使用保留关键字“USER”，如 T-SQL 保留关键字 documentation 中所述。 .

因此，最简单的解决方案可能是将 USER 表重命名为其他名称，例如 USERS，甚至不建议使用保留关键字。

另请参阅this question .