Re: query manipulation

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