org.apache.commons.dbutils.handlers.ScalarHandler Java Examples

@Override
	public <V> V getSingleVal(String sql, Object... args) {
		Connection connection = null;
		
		try {
//			connection = ConnectionContext.getInstance().get();
			connection = JDBCUtils.getConnection();
			return (V)queryRunner.query(connection, sql, new ScalarHandler(), args);
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
			JDBCUtils.release(connection);
		}
		
		return null;
	}
 

/**
 * 获取某个字段值
 * @param field 获取field字段数据
 * @return 返回字段数据
 * @throws Exception if has error
 */
public String getField(String field) throws Exception{
	this.field(field);
	try {
		if (buildSql_Select()) {
			Object res = new QueryRunner().query(conn, sql, new ScalarHandler<Object>(), param_where);
			D.autoCloseConn(conn);
			if (null != res) {
				return res.toString();
			}
		}
	} catch (Exception e) {
		D.autoCloseConn(conn);
		throw e;
	}
	return null;
}
 

/**
 * 
 * @Title:       queryForLong   
 * @Description: TODO(带可变参数查询，返回long类型数据)   
 * @param:       @param countSql 查询记录总条数
 * @param:       @param para 参数
 * @param:       @return
 * @param:       @throws SQLException      
 * @return:      Long      
 * @throws
 */
public Long queryForLong(String countSql, Object... para) throws SQLException {
	logger.debug("queryForLong: {}, para: {}", countSql, ToStringBuilder.reflectionToString(para));
	QueryRunner runner = new QueryRunner();
	Long number = null;
	Connection conn = null;
	try {
		conn = getConnection();
		number = runner.query(conn, countSql, new ScalarHandler<Long>(), para);
	} catch (SQLException e) {
		logger.error("------queryForLong error: {}------", e.getMessage());
		throw e;
	} finally {
		if (conn != null && conn.getAutoCommit() == true) {
			freeConnection();
		}
	}
	return number;
}
 

/**
 * 
 * @Title:       insertSql   
 * @Description: TODO(带可变参数, 执行sql插入，返回新增记录的自增主键。注意： 若插入的表无自增主键则返回 0，异常的话则返回 null)   
 * @param:       @param sql 执行的sql语句
 * @param:       @param para 参数
 * @param:       @return
 * @param:       @throws SQLException      
 * @return:      Long      
 * @throws
 */
public Long insertSql(String sql, Object... para) throws SQLException {
	logger.debug("InsertSql: {}, para: {}", sql, ToStringBuilder.reflectionToString(para));
	QueryRunner runner = new QueryRunner();
	Connection conn = null;
	Long id = null;
	try {
		conn = getConnection();
		id = (Long) runner.insert(conn, sql, new ScalarHandler<Object>(), para);
	} catch (SQLException e) {
		logger.error("------insertSql error: {}------", e.getMessage());
		throw e;
	} finally {
		if (conn != null && conn.getAutoCommit() == true) {
			freeConnection();
		}
	}
	return id;
}
 

@Override
public LogList findByLimitAndFields(int page, int limit, int timeOrder, List<Field> list) throws TxLoggerException {
    if (Objects.isNull(dbHelper)) {
        throw new TxLoggerException("系统日志被禁用");
    }
    StringBuilder countSql = new StringBuilder("select count(*) from t_logger where 1=1 and ");
    StringBuilder sql = new StringBuilder("select * from t_logger where 1=1 and ");
    List<String> values = whereSqlAppender(sql, list);
    whereSqlAppender(countSql, list);
    Object[] params = values.toArray(new Object[0]);
    long total = dbHelper.query(countSql.toString(), new ScalarHandler<>(), params);
    if (total < (page - 1) * limit) {
        page = 1;
    }
    sql.append(timeOrderSql(timeOrder)).append(" limit ").append((page - 1) * limit).append(", ").append(limit);
    List<TxLog> txLogs = dbHelper.query(sql.toString(), new BeanListHandler<>(TxLog.class, processor), params);

    LogList logList = new LogList();
    logList.setTotal(total);
    logList.setTxLogs(txLogs);
    return logList;
}
 

