How to run a select query in SQL Server on Ubuntu using Java is shown in this blog.
Contents
▼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
- Step 3: Proof of concept connecting to SQL using Java https://docs.microsoft.com/en-us/sql/connect/jdbc/step-3-proof-of-concept-connecting-to-sql-using-java?view=sql-server-ver15
- Processing SQL Statements with JDBC
- Java – JDBC Driver for SQL Server on Ubuntuの自動リトライ接続 No53
That’s all. Have a nice day ahead !!!