Solved

Reading a textfile and writing it to a database in JDBC

Posted on 2004-05-03
22
493 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

 
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

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

Java contains several comparison operators (e.g., <, <=, >, >=, ==, !=) that allow you to compare primitive values. However, these operators cannot be used to compare the contents of objects. Interface Comparable is used to allow objects of a cl…
Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
Viewers will learn about the different types of variables in Java and how to declare them. Decide the type of variable desired: Put the keyword corresponding to the type of variable in front of the variable name: Use the equal sign to assign a v…
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.

728 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