Using SELECT in SQL binding

[ozkin]

Could some explain or provide example how to implement a composite reference with SQL binding which defines query with SELECT and WHERE clause?

Idea is to be able to query few values using one value as an argument.

For example: select name, company, address from people where name = #

I guess all attributes name, company, address should be combined into a POJO class. Right?

Then how should the operation in Reference interface look like?

void execute(Person p) or Person execute(Person p) or String execute(Person p)?

In my case the operation seems to get executed fine but it does not return nor populate Person object..

[jorgemoralespou_2]

I guess you haven't looked at the quickstarts: quickstarts/camel-sql-binding at master · jboss-switchyard/quickstarts · GitHub

Give it a look at see if it helps you, otherwise, answer here with more questions.

[ozkin]

Of course I looked at this and used it as a basis for my development.

First, it does not cover same use case as mine (select+where), secondly by following this example I still could not make it working. Thus I was asking for a better example or better explanation since documentation is far from detailed.

I can count number of rows, I can get all rows. I cannot make selective select, nor update the table, basically operations where parameters are involved.

[igarashitm]

Here is an example application using camel-sql reference binding with SELECT query. I guess you're missing appropriate converter for the returned object, I did see null was returned if the converter didn't fit for actual object returned by camel-sql.

switchyard-issues/sql-reference-select at master · igarashitm/switchyard-issues · GitHub

You may also want to read this:

http://camel.apache.org/sql-component.html

[ozkin]

Thanks for the hint and links!! The part with Converter was a bit unclear before. Conversion to and from POJO solved my problems.

One observation though. Using Iterator approach is not too flexible. If you need to have two SELECT statements, one with 1 parameter and another with 2 parameters (from same POJO class), then it will not work with such implementation of the iterator class. Iterator should "contain" exactly same amount of parameters which are used in the query (basically amount of '#'s).

One option, for example, could be like this:

public class PojoIterator implements Iterator<Object> {

    private final Iterator<Object> iterator;

    public PojoIterator(Greeting greeting) {
        List<Object> list = new ArrayList<Object>();
        if (greeting.getId() != null)
            list.add(greeting.getId());
        if (greeting.getReceiver() != null)
            list.add(greeting.getReceiver());
        if (greeting.getSender() != null)
            list.add(greeting.getSender());
        this.iterator = list.iterator();
    }
    @Override
    public boolean hasNext() {
        return iterator.hasNext();
    }
    @Override
    public Object next() {
        return iterator.next();       
    }
    @Override
    public void remove() {
        iterator.remove();
    }

}

That helps a bit to work with different select, insert, delete queries with different amount of parameters of same POJO (of course, order of params is still important).

If I understood the camel-sql doc correctly, the best solution for such cases is to use named parameters. Right? Then you could specify in each query what params you need exactly.

(but unfortunately it comes only with Camel 2.11 and thus not available in SY1.1... as i see it uses camel 2.10).