4 Replies Latest reply on Feb 10, 2015 6:13 PM by Max Ozkin

    Using SELECT in SQL binding

    Max Ozkin Apprentice

      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..

        • 1. Re: Using SELECT in SQL binding
          Jorge Morales Master

          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.

          • 2. Re: Using SELECT in SQL binding
            Max Ozkin Apprentice

            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.

            • 3. Re: Using SELECT in SQL binding
              Tomohisa igarashi Master

              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

              • 4. Re: Using SELECT in SQL binding
                Max Ozkin Apprentice

                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).