read text file and insert output into mysql table

Question

i want to read the txt file content and insert that into my sql table.

I did this by coding but it is showing some error. Also tried by query but it is showing null in the table.

query -

INSERT INTO patent_uspto_tmp (pinv) VALUES (LOAD_FILE('/home/gaurav/Documents/pinv.txt'));

code -

    try{
        String arr[]=null;
    String outputFile = "/home/gaurav/Documents/pinv.txt";

    FileReader fileReader = new FileReader(outputFile);
    BufferedReader bufferedReader = new BufferedReader(fileReader);
    String inputLine;
    List<String> lineList = new ArrayList<String>();
    while ((inputLine = bufferedReader.readLine()) != null) {
        lineList.add(inputLine);
    }
    fileReader.close();

    Class.forName("com.mysql.jdbc.Driver");  

    Connection con=DriverManager.getConnection(  
    "jdbc:mysql://localhost:3306/patent","root","india2000");  
    for (int i = 1;i<outputFile.length();i++)
    {
        arr[i]=outputFile.valueOf(i);

         PreparedStatement ps = (PreparedStatement) con.prepareStatement("insert into patent_uspto_tmp (pinv) values(?)");
            ps.setString(1,arr[i]); //  set the param here with the some value
            ps.executeUpdate();
            con.commit();
            con.close(); 
    }
    }
    catch(Exception e)
    {
        throw new RuntimeException(e);//System.out.println(e);

    }  
}
    }  

        error - null pointer exception

after running the query it is showing null in the mysql table. And if iam running the code, i am getting like this - <ConnectionProperties> <PropertyCategory name="Connection/Authentication"> <Property name="user" required="No" default="" sortOrder="-2147483647" since="all versions"> The user to connect as </Property> — prerna
– prerna, Commented Mar 2, 2016 at 6:58

Abdelhak · Accepted Answer · 2016-03-02 07:10:30Z

1

Try to use PreparedStatement and executeUpdate() and finally commit() method like this:

    PreparedStatement ps = con.prepareStatement("insert into patent_uspto_tmp (pinv) values(?)");
    ps.setString(1, "value");//  set the param here with the some value
    ps.executeUpdate();
    con.commit();

answered Mar 2, 2016 at 7:10

Abdelhak

8,4154 gold badges25 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

prerna Over a year ago

getting output like-- <ConnectionProperties> <PropertyCategory name="Connection/Authentication"> <Property name="user" required="No" default="" sortOrder="-2147483647" since="all versions"> The user to connect as </Property>

FallAndLearn · Accepted Answer · 2016-03-02 06:59:50Z

0

Why are you using ResultSet for insert query. ResultSet fetches the data from db. You have to use statement.executeUpdate(sql)

Statement stmt=con.createStatement(); 
System.out.println("Inserting records into the table...");
stmt = conn.createStatement();
String sql = "INSERT INTO table" +
                   "VALUES (100, 'Zara', 'Ali', 18)";
stmt.executeUpdate(sql);

For reading a file and then inserting into db. You can read the file row by row and store in into a String arr[], then pass the values in the insert query as arr[i];

answered Mar 2, 2016 at 6:59

FallAndLearn

4,1351 gold badge20 silver badges24 bronze badges

1 Comment

prerna Over a year ago

after doing this i am getting the same as output.

Jeet · Accepted Answer · 2016-03-02 07:06:16Z

0

You are passing dynamic parameters to Statement and not setting any value to that dynamic parameter. Use prepared statement instead and pass dynamic values as parameters to query.

answered Mar 2, 2016 at 7:06

Jeet

2481 silver badge8 bronze badges

Collectives™ on Stack Overflow

read text file and insert output into mysql table

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related