CODEWITHSUNDEEP
javajdbc
Mohamed Dahab
Mohamed Dahab

Reputation: 21

Inserting a list of values into Mysql

How do I insert a list of values into a column in a MySQL table.

Here is my project:

public void settingAmount(List<String>lst)throws Exception{

    // Accessing driver from jar files 
    Class.forName("com.mysql.jdbc.Driver");
     // here we create a variable for the connection called con 
    Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/ozon","root","root");
     // here we create our query 
    Statement stmt = (Statement) con.createStatement();
    //performing insert operation :)
    lst.toArray(ss);

    for(String ins:ss){
        double d=Double.parseDouble(ins);
        String insert = "INSERT INTO storage_other(total) VALUES ("+ins+");";
        //String insert = "INSERT INTO storage_other(amount) VALUES ("+ls+");";
        // Here we are going to Execute the query
        stmt.executeUpdate(insert);
        System.out.print("Done Seccesfully :)");
    }
}

Upvotes: 0

Views: 5277

Answers (3)

duffymo
duffymo

Reputation: 308763

Here's how I'd recommend you do it. A few thoughts:

  1. Give the Connection to the object. That method should do one thing: the INSERT.
  2. Should be transactional.
  3. Should clean up resources when done.
  4. Tell users to provide a List of Doubles if that is what the amounts are. Don't parse; let clients do that.

Here is complete code:

public class Inserter {

    private static final String INSERT_SQL = "INSERT INTO storage_other(total) VALUES(?))";

    private Connection connection;

    public Inserter(Connection connection) {
        this.connection = connection;
    }

    public int settingAmount(List<Double> amounts)throws SQLException {
        int numAmountsInserted = 0;
        PreparedStatement ps = null;
        this.connection.setAutoCommit(false);
        try {
            ps = this.connection.prepareStatement(INSERT_SQL);
            for(Double amount : amounts) {
                ps.setDouble(1, amount);
                numAmountsInserted += ps.executeUpdate();
            }
            this.connection.commit();
        } catch (SQLException e) {
            DatabaseUtils.rollback(this.connection);
            throw e;
        } finally {
            DatabaseUtils.close(ps);
            this.connection.setAutoCommit(true);
        }
        return numAmountsInserted;
    }
}

Upvotes: 1

greedybuddha
greedybuddha

Reputation: 7507

What you want to do is use batches. Batches allow you to send a list of statements to be done at the same time.

Here is an example

connection.setAutoCommit(false);
PreparedStatement ps = connection.prepareStatement("INSERT INTO storage_other(total) VALUES (?)");
for (String ins:ss){
    ps.setObject(1, d);
    ps.addBatch();
}
ps.executeBatch();
connection.commit();

This will be significantly faster than individual inserts on any table with indexes.

Upvotes: 8

Panagiotis
Panagiotis

Reputation: 309

This is a method I used in order to insert some data in an Oracle SQL database.

     private boolean submit(Connection con, String query){
             try {
                 PreparedStatement preStatement;
                 preStatement = con.prepareStatement(query);
                 preStatement.executeQuery();
                 preStatement.close();
                 return true;
             }catch (Exception e) {
                 System.out.println("Exception cought, updating log.");
                 return false;
             }
         }

You can prepare your insert statement and call this function to perform the action. Call it using your connection object and the query. It shall return true on completion false in case something goes wrong. If you want to log any errors, use e.getMessage() to get the error message as a String in the exception block.

As mentioned in the comments, try to use the PreparedStatement object to avoid SQL Injection attacks and also try to trim any ' you might have in your data.

Upvotes: 2

Related Questions