i have 25 million record in my database and i want to retrieve it using java

Question

i have 25 million record in my database and i want to retrieve it using java.my connection terminates after 5000 records. what should i do to keep my connection alive

package spliting;
import java.sql.*;
import java.util.ArrayList;


public class SpaceSpliting {

    public Connection getConnection()
    {
        Connection con=null;
        try
        {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","abc","abc");
        }
        catch(Exception e)
        {
           System.out.println(e);   
        }
        return con;
    }
    public void addingValues()
    {
        int m=0,kk=0;
        try
        {
            Connection con=getConnection();
            Statement st=con.createStatement();
            ResultSet rs=st.executeQuery("select * from owner");
            while(rs.next())
            {

                String model=rs.getString(2);
                System.out.println(model);
                if(model==null)
                {
                    kk++;
                    System.out.println(0+" "+kk);
                    m++;

                }
                else
                {

                String[] amodel=model.split(" ");
                System.out.println(amodel.length);              
                if(amodel.length==1)
                {
                    System.out.println("1");


                    Statement st1=con.createStatement();
                    st1.executeUpdate("update owner set maker='"+amodel[0]+"'where model_desc='"+model+"'");



                kk++;
                System.out.println("records updated"+" "+kk);
                }
                if(amodel.length==2)
                {
                    System.out.println("2");


                    Statement st1=con.createStatement();
                    st1.executeUpdate("update owner set maker='"+amodel[0]+"',model1='"+amodel[1]+"' where model_desc='"+model+"'");



                kk++;
                System.out.println("records updated"+" "+kk);
                }
                if(amodel.length==3)
                {
                    System.out.println("3");


                    Statement st1=con.createStatement();
                    st1.executeUpdate("update owner set maker='"+amodel[0]+"',model1='"+amodel[1]+"',model2='"+amodel[2]+"' where model_desc='"+model+"'");


                kk++;
                System.out.println("records updated"+" "+kk);
                }
                if(amodel.length>=4)
                {
                    System.out.println("4");

                    Statement st1=con.createStatement();
                    st1.executeUpdate("update owner set maker='"+amodel[0]+"',model1='"+amodel[1]+"',model2='"+amodel[2]+"',model3='"+amodel[3]+"' where model_desc='"+model+"'");


                kk++;
                System.out.println("records updated"+" "+kk);
                }
                m++;
                //con=getConnection();
                }
                if(m==50)
                {
                    ArrayList al=new ArrayList();
                    al.add(rs);
                    //con.close();
                    con=getConnection();
                    rs=(ResultSet)al.get(0);
                    m=0;
                }
            }

            //System.out.println("COLUMN ADDED");
        }
        catch(Exception e)
        {
            System.out.println(e);  
        }
    }

    public static void main(String[] args) {
        SpaceSpliting ss=new SpaceSpliting();
        //ss.addingColumn();
        ss.addingValues();
        // TODO Auto-generated method stub

    }

}

select * from owner limit 10000 and then do several request in 10,000 counts — maress, Jan 19 '15 at 10:22
You have a variable naming problem. "m" and "kk" are terrible names. I can tell they are counters because you are incrementing them, but their names don't help the reader (myself or yourself) see what is being counted. Sorry that that's not what you asked about but you might still need to hear it. ;-) — Jolta, Jan 19 '15 at 10:23
Just update the values using a smarter SQL statement might work.. You dont' need to select hem all. Just `UPDATE .. WHERE...` and you can `JOIN` stuff if you need more. Updating 25M records one by one is just wrong. — Rob Audenaerde, Jan 19 '15 at 10:25
i was using m and kk to check the number of records it is actually modifying and i going to remove it further becoz it is not of my use — Sachin Singh, Jan 19 '15 at 10:28
Why does it terminate? It shouldn't terminate without an error, and you can't solve this problem without knowing what the error is. — Peter Lawrey, Jan 19 '15 at 10:29
@maress i m using oracle 10g and it doesnot support limit and i have 25 million records so it is not going to help me — Sachin Singh, Jan 19 '15 at 10:31
@RobAu i need to update them one by one coz i have to make changes to all 25 million existing records — Sachin Singh, Jan 19 '15 at 10:34
@SachinSingh `UPDATE` can update all 25m rows in one go. OR a subset. See http://www.w3schools.com/sql/sql_update.asp for example. — Rob Audenaerde, Jan 19 '15 at 10:36
@SachinSingh it must support something (like TOP 10,000) see this http://stackoverflow.com/questions/13667845/alternate-of-sql-server-top-in-oracle — maress, Jan 19 '15 at 10:37
@RobAu i do understand that it can upadate 25 m records in one go.but my problem is that i have to get the existing record and then break it into parts and then update it — Sachin Singh, Jan 19 '15 at 10:44
@maress brother i have tried rownum but it still doesn't work — Sachin Singh, Jan 19 '15 at 10:45
@PeterLawrey java.sql.SQLException: Listener refused the connection with the following error: ORA-12519, TNS:no appropriate service handler found The Connection descriptor used by the client was: localhost:1521:xe this is i what i get — Sachin Singh, Jan 19 '15 at 10:47
@SachinSingh a fairly meaningless error message I will agree, can you do a bulk export of the table. This may be significantly faster. — Peter Lawrey, Jan 19 '15 at 10:50
Why not use a database stored procedure for this and call procedure from Java by passing parameters as arguments to database procedure? — Jacob, Jan 19 '15 at 12:09
@SachinSingh sql also can use functions, Oracle has support for many string manipulation functions. That is the correct tool for this job. I think you don't even need to write your own stored procedure. — Rob Audenaerde, Jan 19 '15 at 14:19

