JDBC & Microsoft SQL Server / How to set AllowMultipleQueries

Question

How to set AllowMultipleQueries on JDBC for Microsoft SQL Server?

My connection string is currently following, but it not works.

private final String url = "jdbc:sqlserver://localhost:11435;databaseName=myDatabase;allowMultiQueries=true";

Answer 1

With Microsoft's JDBC driver for SQL Server you don't need to add anything special to your connection URL in order to enable multiple statements in a single execute. This works fine:

connectionUrl = "jdbc:sqlserver://localhost:52865;databaseName=myDb";
try (Connection conn = DriverManager.getConnection(connectionUrl, myUserID, myPassword)) {
    System.out.printf("Driver version %s%n", conn.getMetaData().getDriverVersion());

    try (Statement st = conn.createStatement()) {
        st.execute("CREATE TABLE #test (id INT IDENTITY PRIMARY KEY, textcol NVARCHAR(50))");
    }

    String[] itemsToInsert = new String[] { "foo", "bar" };

    String sql = 
            "SET NOCOUNT ON;" +
            "INSERT INTO #test (textcol) VALUES (?);" +
            "SELECT @@IDENTITY;";
    try (PreparedStatement ps = conn.prepareStatement(sql)) {
        for (String item : itemsToInsert) {
            ps.setString(1, item);
            try (ResultSet rs = ps.executeQuery()) {
                rs.next();
                int newId = rs.getInt(1);
                System.out.printf("'%s' inserted with id=%d%n", item, newId);
            }
        }
    }

} catch (Exception e) {
    e.printStackTrace(System.err);
}

producing

Driver version 6.0.7728.100
'foo' inserted with id=1
'bar' inserted with id=2

However, in this particular case it would be better to use JDBC's built-in support for retrieving generated keys:

String sql = "INSERT INTO #test (textcol) VALUES (?)";
try (PreparedStatement ps = conn.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS)) {
    for (String item : itemsToInsert) {
        ps.setString(1, item);
        ps.executeUpdate();
        try (ResultSet rs = ps.getGeneratedKeys()) {
            rs.next();
            int newId = rs.getInt(1);
            System.out.printf("'%s' inserted with id=%d%n", item, newId);
        }
    }
}

producing the exact same results.

Answer 2

why not use a stored procedure instead? Instead of setting allowMultiQueries you should rather use a stored procedure like below

create procedure usp_data
as
begin
INSERT INTO ......; 
SELECT @@IDENTITY AS [id];
end

Now call that stored procedure from your code behind. You can as well parameterize the procedure if needed. See this existing post Multiple queries executed in java in single statement