Spring Boot连接SqlServer数据库

1.修改pom.xml,添加sql server支持

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

<dependency>  
  <groupId>org.springframework.boot</groupId>  
  <artifactId>spring-boot-starter-data-jpa</artifactId>  
</dependency>  

<dependency>
    <groupId>com.microsoft.sqlserver</groupId>
    <artifactId>mssql-jdbc</artifactId>
    <version>8.1.0.jre8-preview</version>
</dependency>

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

我在windows+JDK8上开发上开发时，使用的是sqljdbc4的依赖，但是我换到Deepin上，使用了免安装版的OpenJDK13,使用idea打开原先的工程，始终解决不了sqljdbc4的依赖

于是换成了mssql-jdb依赖，结果还真是可以了。

2.修改application.yml配置文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

spring:
 datasource:
    ## default connection pool
    hikari:
      connection-timeout: 20000
      maximum-pool-size: 5
      username: xxx
      password: xxxx
    url: jdbc:sqlserver://xxxx;databasename=ztzn
    driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
  jpa:
    hibernate:
      ddl-auto: update
    properties:
      hibernate.temp.use_jdbc_metadata_defaults: fase
    database-platform: org.hibernate.dialect.SQLServer2008Dialect

也可以在url中指定用户名密码：

1

jdbc:sqlserver://localhost;user=MyUserName;password=*****;

3.编写实体类

4.编写接口，继承自JpaRepository

问题

(1) 对象名 ‘sys.sequences’ 无效

解决方法是在application.yml配置文件中配置数据库版本：database-platform: org.hibernate.dialect.SQLServer2008Dialect

(2) 缺少对象或列名，或者对象或列名为空。

对于 SELECT INTO 语句，请确保每列均具有名称。对于其他语句，请查找空的别名。不允许使用定义为 “” 或 [] 的别名。请将别名更改为有效名称。

今天回来再次解决这个问题。这里有一些网站，都是说要指定模式的名字。

1
2
3
4

<property name="hibernate.dialect" value="org.hibernate.dialect.SQLServer2012Dialect" />
<property name="hibernate.default_catalog" value="[database name]" />
<property name="hibernate.default_schema" value="dbo" />
<property name="hibernate.id.new_generator_mappings" value="false" />

我尝试着使用jpa.show-sql: true,将打印出的select语句，复制到navicate中，去掉where子句（因为生成的where子句是：where vclient0_.SiteNo=?），是可以执行的。出现了占位符的问题，我查了一下，发现sql server好像不支持占位符。

jpa中添加了use-new-id-generator-mappings: false也不起作用，将naming:physical-strategy: org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl，这样就将生成的name_ki类似列名，就改成了Entity中的字符中的大小写固定下来了。

还是没解决啊。我尝试着在网上搜相关的内容，愣是没有人遇到过，真是的。询问群里的人，也没人愿意搭理我。我花了很长时间在这上面了，确实解决不了。项目又推进的急，真是崩溃啊。虽然可以使用Mybatis代替Hibernate，但是我总是不甘心就这样把这个问题留在这里。

