Results 1 to 9 of 9
  1. #1
    nicedad is offline Member
    Join Date
    Nov 2009
    Posts
    4
    Rep Power
    0

    Default Insert text records from text file into a DB

    Hi everyone,

    I actualy need help, i created a class that reads a file which inculds abstructes of scientific researches (assume the file includes 200 abstracts) unfortunately, felled to submit the text into my DB. What I need is that each abstract to be inserted as a individual record in the DB but i could not neither insert the text as a whole nor as records with ID#.

    attached is an example of the file that includes the abstracts.

    appreciate your help

    Java Code:
    import java.io.*;
    import java.sql.*;
    
    public class FileInput {
    
    public static void main(String[] args) {
    Statement statement;
    Connection conn = null; 
    File file = new File("C:\\pubmed_result.txt");
    FileInputStream fis = null;
    BufferedInputStream bis = null;
    DataInputStream dis = null;
    try {
    fis = new FileInputStream(file);
    bis = new BufferedInputStream(fis);
    dis = new DataInputStream(bis);
    while (dis.available() != 0) {
    System.out.println(dis.readLine());
    }
    fis.close();
    bis.close();
    dis.close();
    } catch (FileNotFoundException e) {
    e.printStackTrace();
    } catch (IOException e) {
    e.printStackTrace();
    }
    try
    {
    String userName = "root";
    String password = "";
    String url = "jdbc:mysql://localhost:3306/junkdb";
    Class.forName ("com.mysql.jdbc.Driver").newInstance ();
    conn = DriverManager.getConnection (url, userName, password);
    System.out.println ("Database connection established");
    statement = conn.createStatement(); 
    
    statement.executeUpdate ("INSERT INTO abstructs (ID, abstruct)"
    + " VALUES"
    + "('1', bis)");
    }
    catch (Exception e)
    {
    System.err.println ("Cannot connect to database server");
    }
    finally
    {
    if (conn != null)
    {
    try
    {
    conn.close ();
    System.out.println ("Database connection terminated");
    }
    catch (Exception e) { /* ignore close errors */ }
    }
    }
    }
    }
    Attached Files Attached Files

  2. #2
    Eranga's Avatar
    Eranga is offline Moderator
    Join Date
    Jul 2007
    Location
    Colombo, Sri Lanka
    Posts
    11,371
    Blog Entries
    1
    Rep Power
    20

    Default

    Did you get any error at all?

    If you can clearly read data, and still not written into DB, check very basic things like the query format, data type mismatches and so on. It may boil-down your question upto a certain level.

  3. #3
    travishein's Avatar
    travishein is offline Senior Member
    Join Date
    Sep 2009
    Location
    Canada
    Posts
    684
    Rep Power
    6

    Default

    }
    catch (Exception e)
    {
    System.err.println ("Cannot connect to database server");
    }
    instead of doing just that print cannot connect to database server message have it print out the error message from the sql exception, it could be something else is going wrong like table or column name not found, or there already being a row with id 1 and getting a duplicate key error, no jdbcc driver found in classpath, bad database password, etc
    Java Code:
    }
    catch (Exception e)
    {
    System.err.println ("Database error: " + e);
    e.printStackTrace();
    }

  4. #4
    nicedad is offline Member
    Join Date
    Nov 2009
    Posts
    4
    Rep Power
    0

    Default

    Thanks Eranga thanks travishein for the quick responce.
    However, the program can read the file properly but the problem is in this part:
    Java Code:
    statement.executeUpdate ("INSERT INTO abstructs (ID, abstruct)"
                   + " VALUES"
                   + "('1', bis)");
    Even though when i changed the VALUES to normal values (id, strings) it can read the file (but normaly not inserted into the DB) and insert these values into the DB. Further, I have not any problem with JDBC because it interes the values normlay.

  5. #5
    r035198x is offline Senior Member
    Join Date
    Aug 2009
    Posts
    2,388
    Rep Power
    8

    Default

    Your code doesn't make sense.

    1.) Don't use those classes for reading text files. Use FileReader/BufferedReader combination. Those classes are for reading binary data.
    2.) You do
    Java Code:
    + "('1', bis)");
    That's just plain wrong. You are not going to get the file's contents inserted into the database that way. Think about it. What is contained in your bis? To make matters worse, it's not even your bis which is passed to the database. The whole thing is just a string literal so there is no connection with your bis at all.
    3.) The better approach would be to directly load the file into the database using a database level command e.g LOAD DATA INFILE. If you have to load the data in Java you can still run that command from java using Runtime.exec or similar.
    4.) If you really have to read the lines in Java then you need to use a PreparedStatement (perhaps a batch) so the strings are correctly parsed if they have special characters which means you'll have to read the file first into a collection.

  6. #6
    nicedad is offline Member
    Join Date
    Nov 2009
    Posts
    4
    Rep Power
    0

    Default

    Thanks r035198x for advise,
    Unfortunately, I am totaly got confused because lack of experience in java
    What I am doing right now is research on pubmed website and i really need to insert a huge amount of data (10000 abstracts) from this site into a table in my DB. I already tried the comand LOAD DATA INFILE but it really does not work as I want.
    wat i need is; each abstract in the text file must be inserted as a separate record in the table with its own ID (the table consist of two fields ID and abstract).
    If I do that with handy work it woul be like impossible...

    please advise...

  7. #7
    r035198x is offline Senior Member
    Join Date
    Aug 2009
    Posts
    2,388
    Rep Power
    8

    Default

    You are going to have to be more specific. Did you read the MySQL refmanual for that command? What problems did you get when you tried to use it?

  8. #8
    nicedad is offline Member
    Join Date
    Nov 2009
    Posts
    4
    Rep Power
    0

    Default

    hi,
    first, it work without arror, but put the first line only.
    second, i need every abstract in the file to be indvidual record in the abstruct field in the table. and not the whole file as one record.

    thanks

  9. #9
    r035198x is offline Senior Member
    Join Date
    Aug 2009
    Posts
    2,388
    Rep Power
    8

    Default

    This all depends on what your file looks like. And since it's purely a MySQL question, you should ask in a MySQL forum after going through the relevant section in the refmanual like I suggested.

Similar Threads

  1. Help! - How to insert a new line to a text file
    By matpj in forum New To Java
    Replies: 13
    Last Post: 02-24-2010, 06:28 PM
  2. How to insert new text,box from menu
    By ocean in forum New To Java
    Replies: 10
    Last Post: 10-03-2009, 09:00 PM
  3. how to fetch 20,000 records from excel sheet & insert into access database using Java
    By santosh_tamse in forum JavaServer Pages (JSP) and JSTL
    Replies: 0
    Last Post: 02-22-2009, 11:24 AM
  4. Insert text at abitrary point in a JTextPane
    By angues in forum AWT / Swing
    Replies: 6
    Last Post: 02-02-2009, 05:08 PM
  5. find and replace text from a text file
    By gezzel in forum New To Java
    Replies: 2
    Last Post: 09-19-2008, 05:04 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •