Java – Execute a Select query in SQL Server on Ubuntu No.8

How to run a select query in SQL Server on Ubuntu using Java is shown in this blog.

▼1. Prerequisites

Creating a table “t1” in a database “TestDB” and inserting data intho this table are executed by sqlcmd utility. “sa” user is used in this sample.

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. Executing a query in SQL Server on Ubuntu by Java

2-1. Creating Java code to execute Select query

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);
        }
    }
}

1-2. Results of this code

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

▼2. Reference

That’s all. Have a nice day ahead !!!

Leave a Reply

Your email address will not be published. Required fields are marked *