Java – SQL Server on Linux で select を実行する方法 On Ubuntu No.8

自己投資としてチャレンジしている内容を Blog で公開しています。今回は SQL Server on Linux 上で select を実行する方法について紹介します。(In English: Java – Execute a Select query in SQL Server on Ubuntu No.8)

▼1. 事前準備

sqlcmd のツールを利用して SQL Server の TestDB データベースに接続後、テーブル作成、データのインサートをします。

e.g) ユーザー名 sa　対象となるデータベース名 TestDB

sqlcmd -Usa  -d TestDB
Password:
1> create table t1 (c1 int primary key, c2 int);
2> go
1> insert into t1 values(1,2);
2> go

(1 rows affected)
1> insert into t1 values(2,3),(3,4),(4,5),(5,6);
2> go

(4 rows affected)
1> select * from t1;
2> go
c1          c2        
----------- -----------
          1           2
          2           3
          3           4
          4           5
          5           6

(5 rows affected)
1>

▼2. SQL Server on Linux でクエリを実行

2-1. select を実行する Java コードを作成

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.Date;

public class ExecuteQSQLSvr {
    private static final Logger log = LoggerFactory.getLogger(ExecuteQSQLSvr.class);

    public static void main(String[] args) throws InterruptedException {
        Connection con = null;
        boolean success = false;
        int retryc = 0;
        int max_retries = 3;
        ResultSet resultSet = null;

        while (!success && retryc < max_retries) {
            try {
                Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
                String connectionUrl = "jdbc:sqlserver://localhost:1433;" + "databaseName=TestDB;user=sa;password=password123;loginTimeout=30;socketTimeout=60000;queryTimeout=60;cancelQueryTimeout=60;";
                con = DriverManager.getConnection(connectionUrl);
                Statement statement = con.createStatement();
                System.out.println("Current Timestamps: " + new Timestamp(new Date().getTime()));
                log.info("Connect to SQL Server successfully");
                success = true;
                // execute select
                String selectSql = "select c1, c2 from t1;";
                resultSet = statement.executeQuery(selectSql);
                System.out.println("retrieve c1" + "\t" + "c2 for t1 table");

                while (resultSet.next()){
                    System.out.println(resultSet.getString("c1") + "\t" + resultSet.getString("c2"));
                }
            } catch (ClassNotFoundException e) {
                retryc++;
                System.out.println("Current Timestamps: " + new Timestamp(new Date().getTime()));
                log.error("Error Message: {}, retry count is {}", e, retryc);
                // 30 seconds (30000 milliseconds) interval
                Thread.sleep(30000);
            } catch (SQLException se) {
                retryc++;
                System.out.println("Current Timestamps: " + new Timestamp(new Date().getTime()));
                log.error("Error Messages: {}, retry count is {}", se, retryc);
                // 30 seconds (30000 milliseconds) interval
                Thread.sleep(30000);
            }
        }
        try {
            if (con != null) {
                con.close();
                System.out.println("Current Timestamps: " + new Timestamp(new Date().getTime()));
                log.info("The connection is closed");
            }else{
                log.info("The connection was closed");
            }
        } catch (SQLException sec) {
            System.out.println("Current Timestamps: " + new Timestamp(new Date().getTime()));
            log.error("Error MEssages of fin ", sec);
        }
    }
}

2-2. 実行結果の確認

Current Timestamps: 2020-05-26 06:52:10.564
[main] INFO ExecuteQSQLSvr - Connect to SQL Server successfully
retrieve c1    c2 for t1 table
1    2
2    3
3    4
4    5
5    6
Current Timestamps: 2020-05-26 06:52:11.83
[main] INFO ExecuteQSQLSvr - The connection is closed

▼3. 参考情報

1 Step 3: Proof of concept connecting to SQL using Java
2 Processing SQL Statements with JDBC
3. Java – JDBC Driver for SQL Server on Ubuntuの自動リトライ接続 No53

以上です。参考になりましたら幸いです。