001: package com.sun.portal.sample.j2ee.poll.ejb;
002:
003: import javax.ejb.*;
004: import java.util.Map;
005: import java.util.List;
006: import java.util.HashMap;
007: import java.util.ArrayList;
008:
009: import java.util.Iterator;
010:
011: import javax.naming.Context;
012: import javax.naming.InitialContext;
013: import java.sql.Statement;
014: import java.sql.Connection;
015: import java.sql.ResultSet;
016: import javax.sql.DataSource;
017:
018: /**
019: * Created Apr 10, 2003 5:45:15 PM
020: * Code generated by the Sun ONE Studio EJB Builder
021: * @author ms109488
022: */
023:
024: public class PollBean implements javax.ejb.SessionBean {
025: private javax.ejb.SessionContext context;
026:
027: /**
028: * @see javax.ejb.SessionBean#setSessionContext(javax.ejb.SessionContext)
029: */
030: public void setSessionContext(javax.ejb.SessionContext aContext) {
031: context = aContext;
032: }
033:
034: /**
035: * @see javax.ejb.SessionBean#ejbActivate()
036: */
037: public void ejbActivate() {
038: System.out.println("ejbActivate() on obj " + this );
039: }
040:
041: /**
042: * @see javax.ejb.SessionBean#ejbPassivate()
043: */
044: public void ejbPassivate() {
045: System.out.println("ejbPassivate() on obj " + this );
046: }
047:
048: /**
049: * @see javax.ejb.SessionBean#ejbRemove()
050: */
051: public void ejbRemove() {
052: System.out.println("ejbRemove() on obj " + this );
053: }
054:
055: /**
056: * See section 7.10.3 of the EJB 2.0 specification
057: */
058: public void ejbCreate() {
059: System.out.println("ejbCreate() on obj " + this );
060: }
061:
062: public List getResults(java.lang.String pollName) {
063: List results = new ArrayList();
064: StringBuffer query = new StringBuffer();
065: DataSource ds = getDataSource();
066: long total = 0;
067: if (ds != null) {
068: try {
069: Connection conn = ds.getConnection();
070: Statement stmt = conn.createStatement();
071: ResultSet rs = null;
072:
073: query
074: .append(
075: "SELECT C.CHOICE, V.COUNT FROM POLL.QUESTIONS Q, POLL.CHOICES C, POLL.VOTE V")
076: .append(" WHERE Q.POLLNAME='")
077: .append(pollName)
078: .append("'")
079: .append(" AND Q.ID = V.QID")
080: .append(
081: " AND V.CID = C.ID ORDER BY V.COUNT DESC");
082:
083: rs = stmt.executeQuery(query.toString());
084: while (rs.next()) {
085: Object[] vote = new Object[3];
086: vote[0] = rs.getString("CHOICE");
087: vote[2] = new Long(rs.getInt("COUNT"));
088: total += ((Long) vote[2]).longValue();
089: results.add(vote);
090: }
091:
092: stmt.close();
093: conn.close();
094: } catch (Exception ex) {
095: System.out
096: .println("PollBean.getChoices : can not close the database statment and connection "
097: + ex.toString());
098: }
099: }
100: for (int i = 0; i < results.size(); i++) {
101: Object[] vote = (Object[]) results.get(i);
102: long count = ((Long) vote[2]).longValue();
103: vote[1] = new Long(Math.round((float) count / total * 100));
104: }
105: return results;
106: }
107:
108: public List vote(java.lang.String pollName, java.lang.String choice) {
109:
110: Map results = new HashMap();
111: ;
112: StringBuffer query = new StringBuffer();
113: DataSource ds = getDataSource();
114: if (ds != null) {
115: try {
116: Connection conn = ds.getConnection();
117: Statement stmt = conn.createStatement();
118:
119: query
120: .append("UPDATE POLL.VOTE SET COUNT=COUNT+1 ")
121: .append(
122: " WHERE QID=(SELECT ID FROM POLL.QUESTIONS WHERE POLLNAME='")
123: .append(pollName)
124: .append(
125: "') AND CID=(SELECT ID FROM POLL.CHOICES WHERE CHOICE='")
126: .append(choice).append("')");
127:
128: stmt.executeUpdate(query.toString());
129:
130: stmt.close();
131: conn.close();
132: } catch (Exception ex) {
133: System.out
134: .println("PollBean.getChoices : can not close the database statment and connection "
135: + ex.toString());
136: }
137: }
138: return getResults(pollName);
139: }
140:
141: public List changeVote(java.lang.String pollName,
142: java.lang.String oldChoice, java.lang.String newChoice) {
143: StringBuffer query = new StringBuffer();
144: DataSource ds = getDataSource();
145: if (ds != null) {
146: try {
147: Connection conn = ds.getConnection();
148: conn.setAutoCommit(false);
149: Statement stmt = conn.createStatement();
150:
151: query
152: .append("UPDATE POLL.VOTE SET COUNT=COUNT-1 ")
153: .append(
154: " WHERE QID=(SELECT ID FROM POLL.QUESTIONS WHERE POLLNAME='")
155: .append(pollName)
156: .append("')")
157: .append(
158: " AND CID=(SELECT ID FROM POLL.CHOICES WHERE CHOICE='")
159: .append(oldChoice).append("')");
160:
161: stmt.executeUpdate(query.toString());
162:
163: query.delete(0, query.length());
164: query
165: .append("UPDATE POLL.VOTE SET COUNT=COUNT+1 ")
166: .append(
167: " WHERE QID=(SELECT ID FROM POLL.QUESTIONS WHERE POLLNAME='")
168: .append(pollName)
169: .append("')")
170: .append(
171: " AND CID=(SELECT ID FROM POLL.CHOICES WHERE CHOICE='")
172: .append(newChoice).append("')");
173:
174: stmt.executeUpdate(query.toString());
175:
176: conn.commit();
177:
178: stmt.close();
179: conn.close();
180: } catch (Exception ex) {
181: System.out
182: .println("PollBean.getChoices : can not close the database statment and connection "
183: + ex.toString());
184: }
185: }
186: return getResults(pollName);
187:
188: }
189:
190: public List getChoices(java.lang.String pollName) {
191: List choices = new ArrayList();
192: ;
193: StringBuffer query = new StringBuffer();
194: DataSource ds = getDataSource();
195: if (ds != null) {
196: try {
197: Connection conn = ds.getConnection();
198: Statement stmt = conn.createStatement();
199: ResultSet rs = null;
200:
201: query
202: .append(
203: " SELECT C.CHOICE FROM POLL.QUESTIONS Q, POLL.CHOICES C, POLL.VOTE V ")
204: .append(" WHERE Q.POLLNAME='").append(pollName)
205: .append("'").append(" AND Q.ID = V.QID")
206: .append(" AND V.CID = C.ID");
207:
208: rs = stmt.executeQuery(query.toString());
209:
210: while (rs.next()) {
211: choices.add(rs.getString("CHOICE"));
212: }
213:
214: stmt.close();
215: conn.close();
216: } catch (Exception ex) {
217: System.out
218: .println("PollBean.getChoices : can not close the database statment and connection "
219: + ex.toString());
220: }
221: }
222:
223: return choices;
224: }
225:
226: public java.lang.String getQuestion(java.lang.String pollName) {
227: String question = null;
228: StringBuffer query = new StringBuffer();
229: DataSource ds = getDataSource();
230: if (ds != null) {
231: try {
232: Connection conn = ds.getConnection();
233: Statement stmt = conn.createStatement();
234: ResultSet rs = null;
235:
236: query
237: .append(
238: " SELECT QUESTION FROM POLL.QUESTIONS WHERE POLLNAME = '")
239: .append(pollName).append("'");
240:
241: rs = stmt.executeQuery(query.toString());
242:
243: if (rs.next()) {
244: question = rs.getString("QUESTION");
245: }
246:
247: stmt.close();
248: conn.close();
249: } catch (Exception ex) {
250: System.out
251: .println("PollBean.getQuestion : can not close the database statment and connection "
252: + ex.toString());
253: }
254: }
255: return question;
256: }
257:
258: private Context getContext() {
259: Context c = null;
260: try {
261: c = new InitialContext();
262: } catch (Exception ex) {
263: System.out.println("PollBean: can't get initial context");
264: }
265: return c;
266: }
267:
268: private DataSource getDataSource() {
269: String dsName = "jdbc/poll";
270: Context c = getContext();
271: DataSource ds = null;
272: if (c != null) {
273: try {
274: ds = (javax.sql.DataSource) c.lookup(dsName);
275: } catch (Exception e) {
276: System.out
277: .println("PollBean: Can not interact with the database : "
278: + e.toString());
279: }
280: }
281: return ds;
282: }
283:
284: }
|