westerntour
asked on
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.od bc.JdbcOdb cDriver");
Connection dbConn = DriverManager.getConnectio n("jdbc:od bc:BMAC");
if (dbConn == null)
{
System.out.println("Connec tion 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.getMe ssage());
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)
{
}
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.od
Connection dbConn = DriverManager.getConnectio
if (dbConn == null)
{
System.out.println("Connec
}
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.getMe
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)
{
}
I assume that your select statement is use to find out if the data is already in the database before you insert?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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)
ASKER
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.
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.
ASKER
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)
Web page as like this:
SAN 1012
LOS 2005
SFO 2010
and so on (100 lines)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
PreparedStatement stmt = conn.prepareStatement(quer y);
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();
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();
ASKER
it's error at:
stmt.setString(1, str.substring(0, str.indexOf(' '));
^
stmt.setString(2, str.substring(str.indexOf( ' '));
^
stmt.setString(1, str.substring(0, str.indexOf(' '));
^
stmt.setString(2, str.substring(str.indexOf(
^
what's the error msg?
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();
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();
ASKER
Not the error msg, it can't compile
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
btw, are u using any IDE or just notepad?
and maybe you can post your code:
the query and the loop part
>> stmt.setString(1, str.substring(0, str.indexOf(' '));
You forgot an ) at the end:
stmt.setString ( 1, str.substring ( 0, str.indexOf ( ' ' ) ) ) ;
You forgot an ) at the end:
stmt.setString ( 1, str.substring ( 0, str.indexOf ( ' ' ) ) ) ;
One ) for indexOf (), one for substring (), one for setString ()
argh!!! my mistake..
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>> argh!!! my mistake..
2 pairs of eyes are always better than 1 ;-)
2 pairs of eyes are always better than 1 ;-)
not to mention my pair = old eyes...
ASKER
>>"insert into table x (first_col, second_col) values (?,?)";
That means I put the question mark (?) or the real value in value(?,?).
That means I put the question mark (?) or the real value in value(?,?).
the question mark
you put the REAL value using setString (in your case, if they are String)
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