Inserting multiple rows into database?

I'm trying to insert multiple rows into a database using JAVA code similar to the following:


//[codetype] [int] NULL ,
//[code] [int] NULL ,
//[description] [varchar] (50)  NULL ,
///[active] [int] NULL
              
stmt.executeUpdate("INSERT INTO codeList ("
+ "codetype, code, description, active"            
+ ") VALUES " 
+ "(" + 1 + ", " + 0 + ", " +  "'AAAA'" + ", " + 1 + "), "
+ "(" + 1 + ", " + 5 + ", " + "'BBBB'" + ", " + 2 + "), "
+ "(" + 1 + ", " + 6 + ", " + "'CCCC'" + ", " + 3 + "), "
+ "(" + 1 + ", " + 7 + ", " + "'DDDD'" + ", " +  4 + ") "
);

However, i keep getting: Incorrect syntax near ','

I've seen standard SQL code that let's me do this, like:

INSERT INTO customers(cust_name,
   cust_address,
   cust_city,
   cust_state,
   cust_zip,
   cust_country)
VALUES(
        'Pep E. LaPew',
        '100 Main Street',
        'Los Angeles',
        'CA',
        '90046',
        'USA'
     ),
      (
        'M. Martian',
        '42 Galaxy Way',
        'New York',
        'NY',
        '11213',
        'USA'
   );

However, I can't seem to get the correct code syntax using java.
nickl1Asked:
Who is Participating?
 
lyonstCommented:
// Insert1.java:  Demonstrates how to INSERT data into an SQL
//               database using Java JDBC.
//
// Copyright 1998 Developer's Daily (http://www.DevDaily.com). All rights reserved.

import java.sql.*;

class Insert1 {
 
    public static void main (String[] args) {
        try {
            String url = "jdbc:msql://200.210.220.1:1114/Demo";
            Connection conn = DriverManager.getConnection(url,"","");
            Statement st = conn.createStatement();
            st.executeUpdate("INSERT INTO Customers " +
                "VALUES (1001, 'Simpson', 'Mr.', 'Springfield', 2001)");
            st.executeUpdate("INSERT INTO Customers " +
                "VALUES (1002, 'McBeal', 'Ms.', 'Boston', 2004)");
            st.executeUpdate("INSERT INTO Customers " +
                "VALUES (1003, 'Flinstone', 'Mr.', 'Bedrock', 2003)");
            st.executeUpdate("INSERT INTO Customers " +
                "VALUES (1004, 'Cramden', 'Mr.', 'New York', 2001)");

            conn.close();
        } catch (Exception e) {
            System.err.println("Got an exception! ");
            System.err.println(e.getMessage());
        }
 
    }

}
0
 
CEHJCommented:
Use batching with a PreparedStatement

http://www.exampledepot.com/egs/java.sql/BatchUpdate.html
0
 
nickl1Author Commented:
I appreciate your input, but isn't there an easier way to accompish this?
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
CEHJCommented:
It's a lot easier than what you're doing now ;-) and is the proper way to do it
0
 
nishayrCommented:
A better way to do it is to use prepared statements... It is not that difficult, and has performance benefits on the server side (no need to reparse the query when we use bind parameters)

Also, it allows for better handling of the datatypes. We use the setString method below, but you can use a variety of different types as well. Especially useful for handling dates and nulls...

String sql = "insert into Customers (cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country) "
             + "values (?, ?, ?, ?, ?, ?)";
PreparedStatement stmt = conn.prepareStatement(sql);

stmt.setString(1, "Pep E. LaPew");
stmt.setString(2, "100 Main Street");
stmt.setString(3, "Los Angeles");
stmt.setString(4, "CA");
stmt.setString(5, "90046");
stmt.setString(6, "USA");
stmt.execute();

