Read data from txt file and insert it into database using java

Question

I want to read data from a txt file and insert it into my database, but my code only insert 16 lines and stops. The txt file has 200 lines. I don't know what I'm doing wrong. Any help will be appreciated. Thanks in advance

Here is my code:

public static void main(String[] args) throws ClassNotFoundException, SQLException
{
    String date;
    String heure;
    String parametre;
    String valeur;
    PreparedStatement ps = null;
    Connection con = null;
    ResultSet rs = null;

    try
    {
        BufferedReader br = new BufferedReader(new FileReader("Data_Station_1.txt"));
        String username = "postgres";
        String pwd = "elghorrim";
        String connurl = "jdbc:postgresql://localhost:5432/BDS_CSF_AuqliteEau";

        con = DriverManager.getConnection(connurl, username, pwd);
        Class.forName("org.postgresql.Driver");

        String line = null;
        while ((line = br.readLine()) != null)
        {
            String tmp[] = line.split(",");
            date = tmp[0];
            heure = tmp[1];
            parametre = tmp[2];
            valeur = tmp[3];

            System.out.println(date + "\t" + heure + "\t" + parametre + "\t" + valeur);
            String sql =
                    "INSERT INTO resultat (date_resultat,valeur,code_pc,code_parametre,heure_resultat) values ('"
                            + date + "','" + valeur + "','1','" + parametre + "','" + heure +
                            "')";

            ps = con.prepareStatement(sql);
            ps.executeUpdate();
        }

        br.close();
        con.close();
        ps.close();

    }
    catch (IOException e)
    {
        e.printStackTrace();
    }
}

Read how to (re)use prepared statement (docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html) — agad
– agad, Commented May 11, 2015 at 14:26
check the 17th line, also check the return carriage & encoding, might be an accented character — flafoux
– flafoux, Commented May 11, 2015 at 14:27
thanks @flafoux you are right, the 17th line doesn't contains any data but the 18th does. so i think I should change the 'while' condition ! do you have an idea what should I do instead of [while((line=br.readLine()) != null)] thank you — EL GHORRIM Sara
– EL GHORRIM Sara, Commented May 11, 2015 at 14:38
Are you getting a NullPointerException or ArrayIndexOutOfBoundsException or any other error when it stops? An empty line counts as a \n (cr/lf) not a null. — Jim
– Jim, Commented May 13, 2015 at 20:30

flafoux · Accepted Answer · 2015-05-11 14:59:37Z

1

First problem was the line 17 containing nothing

Now for the second one (managing empty lines) :

while((line=br.readLine())){

//Make sure the line is not null, not empty, and contains 3 comma char
if (line != null && !line.equals("") && line.matches('.*[,].*[,].*[,].*')) {
    String tmp[]=line.split(",");
    date=tmp[0];
    heure=tmp[1];
    parametre=tmp[2];
    valeur=tmp[3];

// do the sql query

} else {
    // manage where the line doesn't fit the pattern
}

edited May 11, 2015 at 14:59

answered May 11, 2015 at 14:52

flafoux

2,1401 gold badge14 silver badges13 bronze badges

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

Comments

Igor · Accepted Answer · 2015-05-11 15:48:56Z

0

You code looks like workable, not the best way but should work.

You are not doing any kind of validation on the file and my guess is that there is a problem with the file. I can not have sure because you did not sent the file.

Some suggestions:

Create a connection class, so you do not need you database user, password, etc... every time you need to do something database related.
Connection pool are also great.

Cheers !!!

//update with example:

     //create a class that will open your connection and do other sql stuff for you 
 //so you busineess rules will be more readable  
 SQLHelper sqlHelper = new SQLHelper();
 sqlHelper.startCON();

String line = null;
while((line=br.readLine()) != null){
String[] tmp=line.split(",");

//I will check here the size of the line readed, I do not know how is a common size so I am using 10 chars, 
//change it as your needs, you also need to validate is the split worked and found your 4 fields
if(line.length()>9 && tmp.length==4)
{
    sqlHelper.toResultat(tmp);
}

edited May 11, 2015 at 15:48

answered May 11, 2015 at 14:46

Igor

911 silver badge12 bronze badges

2 Comments

EL GHORRIM Sara Over a year ago

thank you so much for your help. Actually, you're right the problem is with the file because the 17th line is empty. what should I do so that i can skip empty lines ? thanks is advance

Igor Over a year ago

As I told you should add validation on the string before. You can check the string length, I also saw that you split the line, do you expect always to have 4 fields ? If yes validate it. I just edited my answer with a example and if you like it please do not forget to up vote.

Collectives™ on Stack Overflow

Read data from txt file and insert it into database using java

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related