How to read data column wise using jdbc

Question

enter image description here I am using a table to get data from database ,but my table holds data column wise.I want to retrieve data column wise from the table using resultset.but each time i am getting "Column index out of range error." This is my code which i am using to retrieve data from the table

This is my select statment

 String consultantRatio = " select count(distinct CANDYS.candidateId) from "
                     +" (Select C.candidateID,J.joborderID from db_candidatenote C, db_jobordernote J " 
                     +" where C.noteid=J.noteid and C.action like 'Engaged' " 
                     +" and C.dateadded between "+ "\""+ new java.sql.Timestamp(startDate.getTime())+ "\" and "+ "\""+ new java.sql.Timestamp(endDate.getTime())+ "\"  ) N,"
                     +" (Select distinct Note.candidateId, JNote.jobOrderID " 
                     +" from db_candidatenote Note,db_jobordernote JNote, corporateuser User where " 
                     +" Note.noteid=JNote.noteid "
                     +" and Note.commentingPersonID = User.userid " 
                     +" and User.name= "+"\""+consultantNameList.get(i)+"\""
                     +" and Note.action='Sourcing') CANDYS "
                     +" where N.candidateID= CANDYS.candidateId "
                     +" and N.joborderid=CANDYS.jobOrderID "                

                     +" union"

                     +" select count(distinct CANDYS.candidateId) from "
                     +" (Select C.candidateID,J.joborderID from db_candidatenote C, db_jobordernote J " 
                     +" where C.noteid=J.noteid and C.action like 'Qualification' " 
                     +" and C.dateadded between "+ "\""+ new java.sql.Timestamp(startDate.getTime())+ "\" and "+ "\""+ new java.sql.Timestamp(endDate.getTime())+ "\"  ) N,"
                     +" (Select distinct Note.candidateId, JNote.jobOrderID " 
                     +" from db_candidatenote Note,db_jobordernote JNote, corporateuser User where " 
                     +" Note.noteid=JNote.noteid "
                     +" and Note.commentingPersonID = User.userid " 
                     +" and User.name= "+"\""+consultantNameList.get(i)+"\""
                     +" and Note.action='Sourcing') CANDYS "
                     +" where N.candidateID= CANDYS.candidateId "
                     +" and N.joborderid=CANDYS.jobOrderID "

                     +" union "

                     +" select count(distinct CANDYS.candidateId) from "
                     +" (Select C.candidateID,J.joborderID from db_candidatenote C, db_jobordernote J " 
                     +" where C.noteid=J.noteid and C.action like 'Internal Submission' "
                     +" and C.dateadded between "+ "\""+ new java.sql.Timestamp(startDate.getTime())+ "\" and "+ "\""+ new java.sql.Timestamp(endDate.getTime())+ "\"  ) N,"
                     +" (Select distinct Note.candidateId, JNote.jobOrderID " 
                     +" from db_candidatenote Note,db_jobordernote JNote, corporateuser User where " 
                     +" Note.noteid=JNote.noteid "
                     +" and Note.commentingPersonID = User.userid " 
                     +" and User.name= "+"\""+consultantNameList.get(i)+"\""
                     +" and Note.action='Sourcing') CANDYS "
                     +" where N.candidateID= CANDYS.candidateId "
                     +" and N.joborderid=CANDYS.jobOrderID "

                     +" union "

                    +" select count(distinct CANDYS.candidateId) from sendout S,"
                    +" (Select distinct Note.candidateId, JNote.jobOrderID " 
                    +" from db_candidatenote Note,db_jobordernote JNote, corporateuser User where " 
                    +" Note.noteid=JNote.noteid "
                    +" and Note.commentingPersonID = User.userid " 
                    +" and User.name= "+"\""+consultantNameList.get(i)+"\""
                    +" and Note.action='Sourcing' ) CANDYS "
                    +" where S.candidateID= CANDYS.candidateId "
                    +" and S.joborderid=CANDYS.jobOrderID "
                    +" and S.dateadded between "+ "\""+ new java.sql.Timestamp(startDate.getTime())+ "\" and "+ "\""+ new java.sql.Timestamp(endDate.getTime())+ "\" "

                    +" union"

                    +"  select count(distinct CANDYS.candidateId) from "
                    +"  (Select C.candidateID,J.joborderID from db_candidatenote C, db_jobordernote J " 
                    +"  where C.noteid=J.noteid and C.action like '%Interview%' " 
                    +"  and C.dateadded between "+ "\""+ new java.sql.Timestamp(startDate.getTime())+ "\" and "+ "\""+ new java.sql.Timestamp(endDate.getTime())+ "\"  ) N,"
                    +"  (Select distinct Note.candidateId, JNote.jobOrderID " 
                    +"  from db_candidatenote Note,db_jobordernote JNote, corporateuser User where " 
                    +"  Note.noteid=JNote.noteid "
                    +"  and Note.commentingPersonID = User.userid " 
                    +"  and User.name= "+"\""+consultantNameList.get(i)+"\""
                    +"  and Note.action='Sourcing') CANDYS "
                    +"  where N.candidateID= CANDYS.candidateId "
                    +"  and N.joborderid=CANDYS.jobOrderID "

                    +"  union "

                    +"  select count(distinct CANDYS.candidateId ) from placement P,(Select distinct Note.candidateId, JNote.jobOrderID " 
                    +"  from db_candidatenote Note,db_jobordernote JNote, corporateuser User where " 
                    +"  Note.noteid=JNote.noteid "
                    +"  and Note.commentingPersonID = User.userid " 
                    +"  and User.name= "+"\""+consultantNameList.get(i)+"\""
                    +"  and Note.action='Sourcing') CANDYS "
                    +"  where P.candidateID= CANDYS.candidateId "
                    +"  and P.joborderid=CANDYS.jobOrderID "
                    +"  and P.dateadded between "+ "\""+ new java.sql.Timestamp(startDate.getTime())+ "\" and "+ "\""+ new java.sql.Timestamp(endDate.getTime())+ "\" " ;



                 while (rsServeResource1.next()) {                          

                    // these variables will be divided by actual milestone numbers like source to engage / engage etc within that time range ..

                    if(rsServeResource1.getInt(1)!=0){
                    sourceToEngage = rsServeResource1.getInt(1);
                    }if(rsServeResource1.getInt(2)!=0){
                    sourceToQualification =rsServeResource1.getInt(2);
                    }if(rsServeResource1.getInt(3)!=0){
                    sourceToIs = rsServeResource1.getInt(3);
                    } if(rsServeResource1.getInt(4)!=0){
                    sourceToPresent =rsServeResource1.getInt(4);
                    } if(rsServeResource1.getInt(5)!=0){
                    sourceToInterview = rsServeResource1.getInt(5);
                    } if(rsServeResource1.getInt(6)!=0){
                    sourceToPlacament = rsServeResource1.getInt(6);
                }

Each time i am running the code .Its giving me the following exception.

Column Index out of range, 2 > 1. 
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1074)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:988)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:974)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:919)
at com.mysql.jdbc.ResultSetImpl.checkColumnBounds(ResultSetImpl.java:830)
at com.mysql.jdbc.ResultSetImpl.getInt(ResultSetImpl.java:2714)
at com.msh.servlet.MileStoneMonthlyJob.execute(MileStoneMonthlyJob.java:530)
at org.quartz.core.JobRunShell.run(JobRunShell.java:223)
at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:549)

