Insertion to oracle DB from txt file using java

Question

I am trying to fetch data from a file and insert it to a database using java. Following is my java code:

package edgelist;
import java.io.*;
import java.util.*;
import java.lang.Math.*;
import java.sql.*;

public class Edgelist {
    private static int getRandomNumberInRange(int min, int max){
        if (min >= max) {
            throw new IllegalArgumentException("max must be greater than min");
        }
        Random r = new Random();
        return r.nextInt((max - min) + 1) + min;
    }

    public static void main(String args[]) throws Exception {
        FileInputStream f=null;
        int i,c=1;
        String num="";
        Class.forName("oracle.jdbc.driver.OracleDriver");
        Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE",
                                                     "project",
                                                     "project");
        Statement stmt=con.createStatement();
        String s=null,ver1=null,ver2=null;
        int serial=0,type;
        //PreparedStatement ps=con.prepareStatement(s);

        try {
            f=new FileInputStream("F:\\College Project\\dolphin.txt");
            do {
                i=f.read();
                if(i!=-1) {
                    if((char) i !=' ' && (char) i !='\n' && (char) i !='\r') {
                        num=num+(char) i;
                    } else {
                        if(!num.matches("")) {
                            if((char) i == ' ') {
                                //System.out.println("Random:"+getRandomNumberInRange(1, 10)+" "+num);
                                serial=++c;
                                ver1=num;
                            } else if(!((char) i >= '0') && ((char) i <= '9')) {
                                ver2=num;
                            }
                        }
                        num="";
                        type=getRandomNumberInRange(1, 10);
                        s="insert into edges(serial_no, vertex1, vertex2, edge_type)"
                           +"values("+serial+", '"+ver1+"', '"+ver2+"', "+type+")";
                        stmt.executeQuery(s);
                    }

                }
            } while(i!=-1);
        } catch(FileNotFoundException e) {
            System.out.println(e);
        }
        catch(IOException e) {
            System.out.println(e);
        } finally  {
            try {
                if(f != null)
                    f.close();
                con.close();
            } catch(IOException e) {
                System.out.println(e);
            }
        }
    }
}

This code is fetching the data from the file correctly but while trying to insert into the DB it gives following error

Exception in thread "main" java.sql.SQLException: ORA-00001: unique constraint (PROJECT.SYS_C004002) violated

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288) at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743) at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:207) at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:946) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1169) at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1273) at edgelist.Edgelist.main(Edgelist.java:77) Java Result: 1

Here is the table edge structure

CREATE TABLE  "EDGES" (
    "SERIAL_NO" NUMBER, 
    "VERTEX1" VARCHAR2(10) NOT NULL ENABLE, 
    "VERTEX2" VARCHAR2(10) NOT NULL ENABLE, 
    "EDGE_TYPE" NUMBER, 
     PRIMARY KEY ("SERIAL_NO") ENABLE, 
     FOREIGN KEY ("EDGE_TYPE")
     REFERENCES  "TYPES" ("SERIAL_NO") ENABLE
)

here TYPES table contains values from 1 to 10 and the file dolphin.txt is an edgelist containing following data:

3  8
5  9
6  9
0  10
2  10
5  13
6  13
9  13
0  14
3  14
0  15
14 16
1  17
6  17
9  17
13 17
15 18
1  19
7  19
8  20
16 20
18 20
18 21
17 22
14 24
15 24

Answer 1

That error means that you're trying to insert a row with a key that's already in your table.

You define a primary key on SERIAL_NO, and that field is populated with your serial variable, but the problem is that you don't always perform serial=++c;, because it's inside an if statement.

This means that you can insert the same serial value more than once, hence the error you're getting.

Answer 2

After editing my logic here is the code that works perfectly:

import java.io.*;
import java.util.*;
import java.lang.Math.*;
import java.sql.*;

/**
 *
 * @author Soumen
 */
public class Edgelist {

    private static int getRandomNumberInRange(int min, int max)
    {

        if (min >= max) {
            throw new IllegalArgumentException("max must be greater than min");
        }

        Random r = new Random();
        return r.nextInt((max - min) + 1) + min;
    }
  public static void main(String args[]) throws Exception
    {

        FileInputStream f=null;
        int i,c=0,v1_chck=0,v2_chck=0;
        String num="";

            Class.forName("oracle.jdbc.driver.OracleDriver");
            Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","project","project");
            Statement stmt=con.createStatement();
            String s=null,ver1=null,ver2=null;
            int serial=0,type;




        try
        {
            f=new FileInputStream("F:\\College Project\\dolphin.txt");
            do
            {
                i=f.read();
                if(i!=-1)
                {
                    if((char) i !=' ' && (char) i !='\n' && (char) i !='\r')
                    {
                        num=num+(char) i;
                    }
                    else
                    {
                        if((char) i == ' ') 
                        {
                            ver1=num;
                            v1_chck=1;
                            num="";
                        }
                        else
                        {
                            if((char) i == '\n')
                            {
                                ver2=num;
                                v2_chck=1;
                                num="";
                            }
                        }
                    }
                    if(v1_chck==1 && v2_chck==1)
                    {
                        ++serial;
                        type=getRandomNumberInRange(1, 10);

                        s="insert into edges(serial_no, vertex1, vertex2, edge_type)"
                          +"values("+serial+", '"+ver1+"', '"+ver2+"', "+type+")";
                        stmt.executeQuery(s);


                        v1_chck=0;
                        v2_chck=0;
                    }

                }
            }while(i!=-1);
        }
        catch(FileNotFoundException e)
        {
            System.out.println(e);
        }
        catch(IOException e)
        {
            System.out.println(e);
        }
        finally 
        {
            try
            {
                if(f != null)
                    f.close();
                con.commit();
                con.close();
            }
            catch(IOException e)
            {
                System.out.println(e);
            }
        }


    }
}

you can also use preparedStatement()