NetBeans Forums

 FAQFAQ   SearchSearch   MemberlistMemberlist   RegisterRegister   ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 
  

How to add SQL statement on existing RowSet using Java EE 5

 
Post new topic   Reply to topic    NetBeans Forums -> Java EE Users
View previous topic :: View next topic  
Author Message
HASSAN Kamrul
Posted via mailing list.





PostPosted: Sat Nov 29, 2008 8:02 am    Post subject: How to add SQL statement on existing RowSet using Java EE 5 Reply with quote

Could you help me with followings:
I have been building and running this project using Netbeans 6.1 on Java EE 5 using Apache Tomcat 6.0.16 (in my PC on Windows XP).
I have an existing RowSet which has following SQL statement.
"SELECT ALL TRAVEL.TRIP.PERSONID, TRAVEL.TRIP.DEPCITY FROM TRAVEL.TRIP";
How can I add following SQL statement on to above existing RowSet at run-time (because below line will change as required)
"WHERE TRAVEL.PERSON.PERSONID = ?";
Thank you
kam
Back to top
John Yeary
Posted via mailing list.





PostPosted: Mon Dec 01, 2008 2:17 am    Post subject: How to add SQL statement on existing RowSet using Java EE 5 Reply with quote

Hello Kam,

The case you listed above is a common item. This is the usual method for generating a master-detail components. Typically you would set the ? value to something that either returns a reasonable default set of values, or a null (empty set).

@Override
public void prerender() {

if (dropDown1.getSelected() == null) {
try {
/* NOTE:
* THIS ONLY WORKS IF YOU EXPECT ROWS TO EXIST IN THE TABLE
* FOR THE DROPDOWN TABLE PROVIDER.
*/
personDataProvider.cursorFirst();
Object o = personDataProvider.getValue("personId");
getSessionBean1().getTripRowSet2().setObject(1, o);
tripDataProvider.refresh();
} catch (SQLException ex) {
error(ex.getMessage());
}
}

}

This was used with the query:
SELECT ALL TRAVEL.TRIP.TRIPID, TRAVEL.TRIP.PERSONID, TRAVEL.TRIP.DEPDATE, TRAVEL.TRIP.DEPCITY, TRAVEL.TRIP.DESTCITY, TRAVEL.TRIP.TRIPTYPEID, TRAVEL.TRIP.LASTUPDATED FROM TRAVEL.TRIP WHERE TRAVEL.TRIP.PERSONID = ?

John

On Sat, Nov 29, 2008 at 3:02 AM, HASSAN Kamrul <address-removed ([email]address-removed[/email])> wrote:
Quote:

Could you help me with followings:
I have been building and running this project using Netbeans 6.1 on Java EE 5 using Apache Tomcat 6.0.16 (in my PC on Windows XP).
I have an existing RowSet which has following SQL statement.
"SELECT ALL TRAVEL.TRIP.PERSONID, TRAVEL.TRIP.DEPCITY FROM TRAVEL.TRIP";
How can I add following SQL statement on to above existing RowSet at run-time (because below line will change as required)
"WHERE TRAVEL.PERSON.PERSONID = ?";
Thank you
kam



--
John Yeary
--
http://javaevangelist.blogspot.com

"Far better it is to dare might things, to win glorious triumphs, even though checkered by failure,

than to take rank with those poor spirits who neither enjoy much nor suffer much, because they live in the grey

twilight that knows not victory nor defeat." -- Theodore Roosevelt
Back to top
HASSAN Kamrul
Posted via mailing list.





PostPosted: Mon Dec 01, 2008 3:52 am    Post subject: How to add SQL statement on existing RowSet using Java EE 5 Reply with quote

Thank you so much so John,

Just to make sure if I explain well, allow me to explain it in detail what I am trying to do.
I have 2 dropdowns, one dropdown gets PERSONID and second dropdown get TRIPID. sometimes user would want the result based on PERSONID only or TRIPID only or both PERSONID & TRIPID.
So, if user only select PERSONID then result would based on PERSONID [should not use TRIPID as null and would not get correct result]
SQL query statement should be as
SELECT ..... from ... WHERE PERSONID = ? -- if select PERSONID select only
SELECT ..... from ... WHERE TRIPID = ? -- if select TRIPID select only
SELECT ..... from ... WHERE TRIPID = ? AND PERSONID = ? -- if select both PERSONID & TRIPID
What I am trying do as if I have tripRowSet (existing) [along with tripDataProvider] with SELECT ..... from ... ; How can I do above as required (run-time).