根据参考资料，我再次将ddl-auto: update 改为了ddl-auto: validate，结果出现： wrong column type encountered in column [SiteNo] in table [ztzn.dbo.V_client]; found [smallint (Types#SMALLINT)], but expecting [int (Types#INTEGER)]

数据类型不一致，难道是这个原因吗?我继续探索，将数据类型修改一下。

改成什么呢？改成String不行，int也不行，Integer也不行。

经历了将number对应到BigDecimal；将smalint对应到java的byte；然后将datetime对应java.sql.Timestamp；将char对应于string,并在那一列添加@Column(columnDefinition = “Char(5)”)注解；将tinyint改为byte，等一系列改造。

卧槽，还是报错：缺少对象或列名，或者对象或列名为空。对于 SELECT INTO 语句，请确保每列均具有名称。对于其他语句，请查找空的别名。不允许使用定义为 “” 或 [] 的别名。请将别名更改为有效名称。

最后放弃吧，改成mybatis算了。

惊喜啊，通过同事的帮助，我终于解决了这个问题，问题是出在我的application.yml配置中。
错误的配置：

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
26
27

server:
  port: 8909
spring:
  mvc:
    throw-exception-if-no-handler-found: true
  resources:
    add-mappings: false
  datasource:
    ## default connection pool
    hikari:
      connection-timeout: 20000
      maximum-pool-size: 5
      username: xxx
      password: xxx
    url: jdbc:sqlserver://xxx;databasename=ztzn
    driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
  jpa:
    hibernate:
      ddl-auto: update
      naming:
        physical-strategy: org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
     use-new-id-generator-mappings: false
   properties:
     hibernate:
       temp.use_jdbc_metadata_defaults: fase
    database-platform: org.hibernate.dialect.SQLServer2005Dialect
    show-sql: true

正确的配置：

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
26
27

server:
  port: 8909
spring:
  mvc:
    throw-exception-if-no-handler-found: true
  resources:
    add-mappings: false
  datasource:
    ## default connection pool
    hikari:
      connection-timeout: 20000
      maximum-pool-size: 5
      username: jjjj
      password: jjjj
    url: jdbc:sqlserver://jj.jj.jj.jj;databasename=ztzn
    driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
  jpa:
    hibernate:
      ddl-auto: update
      naming:
        physical-strategy: org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
#      use-new-id-generator-mappings: false
#    properties:
#      hibernate:
#        temp.use_jdbc_metadata_defaults: fase
    database-platform: org.hibernate.dialect.SQLServer2005Dialect
    show-sql: true

注意挥掉的内容

还有一个地方就是你要把@Id下面的@GeneratedValue注解去掉，否则会报:对象名 ‘hibernate_sequence’ 无效 的错误。

总结起来，主要就是自己的配置文件搞得事情，那个配置文件:temp.use_jdbc_metadata_defaults 属性是针对于Postgres的，但是不能在SqlServer中，所以不明白每一个文件配置内容，确实还是头疼啊。总之，还是要多多的学习吧，不能瞎几把抄网上的答案。我记得这个属性是PostgresSql数据库必须配置的，否则会出现：java.sql.SQLFeatureNotSupportedException: 这个 org.postgresql.jdbc.PgConnection.createClob() 方法尚未被实作，详情请收看 Spring-Boot连接PostgresSql数据库

(3) The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: “The server selected protocol version TLS10 is not accepted by client preferences [TLS13, TLS12]

【解决方法】
主要是jdk版本和mssql-jdbc版本不匹配，我安装了JDK17，使用的是:8.1.0.jre8-preview

1
2
3
4
5
6
7
8
9
10
11
12
13

<!--1.8版本-->
<dependency>
    <groupId>com.microsoft.sqlserver</groupId>
    <artifactId>mssql-jdbc</artifactId>
    <version>8.1.0.jre8-preview</version>
</dependency>

<!--jdk17版本-->
<dependency>
    <groupId>com.microsoft.sqlserver</groupId>
    <artifactId>mssql-jdbc</artifactId>
    <version>9.5.0.jre17-preview</version>
</dependency>

以前遇到了这个问题，我修改了mssql-jdbc的版本，就可以了，但是后来，又出现了这个问题，在docker中运行，死活不行，我尝试了 mssql-jdbc 版本 9.5.0.jre11-preview。 根据尝试，我认为第一个出现这个问题的原因，就是sql server服务器启用了TSL1.0，但是openjdk默认禁用了TLS1.0，所以就会出现，客户端不支持服务器的TLS1.0,JDK作为客户端，SQL Server服务器作为服务端。

根据分析，这个问题相应的就有两种方法解决：

• 1.服务器开启TLS 1.2
  关于sql server 服务器开启tls1.2，这个我就先不探索了，本着解决问题的思路进行，我因为不能很方便的接触到 sql server 服务器，所以就暂时先不用这个方法解决了。

• 2.JDK开启TLS 1.0
  我用的是 docker 部署的程序，镜像是 adoptopenjdk/openjdk11-openj9:alpine-slim，

  1 2 3 4 5 6 7 8 9 10

  ## 进入docker容器 docker exec -it id /bin/sh ## 定位 security 配置文件 /opt/java/openjdk/conf/security/java.security ## 将下面的内容 使用 井号注释掉 jdk.tls.disabledAlgorithms=SSLv3, TLSv1, TLSv1.1, RC4, DES, MD5withRSA, \ DH keySize < 1024, EC keySize < 224, 3DES_EDE_CBC, anon, NULL, \ include jdk.disabled.namedCurves ## 或者删除其中的TLSV1和TLSv1.1

经过测试，确实实现了启动TLS1.0的功能，但是还有一个问题，因为是使用的docker部署，不能每一次都直接进入到docker容器中吧，如何能使用dockerfile进行修改呢？

接着上文继续解决问题，dockerfile文件修改容器中的文件,可以使用copy命令，将宿主机上的文件拷贝到容器上。先将容器中修改后的文件，拷贝到本地来

1
2

## 复制security文件
docker cp ph-phems:/opt/java/openjdk/conf/security/java.security /home/bibichuan/phems-cloud/bin

然后编写dockerfile文件，使用copy命令进行覆盖

1
2

## 复制security文件
COPY ./config/java.security /opt/java/openjdk/conf/security/java.security

(4) Cannot invoke “java.lang.reflect.Method.invoke(Object, Object[])” because “com.sun.xml.bind.v2.runtime.reflect.opt.Injector.defineClass”

这个也是因为代码和java版本不匹配导致的问题，暂时没解决，还是把jdk降到了13，没有用17了。