/**
 * 按筛选条件获取记录数
 *
 * @param where  where条件部分
 * @param params 参数
 * @return 总共记录数
 */
private long total(String where, Object... params) {
    if (logDbProperties.isEnabled()) {
        return dbHelper.query("select count(*) from t_logger where " + where, new ScalarHandler<>(), params);
    } else {
        throw new NotEnableLogException("not enable logger");
    }
}
 

@Test
public void givenHandler_whenInserting_thenExpectedId() throws SQLException {
    ScalarHandler<Integer> scalarHandler = new ScalarHandler<>();

    QueryRunner runner = new QueryRunner();
    String insertSQL = "INSERT INTO employee (firstname,lastname,salary, hireddate) VALUES (?, ?, ?, ?)";

    int newId = runner.insert(connection, insertSQL, scalarHandler, "Jenny", "Medici", 60000.60, new Date());

    assertEquals(newId, 6);
}
 

@Test
public void givenResultHandler_whenExecutingQuery_thenExpectedScalar() throws SQLException {
    ScalarHandler<Long> scalarHandler = new ScalarHandler<>();

    QueryRunner runner = new QueryRunner();
    String query = "SELECT COUNT(*) FROM employee";
    long count = runner.query(connection, query, scalarHandler);

    assertEquals(count, 5);
}
 

@Override
public long queryCount(String sql, Object... params) {
    long result;
    try {
        result = queryRunner.query(sql, new ScalarHandler<Long>("count(*)"), params);
    } catch (SQLException e) {
        logger.error("查询出错！", e);
        throw new RuntimeException(e);
    }
    printSQL(sql);
    return result;
}
 

@Override
public <T> T queryColumn(String sql, Object... params) {
    T obj;
    try {
        obj = queryRunner.query(sql, new ScalarHandler<T>(), params);
    } catch (SQLException e) {
        logger.error("查询出错！", e);
        throw new RuntimeException(e);
    }
    printSQL(sql);
    return obj;
}
 

/**
 * 查询指定列
 * @param column 结果集的列索引号
 * @param sql sql语句
 * @param params 查询参数
 * @return 指定列的结果对象
 */
public Object query(int column, String sql, Object... params) {
	Object result;
    try {
    	if(log.isDebugEnabled()) {
    		log.debug("查询单列数据=\n" + sql);
    	}
        result = runner.query(getConnection(), sql, new ScalarHandler(column), params);
    } catch (SQLException e) {
        log.error(e.getMessage(), e);
        throw new RuntimeException(e.getMessage(), e);
    }
    return result;
}
 

@Override
public long queryCount(String sql, Object... params) {
    long result;
    try {
        result = queryRunner.query(sql, new ScalarHandler<Long>("count(*)"), params);
    } catch (SQLException e) {
        logger.error("查询出错！", e);
        throw new RuntimeException(e);
    }
    printSQL(sql);
    return result;
}
 

@Override
public <T> T queryColumn(String sql, Object... params) {
    T obj;
    try {
        obj = queryRunner.query(sql, new ScalarHandler<T>(), params);
    } catch (SQLException e) {
        logger.error("查询出错！", e);
        throw new RuntimeException(e);
    }
    printSQL(sql);
    return obj;
}
 

/**
 * Package-private for unit testing only.
 */
static String getCurrentPathSql(Connection conn, JdbcHelper jdbc, ImmutableSet<PhysicalSchema> physicalSchemas) {
    String path = jdbc.query(conn, "select current path from sysibm.sysdummy1", new ScalarHandler<String>());

    MutableList<String> currentSchemaPathList = Lists.mutable.of(path.split(",")).collect(new Function<String, String>() {
        @Override
        public String valueOf(String object) {
            if (object.startsWith("\"") && object.endsWith("\"")) {
                return object.substring(1, object.length() - 1);
            } else {
                return object;
            }
        }
    });

    // Rules on constructing this "set path" command:
    // 1) The existing default path must come first (respecting the existing connection), followed by the
    // schemas in our environment. The default path must take precedence.
    // 2) We cannot have duplicate schemas listed in the "set path" call; i.e. in case the schemas in our
    // environment config are already in the default schema.
    //
    // Given these two requirements, we use a LinkedHashSet
    LinkedHashSet<String> currentSchemaPaths = new LinkedHashSet(currentSchemaPathList);
    currentSchemaPaths.addAll(physicalSchemas.collect(new Function<PhysicalSchema, String>() {
        @Override
        public String valueOf(PhysicalSchema physicalSchema) {
            return physicalSchema.getPhysicalName();
        }
    }).castToSet());

    // This is needed to work w/ stored procedures
    // Ideally, we'd use "set current path current path, " + physicalSchemaList
    // However, we can't set this multiple times in a connection, as we can't have dupes in "current path"
    // Ideally, we could use BasicDataSource.initConnectionSqls, but this does not interoperate w/ the LDAP
    // datasource for JNDI-JDBC
    return "set path " + CollectionAdapter.adapt(currentSchemaPaths).makeString(",");
}
 

