Tip submitted by @Zyst
Goal: By the end of this tutorial you will have the default JHipster application running on your SQL Server, with everything being functional.
Start by running JHipster normally with yo jhipster
, select the options to use Java 8, token based authentication, SQL, MySQL as the dev. database, MySQL as the prod. database, Yes with ehcache, No Elasticsearch, No clustered HTTP, No Websockets, Maven, Grunt, and no Compass.
We then add the MS SQL Server JDBC dependency to the project pom.xml
file.
pom.xml
[...]
<!-- Microsoft JDBC -->
<dependency>
<groupId>com.microsoft.sqlserver</groupId>
<artifactId>sqljdbc41</artifactId>
<version>4.1</version>
</dependency>
<!-- Liquibase MS SQL Server extensions -->
<dependency>
<groupId>com.github.sabomichal</groupId>
<artifactId>liquibase-mssql</artifactId>
<version>1.4</version>
</dependency>
[...]
I am using Sql JDBC 4.1, and already have it installed to my personal repository, but if you do not this will not work without some further configuration, check out this stackoverflow question for further reference.
The Liquibase MS SQL Server extension allows you to do some neat stuff we will be using later on in this tutorial.
##Database modification
Go into src\main\resources\config\application-dev.yml
and change your application to use the new datasource, and your Hibernate configuration to use the SQL Server dialect as seen below:
application-dev.yml
spring:
profiles:
active: dev
datasource:
driverClassName: com.microsoft.sqlserver.jdbc.SQLServerDriver
dataSourceClassName: com.microsoft.sqlserver.jdbc.SQLServerDataSource
url: jdbc:sqlserver://localhost:1433;databaseName=test
databaseName:
serverName:
username: myuser
password: supersecretpassword
cachePrepStmts: true
prepStmtCacheSize: 250
prepStmtCacheSqlLimit: 2048
useServerPrepStmts: true
jpa:
database-platform: org.hibernate.dialect.SQLServerDialect
database: SQL_SERVER
openInView: false
show_sql: true
generate-ddl: false
[...]
This assuming your database is called test
, change your connection url as necessary.
Now go into *\src\main\resources\config\liquibase\changelog\00000000000000_initial_schema.xml
and at the top of the file change the following properties:
00000000000000_initial_schema.xml
<databaseChangeLog
xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd
http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">
<property name="now" value="now()" dbms="mysql,h2"/>
<property name="now" value="current_timestamp" dbms="postgresql"/>
<property name="now" value="GETDATE()" dbms="mssql"/>
<changeSet id="00000000000000" author="jhipster" dbms="postgresql">
<createSequence sequenceName="hibernate_sequence" startValue="1000" incrementBy="1"/>
</changeSet>
[...]
First, make sure you changed your xml databaseChangeLog property to include the ext. Now inside src\main\resources\config\liquibase\changelog\00000000000000_initial_schema.xml
find the data entries and change them:
00000000000000_initial_schema.xml
<ext:loadData encoding="UTF-8"
file="config/liquibase/users.csv"
separator=";"
tableName="JHI_USER" identityInsertEnabled="true">
<column name="activated" type="boolean"/>
<column name="created_date" type="timestamp"/>
</ext:loadData>
<dropDefaultValue tableName="JHI_USER" columnName="created_date" columnDataType="datetime"/>
<ext:loadData encoding="UTF-8"
file="config/liquibase/authorities.csv"
separator=";"
tableName="JHI_AUTHORITY"
identityInsertEnabled="true" />
<ext:loadData encoding="UTF-8"
file="config/liquibase/users_authorities.csv"
separator=";"
tableName="JHI_USER_AUTHORITY"
identityInsertEnabled="true" />
Adding the identityInsertEnabled="true"
is the same as wrapping your Inserts with IDENTITY_INSERT ON
and IDENTITY_INSERT OFF
which will allow you to insert the project autogenerated identities directly. This is why we are using the MS SQL Server Liquibase for.
Now try running your application! Everything should be working and you should be on your way to continue using your JHipster application with SQL Server.