Solved

Reading a textfile and writing it to a database in JDBC

Posted on 2004-05-03
22
488 Views
Last Modified: 2010-04-16
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
0
Comment
Question by:dave876279
  • 10
  • 7
  • 2
  • +1
22 Comments
 
LVL 37

Expert Comment

by:zzynx
ID: 10977397
>>..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
 
LVL 37

Expert Comment

by:zzynx
ID: 10977407
>> Im not sure of the easiest was to do this ??
What do you have in mind?
0
 

Author Comment

by:dave876279
ID: 10977963
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
 
LVL 37

Expert Comment

by:zzynx
ID: 10978244
What does (one line of) that file contain?
How does it looks like? Could you give an example?
0
 

Author Comment

by:dave876279
ID: 10978281
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
 
LVL 37

Expert Comment

by:zzynx
ID: 10978332
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
 
LVL 30

Expert Comment

by:mayankeagle
ID: 10978336
>> 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
 
LVL 37

Accepted Solution

by:
zzynx earned 150 total points
ID: 10978432
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
 

Author Comment

by:dave876279
ID: 10978524
for some reason it doesnt like the size method in the for loop ??
0
 
LVL 37

Expert Comment

by:zzynx
ID: 10978582
My fault: replace
         size()
by
         length

(I always make mistakes with those two. size is for collections like lists. Sorry :)
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 37

Expert Comment

by:zzynx
ID: 10978603
For the database stuff have a look at http://javaalmanac.com/egs/java.sql/pkg.html
0
 

Author Comment

by:dave876279
ID: 10978648
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
 

Author Comment

by:dave876279
ID: 10979482
bumping the points up as my above method dont work :(
0
 
LVL 13

Expert Comment

by:copyPasteGhost
ID: 10980177
what's with it? you can't connect to the database?
Ghost
0
 

Author Comment

by:dave876279
ID: 10980200
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
 
LVL 37

Expert Comment

by:zzynx
ID: 10981045
>> 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
 

Author Comment

by:dave876279
ID: 10981896
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
 
LVL 13

Assisted Solution

by:copyPasteGhost
copyPasteGhost earned 50 total points
ID: 10982159
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
 
LVL 37

Expert Comment

by:zzynx
ID: 10982323
>> I  was hoping to be able to loop it some how ??
Looping for the records you mean?
That's possible too.
0
 
LVL 30

Expert Comment

by:mayankeagle
ID: 10983569
>> 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
 
LVL 37

Expert Comment

by:zzynx
ID: 11039692
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

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

After being asked a question last year, I went into one of my moods where I did some research and code just for the fun and learning of it all.  Subsequently, from this journey, I put together this article on "Range Searching Using Visual Basic.NET …
Java functions are among the best things for programmers to work with as Java sites can be very easy to read and prepare. Java especially simplifies many processes in the coding industry as it helps integrate many forms of technology and different d…
Viewers learn how to read error messages and identify possible mistakes that could cause hours of frustration. Coding is as much about debugging your code as it is about writing it. Define Error Message: Line Numbers: Type of Error: Break Down…
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:

708 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

17 Experts available now in Live!

Get 1:1 Help Now