Solved

Reading a textfile and writing it to a database in JDBC

Posted on 2004-05-03
22
489 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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:Mayank S
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
 
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:Mayank S
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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction This article is the first of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article explains our test automation goals. Then rationale is given for the tools we use to a…
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 about the “for” loop and how it works in Java. By comparing it to the while loop learned before, viewers can make the transition easily. You will learn about the formatting of the for loop as we write a program that prints even numbers…
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.

810 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