Working with intermittent database connections by writing / executing SQL Script From Scala / Java

Question

I am developing an application that should store information in a database. I would like to use a Scala solution if possible. If for some reason the database connection fails, I would like to write raw SQL statements that would be executed in the .sql script file. The idea is that when / if the connection to the database is restored, I would like to execute this script in Scala / Java to restore the database. It's also nice to have a .sql script around in the event of a program crash, so manual script execution may occur.

How to write sql statements that I am going to execute to a file in Scala / Java? Then, how can I execute this file (or really any .sql script) in Scala / Java?

Answer 1

you can proxy your Connection object:

public class ConnectionProxy { public ConnectionProxy(Object anObject) { super(anObject); } @Override public Object invoke(Object proxy, Method method, Object[] args) throws Throwable { Object result = method.invoke(target, args); String methodName = method.getName(); if (methodName.equals("createStatement")) { result = ProxyBuilder.createProxy(result, new StatementProxy(result)); } return result; } } 

to intercept any call to Statement.execute (String sql) :

 public class StatementProxy { public StatementProxy(Object anObject) { super(anObject); } @Override public Object invoke(Object proxy, Method method, Object[] args) throws Throwable { try { return method.invoke(proxy, args); } catch (SQLException sqle) { if (method.getName().contains("execute")) { String sql = ""; if (args != null && args[0] != null) { sql = args[0].toString(); } saveToFile(arg); } throw sqle; } } } 

where ProxyBuilder is a simple helper class:

 public final class ProxyBuilder { public static Connection tracingConnection(Connection connection) { return createProxy(connection, new ConnectionProxy(connection)); } static <T> T createProxy(T anObject, InvocationHandler invocationHandler) { return createProxy(anObject, invocationHandler, anObject.getClass().getInterfaces()); } static <T> T createProxy(T anObject, InvocationHandler invocationHandler, Class... forcedInterfaces) { return (T) Proxy.newProxyInstance( anObject.getClass().getClassLoader(), forcedInterfaces, invocationHandler); } } 

Of course, this is not your final production code , but it is a good starting point.