1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19 package net.sf.jameleon.data;
20
21 import java.io.IOException;
22 import java.sql.*;
23 import java.util.ArrayList;
24 import java.util.HashMap;
25 import java.util.List;
26 import java.util.Map;
27
28
29 /***
30 * Am implementation of @{link DataDriver} for SQL.
31 */
32 public class SqlDataDriver implements DataDriver {
33 protected String url;
34 protected String jdbcDriver;
35 protected String username;
36 protected String password;
37 protected String query;
38
39 protected Connection conn;
40 protected List keys;
41 protected Statement stmt;
42 protected ResultSet rs;
43
44 /***
45 * Default construtor. After calling this constructor,
46 * The Following will Need to be set.
47 * {@link #setJDBCDriver(java.lang.String)}
48 * {@link #setJDBCUrl(java.lang.String)}
49 */
50 public SqlDataDriver(){
51 keys = new ArrayList();
52 }
53
54 /***
55 * Sets up all required fields.
56 * @param url - the jdbc url used to connect to the database
57 * @param jdbcDriver - the jdbc driver used to connect to the database
58 * @param username - the username used to connect to the database
59 * @param password - the password used to connect to the database
60 */
61 public SqlDataDriver(String url, String jdbcDriver, String username, String password){
62 this();
63 this.url = url;
64 this.jdbcDriver = jdbcDriver;
65 this.username = username;
66 this.password = password;
67 }
68
69 /***
70 * Closes the handle to the data source
71 */
72 public void close() {
73 try {
74 if (rs != null ) rs.close();
75 rs = null;
76 if (stmt != null ) stmt.close();
77 stmt = null;
78 } catch (SQLException sqle) {
79
80 }
81 finally {
82 try {
83 if ( conn != null ) conn.close();
84 conn = null;
85 } catch (SQLException sqle) {
86
87 }
88 }
89 keys.clear();
90 }
91
92 /***
93 * Opens the handle to the data source
94 * @throws IOException when the data source can not be found.
95 */
96 public void open() throws IOException {
97 validate();
98 registerJDBCDriver();
99 try{
100 conn = DriverManager.getConnection(url, username, password);
101 }catch (SQLException sqle){
102 throw new IOException("Could not create connection to the database located at '"+url+"'!");
103 }
104 }
105
106 protected void validate() {
107 if (url == null || url.trim().length() == 0) {
108 throw new NullPointerException("'jdbcUrl' is a required field!");
109 }
110 if (jdbcDriver == null || jdbcDriver.trim().length() == 0) {
111 throw new NullPointerException("'jdbcDriver' is a required field!");
112 }
113 if (username == null || username.trim().length() == 0) {
114 throw new NullPointerException("'jdbcUsername' is a required field!");
115 }
116 }
117
118 protected void registerJDBCDriver() throws IOException {
119 try{
120 Class.forName(jdbcDriver).newInstance();
121 }catch(Exception cnfe){
122 throw new IOException("could not register database driver. Class '" + jdbcDriver +"' not found");
123 }
124 }
125
126 protected void readRecord() throws SQLException{
127 if (rs == null) {
128 try {
129 executeQuery ();
130 } catch ( SQLException sqle ) {
131 throw new SQLException ("Unable to Execute Query - " + sqle );
132 }
133
134
135 if (keys == null || keys.size() == 0) {
136 setKeys();
137 }
138 }
139 }
140
141
142 /***
143 * Executes a query against the database once a valid connection has been established.
144 * @throws SQLException if there is no query defined.
145 */
146 public void executeQuery () throws SQLException {
147 if ( query == null ) {
148 throw new SQLException ("No Query is defined to be executed.");
149 }
150 try {
151 stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
152 rs = stmt.executeQuery(query);
153 } catch ( SQLException sqle ) {
154 throw new SQLException ("Failed to execute SQL statement. Possibly due to invalid SQL. -- " + sqle);
155 }
156 }
157
158 /***
159 * Gets MetaData from the result set and builds an ArrayList of the Column headers
160 */
161 protected void setKeys () {
162 String colName;
163 try {
164
165 for (int x = 1; x <= rs.getMetaData().getColumnCount(); x++) {
166 colName = rs.getMetaData().getColumnName(x);
167 keys.add ( colName );
168 }
169 } catch ( SQLException sqle ) {
170 System.out.println("Unable to get ResultSet MetaData - " + sqle );
171 }
172
173 }
174
175
176 /***
177 * Gets the next row from the data source
178 * @return a key-value HashMap representing the data row or null if
179 * no data row is available
180 */
181 public Map getNextRow() {
182 Map vars = null;
183
184 try {
185 readRecord();
186 if ( rs.next() ) {
187 vars = new HashMap();
188 String key, value;
189 for (int x = 1; x <= keys.size(); x++ ) {
190 key = (String)keys.get(x-1);
191 value = null;
192 try{
193 int columnType = rs.getMetaData().getColumnType(x);
194
195 if (columnType == Types.TIMESTAMP ||
196 columnType == Types.DATE) {
197 Timestamp ts = rs.getTimestamp(x);
198 if (ts != null) {
199 value = ts.toString();
200 }
201 }else{
202 value = rs.getString(x);
203 }
204 }catch (SQLException sqle){
205 System.err.println ( "Unable to get Next Row " + sqle );
206 sqle.printStackTrace();
207 }
208 vars.put( key, value );
209 }
210 }
211 } catch ( SQLException sqle ) {
212 System.err.println ( "Unable to get Next Row " + sqle );
213 }
214 return vars;
215 }
216
217 /***
218 * Tells whether the data source has another row
219 * @return true if the data source still has more rows
220 */
221 public boolean hasMoreRows() {
222 boolean moreRows = false;
223 try {
224 readRecord();
225
226 moreRows = !(rs.isAfterLast() || rs.isLast());
227 } catch ( SQLException sqle ) {
228 System.err.println("Unable to process hasMoreRows()");
229 sqle.printStackTrace();
230 }
231
232 return moreRows;
233 }
234
235 /***
236 * Sets the JDBC Driver.
237 */
238 public void setJDBCDriver (String jdbcDriver) {
239 this.jdbcDriver = jdbcDriver;
240 }
241
242 /***
243 * Sets the JDBC url.
244 */
245 public void setJDBCUrl (String url){
246 this.url = url;
247 }
248
249 /***
250 * Sets the JDBC username.
251 */
252 public void setJDBCUsername (String username) {
253 this.username = username;
254 }
255
256 /***
257 * Sets the JDBC password.
258 */
259 public void setJDBCPassword (String password) {
260 this.password = password;
261 }
262
263 /***
264 * Sets the SQL Query.
265 */
266 public void setQuery (String query) {
267 this.query = query;
268 }
269
270
271 }