Thank you
kam

From: address-removed [mailto:address-removed] On Behalf Of John Yeary
Sent: Sunday, November 30, 2008 8:18 PM
To: address-removed
Subject: Re: [nbj2ee] How to add SQL statement on existing RowSet using Java EE 5



Hello Kam,

The case you listed above is a common item. This is the usual method for generating a master-detail components. Typically you would set the ? value to something that either returns a reasonable default set of values, or a null (empty set).

@Override
public void prerender() {

if (dropDown1.getSelected() == null) {
try {
/* NOTE:
* THIS ONLY WORKS IF YOU EXPECT ROWS TO EXIST IN THE TABLE
* FOR THE DROPDOWN TABLE PROVIDER.
*/
personDataProvider.cursorFirst();
Object o = personDataProvider.getValue("personId");
getSessionBean1().getTripRowSet2().setObject(1, o);
tripDataProvider.refresh();
} catch (SQLException ex) {
error(ex.getMessage());
}
}

}

This was used with the query:
SELECT ALL TRAVEL.TRIP.TRIPID, TRAVEL.TRIP.PERSONID, TRAVEL.TRIP.DEPDATE, TRAVEL.TRIP.DEPCITY, TRAVEL.TRIP.DESTCITY, TRAVEL.TRIP.TRIPTYPEID, TRAVEL.TRIP.LASTUPDATED FROM TRAVEL.TRIP WHERE TRAVEL.TRIP.PERSONID = ?

John

On Sat, Nov 29, 2008 at 3:02 AM, HASSAN Kamrul <address-removed ([email]address-removed[/email])> wrote:
Quote:

Could you help me with followings:
I have been building and running this project using Netbeans 6.1 on Java EE 5 using Apache Tomcat 6.0.16 (in my PC on Windows XP).
I have an existing RowSet which has following SQL statement.
"SELECT ALL TRAVEL.TRIP.PERSONID, TRAVEL.TRIP.DEPCITY FROM TRAVEL.TRIP";
How can I add following SQL statement on to above existing RowSet at run-time (because below line will change as required)
"WHERE TRAVEL.PERSON.PERSONID = ?";
Thank you
kam



--
John Yeary
--
http://javaevangelist.blogspot.com

"Far better it is to dare might things, to win glorious triumphs, even though checkered by failure,

than to take rank with those poor spirits who neither enjoy much nor suffer much, because they live in the grey

twilight that knows not victory nor defeat." -- Theodore Roosevelt
Back to top
Futaleufu_John
Posted via mailing list.





PostPosted: Mon Dec 01, 2008 2:55 pm    Post subject: How to add SQL statement on existing RowSet using Java EE 5 Reply with quote

This situation occurs all the time in my app. What I end up doing writing a
custom query.

