Insert values using a loop

Question

I have a database column with 54 values.I want to create a table for each values.But my code only creates first value table then it shows an error due to closing ResultSet.

How can I reopen the ResultSet using a loop? I need all 54 valued tables.

Code:

public void createTableStudent() {
    try {
        connection = (Connection) dbSource.getConnection();

        String qry = "SELECT studentName From batcha "; 
        System.out.println(qry);

        stmt = (PreparedStatement)  connection.prepareStatement(qry);
            rs =  stmt.executeQuery();

            while (rs.next()) {

                String check=new String(rs.getString(("studentName")));
                String student = check.replaceAll("\\s","");

                Class.forName("com.mysql.jdbc.Driver");
                connection = (Connection) DriverManager.getConnection(DB_URL_table, USER, PASS);

                String sql1 = "CREATE TABLE IF NOT EXISTS "+student+" " +
                    "(id INTEGER not NULL AUTO_INCREMENT, " +
                    " fcltyName VARCHAR(255), " + 

                    "CommunicationOral INTEGER, "+
                    "Communicationwritten INTEGER, "+
                    "Leadership INTEGER, "+
                    "AnalyticalAbilities INTEGER, "+
                    "Interpersonalskills INTEGER, "+
                    "DecisionMakingSkills INTEGER, "+
                    "SelfConfidence INTEGER, "+
                    "Creativity INTEGER, "+
                    "Punctualityregularity INTEGER, "+
                    "GeneralAwareness INTEGER, "+
                    "Commitment INTEGER, "+
                    "Hardwork INTEGER, "+

                    " PRIMARY KEY ( id ))"; 
            stmt.executeUpdate(sql1); 

                        }



    } catch (ClassNotFoundException ex) {
        Logger.getLogger(AdminHome.class.getName()).log(Level.SEVERE, null, ex);
    } catch (SQLException ex) {
        Logger.getLogger(AdminHome.class.getName()).log(Level.SEVERE, null, ex);
    }
}

It shows following error

 java.sql.SQLException: Operation not allowed after ResultSet closed

Ashish Chaurasia · Accepted Answer · 2013-08-14 05:36:39Z

5

You are re-using the same Statement for both queries. Don't do that; instead, create a new Statement object for each query.

answered Aug 14, 2013 at 5:36

Ashish Chaurasia

1,74717 silver badges24 bronze badges

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

Comments

Ankur Lathi · Accepted Answer · 2013-08-14 05:33:04Z

This violates the expectation of the Statement class.

By default, only one ResultSet object per Statement object can be open at the same time. Therefore, if the reading of one ResultSet object is interleaved with the reading of another, each must have been generated by different Statement objects. All execution methods in the Statement interface implicitly close a statment's current ResultSet object if an open one exists.

So you need to create a new Statement for table creation.

public void createTableStudent() 
{
    try {
        connection = (Connection) dbSource.getConnection();

        String qry = "SELECT studentName From batcha "; 
        System.out.println(qry);

        stmt = (PreparedStatement)  connection.prepareStatement(qry);
            rs =  stmt.executeQuery();

            while (rs.next()) 
            {

                Class.forName("com.mysql.jdbc.Driver");
                connection = (Connection) DriverManager.getConnection(DB_URL_table, USER, PASS);

                Statement newStmt = connection.createStatement();

                try {
                        String check=new String(rs.getString(("studentName")));
                        String student = check.replaceAll("\\s","");



                        String sql1 = "CREATE TABLE IF NOT EXISTS "+student+" " +
                            "(id INTEGER not NULL AUTO_INCREMENT, " +
                            " fcltyName VARCHAR(255), " + 

                            "CommunicationOral INTEGER, "+
                            "Communicationwritten INTEGER, "+
                            "Leadership INTEGER, "+
                            "AnalyticalAbilities INTEGER, "+
                            "Interpersonalskills INTEGER, "+
                            "DecisionMakingSkills INTEGER, "+
                            "SelfConfidence INTEGER, "+
                            "Creativity INTEGER, "+
                            "Punctualityregularity INTEGER, "+
                            "GeneralAwareness INTEGER, "+
                            "Commitment INTEGER, "+
                            "Hardwork INTEGER, "+

                            " PRIMARY KEY ( id ))"; 

                        newStmt.executeUpdate(sql1); 
                    }

                finally
                {
                    try 
                    {
                        if(newStmt!=null)
                            newStmt.close();
                    } 
                    catch (SQLException e) 
                    {
                        e.printStackTrace();
                    }
                }

            }



    } catch (ClassNotFoundException ex) {
        Logger.getLogger(AdminHome.class.getName()).log(Level.SEVERE, null, ex);
    } catch (SQLException ex) {
        Logger.getLogger(AdminHome.class.getName()).log(Level.SEVERE, null, ex);
    }
    finally
    {
        try {
                if(rs!=null)
                    rs.close();
                if(stmt!=null)
                    stmt.close();
                if(connection!=null)
                    connection.close();                     
        } 
        catch (SQLException e) 
        {
            e.printStackTrace();
        }
    }
}