stmt.setString(1, "M. Martian");
stmt.setString(2, "42 Galaxy Way);
stmt.setString(3, "New York");
stmt.setString(4, "NY");
stmt.setString(5, "11213");
stmt.setString(6, "USA");
stmt.execute();


Thanks,
Nish
0
 
nickl1Author Commented:
In the PreparedStatement (http://www.exampledepot.com/egs/java.sql/BatchUpdate.html), in the following code:

 // Insert 10 rows of data
for (int i=0; i<10; i++) {
stmt.setString(1, ""+i);
pstmt.setString(1, ""+i);
pstmt.addBatch();
}

what is the "stmt.setString(1, ""+i);" doing?

Thanks
0
 
hoomanvCommented:
> pstmt.setString(1, ""+i);
setXXX supplies values to be used in place of the question mark placeholders
http://java.sun.com/docs/books/tutorial/jdbc/basics/prepared.html
0
 
nickl1Author Commented:
I understand the basic functionality, but what are the quotation arks doing?

Thanks
0
 
nickl1Author Commented:
I am trying to integrate the suggested code and came up with the code below. However, i'm still getting an error within "processUpdateCounts" and receive the following message:

Syntax error "(", ; expected
Syntax error ")", ; expected

I seriously appreciate the help on this one!




package samples.sampleCode;


import java.net.URL;
import java.sql.*;
import java.io.PrintWriter;

class SqlBatchInsert_Sample
{

      public static void main (String args[])
   {
/* ----------------------BEGINNING OF MAIN ------------------------------ */            
               
              try
            {
               // Load driver's class, inilialize, register with DriverManager             
               Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
            }
            catch (ClassNotFoundException e)
            {
               System.out.println("Unable to load driver class");
               return;
            }
            
/* ------------------------------------------------------------ */

            ResultSet rs = null;

            try
            {
               //
               // Call DriverManager's methods (all are static)
               //

               // To print log on sysout
               //DriverManager.setLogStream(System.out); //JDBC 1.x driver
               DriverManager.setLogWriter(new PrintWriter(System.out) );

               // Connect to database.  DriverManager loads each registered driver
               // in turn until one can handle the database URL format
               //Connection con = DriverManager.getConnection(               
              
               Connection con = DriverManager.getConnection(
               //"jdbc:mysql://localhost:3306/civil2","","");
               "jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=myApplication;User=X;Password=X");

               // Check for warnings generated during connect
               areWarnings(con.getWarnings() );

              // Create a Statement object for submitting SQL commands to driver
               Statement stmt = con.createStatement();
              
              // String sql;

/* ---------------------------------------------------------------------------------------------- */
/* ---------------------------------------------------------------------------------------------- */
              
              // Determining If a Database Supports Batching  
              try {
                  DatabaseMetaData dmd = con.getMetaData();
                   if (dmd.supportsBatchUpdates()) {
                       // Batching is supported
                         /*--------------------------------------------------------------------------*/
               try {
                   // Disable auto-commit
                   connection.setAutoCommit(false);
              
                   // Create a prepared statement
                   String sql = "INSERT INTO my_table VALUES(?)";
                   PreparedStatement pstmt = connection.prepareStatement(sql);
              
                   // Insert 10 rows of data
                   for (int i=0; i<10; i++) {
                       pstmt.setString(1, ""+i);
                       pstmt.addBatch();
                   }
              
                   // Execute the batch
                   int [] updateCounts = pstmt.executeBatch();
              
                   // All statements were successfully executed.
                   // updateCounts contains one element for each batched statement.
                   // updateCounts[i] contains the number of rows affected by that statement.
                   processUpdateCounts(updateCounts);
              
                   // Since there were no errors, commit
                   connection.commit();
               } catch (BatchUpdateException e) {
                   // Not all of the statements were successfully executed
                   int[] updateCounts = e.getUpdateCounts();
              
                   // Some databases will continue to execute after one fails.
                   // If so, updateCounts.length will equal the number of batched statements.
                   // If not, updateCounts.length will equal the number of successfully executed statements
                   processUpdateCounts(updateCounts);
              
                   // Either commit the successfully executed statements or rollback the entire batch
                   connection.rollback();
               } catch (SQLException e) {
               }
              
               public static void processUpdateCounts(int[] updateCounts) {
                   for (int i=0; i<updateCounts.length; i++) {
                       if (updateCounts[i] >= 0) {
                           // Successfully executed; the number represents number of affected rows
                       } else if (updateCounts[i] == Statement.SUCCESS_NO_INFO) {
                           // Successfully executed; number of affected rows not available
                       } else if (updateCounts[i] == Statement.EXECUTE_FAILED) {
                           // Failed to execute
                       }
                   }
               }

                         /*--------------------------------------------------------------------------*/
                         
                   } else {
                       // Batching is not supported
                   }
               } catch (SQLException e) {
               }
 
              
              
              
/* ---------------------------------------------------------------------------------------------- */
/* ---------------------------------------------------------------------------------------------- */
              
              
            }
            catch(SQLException ex){printException(ex); }
         }


        

/* -------------------areWarnings--------------------- */
        
        
         // Check for warnings
         private static boolean areWarnings(SQLWarning warn) throws SQLException
         {
            boolean rc = false;

            // Multiple warning smay have been chained to the passed SQLWarning object
            // Or, null may have been passed if there were no warnings
            if(warn != null)
            {
               System.out.println("\n *** Warning ***\n");
               rc = true;
               while(warn != null)
               {
                  System.out.println("SQLState: " + warn.getSQLState() );
                  System.out.println("Message:  " + warn.getMessage() );
                  System.out.println("Vendor:   " + warn.getErrorCode ());
                  System.out.println ("");
                  warn = warn.getNextWarning ();
               }
            }
            return rc;
         }    
        
        
/* --------------------printException---------------------- */
        
        
         //  Print all chained exceptions //
         public static void printException(SQLException ex)
         {
            // Multiple error objects may be chained together
            System.out.println("\n*** SQLException caught ***\n");
            while(ex != null)
            {
               System.out.println("SQLState: " + ex.getSQLState() );
               System.out.println("Message:  " + ex.getMessage() );
               System.out.println("Vendor:   " + ex.getErrorCode ());
               ex.printStackTrace(System.out);
               System.out.println ("");
               // Get next error object in chain. null if none.
               ex = ex.getNextException();
            }
         }
/* --------------------------------------------------- */

}
0
 
hoomanvCommented:
The code has obvious syntax error with the braces
0
 
nishayrCommented:
Hey there,

Even if you get your braces fixed, the program will not work. The table structure from the example had only 1 column, while yours has 6. The data goiing into the table in the example is just sequential numbers.

Your case is significantly different. If you are just hard coding in a few pieces of data, then I suggest that you replace everything between the double line comments with:

String sql = "insert into Customers (cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country) "
             + "values (?, ?, ?, ?, ?, ?)";
PreparedStatement stmt = conn.prepareStatement(sql);

stmt.setString(1, "Pep E. LaPew");
stmt.setString(2, "100 Main Street");
stmt.setString(3, "Los Angeles");
stmt.setString(4, "CA");
stmt.setString(5, "90046");
stmt.setString(6, "USA");
stmt.execute();

stmt.setString(1, "M. Martian");
stmt.setString(2, "42 Galaxy Way);
stmt.setString(3, "New York");
stmt.setString(4, "NY");
stmt.setString(5, "11213");
stmt.setString(6, "USA");
stmt.execute();


Of course you will have to tidy up the exceptions and things...

Thanks,
Nish
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.