public <T> T query(String sql, ScalarHandler<T> scalarHandler, Object... params) {
    try {
        return queryRunner.query(sql, scalarHandler, params);
    } catch (SQLException e) {
        log.error("query error", e);
        return null;
    }
}
 

public <T> T query(String sql, ScalarHandler<T> scalarHandler, Object... params) {
    try {
        return queryRunner.query(sql, scalarHandler, params);
    } catch (SQLException e) {
        log.error("query error", e);
        return null;
    }
}
 

public boolean isfavored(String userId, String newsId) {
	try {
		sql= "SELECT favorId FROM favors WHERE userId = ? AND newsId = ?;";
		return queryRunner.query(sql, new ScalarHandler<Integer>(), userId, newsId) != null;
	} catch (SQLException e) {
		throw new RuntimeException(e);
	}
}
 

public int getTotalTrainingDays(String userId) {
	sql = "SELECT IFNULL(SUM(duration),0) FROM training WHERE userId = ?;";
	try {
		return queryRunner.query(sql, new ScalarHandler<BigDecimal>(), userId).intValue();
	} catch (SQLException e) {
		return 0;
	}
}
 

public int getTotalCheckedDays(String userId) {
	sql = "SELECT COUNT(*) FROM dailycheck WHERE userId = ?;";
	try {
		return queryRunner.query(sql, new ScalarHandler<Long>(), userId).intValue();
	} catch (SQLException e) {
		return 0;
	}
}
 

public boolean isChecked(String userId) {
	sql = "SELECT id FROM dailycheck WHERE userId = ? AND checkDate = CURDATE();";
	try {
		return queryRunner.query(sql, new ScalarHandler<Integer>(), userId) != null;
	} catch (SQLException e) {
		throw new RuntimeException(e);
	}
}
 

public boolean isExist(String username) {
	sql = "SELECT userId FROM user WHERE username = ?";
	try {
		return queryRunner.query(sql, new ScalarHandler<Integer>(), username) != null;
	} catch (SQLException e) {
		throw new RuntimeException(e);
	}
}
 

public Long queryScalar(Class<? extends Table> clazz, String sql, LinkedHashMap<String, Object> condition) {
    try {
        QueryRunner runner = getJdbcTemplate(clazz);
        return runner.query(sql, new ScalarHandler<Long>(), condition.values().toArray());
    } catch (Exception e) {
        LOGGER.error("", e);
    }
    return 0L;
}
 

public <T extends Table<Long>> Long saveAndIncreasePK(T table) {
    try {
        QueryRunner runner = getJdbcTemplate(table.getClass());

        TableInfo info = SuperTable.getTableInfo(table.getClass());
        final String sql = replaceSql.toSqlString(info.annotation().tableName(), info.buildDbColumns());
        final Object[] values = table.writeData();

        return runner.insert(sql, new ScalarHandler<>(), values);
    } catch (Exception e) {
        LOGGER.error("", e);
    }
    return 0L;
}
 

public long count() {
    String sql = "SELECT count(*) FROM TXLCN_LOG";
    return h2DbHelper.query(sql, new ScalarHandler<Long>());
}
 

public static Long total() throws SQLException {
    String sql = "SELECT COUNT(*) FROM tb_sku";
    return queryRunner.query(conn, sql, new ScalarHandler<>());
}