Skip to content

13. SQL Server 2014

We will now discuss porting what was done with MySQL to SQL Server 2014.

13.1. Setting up the work environment

13.1.1. Eclipse Environment

We will be working with the following Eclipse environment:

  

The SQL Server projects listed above can be found in the folder [<examples>/spring-database-config\sqlserver\eclipse].

Note: Press [Alt-F5] to regenerate all Maven projects.

13.1.2. Generating the databases

As we did with Oracle and DB2, we will need to install the SQL Server JDBC driver in the local Maven repository.

  

The [install.bat] file contains the following code:

"%M2_HOME%\bin\mvn.bat" install:install-file -Dfile=sqljdbc4-3.0.jar -Dpackaging=jar -DgroupId=com.microsoft.sqlserver -DartifactId=sqljdbc4 -Dversion=4.0

where [%M2-HOME%] is the Maven installation directory (see section 23.2, page 466). After this installation, the SQL Server JDBC driver can be referenced in the [pom.xml] files using the following dependency:


        <dependency>
            <groupId>com.microsoft.sqlserver</groupId>
            <artifactId>sqljdbc4</artifactId>
            <version>4.0</version>
</dependency>

Throughout the rest of this guide, connections to SQL Server databases are made using the credentials [sa / msde]. Start SQL Server and its client [MsManager] (see section 23.9).

  • In [1], load the SQL script [<examples>\spring-database-config\sqlserver\databases\dbproduits.sql];
  • in [2], it was not possible to use the same [PRODUCTS] table for projects [spring-jdbc-01 to 03]. The reason is that:
    • projects [spring-jdbc-01 and 02] insert rows with their primary keys;
    • the [spring-jdbc-03] project inserts rows without primary keys and expects the DBMS to generate them. For this to work, the primary key [ID] must be of type [Identity]. However, this type in SQL Server only supports the automatic generation of primary keys and does not allow the insertion of a row with a user-defined primary key. An error is then reported, and I was unable to work around it. The [spring-jdbc-01 and 02] projects use the [PRODUCTS] table without automatic primary key generation. The [spring-jdbc-03] project uses the [PRODUCTS2] table with automatic primary key generation.

Now, run the configurations:

  • [spring-jdbc-generic-01.IntroJdbc01];
  • [spring-jdbc-generic-01.IntroJdbc02];
  • [spring-jdbc-generic-03.JUnitTestDao1];
  • [spring-jdbc-generic-03.JUnitTestDao2];

They should all pass.

Now let’s generate the [dbproduitscategories] database. Repeat the procedure used to create [dbproduits] for [dbproduitscategories]. The SQL script to load is located at [<examples>\spring-database-config\sqlserver\databases\ dbproduitscategories.sql] ;

  

Now, run the configurations:

  • [spring-jdbc-generic-04.JUnitTestDao];
  • [spring-jpa-generic-JUnitTestDao-openjpa];

Both should pass.

13.2. Configuring the JDBC Layer

 

The [sqlserver-config-jdbc] project configures the [JDBC] layer of the following test architecture:

The project is similar to the [mysql-config-jdbc] configuration project for the JDBC layer of the MySQL DBMS (see Section 3.3). We present only the changes:

The [pom.xml] file imports the SQL Server JDBC driver:


<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/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>dvp.spring.database</groupId>
    <artifactId>generic-config-jdbc</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>generic jdbc configuration</name>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.2.3.RELEASE</version>
    </parent>

    <dependencies>
        <!-- variable dependencies ********************************************** -->
        <!-- JDBC driver for the DBMS -->
        <dependency>
            <groupId>com.microsoft.sqlserver</groupId>
            <artifactId>sqljdbc4</artifactId>
            <version>4.0</version>
        </dependency>
        <!-- constant dependencies ********************************************** -->
        ...
    </dependencies>
...
</project>
  • lines 18-22: the SQL Server JDBC driver;

The second change is in the [ConfigJdbc] class, which defines the database credentials:


    // connection parameters
    public final static String DRIVER_CLASSNAME = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
    public final static String URL_DBPRODUITS = "jdbc:sqlserver://localhost\\SQLEXPRESS:1433;databaseName=dbproduits";
    public final static String USER_DBPRODUITS = "sa";
    public final static String PASSWD_DBPRODUITS = "msde";
    public final static String PRODUCT_CATEGORIES_DB_URL = "jdbc:sqlserver://localhost\\SQLEXPRESS:1433;databaseName=productcategories";
    public final static String USER_DBPRODUCTSCATEGORIES = "sa";
public final static String PASSWD_PRODUCT_CATEGORIES_DB = "msde";

The third modification that can be made is to the maximum number of parameters that a [PreparedStatement] can support:


    // maximum number of parameters for a [PreparedStatement]
    public final static int MAX_PREPAREDSTATEMENT_PARAMETERS = 2000;

The [JUnitTestPushTheLimits] test generates SQL statements for 5,000 products, which will generate [PreparedStatement] objects with 5,000 parameters. MySQL supported this value. SQL Server returned an error indicating that this limit was 2,100.

