Java tutorial
/** * Copyright 2005-2014 Restlet * * The contents of this file are subject to the terms of one of the following * open source licenses: Apache 2.0 or LGPL 3.0 or LGPL 2.1 or CDDL 1.0 or EPL * 1.0 (the "Licenses"). You can select the license that you prefer but you may * not use this file except in compliance with one of these Licenses. * * You can obtain a copy of the Apache 2.0 license at * http://www.opensource.org/licenses/apache-2.0 * * You can obtain a copy of the LGPL 3.0 license at * http://www.opensource.org/licenses/lgpl-3.0 * * You can obtain a copy of the LGPL 2.1 license at * http://www.opensource.org/licenses/lgpl-2.1 * * You can obtain a copy of the CDDL 1.0 license at * http://www.opensource.org/licenses/cddl1 * * You can obtain a copy of the EPL 1.0 license at * http://www.opensource.org/licenses/eclipse-1.0 * * See the Licenses for the specific language governing permissions and * limitations under the Licenses. * * Alternatively, you can obtain a royalty free commercial license with less * limitations, transferable or non-transferable, directly at * http://restlet.com/products/restlet-framework * * Restlet is a registered trademark of Restlet S.A.S. */ package org.restlet.ext.jdbc; import java.io.IOException; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.List; import java.util.Properties; import java.util.logging.Level; import javax.xml.parsers.DocumentBuilder; import javax.xml.parsers.DocumentBuilderFactory; import javax.xml.parsers.ParserConfigurationException; import org.apache.commons.dbcp.ConnectionFactory; import org.apache.commons.dbcp.DriverManagerConnectionFactory; import org.apache.commons.dbcp.PoolableConnectionFactory; import org.apache.commons.pool.ObjectPool; import org.apache.commons.pool.impl.GenericObjectPool; import org.restlet.Client; import org.restlet.Request; import org.restlet.Response; import org.restlet.data.Method; import org.restlet.data.Protocol; import org.restlet.data.Status; import org.restlet.engine.Engine; import org.restlet.engine.connector.ClientHelper; import org.restlet.ext.jdbc.internal.ConnectionSource; import org.restlet.representation.Representation; import org.w3c.dom.Document; import org.w3c.dom.Element; import org.w3c.dom.Node; import org.w3c.dom.NodeList; import org.xml.sax.InputSource; import org.xml.sax.SAXException; /** * Client connector to a JDBC database. To send a request to the server, create * a new instance of a client supporting the JDBC Protocol and invoke the * handle() method. Alternatively, you can create a new {@link Request} with the * JDBC URI as the resource reference and use an XML request as the entity. * <p> * Database connections are optionally pooled using Apache Commons DBCP. In this * case, a different connection pool is created for each unique combination of * JDBC URI and connection properties. * <p> * Paging is supported via two header elements: "start" for the index of the * first result (0 by default) and "limit" for the maximum number of results * retrieved (unlimited by default). * <p> * Do not forget to register your JDBC drivers before using this client. See * {@link DriverManager} for details. * <p> * Sample XML request:<br> * <br> * {@code <?xml version="1.0" encoding="ISO-8859-1" ?>}<br> * {@code <request>}<br> * {@code <header>}<br> * {@code <connection>}<br> * {@code <usePooling>true</usePooling>}<br> * {@code <property * name="user">scott</property >}<br> * {@code <property * name="password">tiger</property >}<br> * {@code <property * name="...">1234</property >}<br> * {@code <property * name="...">true</property >}<br> * {@code </connection>}<br> * {@code <start>10</start>}<br> * {@code <limit>20</limit>}<br> * * {@code <returnGeneratedKeys>true</returnGeneratedKeys>}<br> * {@code </header>}<br> * {@code <body>}<br> * {@code <statement>UPDATE myTable SET * myField1="value1" </statement>}<br> * {@code <statement>SELECT msField1, myField2 FROM * myTable</statement>}<br> * {@code </body>}<br> * {@code </request>} * <p> * Several SQL Statements can be specified. A {@link RowSetRepresentation} of * the last correctly executed SQL request is returned to the Client. * * @see org.restlet.ext.jdbc.RowSetRepresentation * * @author Jerome Louvel * @author Thierry Boileau */ public class JdbcClientHelper extends ClientHelper { /** * Creates an uniform call. * * @param jdbcURI * The database's JDBC URI (ex: * jdbc:mysql://[hostname]/[database]). * @param request * The request to send (valid XML request). */ public static Request create(String jdbcURI, Representation request) { Request result = new Request(); result.getClientInfo().setAgent(Engine.VERSION_HEADER); result.setMethod(Method.POST); result.setResourceRef(jdbcURI); result.setEntity(request); return result; } /** * Creates a connection pool for a given connection configuration. * * @param uri * The connection URI. * @param properties * The connection properties. * @return The new connection pool. */ public static ObjectPool createConnectionPool(String uri, Properties properties) { // Create an ObjectPool that will serve as the actual pool of // connections ObjectPool result = new GenericObjectPool(null); // Create a ConnectionFactory that the pool will use to create // Connections ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(uri, properties); // Create the PoolableConnectionFactory, which wraps the "real" // Connections created by the ConnectionFactory with // the classes that implement the pooling functionality. PoolableConnectionFactory poolableConnectionFactory = new PoolableConnectionFactory(connectionFactory, result, null, null, false, false); // To remove warnings poolableConnectionFactory.getPool(); return result; } /** * Escapes quotes in a SQL query. * * @param query * The SQL query to escape. * @return The escaped SQL query. */ public static String sqlEncode(String query) { StringBuilder result = new StringBuilder(query.length() + 10); char currentChar; for (int i = 0; i < query.length(); i++) { currentChar = query.charAt(i); if (currentChar == '\'') { result.append("''"); } else { result.append(currentChar); } } return result.toString(); } /** Map of connection factories. */ private volatile List<ConnectionSource> connectionSources; /** * Constructor. * * @param client * The client to help. */ public JdbcClientHelper(Client client) { super(client); getProtocols().add(Protocol.JDBC); // Set up the list of factories this.connectionSources = new ArrayList<ConnectionSource>(); } /** * Returns a JDBC connection. * * @param uri * The connection URI. * @param properties * The connection properties. * @param usePooling * Indicates if the connection pooling should be used. * @return The JDBC connection. * @throws SQLException */ protected Connection getConnection(String uri, Properties properties, boolean usePooling) throws SQLException { Connection result = null; if (usePooling) { for (ConnectionSource c : this.connectionSources) { // Check if the connection URI is identical // and if the same number of properties is present if ((result == null) && c.getUri().equalsIgnoreCase(uri) && (properties.size() == c.getProperties().size())) { // Check that the properties tables are equivalent boolean equal = true; for (Object key : c.getProperties().keySet()) { if (equal && properties.containsKey(key)) { equal = equal && (properties.get(key).equals(c.getProperties().get(key))); } else { equal = false; } } if (equal) { result = c.getConnection(); } } } if (result == null) { // No existing connection source found ConnectionSource cs = new ConnectionSource(uri, properties); this.connectionSources.add(cs); result = cs.getConnection(); } } else { result = DriverManager.getConnection(uri, properties); } return result; } /** * Handles a call. * * @param request * The request to handle. * @param response * The response to update. */ @Override public void handle(Request request, Response response) { Connection connection = null; if (request.getMethod().equals(Method.POST)) { try { // Parse the JDBC URI String connectionURI = request.getResourceRef().toString(); // Parse the request to extract necessary info DocumentBuilder docBuilder = DocumentBuilderFactory.newInstance().newDocumentBuilder(); Document requestDoc = docBuilder.parse(new InputSource(request.getEntity().getReader())); Element rootElt = (Element) requestDoc.getElementsByTagName("request").item(0); Element headerElt = (Element) rootElt.getElementsByTagName("header").item(0); Element connectionElt = (Element) headerElt.getElementsByTagName("connection").item(0); // Read the connection pooling setting Node usePoolingNode = connectionElt.getElementsByTagName("usePooling").item(0); boolean usePooling = usePoolingNode.getTextContent().equals("true") ? true : false; // Read the paging setting Node startNode = headerElt.getElementsByTagName("start").item(0); int start = startNode != null && startNode.getTextContent().trim().length() > 0 ? Integer.parseInt(startNode.getTextContent()) : 0; Node limitNode = headerElt.getElementsByTagName("limit").item(0); int limit = limitNode != null && limitNode.getTextContent().trim().length() > 0 ? Integer.parseInt(limitNode.getTextContent()) : -1; // Read the connection properties NodeList propertyNodes = connectionElt.getElementsByTagName("property"); Node propertyNode = null; Properties properties = null; String name = null; String value = null; for (int i = 0; i < propertyNodes.getLength(); i++) { propertyNode = propertyNodes.item(i); if (properties == null) { properties = new Properties(); } name = propertyNode.getAttributes().getNamedItem("name").getTextContent(); value = propertyNode.getTextContent(); properties.setProperty(name, value); } Node returnGeneratedKeysNode = headerElt.getElementsByTagName("returnGeneratedKeys").item(0); boolean returnGeneratedKeys = returnGeneratedKeysNode.getTextContent().equals("true") ? true : false; // Read the SQL body and get the list of sql statements Element bodyElt = (Element) rootElt.getElementsByTagName("body").item(0); NodeList statementNodes = bodyElt.getElementsByTagName("statement"); List<String> sqlRequests = new ArrayList<String>(); for (int i = 0; i < statementNodes.getLength(); i++) { String sqlRequest = statementNodes.item(i).getTextContent(); sqlRequests.add(sqlRequest); } // Execute the List of SQL requests connection = getConnection(connectionURI, properties, usePooling); JdbcResult result = handleSqlRequests(connection, returnGeneratedKeys, sqlRequests); response.setEntity(new RowSetRepresentation(result, start, limit)); } catch (SQLException se) { getLogger().log(Level.WARNING, "Error while processing the SQL request", se); response.setStatus(Status.SERVER_ERROR_INTERNAL, se); } catch (ParserConfigurationException pce) { getLogger().log(Level.WARNING, "Error with XML parser configuration", pce); response.setStatus(Status.CLIENT_ERROR_BAD_REQUEST, pce); } catch (SAXException se) { getLogger().log(Level.WARNING, "Error while parsing the XML document", se); response.setStatus(Status.CLIENT_ERROR_BAD_REQUEST, se); } catch (IOException ioe) { getLogger().log(Level.WARNING, "Input/Output exception", ioe); response.setStatus(Status.SERVER_ERROR_INTERNAL, ioe); } } else { throw new IllegalArgumentException("Only the POST method is supported"); } } /** * Helper * * @param connection * @param returnGeneratedKeys * @param sqlRequests * @return the result of the last executed SQL request */ private JdbcResult handleSqlRequests(Connection connection, boolean returnGeneratedKeys, List<String> sqlRequests) { JdbcResult result = null; try { connection.setAutoCommit(true); Statement statement = connection.createStatement(); for (String sqlRequest : sqlRequests) { statement.execute(sqlRequest, returnGeneratedKeys ? Statement.RETURN_GENERATED_KEYS : Statement.NO_GENERATED_KEYS); result = new JdbcResult(statement); } // Commit any changes to the database if (!connection.getAutoCommit()) { connection.commit(); } } catch (SQLException se) { getLogger().log(Level.WARNING, "Error while processing the SQL requests", se); try { if (!connection.getAutoCommit()) { connection.rollback(); } } catch (SQLException se2) { getLogger().log(Level.WARNING, "Error while rollbacking the transaction", se); } } return result; } }