Example usage for org.springframework.jdbc.core JdbcTemplate queryForObject

List of usage examples for org.springframework.jdbc.core JdbcTemplate queryForObject

  1. HOME
  2. Java
  3. org.springframework
  4. org.springframework.jdbc.core.*
  5. JdbcTemplate
  6. queryForObject

Introduction

In this page you can find the example usage for org.springframework.jdbc.core JdbcTemplate queryForObject.

Prototype

@Override
    public <T> T queryForObject(String sql, Class<T> requiredType, @Nullable Object... args)
            throws DataAccessException

Source Link

Usage

From source file:com.hexin.core.dao.BaseDaoSupport.java

public <T> Page<T> findMySqlPageWithBlob(JdbcTemplate jdbcTemplate, String sql, Class<T> dtoClass,
        PageCondition pageCondition, Object... args) throws SecurityException, IllegalArgumentException,
        SQLException, InstantiationException, IllegalAccessException, NoSuchFieldException, IOException {

    Page<T> page = new Page<T>();
    StringBuffer countSqlBuf = new StringBuffer();
    int currentPage = 1;
    int pageSize = 10;
    String camelSortBy = "";
    String underlineSortBy = "";
    String orderBy = "";
    long total;//from   ww  w. j ava2 s  . c  o  m
    long totalPage;
    List<T> resultList = null;

    // distinct
    countSqlBuf.append("select count(*) from (");
    countSqlBuf.append(StringUtils.substringBeforeLast(sql, "order "));
    countSqlBuf.append(") tmp_table");
    debugSql(countSqlBuf.toString(), args);

    // 
    total = jdbcTemplate.queryForObject(countSqlBuf.toString(), Long.class, args);
    page.setTotal(total);

    StringBuffer pageSqlBuf = new StringBuffer();
    pageSqlBuf.append("select * from (");
    pageSqlBuf.append(sql);
    pageSqlBuf.append(") t ");

    if (pageCondition != null) {
        currentPage = pageCondition.getPage();
        pageSize = pageCondition.getRows();
        camelSortBy = pageCondition.getSort();
        orderBy = pageCondition.getOrder();

        // ????
        underlineSortBy = IcpObjectUtil.camelToUnderline(camelSortBy);
    }

    if (StringUtils.isNotEmpty(underlineSortBy) && StringUtils.isNotEmpty(orderBy)) {
        pageSqlBuf.append(" order by ");
        pageSqlBuf.append(underlineSortBy).append(" ").append(orderBy).append(" ");
    }
    pageSqlBuf.append(" limit ");
    pageSqlBuf.append((currentPage - 1) * pageSize);
    pageSqlBuf.append(" ,");
    pageSqlBuf.append(pageSize);
    pageSqlBuf.append(" ");

    debugSql(pageSqlBuf.toString(), args);

    resultList = findListWithBlob(pageSqlBuf.toString(), dtoClass, args);

    long mod = total % pageSize;
    if (mod == 0) {
        totalPage = total / pageSize;
    } else {
        totalPage = total / pageSize + 1;
    }

    page.setRows(resultList);
    page.setCurrentPage(currentPage);
    page.setPageSize(pageSize);
    page.setTotalPage(totalPage);
    page.setPageIndex(PageIndex.getPageIndex(Constants.PAGE_RANGE, pageSize, totalPage));

    return page;
}

From source file:org.syncope.core.rest.UserTestITCase.java

@Test(expected = EmptyResultDataAccessException.class)
public void issue213() {
    UserTO userTO = getSampleTO("issue213@syncope-idm.org");
    userTO.addResource("resource-testdb");

    userTO = restTemplate.postForObject(BASE_URL + "user/create", userTO, UserTO.class);
    assertNotNull(userTO);// ww w.j a  v  a2 s . c o m

    JdbcTemplate jdbcTemplate = new JdbcTemplate(testDataSource);

    String username = jdbcTemplate.queryForObject("SELECT id FROM test WHERE id=?", String.class,
            userTO.getUsername());

    assertEquals(userTO.getUsername(), username);

    UserMod userMod = new UserMod();

    userMod.setId(userTO.getId());
    userMod.addResourceToBeRemoved("resource-testdb");

    userTO = restTemplate.postForObject(BASE_URL + "user/update", userMod, UserTO.class);

    assertTrue(userTO.getResources().isEmpty());

    jdbcTemplate.queryForObject("SELECT id FROM test WHERE id=?", String.class, userTO.getUsername());
}

From source file:com.sfs.whichdoctor.dao.RotationDAOImpl.java

/**
 * Load a RotationBean for a specified rotationId and supplied load details.
 * A boolean parameter identifies whether to use the default reader
 * connection or optional writer connection datasource.
 *
 * @param rotationId the rotation id/*ww  w. j  ava  2  s  .  c om*/
 * @param loadDetails the load details
 * @param useWriterConn the use writer conn
 *
 * @return the rotation bean
 *
 * @throws WhichDoctorDaoException the which doctor dao exception
 */
