MSSQL
Running SQL Server in Docker and connecting to it with jTDS and a JDBC connection pool.
1 min read
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-linuxdocker 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-linuxdocker ps -adocker 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;GOQUITHow 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>