001: package fitnesse.responders.sql;
002:
003: import java.sql.Connection;
004: import java.sql.DriverManager;
005: import java.sql.ResultSet;
006: import java.sql.ResultSetMetaData;
007: import java.sql.SQLException;
008: import java.sql.SQLWarning;
009: import java.sql.Statement;
010: import java.util.HashMap;
011: import java.util.LinkedList;
012: import java.util.List;
013: import java.util.Map;
014:
015: /**
016: * Provides connection setup, statement execution, and results retrieval
017: * functionality to the various FIT SQL Fixtures (SQLConnectionFixture,
018: * SQLExecutionFixture, and SQLResultsFixture).
019: */
020: public class SQLState {
021: private static SQLState state;
022:
023: private List lastResults;
024:
025: private long timeInSeconds;
026:
027: private Connection dbConnection;
028:
029: private List messages;
030:
031: private List lastResultsColumns;
032:
033: private String userMessage;
034:
035: private Integer lastRowsAffected;
036:
037: public static SQLState getInstance() {
038: if (state == null) {
039: state = new SQLState();
040: }
041: return state;
042: }
043:
044: public void executeStatement(String sqlStatement)
045: throws SQLException {
046: final Statement dbStatement = createStatement();
047: final long startTime = System.currentTimeMillis();
048:
049: // Reset the results caches.
050: lastResults = null;
051: lastResultsColumns = null;
052: lastRowsAffected = null;
053: if (sqlStatement.toLowerCase().startsWith("select")) {
054: final ResultSet dbResultSet = dbStatement
055: .executeQuery(sqlStatement);
056: setResultsFromResultSet(dbResultSet);
057: dbResultSet.close();
058: userMessage = "Query returned " + getRows() + " rows.";
059: } else {
060: int rowsAffected = dbStatement.executeUpdate(sqlStatement);
061: lastRowsAffected = new Integer(rowsAffected);
062: userMessage = rowsAffected + " rows changed.";
063: }
064: setMessagesFromStatement(dbStatement);
065: dbStatement.close();
066: final long endTime = System.currentTimeMillis();
067: timeInSeconds = (endTime - startTime) / 1000L;
068: }
069:
070: private void setMessagesFromStatement(final Statement dbStatement)
071: throws SQLException {
072: messages = new LinkedList();
073: SQLWarning warning = dbStatement.getWarnings();
074: while (warning != null) {
075: messages.add(warning.getMessage());
076: warning = warning.getNextWarning();
077: }
078: }
079:
080: public void setResultsFromResultSet(ResultSet resultSet)
081: throws SQLException {
082: lastResults = new LinkedList();
083: lastResultsColumns = new LinkedList();
084: final ResultSetMetaData metaData = resultSet.getMetaData();
085: final int columnCount = metaData.getColumnCount();
086: for (int i = 1; i <= columnCount; i++) {
087: lastResultsColumns.add(metaData.getColumnName(i));
088: }
089: while (resultSet.next()) {
090: final Map row = new HashMap();
091: for (int i = 1; i <= columnCount; i++) {
092: row.put(metaData.getColumnName(i).toLowerCase(),
093: resultSet.getString(i));
094: }
095: lastResults.add(row);
096: }
097: }
098:
099: public List getResults() {
100: return lastResults;
101: }
102:
103: public List getMessages() {
104: return messages;
105: }
106:
107: public int getRows() {
108: if (lastResults == null) {
109: throw new IllegalStateException(
110: "Cannot get rows prior to executing an SQL query.");
111: } else {
112: return lastResults.size();
113: }
114: }
115:
116: public long getExecutionTime() {
117: return timeInSeconds;
118: }
119:
120: public String getAt(int row, String column) {
121: if (row >= getRows()) {
122: throw new IllegalArgumentException(
123: "Cannot retrieve a value from row " + row
124: + ": only " + getRows() + " rows present.");
125: }
126: Map rowMap = (Map) lastResults.get(row);
127: if (rowMap.containsKey(column.toLowerCase())) {
128: return (String) rowMap.get(column.toLowerCase());
129: } else {
130: throw new IllegalArgumentException("The column named '"
131: + column + "' was not found.");
132: }
133: }
134:
135: public String getResultsAsXml() {
136: StringBuffer asXml = new StringBuffer();
137: asXml.append("<?xml version=\"1.0\"?>");
138: if ((lastResults == null) && (lastRowsAffected == null)) {
139: asXml.append("<nopreviouscommand />");
140: return asXml.toString();
141: }
142: asXml.append("<sqlresults>");
143: asXml.append("<userMessage>" + userMessage + "</userMessage>");
144: if (lastResults != null) {
145: asXml.append("<rowcount>" + getRows() + "</rowcount>");
146: asXml.append("<rows>");
147: for (int i = 0; i < getRows(); i++) {
148: asXml.append("<row>");
149: for (int columnIndex = 0; columnIndex < lastResultsColumns
150: .size(); columnIndex++) {
151: String columnName = (String) lastResultsColumns
152: .get(columnIndex);
153: asXml
154: .append("<column name=\"" + columnName
155: + "\">");
156: asXml.append("<![CDATA[");
157: asXml.append(getAt(i, columnName));
158: asXml.append("]]>");
159: asXml.append("</column>");
160: }
161: asXml.append("</row>");
162: }
163: asXml.append("</rows>");
164: } else if (lastRowsAffected != null) {
165: asXml.append("<rowsAffected>" + lastRowsAffected
166: + "</rowsAffected>");
167: }
168: asXml.append("<messages>");
169: for (int i = 0; i < messages.size(); i++) {
170: asXml.append("<message>" + messages.get(i) + "</message>");
171: }
172: asXml.append("</messages>");
173: asXml.append("<executiontime>" + timeInSeconds
174: + "</executiontime>");
175: asXml.append("</sqlresults>");
176: return asXml.toString();
177: }
178:
179: public void setData(final String driver,
180: final String connectionString, final String username,
181: final String password) throws ClassNotFoundException,
182: SQLException {
183: Class.forName(driver);
184: dbConnection = DriverManager.getConnection(connectionString,
185: username, password);
186: }
187:
188: public Statement createStatement() throws SQLException {
189: return dbConnection.createStatement();
190: }
191: }
|