• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 782
  • Last Modified:

insert string array to database

Hi Expert,
I'm doing the project that need to download data from web page as a string array and use these data insert to database.  Any help will be great.
Here is my example code
-----------
try
         {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            Connection dbConn = DriverManager.getConnection("jdbc:odbc:BMAC");
            if (dbConn == null)  
            {
               System.out.println("Connection to database failed");
            }
            else
            {                        
            try
         {
            InetTemplate obj = new Inet () ;
            String strContent = obj.getURL("http://my_webpage");
            String strData = obj.getData(strContent);
            BufferedReader reader = new BufferedReader(new StringReader(strData));            
            String line;
            int count = 0;
            array = new String[120];
                  
            while ((line = reader.readLine()) != null)
            {
               array[count] = new String((line)+ "\r\n ");      
               count++;
            }
      }
       catch (Exception e)
            {
               System.out.println(e.getMessage());
               System.exit(0);
            }

     Statement stmt = dbConn.createStatement();
      String      dbSQL = "SELECT * FROM table WHERE col_Name=''";            
            ResultSet rs = stmt.executeQuery(dbSQL);
            if (!rs.next())
            {
                dbSQL = "INSERT INTO table (col_Name) VALUES (";
            for (int i=0;i<array.length;i++)
            {
            dbSQL  += "' " + array[i] + " ',";
            }
            dbSQL += ")";
            stmt.executeUpdate(dbSQL);
            }
              rs.close();
       }
                             
            dbConn.close();
            Statement stmt = null;
                                 
         }
             catch (Exception e)
            {
            }
0
westerntour
Asked:
westerntour
  • 9
  • 5
  • 4
  • +2
3 Solutions
 
objectsCommented:
>    ResultSet rs = stmt.executeQuery(dbSQL);

why do you do a select if you want to *insert* the strings?

sounds like u should be instead looping thru your array inserting a row for each string

for (int i=0; i<array.length; i++) {
   // insert array[i]


}

A PreparedSTatement could be used for this
0
 
InNoCenT_Ch1ldCommented:
I assume that your select statement is use to find out if the data is already in the database before you insert?
0
 
InNoCenT_Ch1ldCommented:
If you are.. try this script (provided you know how many fields the table have and the field name). Just replace the  'DATA1', 'DATA2', 'DATA3'.... with the value in the array, and field1, field2... with the field name in the table.

insert into table_1
      select top 1
      'DATA1', 'DATA2', 'DATA3'
      from table_1
      where
      not exists (
            select * from table_1
            where      field1 = 'DATA1'
            and      field2 = 'DATA2'
            and       field3 = 'DATA3')

using PreparedStatement:

String query =
        "insert into table_1 " +
      "select top 1 " +
      "?,?,? " +
      "from table_1 " +
      "where  " +
      "not exists ( " +
            "select * from table_1 " +
            "where      field1 = ? " +
            "and      field2 = ? " +
            "and       field3 = ?)";

PreparedStatement stmt = conn.prepareStatement(query);

stmt.setString(1, array1[0]);
stmt.setString(2, array1[1]);
//and so on

stmt.executeUpdate();
-----------------------------------

hope this helps.... if not, just ignore me ;-)


0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
InNoCenT_Ch1ldCommented:
note that the 'top 1' must be there, else it will insert more than 1.. (the number of insert = the total row you have in the table, i guess)
0
 
westerntourAuthor Commented:
Note that before I insert array to table, all records were deleted (table is empty). String array have data like this:

String [] array = {"SAN 2012","LOS 2005","SFO 2010",...} (this array has about 100 list that are the same lenght)

I need to insert these data into 2 column of the database.
0
 
westerntourAuthor Commented:
In general, I have to download the data on web page and populate the database with that information. What should I start?
Web page as like this:

SAN    1012
LOS    2005
SFO    2010

and so on (100 lines)
0
 
objectsCommented:
Doesn't sound like you need to do an insert then.

batch update may be the way to go

http://javaalmanac.com/egs/java.sql/BatchUpdate.html
0
 
InNoCenT_Ch1ldCommented:
PreparedStatement stmt = conn.prepareStatement(query);

for(int i = 0; i < your_array.length; i++){
    String str = your_array[i];
    stmt.setString(1, str.substring(0, str.indexOf(' '));
    stmt.setString(2, str.substring(str.indexOf(' '));

    stmt.addBatch();
}
stmt.executeBatch();
0
 
westerntourAuthor Commented:
it's error at:
stmt.setString(1, str.substring(0, str.indexOf(' '));
      ^
 stmt.setString(2, str.substring(str.indexOf(' '));
       ^
0
 
InNoCenT_Ch1ldCommented:
what's the error msg?
0
 
objectsCommented:
u can avaoid using substring()

for(int i = 0; i < your_array.length; i++){
    String[] str = your_array[i].split(" ");
    stmt.setString(1, str[0]);
    stmt.setString(2, str[1]);

    stmt.addBatch();
}
stmt.executeBatch();
0
 
westerntourAuthor Commented:
Not the error msg, it can't compile
0
 
InNoCenT_Ch1ldCommented:
when u compile the source code, it will shows error message, we need to know that.

btw, are u using any IDE or just notepad?

and maybe you can post your code:
the query and the loop part
0
 
Mayank SAssociate Director - Product EngineeringCommented:
>> stmt.setString(1, str.substring(0, str.indexOf(' '));

You forgot an ) at the end:

stmt.setString ( 1, str.substring ( 0, str.indexOf ( ' ' ) ) ) ;
0
 
Mayank SAssociate Director - Product EngineeringCommented:
One ) for indexOf (), one for substring (), one for setString ()
0
 
InNoCenT_Ch1ldCommented:
argh!!! my mistake..
0
 
CEHJCommented:
>>
it's error at:
stmt.setString(1, str.substring(0, str.indexOf(' '));
>>

Your statement should be something like

"insert into table x (first_col, second_col) values (?,?)";

>>stmt.setString(2, str.substring(str.indexOf(' '));

should be

stmt.setString(2, str.substring(str.indexOf(' ') + 1);


0
 
Mayank SAssociate Director - Product EngineeringCommented:
>> argh!!! my mistake..

2 pairs of eyes are always better than 1 ;-)
0
 
InNoCenT_Ch1ldCommented:
not to mention my pair = old eyes...
0
 
westerntourAuthor Commented:
>>"insert into table x (first_col, second_col) values (?,?)";
That means I put the question mark (?) or the real value in value(?,?).
0
 
objectsCommented:
the question mark
0
 
InNoCenT_Ch1ldCommented:
you put the REAL value using setString (in your case, if they are String)
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 9
  • 5
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now