StringBuffer sql = new StringBuffer();
sql.append("SELECT ALL TRAVEL.TRIP.PERSONID, TRAVEL.TRIP.DEPCITY FROM
TRAVEL.TRIP ");
Integer personId = (Integer)personDD.getSelected();
Integer tripIp = (Integer)tripDD.getSelected();
if(personId != null && tripId != null) {
sql.append("WHERE PERSONID = " + personId + " AND TRIPIP = " +
tripId);
}
else if(personId != null) {
sql.append("WHERE PERSONID = " + personId);
}
else if(tripId != null) {
sql.append("WHERE TRIPIP = " + tripId);
}
tripRowSet.setCommand(sql.toString());
tripRowSet.execute();
tripDataProvider.refresh();

Note that the I initially define tripRowSet to use the basic SELECT...FROM
(no WHERE clause) so I can bind its data provider to a table.

In general this query would go in prerender (or in a method called by
prerender).


HASSAN Kamrul wrote:
Quote:

I have 2 dropdowns, one dropdown gets PERSONID and second dropdown get
TRIPID. sometimes user would want the result based on PERSONID only or
TRIPID only or both PERSONID & TRIPID.
So, if user only select PERSONID then result would based on PERSONID
[should not use TRIPID as null and would not get correct result]
SQL query statement should be as
SELECT ..... from ... WHERE PERSONID = ?
-- if select PERSONID select only
SELECT ..... from ... WHERE TRIPID = ?
-- if select TRIPID select only
SELECT ..... from ... WHERE TRIPID = ? AND PERSONID = ? -- if
select both PERSONID & TRIPID
What I am trying do as if I have tripRowSet (existing) [along with
tripDataProvider] with SELECT ..... from ... ; How can I do above as
required (run-time).


--
View this message in context: http://www.nabble.com/How-to-add-SQL-statement-on-existing-RowSet-using-Java-EE-5-tp20744852p20773427.html
Sent from the Netbeans - J2EE mailing list archive at Nabble.com.
Back to top
John Yeary
Posted via mailing list.





PostPosted: Mon Dec 01, 2008 2:58 pm    Post subject: How to add SQL statement on existing RowSet using Java EE 5 Reply with quote

Hello John,

That is a very nice example of how to accomplish it.

John

On Mon, Dec 1, 2008 at 9:54 AM, Futaleufu_John <address-removed ([email]address-removed[/email])> wrote:
Quote:

This situation occurs all the time in my app. What I end up doing writing a
custom query.

StringBuffer sql = new StringBuffer();
sql.append("SELECT ALL TRAVEL.TRIP.PERSONID, TRAVEL.TRIP.DEPCITY FROM
TRAVEL.TRIP ");
Integer personId = (Integer)personDD.getSelected();
Integer tripIp = (Integer)tripDD.getSelected();
if(personId != null && tripId != null) {
sql.append("WHERE PERSONID = " + personId + " AND TRIPIP = " +
tripId);
}
else if(personId != null) {
sql.append("WHERE PERSONID = " + personId);
}
else if(tripId != null) {
sql.append("WHERE TRIPIP = " + tripId);
}
tripRowSet.setCommand(sql.toString());
tripRowSet.execute();
tripDataProvider.refresh();

Note that the I initially define tripRowSet to use the basic SELECT...FROM
(no WHERE clause) so I can bind its data provider to a table.

In general this query would go in prerender (or in a method called by
prerender).



HASSAN Kamrul wrote:
Quote:

I have 2 dropdowns, one dropdown gets PERSONID and second dropdown get
TRIPID. sometimes user would want the result based on PERSONID only or
TRIPID only or both PERSONID & TRIPID.
So, if user only select PERSONID then result would based on PERSONID
[should not use TRIPID as null and would not get correct result]
SQL query statement should be as
SELECT ..... from ... WHERE PERSONID = ?
-- if select PERSONID select only
SELECT ..... from ... WHERE TRIPID = ?
-- if select TRIPID select only
SELECT ..... from ... WHERE TRIPID = ? AND PERSONID = ? -- if
select both PERSONID & TRIPID
What I am trying do as if I have tripRowSet (existing) [along with
tripDataProvider] with SELECT ..... from ... ; How can I do above as
required (run-time).




--
View this message in context: http://www.nabble.com/How-to-add-SQL-statement-on-existing-RowSet-using-Java-EE-5-tp20744852p20773427.html
Sent from the Netbeans - J2EE mailing list archive at Nabble.com.




--
John Yeary
--
http://javaevangelist.blogspot.com

"Far better it is to dare might things, to win glorious triumphs, even though checkered by failure,

than to take rank with those poor spirits who neither enjoy much nor suffer much, because they live in the grey

twilight that knows not victory nor defeat." -- Theodore Roosevelt
Back to top
HASSAN Kamrul
Posted via mailing list.





PostPosted: Thu Dec 04, 2008 11:13 pm    Post subject: How to add SQL statement on existing RowSet using Java EE 5 Reply with quote

Thanks John,could not get followings working WHERE clause is not being added on to the existing tripRowSet.
Could anyone help me with this issue.
So, I have RowSet called tripRowSet (CachedRowSet(SessionBean1)) with tripDataProvider
I have created tripRowSet and tripDataProvider by dropping TRIP table into the table.
Existing tripRowSet has "SELECT ALL TRAVEL.TRIP.PERSONID, TRAVEL.TRIP.DEPCITY FROM TRAVEL.TRIP"
Please explain how I can add followings (on run-time) as required.
Integer personId = (Integer)dropDown1.getSelected();
Integer tripIp = (Integer)dropDown2.getSelected();
if(personId != null && tripId != null) {
sql.append("WHERE PERSONID = " + personId + " AND TRIPIP = " + tripId);
}
else if(personId != null) {
sql.append("WHERE PERSONID = " + personId);
}
else if(tripId != null) {
sql.append("WHERE TRIPIP = " + tripId);
}
tripRowSet.setCommand(sql.toString());
tripRowSet.execute();
tripDataProvider.refresh();
Thanks
kam
-----Original Message-----
From: Futaleufu_John [mailto:address-removed ([email]address-removed[/email])]
Sent: Monday, December 01, 2008 8:55 AM
To: address-removed
Subject: Re: [nbj2ee] How to add SQL statement on existing RowSet using Java EE 5

This situation occurs all the time in my app. What I end up doing writing a custom query.
StringBuffer sql = new StringBuffer();
sql.append("SELECT ALL TRAVEL.TRIP.PERSONID, TRAVEL.TRIP.DEPCITY FROM TRAVEL.TRIP ");
Integer personId = (Integer)personDD.getSelected();
Integer tripIp = (Integer)tripDD.getSelected();
if(personId != null && tripId != null) {
sql.append("WHERE PERSONID = " + personId + " AND TRIPIP = " + tripId);
}
else if(personId != null) {
sql.append("WHERE PERSONID = " + personId);
}
else if(tripId != null) {
sql.append("WHERE TRIPIP = " + tripId);
}
tripRowSet.setCommand(sql.toString());
tripRowSet.execute();
tripDataProvider.refresh();
Note that the I initially define tripRowSet to use the basic SELECT...FROM (no WHERE clause) so I can bind its data provider to a table.
In general this query would go in prerender (or in a method called by prerender).

HASSAN Kamrul wrote:
Quote:

I have 2 dropdowns, one dropdown gets PERSONID and second dropdown get
TRIPID. sometimes user would want the result based on PERSONID only or
TRIPID only or both PERSONID & TRIPID.
So, if user only select PERSONID then result would based on PERSONID
[should not use TRIPID as null and would not get correct result]
SQL query statement should be as
SELECT ..... from ... WHERE PERSONID = ?
-- if select PERSONID select only
SELECT ..... from ... WHERE TRIPID = ?
-- if select TRIPID select only
SELECT ..... from ... WHERE TRIPID = ? AND PERSONID = ? -- if
select both PERSONID & TRIPID
What I am trying do as if I have tripRowSet (existing) [along with
tripDataProvider] with SELECT ..... from ... ; How can I do above as
required (run-time).

--
View this message in context: http://www.nabble.com/How-to-add-SQL-statement-on-existing-RowSet-using-Java-EE-5-tp20744852p20773427.html
Sent from the Netbeans - J2EE mailing list archive at Nabble.com.
Back to top
Futaleufu_John
Posted via mailing list.





PostPosted: Fri Dec 05, 2008 4:36 pm    Post subject: How to add SQL statement on existing RowSet using Java EE 5 Reply with quote

tripRowSet.setCommand([sqlString]) replaces the current SQL statement with a
new SQL statement.

The line

tripRowSet.setCommand(sql.toString());

does this.

If no WHERE clause is appearing in this SQL string, then
dropdown1.getSelected() and dropdown2.getSelected() are returning null. If
that's the case then either you are not selecting an item or the dropdowns
are not correctly configured.

Figure out which is the case and go from there. To do that you may want to
set a breakpoint in your code and step through it in the debugger.


HASSAN Kamrul wrote:
Quote:

Thanks John,could not get followings working WHERE clause is not being
added on to the existing tripRowSet.

So, I have RowSet called tripRowSet (CachedRowSet(SessionBean1)) with
tripDataProvider
I have created tripRowSet and tripDataProvider by dropping TRIP
table into the table.
Existing tripRowSet has "SELECT ALL TRAVEL.TRIP.PERSONID,
TRAVEL.TRIP.DEPCITY FROM TRAVEL.TRIP"
Please explain how I can add followings (on run-time) as required.

Integer personId = (Integer)dropDown1.getSelected();
Integer tripIp = (Integer)dropDown2.getSelected();
if(personId != null && tripId != null) {
sql.append("WHERE PERSONID = " + personId + " AND TRIPIP = " +
tripId);
}
else if(personId != null) {
sql.append("WHERE PERSONID = " + personId);
}
else if(tripId != null) {
sql.append("WHERE TRIPIP = " + tripId);
}
tripRowSet.setCommand(sql.toString());
tripRowSet.execute();
tripDataProvider.refresh();


--
View this message in context: http://www.nabble.com/How-to-add-SQL-statement-on-existing-RowSet-using-Java-EE-5-tp20744852p20857726.html
Sent from the Netbeans - J2EE mailing list archive at Nabble.com.
Back to top
Display posts from previous:   
Post new topic   Reply to topic    NetBeans Forums -> Java EE Users All times are GMT
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
You can attach files in this forum
You can download files in this forum


Powered by phpBB
By use of this website, you agree to the NetBeans Policies and Terms of Use. © 2012, Oracle Corporation and/or its affiliates. Sponsored by Oracle logo