In the examples of Huong Dan Java on JPA, I often use MySQL as an example because it is free and easy to use, mainly because I’m familiar with it: D. I think some of you, especially the students, often use MSSQL Server. So, I write this tutorial to guide you all how to connect MSSQL Server in JPA.
First, I will create a Maven project as an example:
Since I’m going to use JPA with Hibernate’s implementation, I’ll add Hibernate dependencies like this:
|
1 2 3 4 5 6 7 8 9 10 |
<dependency> <groupId>org.hibernate</groupId> <artifactId>hibernate-core</artifactId> <version>5.3.0.Final</version> </dependency> <dependency> <groupId>org.hibernate</groupId> <artifactId>hibernate-entitymanager</artifactId> <version>5.3.0.Final</version> </dependency> |
Project Lombok:
|
1 2 3 4 5 6 |
<dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <version>1.16.20</version> <scope>provided</scope> </dependency> |
To use JPA with MSSQL Server, we need to add the JDBC Driver dependency for MSSQL Server as mssql-jdbc. You can find the latest version of mssql-jdbc from Microsoft on the Remote Maven Repository at https://mvnrepository.com/artifact/com.microsoft.sqlserver/mssql-jdbc. This JDBC Driver is available in many versions for different versions of Java, please select the correct version of Java that you are using.
Here, I am using Java 8 so I will add the dependency of mssql-jdbc as follows:
|
1 2 3 4 5 |
<dependency> <groupId>com.microsoft.sqlserver</groupId> <artifactId>mssql-jdbc</artifactId> <version>6.4.0.jre8</version> </dependency> |
In this example, I will define a simple table that contains class information:
|
1 2 3 4 |
CREATE TABLE dbo.clazz ( ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY, Name VARCHAR(25) NOT NULL ) |
Entity of this table:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
package com.huongdanjava.jpa; import javax.persistence.Column; import javax.persistence.Entity; import javax.persistence.GeneratedValue; import javax.persistence.GenerationType; import javax.persistence.Id; import javax.persistence.Table; import lombok.Getter; import lombok.Setter; @Entity @Table @Getter @Setter public class Clazz { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Long id; @Column private String name; } |
The most important part is the configuration file for JPA’s persistence.xml configuration file. The contents of this file will be configured as follows:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
<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_2_1.xsd"> <persistence-unit name="jpaexample" transaction-type="RESOURCE_LOCAL"> <class>com.huongdanjava.jpa.Clazz</class> <exclude-unlisted-classes>true</exclude-unlisted-classes> <properties> <property name="javax.persistence.jdbc.driver" value="com.microsoft.sqlserver.jdbc.SQLServerDriver" /> <property name="javax.persistence.jdbc.url" value="jdbc:sqlserver://localhost:1433;databaseName=jpa_example" /> <property name="javax.persistence.jdbc.user" value="sa" /> <property name="javax.persistence.jdbc.password" value="abczxy1T" /> <property name="hibernate.format_sql" value="true" /> <property name="hibernate.use_sql_comments" value="true" /> </properties> </persistence-unit> </persistence> |
Here, the MSSQL class driver will be com.microsoft.sqlserver.jdbc.SQLServerDriver and the url to connect to the database will look like jdbc:sqlserver://<ip>:<port>;databaseName=<database_name>. Inside:
- ip is the address of MSSQL Server
- The port is the port that MSSQL Server runs on this server. The default port of MSSQL is 1443. If you change the port then correct it.
- database_name is the name of the database that we need to work on.
OK, everything needs to be done, now try running a small example:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
package com.huongdanjava.jpa; import javax.persistence.EntityManager; import javax.persistence.EntityManagerFactory; import javax.persistence.EntityTransaction; import javax.persistence.Persistence; public class Application { public static void main(String[] args) { EntityManagerFactory emf = Persistence.createEntityManagerFactory("jpaexample"); EntityManager em = emf.createEntityManager(); EntityTransaction transaction = em.getTransaction(); transaction.begin(); Clazz clazz = new Clazz(); clazz.setName("Class A"); em.persist(clazz); transaction.commit(); } } |
Result:
