From c2c3d94965b6c4a086a6751b8252511bff32eb4f Mon Sep 17 00:00:00 2001
From: Julien Lancelot <julien.lancelot@sonarsource.com>
Date: Tue, 26 Apr 2016 10:50:18 +0200
Subject: [PATCH] SONAR-7549 Fix SqlExecutorTest on MySQL and MsSQL

connection.commit() should not be used on these 2 DB
executeInsert should use parametrized query
---
 .../src/test/java/org/sonar/db/DbTester.java  | 21 +++++++++++--------
 .../org/sonar/db/charset/SqlExecutorTest.java |  5 +----
 2 files changed, 13 insertions(+), 13 deletions(-)

diff --git a/sonar-db/src/test/java/org/sonar/db/DbTester.java b/sonar-db/src/test/java/org/sonar/db/DbTester.java
index d58f519899f..cf3f4f4c22b 100644
--- a/sonar-db/src/test/java/org/sonar/db/DbTester.java
+++ b/sonar-db/src/test/java/org/sonar/db/DbTester.java
@@ -29,6 +29,7 @@ import java.sql.PreparedStatement;
 import java.sql.ResultSet;
 import java.sql.ResultSetMetaData;
 import java.sql.SQLException;
+import java.util.Collections;
 import java.util.List;
 import java.util.Locale;
 import java.util.Map;
@@ -66,6 +67,7 @@ import static org.junit.Assert.fail;
  */
 public class DbTester extends ExternalResource {
 
+  private static final Joiner COMMA_JOINER = Joiner.on(", ");
   private final System2 system2;
   private final TestDb db;
   private DbClient client;
@@ -129,9 +131,9 @@ public class DbTester extends ExternalResource {
     return client;
   }
 
-  public void executeUpdateSql(String sql) {
+  public void executeUpdateSql(String sql, String... params) {
     try (Connection connection = db.getDatabase().getDataSource().getConnection()) {
-      new QueryRunner().update(connection, sql);
+      new QueryRunner().update(connection, sql, params);
     } catch (Exception e) {
       throw new IllegalStateException("Fail to execute sql: " + sql, e);
     }
@@ -141,16 +143,17 @@ public class DbTester extends ExternalResource {
    * Very simple helper method to insert some data into a table.
    * It's the responsibility of the caller to convert column values to string.
    */
-  public void executeInsert(String table, Map<String, String> valuesByColumn){
+  public void executeInsert(String table, Map<String, String> valuesByColumn) {
     if (valuesByColumn.isEmpty()) {
       throw new IllegalArgumentException("Values cannot be empty");
     }
-    String sql = "insert into " + table.toLowerCase() + " (" +
-      Joiner.on(", ").join(valuesByColumn.keySet()) +
-      ") values ('" +
-      Joiner.on("', '").join(valuesByColumn.values()) +
-      "')";
-    executeUpdateSql(sql);
+
+    String sql = "insert into " + table.toLowerCase(Locale.ENGLISH) + " (" +
+      COMMA_JOINER.join(valuesByColumn.keySet()) +
+      ") values (" +
+      COMMA_JOINER.join(Collections.nCopies(valuesByColumn.size(), '?')) +
+      ")";
+    executeUpdateSql(sql, valuesByColumn.values().toArray(new String[valuesByColumn.size()]));
   }
 
   /**
diff --git a/sonar-db/src/test/java/org/sonar/db/charset/SqlExecutorTest.java b/sonar-db/src/test/java/org/sonar/db/charset/SqlExecutorTest.java
index 4c806bebc29..4b4da278cb2 100644
--- a/sonar-db/src/test/java/org/sonar/db/charset/SqlExecutorTest.java
+++ b/sonar-db/src/test/java/org/sonar/db/charset/SqlExecutorTest.java
@@ -57,8 +57,6 @@ public class SqlExecutorTest {
     dbTester.executeInsert(USERS_DB_TABLE, ImmutableMap.of(LOGIN_DB_COLUMN, "login1", NAME_DB_COLUMN, "name one"));
     dbTester.executeInsert(USERS_DB_TABLE, ImmutableMap.of(LOGIN_DB_COLUMN, "login2", NAME_DB_COLUMN, "name two"));
 
-    dbTester.commit();
-
     try (Connection connection = dbTester.openConnection()) {
       List<String[]> users = underTest.executeSelect(connection, "select " + LOGIN_DB_COLUMN + ", " + NAME_DB_COLUMN + " from users order by id", new SqlExecutor.StringsConverter(
         2));
@@ -73,13 +71,12 @@ public class SqlExecutorTest {
   @Test
   public void executeUpdate_executes_PreparedStatement() throws Exception {
     dbTester.executeInsert(USERS_DB_TABLE, ImmutableMap.of(LOGIN_DB_COLUMN, "the_login", NAME_DB_COLUMN, "the name"));
-    dbTester.commit();
 
     try (Connection connection = dbTester.openConnection()) {
       underTest.executeUpdate(connection, "update users set " + NAME_DB_COLUMN + "='new name' where " + LOGIN_DB_COLUMN + "='the_login'");
-      connection.commit();
     }
     Map<String, Object> row = dbTester.selectFirst("select " + NAME_DB_COLUMN + " from users where " + LOGIN_DB_COLUMN + "='the_login'");
+    assertThat(row).isNotEmpty();
     assertThat(row.get("NAME")).isEqualTo("new name");
   }
 
-- 
2.39.5