Solved

Inserting multiple rows into database?

Posted on 2006-11-29
11
296 Views
Last Modified: 2009-07-29
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.
0
Comment
Question by:nickl1
  • 4
  • 2
  • 2
  • +2
11 Comments
 
LVL 86

Expert Comment

by:CEHJ
ID: 18041267
Use batching with a PreparedStatement

http://www.exampledepot.com/egs/java.sql/BatchUpdate.html
0
 

Author Comment

by:nickl1
ID: 18041316
I appreciate your input, but isn't there an easier way to accompish this?
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 18041410
It's a lot easier than what you're doing now ;-) and is the proper way to do it
0
 
LVL 15

Accepted Solution

by:
lyonst earned 250 total points
ID: 18041729
// 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
 
LVL 3

Expert Comment

by:nishayr
ID: 18042084
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:nickl1
ID: 18042358
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
 
LVL 14

Expert Comment

by:hoomanv
ID: 18043134
> 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
 

Author Comment

by:nickl1
ID: 18046268
I understand the basic functionality, but what are the quotation arks doing?

Thanks
0
 

Author Comment

by:nickl1
ID: 18047310
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
 
LVL 14

Expert Comment

by:hoomanv
ID: 18047936
The code has obvious syntax error with the braces
0
 
LVL 3

Expert Comment

by:nishayr
ID: 18049975
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

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

INTRODUCTION Working with files is a moderately common task in Java.  For most projects hard coding the file names, using parameters in configuration files, or using command-line arguments is sufficient.   However, when your application has vi…
Java Flight Recorder and Java Mission Control together create a complete tool chain to continuously collect low level and detailed runtime information enabling after-the-fact incident analysis. Java Flight Recorder is a profiling and event collectio…
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now