@SuppressWarnings("unchecked")
private RotationBean load(final int rotationId, final BuilderBean loadDetails, final boolean useWriterConn)
        throws WhichDoctorDaoException {

    RotationBean rotation = null;

    final String loadRotationId = getSQL().getValue("rotation/load") + " AND rotation.RotationId = ?";

    JdbcTemplate jdbcTemplate = this.getJdbcTemplateReader();
    if (useWriterConn) {
        jdbcTemplate = this.getJdbcTemplateWriter();
    }

    try {
        rotation = (RotationBean) jdbcTemplate.queryForObject(loadRotationId, new Object[] { rotationId },
                new RowMapper() {
                    public Object mapRow(final ResultSet rs, final int rowNum) throws SQLException {
                        return loadRotation(rs, loadDetails);
                    }
                });

    } catch (IncorrectResultSizeDataAccessException ie) {
        dataLogger.debug("No results found for the search: " + ie.getMessage());
    }
    return rotation;
}

From source file:com.sfs.whichdoctor.dao.PersonDAOImpl.java

/**
 * Load a PersonBean for a specified personId using the load options
 * provided. A boolean parameter identifies whether to use the default
 * reader connection or optional writer connection datasource
 *
 * @param personId the person id//ww  w .j  a  v  a 2 s.  com
 * @param loadDetails the load details
 * @param useWriterConn the use writer conn
 *
 * @return the person bean
 *
 * @throws WhichDoctorDaoException the which doctor dao exception
 */
@SuppressWarnings("unchecked")
private PersonBean load(final int personId, final BuilderBean loadDetails, final boolean useWriterConn)
        throws WhichDoctorDaoException {

    PersonBean person = null;

    final String loadPersonId = getSQL().getValue("person/load") + " AND people.PersonId = ?";

    JdbcTemplate jdbcTemplate = this.getJdbcTemplateReader();
    if (useWriterConn) {
        jdbcTemplate = this.getJdbcTemplateWriter();
    }

    try {
        person = (PersonBean) jdbcTemplate.queryForObject(loadPersonId, new Object[] { personId },
                new RowMapper() {
                    public Object mapRow(final ResultSet rs, final int rowNum) throws SQLException {
                        return loadPerson(rs, loadDetails);
                    }
                });

    } catch (IncorrectResultSizeDataAccessException ie) {
        dataLogger.debug("No results found for search: " + ie.getMessage());
    }

    return person;
}

From source file:org.syncope.core.rest.UserTestITCase.java

@Test
public void createWithApproval() {
    JdbcTemplate jdbcTemplate = new JdbcTemplate(testDataSource);

    UserTO userTO = getSampleTO("createWithApproval@syncope-idm.org");
    userTO.addResource("resource-testdb");

    // User with role 9 are defined in workflow as subject to approval
    MembershipTO membershipTO = new MembershipTO();
    membershipTO.setRoleId(9L);/* www  .ja  v  a  2 s.  c om*/
    userTO.addMembership(membershipTO);

    // 1. create user with role 9 (and verify that no propagation occurred)
    userTO = restTemplate.postForObject(BASE_URL + "user/create", userTO, UserTO.class);
    assertNotNull(userTO);
    assertEquals(1, userTO.getMemberships().size());
    assertEquals(9, userTO.getMemberships().get(0).getRoleId());
    assertEquals("createApproval", userTO.getStatus());
    assertEquals(Collections.singleton("resource-testdb"), userTO.getResources());

    assertTrue(userTO.getPropagationTOs().isEmpty());

    Exception exception = null;
    try {
        jdbcTemplate.queryForInt("SELECT id FROM test WHERE id=?", userTO.getUsername());
    } catch (EmptyResultDataAccessException e) {
        exception = e;
    }
    assertNotNull(exception);

    // 2. request if there is any pending task for user just created
    WorkflowFormTO form = restTemplate.getForObject(BASE_URL + "user/workflow/form/{userId}",
            WorkflowFormTO.class, userTO.getId());
    assertNotNull(form);
    assertNotNull(form.getTaskId());
    assertNull(form.getOwner());

    // 4. claim task (from admin)
    form = restTemplate.getForObject(BASE_URL + "user/workflow/form/claim/{taskId}", WorkflowFormTO.class,
            form.getTaskId());
    assertNotNull(form);
    assertNotNull(form.getTaskId());
    assertNotNull(form.getOwner());

    // 5. approve user (and verify that propagation occurred)
    Map<String, WorkflowFormPropertyTO> props = form.getPropertiesAsMap();
    props.get("approve").setValue(Boolean.TRUE.toString());
    form.setProperties(props.values());
    userTO = restTemplate.postForObject(BASE_URL + "user/workflow/form/submit", form, UserTO.class);
    assertNotNull(userTO);
    assertEquals("active", userTO.getStatus());
    assertEquals(Collections.singleton("resource-testdb"), userTO.getResources());

    exception = null;
    try {
        String username = jdbcTemplate.queryForObject("SELECT id FROM test WHERE id=?", String.class,
                userTO.getUsername());
        assertEquals(userTO.getUsername(), username);
    } catch (EmptyResultDataAccessException e) {
        exception = e;
    }
    assertNull(exception);

    // 6. update user
    UserMod userMod = new UserMod();
    userMod.setId(userTO.getId());
    userMod.setPassword("anotherPassword123");

    userTO = restTemplate.postForObject(BASE_URL + "user/update", userMod, UserTO.class);
    assertNotNull(userTO);
}

