CODEWITHSUNDEEP
javamysql
prerna
prerna

Reputation: 1

read text file and insert output into mysql table

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

Upvotes: 0

Views: 600

Answers (3)

Abdelhak
Abdelhak

Reputation: 8387

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();

Upvotes: 1

Jeet
Jeet

Reputation: 248

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.

Upvotes: 0

FallAndLearn
FallAndLearn

Reputation: 4135

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];

Upvotes: 0

Related Questions