Java – Update Statistics in SQL Server on Ubuntu No.91

mvn archetype:generate -DinteractiveMode=false -DgroupId=org.example.sqlsrv -DartifactId=sqlupdstat -DarchetypeArtifactId=maven-archetype-quickstart

rm ./sqlupdstat/src/main/java/org/example/sqlsrv/App.java
rm ./sqlupdstat/src/test/java/org/example/sqlsrv/AppTest.java

cd ./sqlupdstat/
code .

(a part of pom.xml)

  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <maven.compiler.source>1.8</maven.compiler.source>
    <maven.compiler.target>1.8</maven.compiler.target>
  </properties>

  <dependencies>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.11</version>
      <scope>test</scope>
    </dependency>
    <dependency>
      <groupId>com.microsoft.sqlserver</groupId>
      <artifactId>mssql-jdbc</artifactId>
      <version>11.2.1.jre8</version>
    </dependency>
  </dependencies>

Exception in thread "main" java.sql.SQLException: No suitable driver found for jdbc:sqlserver://localhost:1433;databaseName=master;user=sa;password=yourpassowrd;encrypt=false
        at java.sql.DriverManager.getConnection(DriverManager.java:689)
        at java.sql.DriverManager.getConnection(DriverManager.java:270)
        at org.example.sqlsrv.sqlupdstat.main(sqlupdstat.java:36)

(*1) updating statistics for sampletable in testdb database
update statistics testdb.dbo.sampletable

(*2) clearing the query execution plan in sampletable.
use testdb;
Exec sp_recompile N’dbo.sampletable’

package org.example.sqlsrv;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class sqlupdstat {

    // create an insert query and save it to a string
    public static String insertQuery() {
        String query = "INSERT INTO testdb.dbo.sampletable (id, num, cdatetime, note) VALUES (100,0,CURRENT_TIMESTAMP,'init')";

        for (int num = 0; num < 10; num++) {
            String AlphaNumericString = "ABCDEFGHIJKLMNOPQRSTUVWXYZ" + "0123456789" + "abcdefghijklmnopqrstuvxyz";
            StringBuilder sb = new StringBuilder(10);

            for (int i = 1; i < 10; i++) {
                int index = (int) (AlphaNumericString.length() * Math.random());
                sb.append(AlphaNumericString.charAt(index));
            }
            String randomString = sb.toString();
            query += ",(" + num*10 +"," + num + ",CURRENT_TIMESTAMP,'" + randomString + "')";
        }
        query += ";";

        return query;
    }

    public static void main(String[] args) throws SQLException {

        String url = "jdbc:sqlserver://localhost:1433;databaseName=master;user=sa;password=yourpassword;encrypt=false";
        Connection conn = DriverManager.getConnection(url);

        // Create databas
        String createdb = "IF DB_ID (N'testdb') IS NULL CREATE DATABASE testdb";
        Statement cd = conn.createStatement();
        cd.execute(createdb);
        System.out.println("Database was created.");
        cd.close();

        // Create table
        String createtable = "IF NOT EXISTS (select * from testdb.dbo.sysobjects where name like 'sampletable') CREATE TABLE testdb.dbo.sampletable (id int primary key, num int, cdatetime datetime, note varchar(50))";
        Statement ct = conn.createStatement();
        ct.execute(createtable);
        System.out.println("Table was created.");
        ct.close();

        // Insert data
        String insertdata = insertQuery();
        //System.out.println(insertdata);
        Statement id = conn.createStatement();
        id.execute(insertdata);
        System.out.println("Data was inserted.");
        id.close();

        // Update statistics
        String UpdateStatistics = "update statistics testdb.dbo.sampletable";
        Statement us = conn.createStatement();
        us.execute(UpdateStatistics);
        System.out.println("Statistics was updated.");
        us.close();

        // Recompile
        String Recompile = "use testdb;Exec sp_recompile N'dbo.sampletable';";
        System.out.println(Recompile);
        Statement rc = conn.createStatement();
        System.out.println("The query execution plan was updated.");

        rc.close();
        conn.close();
    }
}

(a part of results)

java -cp /tmp/cp_2mki4e7qhllr13hydrsbsfhep.jar org.example.sqlsrv.sqlupdstat 
Database was created.
Table was created.
Data was inserted.
Statistics was updated.
use testdb;Exec sp_recompile N'dbo.sampletable';
The query execution plan was updated.