First, concatenating String s to create SQL queries in this way is very dangerous as it opens your system to a security problem known as SQL Injection .
Doing this is bad programming practice, because in the case of concatenating String s, a hacker or malicious user who has control over one of the concatenated values (for example, some form field that he filled out) , by putting a quote ( ' ) and adding SQL fragments in this field, the malicious user will be able to change the structure of your 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 PreparedStatement . Other programming languages also feature this concept.
Finally, since you seem to want to do multiple batch inserts, it's best to use the batch mechanism. For this, I borrowed the idea from this SOen response . Having said that, 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 since your SQL is a String monolithic, so it is not possible to do SQL injection. Also note that due to the try-with-resources of Java 7 or higher, we do not have to explicitly close PreparedStatement and Connection . In addition, the above code ensures that your SQL only needs to be interpreted once instead of once for each insert. In addition only one connection will open at the beginning and closed at the end, instead a lot of connections being opened and then closed. Finally, all of them will be fulfilled together. All this ensures a much better performance and network consumption than it would be when you insert them one-by-one.
If your array is too large (kind, thousands of elements), you might want to divide it into batches so as not to get a very large consuming lots of memory on both the client and the server). Here is the code that automatically divides array into batches 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.
}
}
}
