Reading a textfile and writing it to a database in JDBC

ok i've got a problem in that i need to write a program in java which can read data from a textfile and then break it down into sections (the sections are separated by a comma in the textfile) then to take the sections and input them into a database using the JDBC.

Im not sure of the easiest was to do this ??

Any help would be much appreciated
dave876279Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
zzynxConnect With a Mentor Software engineerCommented:
public class insertJDBC
{
     public static void main(String args[])
     {
     String input;
     boolean more = true;
     
     try
     {
          BufferedReader in = new BufferedReader(new FileReader("jdbc.txt"));
          String str;
         
          //while string being read is not null print out string
          int count = 1;
          while ((str = in.readLine()) != null)
          {
               System.out.println("======= Record nr. "+ count);
               count++;
               String items[] = str.split(",");
               for (int i=0; i<items.size(); i++)
                  System.out.println("field nr. " + i + ": " + items[i]);
          }
               
                //close buffered reader
                    in.close();
               }
     
     catch (IOException e)
      {
       System.out.println("Output error");
      }
     }          
}
0
 
zzynxSoftware engineerCommented:
>>..read data from a textfile...
http://javaalmanac.com/egs/java.io/ReadLinesFromFile.html

>> and then break it down into sections (the sections are separated by a comma in the textfile)
Use the String.split() function for that

>>then to take the sections and input them into a database using the JDBC
Dou you have some code already?

According to the EE guidelines, we can't write complete code for you.
But we can help you if you - at least try to write it and - get stuck with it.
0
 
zzynxSoftware engineerCommented:
>> Im not sure of the easiest was to do this ??
What do you have in mind?
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
dave876279Author Commented:
thanks very much for you help, currently ive just wrote some simple code using the link you gave me which reads the textfile and prints it out
now from here i need to use split string to split the text down into sections and then input the sections into a database ??

import java.io.*;
import java.sql.*;

public class insertJDBC
{
      public static void main(String args[])
      {
      String input;
      boolean more = true;
      
      try
      {
            BufferedReader in = new BufferedReader(new FileReader("jdbc.txt"));
            String str;
            
            //while string being read is not null print out string
            while ((str = in.readLine()) != null)
            {
                              System.out.println(str);      
            }
                  
                   //close buffered reader
                        in.close();
                  }
      
      catch (IOException e)
       {
        System.out.println("Output error");
       }
      }             
}
0
 
zzynxSoftware engineerCommented:
What does (one line of) that file contain?
How does it looks like? Could you give an example?
0
 
dave876279Author Commented:
ok the file looks like this:

ID12, 100GB Hard drive, 13, 30, 0
ID132, Keyboard, 5, 10, 1, 05/09/04
1D234, Mouse, 6, 15, 0, 04/10/04

0
 
zzynxSoftware engineerCommented:
So each line corresponds with one database record?
And each item on the line corresponds with a column of the database table (a field of one record)?
Right?
0
 
Mayank SAssociate Director - Product EngineeringCommented:
>> split string to split the text down into sections

On what do you want to split the String? If you're on 1.4, you can use the split () method provided by String, otherwise you can use a StringTokenizer.

http://www.javaalmanac.com/egs/java.util/ParseString.html

Inserting into a DB:

http://www.javaalmanac.com/egs/java.sql/Insert.html

http://www.javaalmanac.com/egs/java.sql/InsertPs.html?l=rel
0
 
dave876279Author Commented:
for some reason it doesnt like the size method in the for loop ??
0
 
zzynxSoftware engineerCommented:
My fault: replace
         size()
by
         length

(I always make mistakes with those two. size is for collections like lists. Sorry :)
0
 
zzynxSoftware engineerCommented:
For the database stuff have a look at http://javaalmanac.com/egs/java.sql/pkg.html
0
 
dave876279Author Commented:
Dont be sorry youre helping me out of a big hole :D

the db stuff i've actually done before connecting to db should be no problem just that im not all that sure on inserting the split strings into the db itself ?? Do you INSERT INTO items VALUES (items[i]) ??? below is a bit of code i did to run a select query i assumei can use most of that again ??                                                                              

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

                              String protocol="jdbc:odbc:";
                              String url=protocol+"stockJDBC";

                              Connection conn = DriverManager.
                              getConnection(url,"","");
                              Statement stmt =
                              conn.createStatement();

                              /*create query to search for records in database where the reorder level is greater than stock level
                                and the item hasnt been reordered*/      
                              String query =
                              "SELECT * FROM Items WHERE reorderLevel > stockLevel AND isReordered = false ";
0
 
dave876279Author Commented:
bumping the points up as my above method dont work :(
0
 
copyPasteGhostCommented:
what's with it? you can't connect to the database?
Ghost
0
 
dave876279Author Commented:
No i can connect to the database fine but trying to input the split string into the database causes an exception that the sql number of fields isnt same as number im trying to insert
0
 
zzynxSoftware engineerCommented:
>> that the sql number of fields isnt same as number im trying to insert
Oh... Well, that's really database stuff.
The correct SQL call looks like:

                INSERT INTO yourTable VALUES (item[0], item[1], item[2], item[3])

If you really want to be sure that the right value comes into the right field you can specify them explicitely
See http://www.w3schools.com/sql/sql_insert.asp.

So, building up your query will be something like this (mind the needed quotes for string values!):

String query = "INSERT INTO items VALUES (' " + item[0] + " ', ' " + item[1] + " ', ' " + item[2] + " ', ' " + item[3] + " ' ) ";

Success.
0
 
dave876279Author Commented:
see my problem is that i need it to insert 10-15 records at once and its a bit long winded putting each number of the array in
I was hoping to be able to loop it some how ??
0
 
copyPasteGhostConnect With a Mentor Commented:
String sqlQuery = "INSERT INTO items VALUES (" ;

for (int i=0;i<AMOUNT OF ITEMS;i++) {
sqlQuery  += "' " + item[i] + " ',";
}
sqlQuery += ")";

that is to build your query string

need anything else?
Cheers,
Ghost
0
 
zzynxSoftware engineerCommented:
>> I  was hoping to be able to loop it some how ??
Looping for the records you mean?
That's possible too.
0
 
Mayank SAssociate Director - Product EngineeringCommented:
>> see my problem is that i need it to insert 10-15 records at once

Do you mean 10-15 rows? You can use the executeBatch () method of Statement.
0
 
zzynxSoftware engineerCommented:
Thanks for accepting.

Since you gave a B-grade I conclude that you feel like our comments could be better.
Could you in the future please guide us more frequently/detailed about what exactly you want?
Thanks. CU.
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.