First, what to concatenate Strings to create SQL queries this way is very dangerous as it opens your system to a security issue known as injection of SQL.
Doing this is a bad programming practice, because in the case of concatenating Strings, a hacker or malicious user who has control over one of the concatenated values (for example, some form field he filled in), when placing a quote (') and add fragments of SQL in this field, the malicious user will be able to change the structure of his SQL, and may even find a way to put a DROP DATABASE there in the worst case.
To solve this problem (or possible problem) of SQL injection is that Java has the PreparedStatement. Other programming languages also present this concept.
Finally, as you seem to want to make several batch inserts, it is best to use the mechanism of batch. For that, I borrowed the idea of Soen’s reply. That said, then I think this would be your solution:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class MeuDAO {
private static final String URL = ...;
private static final String LOGIN = ...;
private static final String SENHA = ...;
public MeuDAO() {
}
private static final String SQL_INSERT =
"INSERT INTO tabela (col_codigo, col_chave) VALUES (?, ?)";
public Connection obterConexao() {
return DriverManager.getConnection(URL, LOGIN, SENHA);
}
public void fazMeuInsert(int chave, int[] valores) {
if (valores.length == 0) return;
try (
Connection conn = obterConexao;
PreparedStatement ps = conn.prepareStatement(SQL_INSERT))
) {
ps.setInt(1, chave);
for (int valor : valores) {
ps.setInt(2, valor);
ps.addBatch();
}
ps.executeBatch();
} catch (SQLException e) {
// Tratar o erro adequadamente.
// Ou então relançar a exceção declarando "throws SQLException".
// Ou então encapsular em uma outra exceção e lançá-la.
}
}
}
Note that once your SQL is a String monolithic, so it is not possible to do SQL injection. Also note that due to the use of the mechanism of Try-with-Resources Java 7 or higher, we don’t need to close the PreparedStatement and the Connection explicitly. In addition, the above code ensures that your SQL only needs to be interpreted once instead of once for each insertion. Also only one connection will open at the beginning and close at the end, instead a lot of connections being opened and closed afterwards. Finally, all of them will be implemented together. All of this guarantees a much better performance and network consumption than it would be to insert them one-by-one.
If your array is very large (type, thousands of elements), maybe you want to divide it into lots so as not to get a batch very large (which can consume a lot of memory both on the client and on the server). Here is the code that automatically divides the array in lots of 1000 elements:
public void fazMeuInsert(int chave, int[] valores) {
if (valores.length == 0) return;
try (
Connection conn = obterConexao;
PreparedStatement ps = conn.prepareStatement(SQL_INSERT))
) {
int i = 0;
ps.setInt(1, chave);
for (int valor : valores) {
ps.setInt(2, valor);
ps.addBatch();
i++;
if (i % 1000 == 0 || i == valores.size() {
ps.executeBatch();
}
} catch (SQLException e) {
// Tratar o erro adequadamente.
// Ou então relançar a exceção declarando "throws SQLException".
// Ou então encapsular em uma outra exceção e lançá-la.
}
}
}
I won’t be able to answer right now, but this is a serious security problem.
– Maniero
Why don’t you mount your query in the loop? And return a string?
– Marco Souza
Something else for each new line in the Insert should have a separate comma.
– Marco Souza