How to pass array of sql query to function

Question

What's wrong in code. Please help me on this. How do I pass array of query to function exeSearchQuery();

  public class  MyDBConnect
    {
    Connection dbCon = null;
        public String DB_USERNAME = "root";
        public String DB_PASSWORD = "root";
        private String DB_DRIVER_CLASS = "com.mysql.jdbc.Driver";
        private String DB_URL = "jdbc:mysql://localhost/yatin";
    public  boolean connectToDb( ) throws Exception{
        try{
    Class.forName (DB_DRIVER_CLASS);
            dbCon = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
            //dbCon = DriverManager.getConnection(tns, DB_USERNAME, DB_PASSWORD);
            return true;
        }catch(Exception e){
            System.out.println("Error in DBConnect "+e);
            return false;
        }
    }

    public ArrayList exeSearchQuery(String sql[]) throws Exception{

        Statement stmt = null;
        ResultSet rs = null;
        ArrayList  data = null;
        int recordSize=0;
            try{

                connectToDb( );
                stmt = dbCon.createStatement( );
                rs = stmt.executeQuery(sql);
                ResultSetMetaData rsmd = rs.getMetaData();
                int numOfCols = rsmd.getColumnCount();

                data = new ArrayList( );
                String q="";
                for(int k=0;k<2;k++)
                {
                    boolean chkQuery=true;
                    q=sql[k];
                    while(rs.next()){
                    chkQuery=true;
                    recordSize=recordSize+1;
                    String row[] = new String[numOfCols];
                    for(int col=1;col<=numOfCols ;col++)
                    {
                        row[col-1] = rs.getString(col);
                    }
                    data.add(row);
                    }
                    System.out.println("data="+data);

                    if(chkQuery=true)
                        {
                        break;
                        }
                        else
                        {
                            q=sql[1];
                            while(rs.next())
                            {
                                chkQuery=true;
                                recordSize=recordSize+1;
                                String row[] = new String[numOfCols];
                                for(int col=1;col<=numOfCols ;col++)
                                {
                                    row[col-1] = rs.getString(col);
                                }
                                data.add(row);
                                System.out.println("data="+data);
                            }
                        }
                        if(chkQuery=true)
                        {
                        break;
                        }
                    }

            }catch(Exception e){
                System.out.println("Error in DBConnect "+e);
            }finally{
                if(rs !=null) rs.close();
                if(stmt !=null)stmt.close();
                if(dbCon !=null) dbCon.close();
            }
        return data;
        //return recordSize;
    }

        public static void main(String a[]) throws Exception{
            MyDBConnect db = new MyDBConnect( );
            //db.connectToDb();
                            System.out.println("Connected");
            String query[] ={"select a.ID,a.VDT,a.AID,a.NAME,a.ADD,a.CONTACT,a.PTM,a.DEPT_TEXT,a.FLOOR,b.LINK from tm_co123 a LEFT JOIN tm_co456 b ON a.ID=b.PID where a.NAME like 'anil' ORDER BY ID","select a.ID,a.VDT,a.AID,a.NAME,a.ADD,a.CONTACT,a.PTM,a.DEPT_TEXT,b.LINK from tm_co123 a LEFT JOIN tm_co456 b ON a.ID=b.PID where a.ADD like 'anil' ORDER BY ID"};       
            db.exeSearchQuery(query[]);
        }
    }

If I run above code I am getting Error as

MyDBConnect.java:473: '.class' expected
                db.exeSearchQuery(query[]);
                                         ^
1 error

If I replaces query[] with query then it givs me Error as

MyDBConnect.java:406: executeQuery(java.lang.String) in java.sql.Statement canno
t be applied to (java.lang.String[])
                        rs = stmt.executeQuery(sql);
                                 ^
Note: MyDBConnect.java uses unchecked or unsafe operations.
Note: Recompile with -Xlint:unchecked for details.
1 error

João Silva · Accepted Answer · 2012-08-22 09:57:00Z

2

You can't pass a String[] to executeQuery, it expects a single String as an argument. Either execute each query separately, or turn them into one using perhaps UNION.

answered Aug 22, 2012 at 9:57

João Silva

91.8k29 gold badges156 silver badges158 bronze badges

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

Comments

mssb · Accepted Answer · 2012-08-22 10:01:11Z

1

You can't pass array, to the executeQuery(); method only you can pass string eg-executeQuery(sql[0]);

answered Aug 22, 2012 at 10:01

mssb

8781 gold badge8 silver badges19 bronze badges

Comments

StepTNT · Accepted Answer · 2012-08-22 10:02:37Z

1

Even if your code is not the best that you can do, your sql is an array and not a String. And array can't be passed to executeQuery.

Here's a quick edit of your method :

        ...
            connectToDb( );
            for(String str : sql){ //This way you're doing each query of your array
                stmt = dbCon.createStatement( );
                rs = stmt.executeQuery(str);
                ResultSetMetaData rsmd = rs.getMetaData();
                int numOfCols = rsmd.getColumnCount();
                data = new ArrayList( );
                String q="";
                for(int k=0;k<2;k++){
                    boolean chkQuery=true;
                    q=sql[k];
                    while(rs.next()){
                        chkQuery=true;
                        recordSize=recordSize+1;
                        String row[] = new String[numOfCols];
                        for(int col=1;col<=numOfCols ;col++){
                            row[col-1] = rs.getString(col);
                        }
                    data.add(row);
                }
            }
        ...

answered Aug 22, 2012 at 10:02

StepTNT

3,9778 gold badges46 silver badges85 bronze badges

1 Comment

yatinbc Over a year ago

But this foreach loop doesn't support in lower version of tomcat say Tomcat 4.

swemon · Accepted Answer · 2012-08-22 10:15:31Z

1

stmt.executeQuery() method only accepts String parameter.

In your main(), call db.exeSearchQuery() method, it accepts parameter String[]. But for writing like exeSearchQuery(query[]), query is already 1D array. it is first mistake.
In your exeSearchQuery(String sql[]) method, stmt.executeQuery() requires String parameter. Writing like this stmt.executeQuery(sql). Since now sql is 1D array. It is second mistake.

I try like this, in exeSearchQuery(String sql[]) method

Edit rs = stmt.executeQuery(sql[0]);

in main() method,

String[] query = {.........};
db.exeSearchQuery(query);// edited

It connects to db.

edited Aug 22, 2012 at 10:15

answered Aug 22, 2012 at 10:03

swemon

5,9664 gold badges36 silver badges55 bronze badges

Comments

amicngh · Accepted Answer · 2012-08-22 10:07:23Z

0

executeQuery accept String not String[]do something like below.

List<ResultSet> lList=new ArrayList<ResultSet>();
for(String query :sql){
            ResultSet rs = stmt.executeQuery(query);
            lList.add(rs);
        }

answered Aug 22, 2012 at 10:07

amicngh

7,9293 gold badges37 silver badges55 bronze badges

Comments

yatinbc · Accepted Answer · 2012-08-30 05:09:20Z

0

I passed it as executeSearchQuery(query) as I defined it as String query[]={} and it worked and I got desired results.

answered Aug 30, 2012 at 5:09

yatinbc

6253 gold badges16 silver badges37 bronze badges

Collectives™ on Stack Overflow

How to pass array of sql query to function

6 Answers 6

Comments

Comments

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

Comments

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related