ResultSet

[Alan J]

When I do this
  List<Map<String,Object>> sel = dbh.createQuery('select * from blah')

then want to go thru the Map keyset, theyre in sorted order.
How can I use the Map functionality but preserve DB column ordering?

[Brian McCallister]

You will probably need to write your own Mapper to accomplish this. The mapper will be called once per row, passing in the (already advanced) ResultSet. You can then put the contents of the result set into any data structure you want.

-Brian

[Alan J]

Seems like if one cloned DefaultMapper.java and called it OrderedMapper ...
and OrderedHashMap extends LinkedHashMap<String, Object>

then It'd be in the column order gotten on the select?

This sounds like a good feature to consider adding to your project.

Alan

[Alan J]

OK so I built a OrderedMapper (using LinkedHashMap, based on DefaultMapper)...
but not sure howto register and use it. this doesnt work...
            dbi = new DBI(ds);
            if (dbi != null) {
                dbh = dbi.open();
                dbh.registerMapper(new OrderedMapper());
                qry = dbh.createQuery(sql);
                Do some qry.bind() calls...
            qry = qry.mapTo(OrderedMapper.class);
            List<Map<String,Object>> sel = qry.list();
I get:
        exception org.skife.jdbi.v2.MappingRegistry$1: No mapper registered for OrderedMapper

[Brian McCallister]

Try

  h.createQuery("select * from waffles").map(new OrderedMapper()).list()

-Brian

[Brian McCallister]

try

  h.createQuery("select * from waffles").map(OrderedMapper.class).list()

-

On Thu, Aug 2, 2012 at 8:31 AM, Alan J <alan.ju...@gmail.com> wrote:

[Alan J]

Sorry but I get: 
exception java.lang.ClassCastException: OrderedMapper cannot be cast to java.util.Map

public class OrderedMapper implements ResultSetMapper<Map<String, Object>>
{
    public Map<String, Object> map(int index, ResultSet r, StatementContext ctx)
    {
        Map<String, Object> row = new OrderedResultMap();
        ResultSetMetaData m;
        try
        {
            m = r.getMetaData();
        }
        catch (SQLException e)
        {
            throw new ResultSetException("Unable to obtain metadata from result set", e, ctx);
        }

        try
        {
            for (int i = 1; i <= m.getColumnCount(); i ++)
            {
                String key = m.getColumnName(i);
                String alias = m.getColumnLabel(i);
                Object value = r.getObject(i);
                row.put(alias != null ? alias : key, value);
            }
        }
        catch (SQLException e)
        {
            throw new ResultSetException("Unable to access specific metadata from " +
                                         "result set metadata", e, ctx);
        }
        return row;
    }

    private static class OrderedResultMap extends LinkedHashMap<String, Object>
    {
        public static final long serialVersionUID = 1L;

        @Override
        public Object get(Object o)
        {
            return super.get(((String)o).toLowerCase());
        }

        @Override
        public Object put(String key, Object value)
        {
            return super.put(key.toLowerCase(), value);
        }

        @Override
        public boolean containsKey(Object key)
        {
            return super.containsKey(((String)key).toLowerCase());

        }
    }
}

On Wednesday, August 1, 2012 10:27:07 PM UTC-5, Alan J wrote:

[Brian McCallister]

Sorry.

.map(new OrderedMapper())

-Brian

[Sathish V]

the h.createCall(procedure) doesnt have any method like map? How can I attach that to a Mapper?
I am trying to map a cursor output from a procedure.

Regards
Sathish

[Sathish V]

I Just figured out to use registerOutParameter(int position, int sqlType, CallableStatementMapper mapper) CallableStatementMapper for this... am I correct?

-Sathish

[Brian McCallister]

On Thu, Aug 16, 2012 at 6:53 AM, Sathish V <sathi...@gmail.com> wrote:

I Just figured out to use registerOutParameter(int position, int sqlType, CallableStatementMapper mapper) CallableStatementMapper for this... am I correct?

You are correct :-)

-Brian

[Sathish V]

Thanks for your reply.

I was wondering if there is a way to directly call a procedure and bind variable to it using my .sql.db file with dropwizard.

e.g my .sql.db file contains below text

findById() ::= <<
  select employee_id,employee_name from t_employee where manager_id > :id
>>

My interface file is

@ExternalizedSqlViaStringTemplate3
public interface EmployeeDAO {

    @SqlQuery
    List<Map<String, Object>> findAll(@Bind("id") long id);

}


This gives me a List<Map<String,Object>>>

In my file I am binding the variable :id and get the result

Can I achieve the same thing for a procedure call.Is it possible?I could not find any direct example for proc calls.

Any help is appreciated

Regards
Sathish

[Brian McCallister]

On Fri, Aug 17, 2012 at 6:17 AM, Sathish V <sathi...@gmail.com> wrote:

Thanks for your reply.

I was wondering if there is a way to directly call a procedure and bind variable to it using my .sql.db file with dropwizard.

e.g my .sql.db file contains below text

Right, the StringTemplate3StatementLocator expects, for the class com.example.wombat.Foo, a template group file on the classpath at

/com/example/wombat/Foo.sql.stg

This is different from how the Dropwizard sql locator works. I have not yet explored a mechanism for using the DW stuff in conjunction with the ST stuff.

 

findById() ::= <<
  select employee_id,employee_name from t_employee where manager_id > :id
>>

My interface file is

@ExternalizedSqlViaStringTemplate3
public interface EmployeeDAO {

    @SqlQuery
    List<Map<String, Object>> findAll(@Bind("id") long id);

}


This gives me a List<Map<String,Object>>>

In my file I am binding the variable :id and get the result

Can I achieve the same thing for a procedure call.Is it possible?I could not find any direct example for proc calls.

It's not pretty, but you can return an OutParameters instance from the sql object method, which should have all the out parameters on it. I don't remember implementing this bit, but git says I did :-)

We probably need a better way to do it.

-Brian