References

MSSQL

Running the SQL Server 2017 container image with Docker

The default memory on Docker is 2 GB. Increase Docker memory to 4 GB.

docker pull microsoft/mssql-server-linux
docker run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=<YourPassword>' -e 'MSSQL_PID=Developer' --cap-add SYS_PTRACE -p 1401:1433 -d microsoft/mssql-server-linux
docker ps -a
docker exec -it <container-id> "bash"
/opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P '<YourPassword>'

CREATE DATABASE TestDB;
USE TestDB;
CREATE TABLE Inventory (id INT, name NVARCHAR(50), quantity INT);
INSERT INTO Inventory VALUES (1, 'banana', 150); INSERT INTO Inventory VALUES (2, 'orange', 154);
SELECT * FROM Inventory WHERE quantity > 152;
GO
QUIT

How to access MSSQL instance on Docker using jtds

pom dependency

<dependency>
    <groupId>net.sourceforge.jtds</groupId>
    <artifactId>jtds</artifactId>
    <version>1.3.1</version>
</dependency>

java code

public static void main(String[] args) throws ClassNotFoundException, SQLException {
    Class.forName("net.sourceforge.jtds.jdbc.Driver");
    Connection conn = DriverManager.getConnection("jdbc:jtds:sqlserver://localhost:1401/TestDB;instance=SQLEXPRESS;", "sa", "<YourPassword>");
    Statement sta = conn.createStatement();

    String jdbcJtdsSql = "SELECT Name from sys.Databases";
    ResultSet rs = sta.executeQuery(jdbcJtdsSql);
    while (rs.next()) {
        String name = rs.getString("Name");
        System.out.println("name: " + name);
    }

    jdbcJtdsSql = "SELECT * FROM Inventory";
    rs = sta.executeQuery(jdbcJtdsSql);
    while (rs.next()) {
        long id = rs.getLong("id");
        String name = rs.getString("name");
        int quantity = rs.getInt("quantity");
        System.out.println("id: " + id + ", name: " + name + ", quantity: " + quantity);
    }

}

JDBC Connection Pool for MSSQL

<jdbc-resource enabled=”true”
               jndi-name=”jdbc/b2bc_mssql”
               object-type=”user”
               pool-name=”b2bc_mssql”>
</jdbc-resource>
<jdbc-connection-pool allow-non-component-callers=”false”
                      associate-with-thread=”false”
                      connection-creation-retry-attempts=”0”
                      connection-creation-retry-interval-in-seconds=”10”
                      connection-leak-reclaim=”false”
                      connection-leak-timeout-in-seconds=”0”
                      connection-validation-method=”table”
                      datasource-classname=”net.sourceforge.jtds.jdbcx.JtdsDataSource”
                      fail-all-connections=”false”
                      idle-timeout-in-seconds=”300”
                      is-connection-validation-required=”false”
                      is-isolation-level-guaranteed=”true”
                      lazy-connection-association=”false”
                      lazy-connection-enlistment=”false”
                      match-connections=”false”
                      max-connection-usage-count=”0”
                      max-pool-size=”32”
                      max-wait-time-in-millis=”60000”
                      name=”b2bc_mssql”
                      non-transactional-connections=”false”
                      ping=”false”
                      pool-resize-quantity=”2”
                      pooling=”true”
                      res-type=”javax.sql.XADataSource”
                      statement-cache-size=”0”
                      statement-leak-reclaim=”false”
                      statement-leak-timeout-in-seconds=”0”
                      statement-timeout-in-seconds=”-1”
                      steady-pool-size=”8”
                      validate-atmost-once-period-in-seconds=”0”
                      wrap-jdbc-objects=”true”>

    <property name=”URL” value=”jdbc:jtds:sqlserver://127.0.0.1/b2bc”/>
    <property name=”User” value=”b2bc”/>
    <property name=”Password” value=”b2bc”/>
    <property name=”serverName” value=”127.0.0.1”/>
    <property name=”instance” value=”LOGO”/>
</jdbc-connection-pool>