001: /*
002: * Copyright 2005-2007 Noelios Consulting.
003: *
004: * The contents of this file are subject to the terms of the Common Development
005: * and Distribution License (the "License"). You may not use this file except in
006: * compliance with the License.
007: *
008: * You can obtain a copy of the license at
009: * http://www.opensource.org/licenses/cddl1.txt See the License for the specific
010: * language governing permissions and limitations under the License.
011: *
012: * When distributing Covered Code, include this CDDL HEADER in each file and
013: * include the License file at http://www.opensource.org/licenses/cddl1.txt If
014: * applicable, add the following below this CDDL HEADER, with the fields
015: * enclosed by brackets "[]" replaced with your own identifying information:
016: * Portions Copyright [yyyy] [name of copyright owner]
017: */
018:
019: package com.noelios.restlet.ext.jdbc;
020:
021: import java.io.IOException;
022: import java.sql.Connection;
023: import java.sql.DriverManager;
024: import java.sql.SQLException;
025: import java.sql.Statement;
026: import java.util.ArrayList;
027: import java.util.List;
028: import java.util.Properties;
029: import java.util.logging.Level;
030:
031: import javax.xml.parsers.DocumentBuilder;
032: import javax.xml.parsers.DocumentBuilderFactory;
033: import javax.xml.parsers.ParserConfigurationException;
034:
035: import org.apache.commons.dbcp.ConnectionFactory;
036: import org.apache.commons.dbcp.DriverManagerConnectionFactory;
037: import org.apache.commons.dbcp.PoolableConnectionFactory;
038: import org.apache.commons.dbcp.PoolingDataSource;
039: import org.apache.commons.pool.ObjectPool;
040: import org.apache.commons.pool.impl.GenericObjectPool;
041: import org.restlet.Client;
042: import org.restlet.data.Method;
043: import org.restlet.data.Protocol;
044: import org.restlet.data.Request;
045: import org.restlet.data.Response;
046: import org.restlet.data.Status;
047: import org.restlet.resource.Representation;
048: import org.w3c.dom.Document;
049: import org.w3c.dom.Element;
050: import org.w3c.dom.Node;
051: import org.w3c.dom.NodeList;
052: import org.xml.sax.SAXException;
053:
054: import com.noelios.restlet.ClientHelper;
055: import com.noelios.restlet.Engine;
056:
057: /**
058: * Client connector to a JDBC database.<br/> To send a request to the server,
059: * create a new instance of a client supporting the JDBC Protocol and invoke the
060: * handle() method.<br/> Alternatively, you can create a new Call with the JDBC
061: * URI as the resource reference and use an XML request as the entity.<br/><br/>
062: * Database connections are optionally pooled using Apache Commons DBCP. In this
063: * case, a different connection pool is created for each unique combination of
064: * JDBC URI and connection properties.<br/><br/> Do not forget to register
065: * your JDBC drivers before using this client. See <a
066: * href="http://java.sun.com/j2se/1.5.0/docs/api/java/sql/DriverManager.html">
067: * JDBC DriverManager API</a> for details<br/><br/> Sample XML request:<br/><br/>
068: * {@code <?xml version="1.0" encoding="ISO-8859-1" ?>}<br/> {@code <request>}<br/>
069: * {@code <header>}<br/> {@code <connection>}<br/>
070: * {@code <usePooling>true</usePooling>}<br/>
071: * {@code <property name="user">scott</property >}<br/>
072: * {@code <property name="password">tiger</property >}<br/>
073: * {@code <property name="...">1234</property >}<br/>
074: * {@code <property name="...">true</property >}<br/>
075: * {@code </connection>}<br/> {@code <returnGeneratedKeys>true</returnGeneratedKeys>}<br/>
076: * {@code </header>}<br/> {@code <body>}<br/>
077: * {@code <statement>UPDATE myTable SET myField1="value1" </statement>}<br/>
078: * {@code <statement>SELECT msField1, myField2 FROM myTable</statement>}<br/>
079: * {@code </body>}<br/> {@code </request>}<br/><br/>Several SQL
080: * Statements can be specified.<br/> A RowSetRepresentation of the last
081: * correctly executed SQL request is returned to the Client.</br>
082: *
083: * @see com.noelios.restlet.ext.jdbc.RowSetRepresentation
084: *
085: * @author Jerome Louvel (contact@noelios.com)
086: * @author Thierry Boileau
087: */
088: public class JdbcClientHelper extends ClientHelper {
089: /** Map of connection factories. */
090: private List<ConnectionSource> connectionSources;
091:
092: /**
093: * Constructor.
094: *
095: * @param client
096: * The client to help.
097: */
098: public JdbcClientHelper(Client client) {
099: super (client);
100:
101: getProtocols().add(Protocol.JDBC);
102:
103: // Set up the list of factories
104: this .connectionSources = new ArrayList<ConnectionSource>();
105: }
106:
107: /**
108: * Creates an uniform call.
109: *
110: * @param jdbcURI
111: * The database's JDBC URI (ex:
112: * jdbc:mysql://[hostname]/[database]).
113: * @param request
114: * The request to send (valid XML request).
115: */
116: public static Request create(String jdbcURI, Representation request) {
117: Request result = new Request();
118: result.getClientInfo().setAgent(Engine.VERSION_HEADER);
119: result.setMethod(Method.POST);
120: result.setResourceRef(jdbcURI);
121: result.setEntity(request);
122: return result;
123: }
124:
125: /**
126: * Handles a call.
127: *
128: * @param request
129: * The request to handle.
130: * @param response
131: * The response to update.
132: */
133: public void handle(Request request, Response response) {
134: Connection connection = null;
135:
136: if (request.getMethod().equals(Method.POST)) {
137: try {
138: // Parse the JDBC URI
139: String connectionURI = request.getResourceRef()
140: .toString();
141:
142: // Parse the request to extract necessary info
143: DocumentBuilder docBuilder = DocumentBuilderFactory
144: .newInstance().newDocumentBuilder();
145: Document requestDoc = docBuilder.parse(request
146: .getEntity().getStream());
147:
148: Element rootElt = (Element) requestDoc
149: .getElementsByTagName("request").item(0);
150: Element headerElt = (Element) rootElt
151: .getElementsByTagName("header").item(0);
152: Element connectionElt = (Element) headerElt
153: .getElementsByTagName("connection").item(0);
154:
155: // Read the connection pooling setting
156: Node usePoolingNode = connectionElt
157: .getElementsByTagName("usePooling").item(0);
158: boolean usePooling = usePoolingNode.getTextContent()
159: .equals("true") ? true : false;
160:
161: // Read the connection properties
162: NodeList propertyNodes = connectionElt
163: .getElementsByTagName("property");
164: Node propertyNode = null;
165: Properties properties = null;
166: String name = null;
167: String value = null;
168: for (int i = 0; i < propertyNodes.getLength(); i++) {
169: propertyNode = propertyNodes.item(i);
170:
171: if (properties == null)
172: properties = new Properties();
173: name = propertyNode.getAttributes().getNamedItem(
174: "name").getTextContent();
175: value = propertyNode.getTextContent();
176: properties.setProperty(name, value);
177: }
178:
179: Node returnGeneratedKeysNode = headerElt
180: .getElementsByTagName("returnGeneratedKeys")
181: .item(0);
182: boolean returnGeneratedKeys = returnGeneratedKeysNode
183: .getTextContent().equals("true") ? true : false;
184:
185: // Read the SQL body and get the list of sql statements
186: Element bodyElt = (Element) rootElt
187: .getElementsByTagName("body").item(0);
188: NodeList statementNodes = bodyElt
189: .getElementsByTagName("statement");
190: List<String> sqlRequests = new ArrayList<String>();
191: for (int i = 0; i < statementNodes.getLength(); i++) {
192: String sqlRequest = statementNodes.item(i)
193: .getTextContent();
194: sqlRequests.add(sqlRequest);
195: }
196:
197: // Execute the List of SQL requests
198: connection = getConnection(connectionURI, properties,
199: usePooling);
200: JdbcResult result = handleSqlRequests(connection,
201: returnGeneratedKeys, sqlRequests);
202: response.setEntity(new RowSetRepresentation(result));
203:
204: } catch (SQLException se) {
205: getLogger().log(Level.WARNING,
206: "Error while processing the SQL request", se);
207: response.setStatus(new Status(
208: Status.SERVER_ERROR_INTERNAL,
209: "Error while processing the SQL request"));
210: } catch (ParserConfigurationException pce) {
211: getLogger().log(Level.WARNING,
212: "Error with XML parser configuration", pce);
213: response.setStatus(new Status(
214: Status.CLIENT_ERROR_BAD_REQUEST,
215: "Error with XML parser configuration"));
216: } catch (SAXException se) {
217: getLogger().log(Level.WARNING,
218: "Error while parsing the XML document", se);
219: response.setStatus(new Status(
220: Status.CLIENT_ERROR_BAD_REQUEST,
221: "Error while parsing the XML document"));
222: } catch (IOException ioe) {
223: getLogger().log(Level.WARNING,
224: "Input/Output exception", ioe);
225: response.setStatus(new Status(
226: Status.SERVER_ERROR_INTERNAL,
227: "Input/Output exception"));
228: }
229: } else {
230: throw new IllegalArgumentException(
231: "Only the POST method is supported");
232: }
233: }
234:
235: /**
236: * Helper
237: *
238: * @param connection
239: * @param returnGeneratedKeys
240: * @param sqlRequests
241: * @return the result of the last executed SQL request
242: */
243: private JdbcResult handleSqlRequests(Connection connection,
244: boolean returnGeneratedKeys, List<String> sqlRequests) {
245: JdbcResult result = null;
246: try {
247: connection.setAutoCommit(true);
248: Statement statement = connection.createStatement();
249: for (String sqlRequest : sqlRequests) {
250: statement
251: .execute(
252: sqlRequest,
253: returnGeneratedKeys ? Statement.RETURN_GENERATED_KEYS
254: : Statement.NO_GENERATED_KEYS);
255: result = new JdbcResult(statement);
256: }
257:
258: // Commit any changes to the database
259: if (!connection.getAutoCommit()) {
260: connection.commit();
261: }
262: } catch (SQLException se) {
263: getLogger().log(Level.WARNING,
264: "Error while processing the SQL requests", se);
265: try {
266: if (!connection.getAutoCommit()) {
267: connection.rollback();
268: }
269: } catch (SQLException se2) {
270: getLogger().log(Level.WARNING,
271: "Error while rollbacking the transaction", se);
272: }
273: }
274: return result;
275:
276: }
277:
278: /**
279: * Returns a JDBC connection.
280: *
281: * @param uri
282: * The connection URI.
283: * @param properties
284: * The connection properties.
285: * @param usePooling
286: * Indicates if the connection pooling should be used.
287: * @return The JDBC connection.
288: * @throws SQLException
289: */
290: protected Connection getConnection(String uri,
291: Properties properties, boolean usePooling)
292: throws SQLException {
293: Connection result = null;
294:
295: if (usePooling) {
296: for (ConnectionSource c : connectionSources) {
297: // Check if the connection URI is identical
298: // and if the same number of properties is present
299: if ((result == null)
300: && c.getUri().equalsIgnoreCase(uri)
301: && (properties.size() == c.getProperties()
302: .size())) {
303: // Check that the properties tables are equivalent
304: boolean equal = true;
305: for (Object key : c.getProperties().keySet()) {
306: if (equal && properties.containsKey(key)) {
307: equal = equal
308: && (properties.get(key).equals(c
309: .getProperties().get(key)));
310: } else {
311: equal = false;
312: }
313: }
314:
315: if (equal) {
316: result = c.getConnection();
317: }
318: }
319: }
320:
321: if (result == null) {
322: // No existing connection source found
323: ConnectionSource cs = new ConnectionSource(uri,
324: properties);
325: this .connectionSources.add(cs);
326: result = cs.getConnection();
327: }
328: } else {
329: result = DriverManager.getConnection(uri, properties);
330: }
331:
332: return result;
333: }
334:
335: /**
336: * Escapes quotes in a SQL query.
337: *
338: * @param query
339: * The SQL query to escape.
340: * @return The escaped SQL query.
341: */
342: public static String sqlEncode(String query) {
343: StringBuilder result = new StringBuilder(query.length() + 10);
344: char currentChar;
345:
346: for (int i = 0; i < query.length(); i++) {
347: currentChar = query.charAt(i);
348: if (currentChar == '\'') {
349: result.append("''");
350: } else {
351: result.append(currentChar);
352: }
353: }
354:
355: return result.toString();
356: }
357:
358: /**
359: * Creates a connection pool for a given connection configuration.
360: *
361: * @param uri
362: * The connection URI.
363: * @param properties
364: * The connection properties.
365: * @return The new connection pool.
366: */
367: protected static ObjectPool createConnectionPool(String uri,
368: Properties properties) {
369: // Create an ObjectPool that will serve as the actual pool of
370: // connections
371: ObjectPool result = new GenericObjectPool(null);
372:
373: // Create a ConnectionFactory that the pool will use to create
374: // Connections
375: ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(
376: uri, properties);
377:
378: // Create the PoolableConnectionFactory, which wraps the "real"
379: // Connections created by the ConnectionFactory with
380: // the classes that implement the pooling functionality.
381: PoolableConnectionFactory poolableConnectionFactory = new PoolableConnectionFactory(
382: connectionFactory, result, null, null, false, false);
383:
384: // To remove warnings
385: poolableConnectionFactory.getPool();
386:
387: return result;
388: }
389:
390: /**
391: * Pooling data source which remembers its connection properties and URI.
392: */
393: private static class ConnectionSource extends PoolingDataSource {
394: /** The connection URI. */
395: protected String uri;
396:
397: /** The connection properties. */
398: protected Properties properties;
399:
400: /**
401: * Constructor.
402: *
403: * @param uri
404: * The connection URI.
405: * @param properties
406: * The connection properties.
407: */
408: public ConnectionSource(String uri, Properties properties) {
409: super (createConnectionPool(uri, properties));
410: this .uri = uri;
411: this .properties = properties;
412: }
413:
414: /**
415: * Returns the connection URI.
416: *
417: * @return The connection URI.
418: */
419: public String getUri() {
420: return uri;
421: }
422:
423: /**
424: * Returns the connection properties.
425: *
426: * @return The connection properties.
427: */
428: public Properties getProperties() {
429: return properties;
430: }
431: }
432: }
|