1
2
3
4 package ca.spaz.sql;
5
6 import java.sql.*;
7 import java.util.*;
8 import java.util.ArrayList;
9
10 import org.apache.log4j.Logger;
11
12 /***
13 * Conveniently produces simple SQL SELECT statements
14 *
15 * Currently only handles basic column selection and where based on column
16 * equality.
17 *
18 * @author Aaron Davidson
19 */
20 public class SQLSelect extends SQLSelectableStatement {
21 /***
22 * Logger for this class
23 */
24 private static final Logger logger = Logger.getLogger(SQLSelect.class);
25
26 protected List items = new ArrayList();
27
28 protected ResultSet results = null;
29
30 private SQLSelect subSelection = null;
31
32 private String[] joinedTables = null;
33
34 private List orders = new ArrayList();
35
36 /***
37 * Create a new SQLUpdate command for the given table
38 *
39 * @param tableName
40 * the name of the table to update on
41 */
42 public SQLSelect(String tableName) {
43 super(tableName, true, true, false);
44 }
45
46 public SQLSelect(SQLSelect subSelection) {
47 super("", true, true, false);
48 this.subSelection = subSelection;
49 }
50
51 public SQLSelect(String[] joinedTables) {
52 super("", true, true, false);
53 this.joinedTables = joinedTables;
54 }
55
56 public void addOrderBy(String orderClause) {
57 orders.add(orderClause);
58 }
59
60 /***
61 * Add an item to select
62 *
63 * @param field
64 * a valid SQL selection item
65 */
66 public void addSelection(String field) {
67 items.add(field);
68 }
69
70 public void addSelection(String field, String as) {
71 items.add(field + " as " + as);
72 }
73
74 /***
75 * Execute the query and return the results.
76 */
77 protected ResultSet doExecuteQuery(Connection con) throws SQLException {
78 Statement stmt = con.createStatement();
79 String query = this.getQueryString();
80 if (logger.isDebugEnabled()) {
81 logger.debug("executeQuery() - Statement to be executed: " + query);
82 }
83
84 results = stmt.executeQuery(query);
85 return results;
86 }
87
88 private String getOrder() {
89 StringBuffer sb = new StringBuffer();
90 if (orders.size() > 0) {
91 sb.append(" ORDER BY ");
92 for (int i = 0; i < orders.size(); i++) {
93 Object w = orders.get(i);
94 sb.append(w.toString());
95 if (i < orders.size() - 1) {
96 sb.append(",");
97 }
98 }
99 }
100 return sb.toString();
101 }
102
103 /***
104 * Generate the SQL string for a SELECT command.
105 */
106 protected String getQueryString() {
107 StringBuffer sb = new StringBuffer();
108 sb.append("SELECT ");
109 if (items.size() == 0) {
110 sb.append(" * ");
111 } else {
112 for (int i = 0; i < items.size(); i++) {
113 String term = (String) items.get(i);
114 if (term != null) {
115 sb.append(term);
116 if (i < items.size() - 1) {
117 sb.append(", ");
118 }
119 }
120 }
121 }
122 sb.append(" FROM ");
123 if (null != joinedTables) {
124 for (int i = 0; i < joinedTables.length; i++) {
125 sb.append(joinedTables[i]);
126 if (i < joinedTables.length - 1) {
127 sb.append(", ");
128 }
129 }
130 } else if (null != subSelection) {
131 sb.append("(");
132 sb.append(subSelection.toString());
133 sb.append(")");
134 } else {
135 sb.append(getTableName());
136 }
137 sb.append(getWhere());
138 sb.append(getOrder());
139 return sb.toString();
140 }
141
142 }