Try SQL Server on Fedora
Tags: sqlserver, java
Published: 2023-12-16 14:06:50

## Intro
I will try installing Microsoft SQL Server on Fedora 39 in this time.
After installing, I will try accessing it from my Spring Boot application.

### Environments
* Fedora Linux 39(Workstation Edition)
* SQL Server 2022
* openjdk 17.0.9 2023-10-17(Red_Hat-17.0.9.0.9-2)

## Installing
According to the document, there is no SQL Server installing for Fedora.
So I will add repositories first.

```
sudo dnf config-manager --add-repo=https://packages.microsoft.com/config/rhel/9/mssql-server-2022.repo
sudo dnf config-manager --add-repo=https://packages.microsoft.com/config/rhel/9/prod.repo

sudo dnf check-update
sudo dnf -y install mssql-server mssql-tools
```

After installing, I will execute setting up command.

```
sudo /opt/mssql/bin/sqlservr-setup
```

Opening the port to accept access from another PC.

```
sudo firewall-cmd --zone=public --add-port=1433/tcp --permanent
sudo firewall-cmd --reload
```

Adding "mssql-tools" path into .bashrc.
```
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc
```

* [Installation guidance for SQL Server on Linux - Microsoft Learn](https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-setup?view=sql-server-ver16)
* [Install the SQL Server command-line tools sqlcmd and bcp on Linux - Microsoft Learn](https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-setup-tools?view=sql-server-ver16&tabs=redhat-install)
* [Quickstart: Install SQL Server and create a database on Red Hat - Microsoft Learn](https://learn.microsoft.com/en-us/sql/linux/quickstart-install-connect-red-hat?view=sql-server-ver16&tabs=rhel8)
* [【 dnf 】コマンド（応用編その7）――リポジトリを追加する - @IT](https://atmarkit.itmedia.co.jp/ait/articles/2001/31/news006.html)

After installing SQL Server, I will also install DBeaver to access it by GUI tool.
```
sudo rpm -Uvh ./dbeaver-ce-23.3.0-stable.x86_64.rpm
```

* [DBeaver Community](https://dbeaver.io/)

```sql
CREATE TABLE users (id bigint identity(1, 1) primary key,
    name varchar(32) not null,
    last_update_date datetimeoffset default current_timestamp AT TIME ZONE 'Tokyo Standard Time');
    go
```

* [Configure the time zone for SQL Server 2022 on Linux - Microsoft Learn](https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-configure-time-zone?view=sql-server-ver16)

## Access SQL Server from my Spring Boot project
To access SQL Server from my Spring Boot project, I will add Microsoft JDBC Driver for SQL Server.

* [Try Spring Boot](https://dev.to/masanori_msl/try-springboot-lg0)
* [Try Spring Boot 2](https://dev.to/masanori_msl/try-springboot-2-57d5)
* [[Spring Boot] Reading spreadsheets with Apache POI](https://dev.to/masanori_msl/spring-boot-reading-spreadsheets-with-apache-poi-64)

#### build.gradle
```groovy
...

dependencies {
	implementation 'org.springframework.boot:spring-boot-starter-thymeleaf'
	implementation 'org.springframework.boot:spring-boot-starter'
	implementation 'org.springframework.boot:spring-boot-starter-web'
	implementation 'org.apache.poi:poi:5.2.5'
	implementation 'org.apache.poi:poi-ooxml:5.2.5'
	implementation 'com.microsoft.sqlserver:mssql-jdbc:12.4.2.jre11'

	developmentOnly 'org.springframework.boot:spring-boot-devtools'
	testImplementation 'org.springframework.boot:spring-boot-starter-test'
}
...
```

* [Microsoft JDBC Driver For SQL Server » 12.4.2.jre11 - Maven Repository](https://mvnrepository.com/artifact/com.microsoft.sqlserver/mssql-jdbc/12.4.2.jre11)
* [Download Microsoft JDBC Driver for SQL Server - Microsoft Learn](https://learn.microsoft.com/en-us/sql/connect/jdbc/download-microsoft-jdbc-driver-for-sql-server?view=sql-server-ver16)

#### UserAccessSample.java
```java
package jp.masanori.springbootsample.users;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class UserAccessSample {
    public String accessSample() {
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            String connectionUrl = "jdbc:sqlserver://localhost;encrypt=false;database=master;integratedSecurity=false;user=sa;password=PASSWORD";
            Connection con = DriverManager.getConnection(connectionUrl);
            String SQL = "SELECT * FROM users";
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(SQL);

            while (rs.next()) {
                System.out.println(
                        rs.getInt("id")
                                + ", " + rs.getString("name"));
            }
            return "OK";
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return "NG";
    }
}
```

* [Working with a connection - Microsoft Learn](https://learn.microsoft.com/en-us/sql/connect/jdbc/working-with-a-connection?view=sql-server-ver16)