[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 368
  • Last Modified:

java - jdbc query construction

Dear java experts,
                           I would like to get theall UNIQUE project id from all unique emp id. My tables are as follows

create table emp_table (
      empid              varchar(128) not null,
      description     varchar(128),
      primary key (empid)
);
create table emp_project (
      projid              varchar(128) not null,
      empid_a         varchar(128),
      proj_name  varchar(128),
      empid_b         varchar(128),
      primary key (projid )
);

I havde java object
Arraylist  empids = new Arraylist ();
the arraylist is filled with String  empids

I would like to construct a query to get all UNIQUE projects by getting UNIQUE projids

String EXEC_CONSTRAINT_GETALL  = "select emp_project .projid  from emp_project where "---help required. In here empids can be empid_a or empid_b and it needs to give the unique ones

 
0
Testsubbu
Asked:
Testsubbu
  • 2
  • 2
  • 2
  • +2
1 Solution
 
petmagdyCommented:
ok,

the query will look like this:

 "select DISTINCT emp_project.projid from emp_project, emp_table where emp_project.empid_a =  emp_table.empid or emp_project.empid_b =  emp_table.empid"
u will just retrieve and iterate on the returned ResultSet to get the results

Cheers!
0
 
CEHJCommented:
Why do you have empid_a *and* empid_b?
0
 
TestsubbuAuthor Commented:
actually i do not understand thie query



"select DISTINCT emp_project.projid from emp_project, emp_table where emp_project.empid_a =  emp_table.empid or emp_project.empid_b =  emp_table.empid"

how do you extend this to accomodate the  Arraylist  empids = new Arraylist (); from my question..meaning i will be passing a whole bunch of empids

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
petmagdyCommented:
As I told u u iterate on the returned ResultSet using:

Arraylist  empids = new Arraylist ();

ResultSet rs = stmt.executeQuery("select DISTINCT emp_project.projid from emp_project, emp_table where emp_project.empid_a =  emp_table.empid or emp_project.empid_b =  emp_table.empid");
 while(rs.next()) {
    String empid = theResultSet.getString(1);
    empids.add(empid);
}
0
 
TestsubbuAuthor Commented:
If you go back to my question..
  I am really looking for a query of this kind

"select DISTINCT emp_project.projid from emp_project, emp_table where

emp_project.empid_a = " empid[i] or "emp_project.empid_b = "empid[i]

where i will be populated by 1 - how many ever empid are in the array list
Arraylist  empids = new Arraylist ();

is this right? sir..
thanks a lot ---any help on query construction will be more useful


0
 
aozarovCommented:
You can use java.sql.Statement (instead of PreparedStatement) in this case and construct the query this way:
String stm = "select DISTINCT emp_project.projid from emp_project";
if (empids.size() > 0)
{
stm+= ", emp_table where";
for (Iterator i = empids.iterator(); i.hasNext(); )
{
String id = i.next().toString();
stm += " emp_project.empid_a=" + id + " or emp_project.empid_b="  + id;
}
}
Statement st = connection.createStatment(stm);
ResultSet rs = st.executeQuery();
while (rs.next())
...

You can use StringBuffer instead of String to make it a bit more efficient.
Or you can use a stored Procedure and pass the Array of employs to that proc.
0
 
asatheCommented:
Use a PreparedStatement and have the query as

PreparedStatement ps = conn.prepareStatement("select DISTINCT emp_project.projid from emp_project, emp_table where emp_project.empid_a in (?,?,?) or emp_project.empid_b in (?,?,?)");

You can generate the SQL easily, as you just have the same number of question marks for the size of your ArrayList.

You will need to add each parameter in twice. Assuming you have a bunch of Integers for empids:

int len = empids.size();
for (int i = 0; i < len; i++)
{
  Integer empid = (Integer) empids.get(i);
  ps.setInt(i + 1, empid.intValue());
}
for (int i = 0; i < len; i++)
{
  Integer empid = (Integer) empids.get(i);
  ps.setInt(i + len + 1, empid.intValue());
}

Remember that the parameters are set starting with 1, not 0!
0
 
aozarovCommented:
:-)
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now