how can i create 54 Statements in my code?How can i create a loop for that?

Darshan Mehta · Accepted Answer · 2013-08-14 05:13:26Z

1

You need to create a new statement (a separate variable) to create a table. As result set is a cursor over statement object, it becomes invalid once you execute another statement on same object.

answered Aug 14, 2013 at 5:13

Darshan Mehta

31k14 gold badges75 silver badges111 bronze badges

2 Comments

Deepthi Dipin Over a year ago

how can i create 54 Statements in my code?How can i create a loop for that?

ds345 Over a year ago

You will have to create a new statement object inside your rs.next loop and can close the statement after executing it. Refer: stackoverflow.com/questions/6984783/…

Suryaprakash Pisay · Accepted Answer · 2013-08-14 05:58:36Z

I think you need to create a new statement inside a while loop

Because you are executing a statement and overriding a statement with another query inside a while loop

That's why only first value is executing and remaining overriding.

Below is the modified code.

public void createTableStudent() {
    try {
        connection = (Connection) dbSource.getConnection();

        String qry = "SELECT studentName From batcha "; 
        System.out.println(qry);

        stmt = (PreparedStatement)  connection.prepareStatement(qry);
            rs =  stmt.executeQuery();

            while (rs.next()) {



                String check=new String(rs.getString(("studentName")));
                String student = check.replaceAll("\\s","");

                Class.forName("com.mysql.jdbc.Driver");
                connection = (Connection) DriverManager.getConnection(DB_URL_table, USER, PASS);
                Statement statement2 = connection.createStatement();

                String sql1 = "CREATE TABLE IF NOT EXISTS "+student+" " +
                    "(id INTEGER not NULL AUTO_INCREMENT, " +
                    " fcltyName VARCHAR(255), " + 

                    "CommunicationOral INTEGER, "+
                    "Communicationwritten INTEGER, "+
                    "Leadership INTEGER, "+
                    "AnalyticalAbilities INTEGER, "+
                    "Interpersonalskills INTEGER, "+
                    "DecisionMakingSkills INTEGER, "+
                    "SelfConfidence INTEGER, "+
                    "Creativity INTEGER, "+
                    "Punctualityregularity INTEGER, "+
                    "GeneralAwareness INTEGER, "+
                    "Commitment INTEGER, "+
                    "Hardwork INTEGER, "+

                    " PRIMARY KEY ( id ))"; 
                statement2.executeUpdate(sql1); 

                        }



    } catch (ClassNotFoundException ex) {
        Logger.getLogger(AdminHome.class.getName()).log(Level.SEVERE, null, ex);
    } catch (SQLException ex) {
        Logger.getLogger(AdminHome.class.getName()).log(Level.SEVERE, null, ex);
    }
}

Hope it helps

Eswar · Accepted Answer · 2013-08-14 05:41:50Z

-1

I remember reading that we should avoid using 'Create table if not exists'.Alternatively java.sql.DatabaseMetaData can be used to check if a table exists. The below link explains this.

http://somesimplethings.blogspot.co.uk/2010/03/derby-create-table-if-not-exists.html

answered Aug 14, 2013 at 5:41

Eswar

1,0651 gold badge8 silver badges11 bronze badges

1 Comment

Uooo Over a year ago

Please post relevant information here, external links might be dead in the future.

Collectives™ on Stack Overflow

Insert values using a loop

5 Answers 5

Comments

3 Comments

2 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

3 Comments

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related