Java - Calling a stored procedure with generic arguments

Question

I would like to call a stored procedure (Oracle). As I my procedure calls are dynamical, I need to manage the procedures parameters dynamically. The problem is that I would like to avoid using the instanceof keyword since it does not work well with primary types. Is there a cleaner way to call a such procedure?

    public void executeProcedure(StoredProcedure proc) {        
        LinkedList<ProcedureParameter<?>> parameters = proc.getParameters();

        CallableStatement statement = connection.prepareCall("{ call MY_PACKAGE.MY_PROCEDURE( ?, ?, ? ) }");

        for (int i = 0; i < parameters.size(); i++) {
            Object paramValue = parameters.get(i).getValue();

            // Beginning of smelly code

            if (paramValue instanceof String) {
                statement.setString(i + 1, (String) paramValue);
            }
            else if (paramValue instanceof ...) {
                ...
            }
            else {
                ...
            }

            // End of smelly code
        }

        statement.execute();            
    }

    public class ProcedureParameter<E> {
        private String name;
        private E value;

        public ProcedureParameter(String name) {
            this.name = name;
        }

        public ProcedureParameter(String name, E value) {
            this.name = name;
            this.value = value;
        }

        // Getters and setters
    }

Thank you for any idea.

If your only problem is with primitive types, I would suggest you just use their object wrapper equivalents and make life easy for you — ControlAltDel
– ControlAltDel, Commented Jun 21, 2012 at 13:57
@ControlAltDel I think the issue is more that when adding a parameter to a Statement, you have to specify the type - ie statement.setString, statement.setInteger, etc. — chama
– chama, Commented Jun 21, 2012 at 14:00
@ControlAltDel That's a good idea, but the problem scope is wider; I mean, the amount of possible parameters types is quite big, and I am looking for a solution that would avoid a long if-else sequence. — dounyy
– dounyy, Commented Jun 21, 2012 at 14:04

Vladislav Bauer · Accepted Answer · 2012-06-21 14:20:56Z

1

I think it could help you to implement needed functionality without instanceof: http://en.wikipedia.org/wiki/Visitor_pattern

answered Jun 21, 2012 at 14:20

Vladislav Bauer

9628 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

dounyy Over a year ago

Thank you, however it won't help me since I don't use custom types but primary types or "java" types (like String)

Michael · Accepted Answer · 2012-06-21 14:46:30Z

1

You could use reflection, although this isn't good practice because it's slower and prevents the compiler from checking to make sure the method calls are correct. You would map each class to its corresponding CallableStatement method. For example String would be mapped to "setString".

List<ProcedureParameter<?>> parameters = proc.getParameters();
CallableStatement statement = connection.prepareCall("{ call MY_PACKAGE.MY_PROCEDURE( ?, ?, ? ) }");

Map<Class<?>, String> methods = new HashMap<Class<?>, String>();
methods.put(String.class, "setString");
//...

for (int i = 0; i < parameters.size(); i++) {
  Object paramValue = parameters.get(i).getValue();
  Class<?> paramValueClass = paramValue.getClass();
  String stmtMethod = methods.get(paramValueClass);
  if (stmtMethod != null){
    Method method = statement.getClass().getMethod(stmtMethod, int.class, paramValueClass);
    method.invoke(statement, i+1, paramValue);
  } else {
    //error
  }
}

answered Jun 21, 2012 at 14:46

Michael

35.5k17 gold badges79 silver badges112 bronze badges

2 Comments

dounyy Over a year ago

Well, that solution seems to be even heavier than the "if-else" solution... Thank you anyway :)

Michael Over a year ago

@dounyy Yes I agree I just made this answer because I'm bored. :) The if/else solution is also very readable--if I am new to the code base, I can easily see what is going on. If this is all refactored and gets hidden behind some other classes, then it makes it harder to figure out what is going on at a glance.

dounyy · Accepted Answer · 2018-07-12 13:02:45Z

1

I will implement another solution, that is probably not be best one, but in my opinion, not smelly though.

As StoredProcedure is an interface, each stored procedure implements this interface. Thus, each implementation will contain a method that returns the call ... statement, as the developer knows the type of the parameters needed for the procedure he implements.

edited Jul 12, 2018 at 13:02

answered Jun 21, 2012 at 15:24

dounyy

66513 silver badges26 bronze badges

1 Comment

user1580431 Over a year ago

would you mind expanding a little more on this answer? I am very new to using StoredProcedures and trying to wrap my head around this. More searching, i found this implementation, is it something similar? codeguru.com/csharp/csharp/cs_misc/sampleprograms/article.php/… Thanks!

Collectives™ on Stack Overflow

Java - Calling a stored procedure with generic arguments

3 Answers 3

1 Comment

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related