Done

So where the problem lies i can not make it work.Somebody please help .

What's the select statement look? How many columns are in the table? — MadProgrammer, May 28 '15 at 09:07
Well, basically, the error is telling everything, there is only on column in the result set, I'd suggest the problem is in the query — MadProgrammer, May 28 '15 at 10:16
@MadProgrammer but the query is giving fine results when running — lucifer, May 28 '15 at 13:09
@MadProgrammer and can you edit my code and suggest something — lucifer, May 28 '15 at 13:12
@MadProgrammer getting error in this line if(rsServeResource1.getInt(2)!=0){ sourceToQualification =rsServeResource1.getInt(2); — lucifer, May 28 '15 at 13:13
From your picture, you're getting one column and multiple rows... — MadProgrammer, May 28 '15 at 21:18

rmbl · Answer 1 · 2015-05-28T09:40:21.490

0

There is only one column in the Resultset... So if you try to point to the next column when there is none, the IndexOutOfRangeException will be thrown.

But for further statements the following could be useful for you:

You can try to read the Columncount from the Metadata like:

rsServeResource1.getMetaData().getColumnCount();

and loop throw your row with it. Should look like:

int[] colcontent = new int[6];
while (rsServeResource1.next()) {
 int count = rsServeResource1.getMetaData().getColumnCount();

 for (int i = 0; i <= count; i++){

  colcontent[i] = rs.getInt(1);

 }
}

sourceToEngage = colcontent(0);
sourceToQualification =colcontent(1);
sourceToIs = colcontent(2);
sourceToPresent =colcontent(3);
sourceToInterview = colcontent(4);
sourceToPlacament = colcontent(5);

Guess now the properties should be filled correctly?

edited May 28 '15 at 09:40

answered May 28 '15 at 09:17

rmbl

50
8

No still it is showing the same exception and the problem is in the line }if(rsServeResource1.getInt(2)!=0){ – lucifer May 28 '15 at 09:25
be aware i didnt try it by myself, just wrote it on the fly. Mybe there are some changes required. And indeed, you still cannot point on the next column if there is no. – rmbl May 28 '15 at 09:26
this question has some very nice solutions that you could use http://stackoverflow.com/questions/696782/retrieve-column-names-from-java-sql-resultset – Hector May 28 '15 at 09:27
the defect in your code is that you have hard coded rsServeResource1.getInt(N), where N is hard coded to 1, 2, 3, 4, 5, & 6. your result set has only one column returned NOT 6. what makes you think you will always have 6 columns returned in your result set? the answers in that link i have pasted have some great approaches for coding access to all columns from all rows returned by any result set. I would suggest trying some of them out on your code so you can see for yourself how they work – Hector May 28 '15 at 09:59
@Hector i this application i will always have 6 – lucifer May 28 '15 at 10:01
6 rows, not columns. Hector is right. Look at http://www.w3schools.com/sql/trysql.asp?filename=trysql_select_union you are doing it exactly the same way: **select count(distinct CANDYS.candidateId) from** – rmbl May 28 '15 at 10:06
if you Always had 6 columns you would nt be getting Column Index out of range, 2 > 1. this error is informing you that your code is trying to get column index 2 when you have a max column index of 1. – Hector May 28 '15 at 10:08

score 0 · Answer 2 · answered May 29 '15 at 05:44

0

Your data is coming in rows and there is only one column. so we should use index in resultset always 1. there is not provision to rotate data in resultset

answered May 29 '15 at 05:44

Gourav malhotra

169
4

How to read data column wise using jdbc

2 Answers2