The fourth change is in the table used by the [spring-jdbc-03] project. It is no longer [PRODUCTS] but [PRODUCTS2]:


    // SQL statements [jdbc-03]
    public final static String V2_INSERT_PRODUITS = "INSERT INTO PRODUITS2(NAME, CATEGORY, PRICE, DESCRIPTION) VALUES (?, ?, ?, ?)";
    public final static String V2_DELETE_ALLPRODUCTS = "DELETE FROM PRODUCTS2";
    public final static String V2_DELETE_PRODUCTS = "DELETE FROM PRODUCTS2 WHERE ID=?";
    public final static String V2_SELECT_ALLPRODUCTS = "SELECT ID, NAME, CATEGORY, PRICE, DESCRIPTION FROM PRODUCTS2";
    public final static String V2_SELECT_PRODUCT_BYID = "SELECT NAME, CATEGORY, PRICE, DESCRIPTION FROM PRODUCTS2 WHERE ID=?";
    public final static String V2_SELECT_PRODUCT_BYNAME = "SELECT ID, CATEGORY, PRICE, DESCRIPTION FROM PRODUCTS2 WHERE NAME=?";
public final static String V2_UPDATE_PRODUCTS = "UPDATE PRODUCTS2 SET NAME=?, PRICE=?, CATEGORY=?, DESCRIPTION=? WHERE ID=?";

13.3. Configuring the OpenJPA JPA Layer

 

The [sqlserver-config-jpa-openjpa] project configures the [JPA] layer of the test architecture:

The project is analogous to the [mysql-config-jpa-openjpa] configuration project for the OpenJpa JPA layer of the MySQL DBMS (see Section 8.3). In fact, both DBMSs use the [@GeneratedValue(strategy = GenerationType.IDENTITY)] annotation to generate primary keys. There are two changes to make. They are in the definition of the [jpaVendorAdapter] bean in the [ConfigJpa] class:


    // the JPA provider
    @Bean
    public JpaVendorAdapter jpaVendorAdapter() {
        OpenJpaVendorAdapter openJpaVendorAdapter = new OpenJpaVendorAdapter();
        openJpaVendorAdapter.setShowSql(false);
        openJpaVendorAdapter.setDatabase(Database.SQL_SERVER);
        openJpaVendorAdapter.setGenerateDdl(true);
        return openJpaVendorAdapter;
}
  • Line 6: We tell the JPA implementation that it will be working with an SQL Server database. The JPA implementation will then adopt both the proprietary data types and the proprietary SQL of this DBMS.

The second change concerns the JPA entities associated with the [PRODUCTS] and [PRODUCTS2] tables:

  

The [Product] entity is associated with the [PRODUCTS] table without automatic generation of primary keys (no [@GeneratedValue] annotation):


@Entity(name = "Product1")
@Table(name = ConfigJdbc.TAB_PRODUITS)
public class Product {

// fields
@Id
@Column(name = ConfigJdbc.TAB_PRODUCTS_ID)
private Long id;

The [Product2] entity is associated with the [PRODUCTS2] table with automatic generation of primary keys:


@Entity(name = "Product2")
@Table(name = ConfigJdbc.TAB_PRODUITS2)
public class Product2 {

// fields
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = ConfigJdbc.TAB_PRODUITS_ID)
private Long id;

Additionally, the project that generates the [dbproduits] database must be modified to indicate that there are now two JPA entities in the database:

  

The [persistence.xml] file changes as follows:


<?xml version="1.0" encoding="UTF-8"?>
<persistence version="1.0" 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">
    <persistence-unit name="generic-jpa-entities-dbproduits" transaction-type="RESOURCE_LOCAL">
        <!-- JPA entities -->
        <class>generic.jpa.entities.dbproduits.Product</class>
        <class>generic.jpa.entities.dbproduits.Product2</class>
        <exclude-unlisted-classes>true</exclude-unlisted-classes>
    </persistence-unit>
</persistence>

The [generic-create-dbproduits] project is common to all DBMSs. The JPA layer of those examined previously did not have the [Product2] entity. One might wonder, then, whether referencing a non-existent JPA entity will cause the project to fail for these DBMSs. Tests show that it does not.

With these changes made, the execution of the [spring-jpa-generic-JUnitTestDao-openjpa] configuration should succeed.

13.4. Configuring the Hibernate JPA layer

 

Note: Press [Alt-F5] to regenerate all Maven projects.

The [sqlserver-config-jpa-hibernate] project is analogous to the [mysql-config-jpa-hibernate] project (Section 6.3) with the same modifications that were used to port the [mysql-config-jpa-openjpa] project to the [sqlserver-config-jpa-openjpa] project (Section 8.3).

Once these changes have been made, running the [spring-jpa-generic-JUnitTestDao-hibernate-eclipselink] configuration should succeed.

 

Note: Press [Alt-F5] to regenerate all Maven projects.

The [sqlserver-config-jpa-eclipselink] project is analogous to the [mysql-config-jpa-eclipselink] (Section 7.3) with the same modifications that were used to port the [mysql-config-jpa-openjpa] project to the [sqlserver-config-jpa-openjpa] project (Section 8.3).

With these modifications in place, the execution of the [spring-jpa-generic-JUnitTestDao-hibernate-eclipselink] configuration should succeed.

https://tahe.developpez.com
This tutorial written by Serge Tahé is made available to the public under the terms of the Creative Commons Attribution – Non-Commercial – ShareAlike 3.0 Unported.