From source file:org.agnitas.dao.impl.MailingDaoImpl.java

/**
 * Build an SQL-expression from th egiven target_expression.
 * The expression is a list of targetIDs connected with the operators:
 * <ul>//from ww w  . ja v  a  2 s  .c o  m
 * <li>( - block start
 * <li>) - block end
 * <li>&amp; - AND 
 * <li>| - OR 
 * <li>! - NOT 
 * </ul>
 * @param targetExpression The expression as string.
 * @param jdbc Template for SQL queries.
 * @return the resulting where clause.
 */
static String getSQLExpression(String targetExpression, JdbcTemplate jdbc) {
    StringBuffer buf = new StringBuffer();
    int tlen = targetExpression.length();

    if (targetExpression == null) {
        return null;
    }
    for (int n = 0; n < tlen; ++n) {
        char ch = targetExpression.charAt(n);

        if ((ch == '(') || (ch == ')')) {
            buf.append(ch);
        } else if ((ch == '&') || (ch == '|')) {
            if (ch == '&')
                buf.append(" AND");
            else
                buf.append(" OR");
            while (((n + 1) < tlen) && (targetExpression.charAt(n + 1) == ch))
                ++n;
        } else if (ch == '!') {
            buf.append(" NOT");
        } else if (Character.isDigit(ch)) {
            String temp = "";
            int first = n;
            int tid = (-1);

            while (n < tlen && Character.isDigit(ch)) {
                n++;
            }
            tid = Integer.parseInt(targetExpression.substring(first, n));
            temp = (String) jdbc.queryForObject("select target_sql from dyn_target_tbl where target_id = ?",
                    new Object[] { new Integer(tid) }, temp.getClass());
            if (temp != null && temp.trim().length() > 2)
                buf.append(" (" + temp + ")");
        }
    }
    if (buf.length() >= 3)
        return buf.toString();
    return null;
}

From source file:org.agnitas.dao.impl.MailingDaoImpl.java

public String getAutoURL(int mailingID) {
    JdbcTemplate jdbc = new JdbcTemplate((DataSource) applicationContext.getBean("dataSource"));
    String sql = "select auto_url from mailing_tbl where mailing_id=?";

    try {//w  w w.ja v  a  2s.  c  o m
        return (String) jdbc.queryForObject(sql, new Object[] { new Integer(mailingID) }, sql.getClass());
    } catch (Exception e) {
        AgnUtils.logger().error("getAutoURL: " + e.getMessage());
    }
    return null;
}

From source file:org.agnitas.dao.impl.MailingDaoImpl.java

public String getAutoURL(int mailingID, int companyID) {
    JdbcTemplate jdbc = new JdbcTemplate((DataSource) applicationContext.getBean("dataSource"));
    String rdirdomain = null;//from   w  w w .  j a  v a2 s.  co m
    String rdir_mailinglistquery = "select  ml.RDIR_DOMAIN  FROM MAILINGLIST_TBL ml JOIN MAILING_TBL m ON ( ml.MAILINGLIST_ID = m.MAILINGLIST_ID) WHERE  m.MAILING_ID=?";
    rdirdomain = (String) jdbc.queryForObject(rdir_mailinglistquery, new Object[] { new Integer(mailingID) },
            String.class);
    if (rdirdomain != null) {
        return rdirdomain;
    }
    String rdir_companyquery = "select RDIR_DOMAIN FROM COMPANY_TBL where company_id=?";
    rdirdomain = (String) jdbc.queryForObject(rdir_companyquery, new Object[] { new Integer(companyID) },
            String.class);
    return rdirdomain;
}

From source file:org.agnitas.dao.impl.MailingDaoImpl.java

public String getFormat(int type) {
    String format = "d.M.yyyy";
    JdbcTemplate jdbc = new JdbcTemplate((DataSource) applicationContext.getBean("dataSource"));
    try {//  w  w  w .  j  a  v  a  2  s. c  o m
        String sql = "SELECT format FROM date_tbl WHERE type = ?";
        format = (String) jdbc.queryForObject(sql, new Object[] { new Integer(type) }, String.class);
    } catch (Exception e) {
        AgnUtils.logger().error("Query failed for data_tbl: " + e);
    }
    return format;
}