Re: query manipulation - Mailing list pgsql-jdbc

Turns out I was on the right track here.  Got to trust my intuition
sometimes.  It was merely a matter of changing this:

     if(method.getReturnType().isInstance(PreparedStatement.class)) {

to this:

     if(method.getReturnType() == PreparedStatement.class) {

and altering args.

Thanks to everyone and especially Mark for the code contribution.

-Randall

Randall Smith wrote:
> Mark Lewis wrote:
>> Randall,
>>
>> I happen to have an example right here that might be useful for you.
>> The attached class will wrap a driver (in this case hard-coded to
>> "org.postgresql.Driver") and transparently implement prepared statement
>> caching.  Shouldn't be too hard to adapt it to your case.
>>
>> Disclaimer: this is for demonstration only, not a production-quality
>> implementation.  Don't use this in your product without reading through
>> it, understanding the limitations and adding some sanity checking.
>> Also, this is based on a real class we use for testing here, but the
>> process of simplifying it for the list may have broken stuff.
>>
>> -- Mark
>>
>> On Mon, 2007-03-19 at 16:37 -0400, Dave Cramer wrote:
>>> Randall,
>>>
>>> Yeah, you would have to wrap the jdbc driver with a proxy driver.
>>> It's not that hard to write one.
>>>
>>> Dave
>>> On 19-Mar-07, at 4:19 PM, Randall Smith wrote:
>>>
>>>> I'm trying to find a way to manipulate some poorly written queries
>>>> in which I don't have access to the application source.  I can't do
>>>> it at the Postgresql server, so I'm thinking of attacking it at the
>>>> jdbc driver.
>>>>
>>>> I was thinking of a wrapper that would look at and alter the SQL
>>>> before it went to the server.  Anyone know of something like this?
>>>> Specifically, I'm trying to drop "FOR UPDATE column name" off of
>>>> some queries.  They were written for Oracle, but Postgresql uses
>>>> "FOR UPDATE table name".  I'm not a Java programmer, but I won't
>>>> let that stop me if it is required.
>>>>
>>>> Randall
>>>>
>>>>
>>>> ---------------------------(end of
>>>> broadcast)---------------------------
>>>> TIP 7: You can help support the PostgreSQL project by donating at
>>>>
>>>>                http://www.postgresql.org/about/donate
>>>>
>>>
>>> ---------------------------(end of broadcast)---------------------------
>>> TIP 6: explain analyze is your friend
>>>
>>> ------------------------------------------------------------------------
>>>
>>> package com.mir3.sawtooth.service.piimpl;
>>>
>>> import java.lang.reflect.InvocationHandler;
>>> import java.lang.reflect.InvocationTargetException;
>>> import java.lang.reflect.Method;
>>> import java.lang.reflect.Proxy;
>>> import java.sql.Connection;
>>> import java.sql.Driver;
>>> import java.sql.DriverManager;
>>> import java.sql.DriverPropertyInfo;
>>> import java.sql.PreparedStatement;
>>> import java.sql.SQLException;
>>> import java.util.Enumeration;
>>> import java.util.Properties;
>>>
>>> import org.apache.commons.collections.LRUMap;
>>>
>>> /**
>>>  * Wraps a real JDBC driver to implement prepared statement pooling.
>>>  * @author Mark Lewis
>>>  * Mar 19, 2007
>>>  */
>>> public class PoolingDriver implements Driver {
>>>
>>>     public PoolingDriver(Driver realDriver) {
>>>         this.realDriver = realDriver;
>>>     }
>>>
>>>     public Connection connect(String url, Properties info) throws
>>> SQLException {
>>>         return wrap(realDriver.connect(url, info));
>>>     }
>>>
>>>     public boolean acceptsURL(String url) throws SQLException {
>>>         return realDriver.acceptsURL(url);
>>>     }
>>>
>>>     public DriverPropertyInfo[] getPropertyInfo(String url,
>>> Properties info) throws SQLException {
>>>         return realDriver.getPropertyInfo(url, info);
>>>     }
>>>
>>>     public int getMajorVersion() {
>>>         return realDriver.getMajorVersion();
>>>     }
>>>
>>>     public int getMinorVersion() {
>>>         return realDriver.getMinorVersion();
>>>     }
>>>
>>>     public boolean jdbcCompliant() {
>>>         return realDriver.jdbcCompliant();
>>>     }
>>>
>>>     private Connection wrap(final Connection con) {
>>>         ClassLoader loader = getClass().getClassLoader();
>>>         return (Connection)Proxy.newProxyInstance(loader, new
>>> Class[]{Connection.class}, new InvocationHandler() {
>>>             public Object invoke(Object proxy, Method method,
>>> Object[] args) throws Throwable {
>>>                 try {
>>>
>>> if(method.getReturnType().isInstance(PreparedStatement.class)) {
>>>                         String sql = (String)args[0];
>>>                         PreparedStatement ps = (PreparedStatement)
>>> statementCache.get(sql);
>>>                         if(ps == null) {
>>>                             ps = (PreparedStatement)
>>> method.invoke(con, args);
>>>                             statementCache.put(sql, ps);
>>>                         }
>>>                         return ps;
>>>                     }
>>>                     else {
>>>                         return method.invoke(con, args);
>>>                     }
>>>                 }
>>>                 catch(InvocationTargetException ex) {
>>>                     throw ex.getCause();
>>>                 }
>>>             }
>>>
>>>             private LRUMap statementCache = new LRUMap(100);
>>>         });
>>>     }
>>>
>>>     private Driver realDriver;
>>>
>>>     static {
>>>         try {
>>>             // Load the real class, then deregister its driver and
>>> register mine in its place.
>>>             Class realClass = Class.forName("org.postgresql.Driver");
>>>             Driver realDriver = null;
>>>             for(Enumeration e=DriverManager.getDrivers();
>>> e.hasMoreElements(); ) {
>>>                 Driver d = (Driver) e.nextElement();
>>>                 if(realClass.isInstance(d)) {
>>>                     realDriver = d;
>>>                     DriverManager.deregisterDriver(d);
>>>                     break;
>>>                 }
>>>             }
>>>             PoolingDriver driver = new PoolingDriver(realDriver);
>>>             DriverManager.registerDriver(driver);
>>>         }
>>>         catch (Exception ex) {
>>>             ex.printStackTrace();
>>>         }
>>>     }
>>> }
>>>
>>> ------------------------------------------------------------------------
>>>
>>>
>>> ---------------------------(end of broadcast)---------------------------
>>> TIP 5: don't forget to increase your free space map settings
>
> This particular statement:
>
>     if(method.getReturnType().isInstance(PreparedStatement.class))
>
> is returning false for my prepared statement.  I'm printing the type
> like this:
>
>     System.out.println(method.getReturnType());
>
> and it shows type type is:
>
>     interface java.sql.PreparedStatement
>
> I'm using Jython to test, and my test program looks like so:
>
> from java.lang import *
> from java.sql import *
> Class.forName("randall.PGProxyDriver")
> con = DriverManager.getConnection('jdbc:postgresql:mydb','myuser','mypass')
> stmt = con.prepareStatement('select * from mytablename where myfield = ?')
> stmt.setString(1, myvalue)
> rs = stmt.executeQuery()
>
> How do I match "interface java.sql.PreparedStatement" ?
>
> Randall
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>               http://archives.postgresql.org
>