Stephen C · Answer 1 · 2016-08-02T12:28:42.677

5

I think that the immediate problem is that you are leaking connections like crazy in this section of the code:

        ...
        m++;
        //con=getConnection();
    }
    if(m==50)
    {
        ArrayList al=new ArrayList();
        al.add(rs);
        //con.close();
        con=getConnection();
        rs=(ResultSet)al.get(0);
        m=0;
    }

It appears that for each 50 results you read from the main ResultSet you create a new database connection. I can't see where you are closing them. It won't be long before the Oracle DB server refuses to give you more connections.

But retrieving 25 million results and performing some significant fraction of 25 million individual updates is going to take a really long time. I think you should try to do this with more clever SQL ... or in PLSQL. (Ask a database expert. I'm just a Java guy :-) )

edited Aug 02 '16 at 12:28

answered Jan 19 '15 at 10:57

Stephen C

698,415
94
811
1,216

i tried lots of thing but it was the only thing that was working so i used this approach. i know that i should i have closed the connection but you can see in the code that i have commented it because whenever i was doing that it was giving me only 200 approx record. and i m also a java guy so i m facing this problem plz anyone who has a knowledge of sql ,psql or java help me out thank u in advance – Sachin Singh Jan 19 '15 at 11:17
1

*"I tried lots of things ..."* - Seriously, that is the wrong way to program. What you need to do is to go back to where you started, and debug your code properly. If it doesn't work, then you need to **work out why** it doesn't work, rather than trying other things randomly in the hope that they will be better. – Stephen C Jan 19 '15 at 11:21
You don't need to be an SQL expert to get something working here. The problem is in the JDBC calls you are making. You need to read a good tutorial on JDBC, paying particular attention to how you are supposed to manage connections. – Stephen C Jan 19 '15 at 11:23

score 2 · Answer 2 · answered Jan 19 '15 at 10:35

2

I think there is problem with time out, so increase the time out like this

con.setNetworkTimeout(null, 100000);

answered Jan 19 '15 at 10:35

Kishore

92
1
5

Exception in thread "main" java.lang.AbstractMethodError: oracle.jdbc.driver.T4CConnection.setNetworkTimeout(Ljava/util/concurrent/Executor;I)V at spliting.SpaceSpliting.addingValues(SpaceSpliting.java:42) at spliting.SpaceSpliting.main(SpaceSpliting.java:138) this is what i m getting when i set the time – Sachin Singh Jan 19 '15 at 10:52

score 2 · Answer 3 · answered Jan 19 '15 at 11:14

Maybe the problem is that you are releasing the variable con used to read the owner table:

    public void addingValues()
    {
        try
        {
            Connection con=getConnection();
            Statement st=con.createStatement();
            ResultSet rs=st.executeQuery("select * from owner");
            while(rs.next())
            {

                [...]

                if(m==50)
                {
                    ArrayList al=new ArrayList();
                    al.add(rs);
                    //con.close();
                    con=getConnection();
                    rs=(ResultSet)al.get(0);
                    m=0;
                }

By assigning a new value to con the original Connection object is orphaned. If nothing refers to the original Connection object any more, it could well be discarded by the garbage collector and the running request be closed as a result.

score 1 · Answer 4 · edited May 23 '17 at 12:32

1

I had a similar problem and setting the autocommit flag to false and manually committing the transaction did the trick. Since number of records is 25 million, you should also take a batch of larger size.

IF you tried solutions given in other answers and still the error did not resolve, then you might want to try this:

JAVA: sqlite throws exception after writing a few records?

edited May 23 '17 at 12:32

Community

1
1

answered Jan 14 '16 at 04:49

tryingToLearn

10,691
12
80
114

i have 25 million record in my database and i want to retrieve it using java

4 Answers4