Thread: OT: easiest way to create a custom PreparedStatement class

I am modifying some existing data loading code (that only makes use of 4
or 5 methods from PreparedStatement) to optionally be able to *not*
connect to a database but instead dump either the generated SQL code or
the raw insert data to files on the file system.

It would be great if I could just overload PreparedStatement with my own
classes, but unfortunately it is an interface, so I presumably need to
implement stubs for the umpty-ump methods defined by PreparedStatement?

Is this true, and if so, what is the easiest/fastest way to do this (if
it is even possible)?

I am a java newbie, by the way.  Don't flame me too hard.  I did say
"OT", after all ;-)

Thanks,
Kevin

Or you could use:

java.lang.reflect.Proxy




On Tuesday 28 June 2005 01:55 pm, Kevin Murphy wrote:
> I am modifying some existing data loading code (that only makes use of 4
> or 5 methods from PreparedStatement) to optionally be able to *not*
> connect to a database but instead dump either the generated SQL code or
> the raw insert data to files on the file system.
>
> It would be great if I could just overload PreparedStatement with my own
> classes, but unfortunately it is an interface, so I presumably need to
> implement stubs for the umpty-ump methods defined by PreparedStatement?
>
> Is this true, and if so, what is the easiest/fastest way to do this (if
> it is even possible)?
>
> I am a java newbie, by the way.  Don't flame me too hard.  I did say
> "OT", after all ;-)
>
> Thanks,
> Kevin
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly

Or you could use:

java.lang.reflect.Proxy


On Tuesday 28 June 2005 01:55 pm, Kevin Murphy wrote:
> I am modifying some existing data loading code (that only makes use of 4
> or 5 methods from PreparedStatement) to optionally be able to *not*
> connect to a database but instead dump either the generated SQL code or
> the raw insert data to files on the file system.
>
> It would be great if I could just overload PreparedStatement with my own
> classes, but unfortunately it is an interface, so I presumably need to
> implement stubs for the umpty-ump methods defined by PreparedStatement?
>
> Is this true, and if so, what is the easiest/fastest way to do this (if
> it is even possible)?
>
> I am a java newbie, by the way.  Don't flame me too hard.  I did say
> "OT", after all ;-)
>
> Thanks,
> Kevin
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly

We use java.lang.reflect.Proxy in our production systems to do things like log SQL statements, do a little bookkeeping for application-level replication, log timings and the like.  Works great.

Here's a snippet that we use to wrap a PreparedStatement with a proxy to do some logging every time somebody calls any method on it.

-- Mark Lewis


         final PreparedStatement realStatement = xxx;

        PreparedStatement debugWrapped = (PreparedStatement)Proxy.newProxyInstance(getClass().getClassLoader(), new Class[]{PreparedStatement.class}, new InvocationHandler() {
            public Object invoke(Object instance, Method method, Object [] params) throws Throwable {
            StringBuffer buf = new StringBuffer();
            buf.append("Called ").append(method.getName());
            buf.append('(');
            if(params != null) {
            for(int i=0; i<params.length; i++) {
            if(i > 0) buf.append(',');
            buf.append(String.valueOf(params[i]));
            }
            }
            buf.append(')');
            log.debug(buf);
            try {
            return method.invoke(realStatement, params);
            }
            catch(InvocationTargetException ex) {
            throw ex.getCause();
            }
            }
            });




On Tue, 2005-06-28 at 14:55 -0500, Craig Servin wrote:

Or you could use:

java.lang.reflect.Proxy


On Tuesday 28 June 2005 01:55 pm, Kevin Murphy wrote:
> I am modifying some existing data loading code (that only makes use of 4
> or 5 methods from PreparedStatement) to optionally be able to *not*
> connect to a database but instead dump either the generated SQL code or
> the raw insert data to files on the file system.
>
> It would be great if I could just overload PreparedStatement with my own
> classes, but unfortunately it is an interface, so I presumably need to
> implement stubs for the umpty-ump methods defined by PreparedStatement?
>
> Is this true, and if so, what is the easiest/fastest way to do this (if
> it is even possible)?
>
> I am a java newbie, by the way.  Don't flame me too hard.  I did say
> "OT", after all ;-)
>
> Thanks,
> Kevin
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

In the context of my current project of writing code that can 1) access
a database directly, 2) write sql statements to a file instead of
executing them, and also, 3) write raw data for insert to flat files:

it would be nice if the JDBC driver could somehow expose the
functionality to do the quoting and escaping required in steps 2 and 3
without ever requiring a database connection.

I know it's not the driver's main line of business, but since it is able
to do these things anyway (in PreparedStatement.toString), and they are
useful but non-trivial to do properly, why not expose the functionality?

I know very little about java and JDBC, so maybe this doesn't make sense.

BTW, thanks to Craig Servin and Mark Lewis for turning me on to
java.lang.reflect.Proxy.

Kevin Murphy

Kevin Murphy wrote:

> it would be nice if the JDBC driver could somehow expose the
> functionality to do the quoting and escaping required in steps 2 and 3
> without ever requiring a database connection.
>
> I know it's not the driver's main line of business, but since it is able
> to do these things anyway (in PreparedStatement.toString), and they are
> useful but non-trivial to do properly, why not expose the functionality?

Uh, PreparedStatement.toString() *doesn't* necessarily produce correctly
quoted queries; the PostgreSQL driver implementation produces a query
string by blindly substituting parameter values with no escaping. It's
more of a debugging tool than anything else. At the protocol level, the
driver isn't escaping strings anyway because they're being passed
separately to the query in a Bind message rather than embedded in the
query string itself.

Also, the plans for post-8.1 string escaping mean that you will need to
know what version of the server you are talking to (or something
equivalent via a read-only GUC) to know which type of escaping is
correct.. it's hard to see how that works without a database connection.

-O