Java JdbcTemplate.query方法代碼示例

import org.springframework.jdbc.core.JdbcTemplate; //導入方法依賴的package包/類
/**
 * Finds the single Category record having the specified ID,
 * and returns it.
 * 
 * @param id
 *          The ID of the record to find.
 * 
 * @return Category - the Category object whose id matches the
 *         specified ID, or null if no match was found.
 */
public Category findById(Long id) {
  Category ret = null;
  String sql = "SELECT * FROM " + Category.TABLE_NAME + " WHERE id = ?";
  Object[] paramValues = { id };
  JdbcTemplate jdbc = new JdbcTemplate(getDataSource());
  List<Category> categories = jdbc.query(sql, paramValues, new CategoryRowMapper());
  if (categories.size() > 1) {
    throw new RuntimeException("Expected 1 result from findById(), instead found " + categories.size()
        + " (DB configuration error, maybe?)");
  }
  if (!categories.isEmpty()) {
    ret = categories.get(0);
  }
  return ret;
}
 

import org.springframework.jdbc.core.JdbcTemplate; //導入方法依賴的package包/類
/**
 * Finds the single Category record having the specified Name,
 * and returns it.
 * 
 * @param name
 *          The Name column value of the record to find.
 * @return Category - the Category object whose Name matches
 *         the specified Name, or null if no match was found.
 */
public Category findByName(String name) {
  Category ret = null;
  String sql = "SELECT * FROM " + Category.TABLE_NAME + " WHERE name = ?";
  Object[] paramValues = { name };
  JdbcTemplate jdbc = new JdbcTemplate(getDataSource());
  List<Category> categories = jdbc.query(sql, paramValues, new CategoryRowMapper());
  if (categories.size() > 1) {
    throw new RuntimeException("Expected 1 result from findByName(), instead found " + categories.size()
        + " (DB configuration error, maybe?)");
  }
  if (!categories.isEmpty()) {
    ret = categories.get(0);
  }
  return ret;
}
 

import org.springframework.jdbc.core.JdbcTemplate; //導入方法依賴的package包/類
/**
 * Finds the single Item record having the specified ID,
 * and returns it.
 * 
 * @param id
 *          The ID of the record to find.
 * 
 * @return Item - the Item object whose id matches the
 *         specified ID, or null if no match was found.
 */
public Item findById(Long id) {
  Item ret = null;
  String sql = "SELECT * FROM " + Item.TABLE_NAME + " WHERE id = ?";
  Object[] paramValues = { id };
  JdbcTemplate jdbc = new JdbcTemplate(getDataSource());
  List<Item> items = jdbc.query(sql, paramValues, new ItemRowMapper());
  if (items.size() > 1) {
    throw new RuntimeException("Expected 1 result from findById(), instead found " + items.size()
        + " (DB configuration error, maybe?)");
  }
  if (!items.isEmpty()) {
    ret = items.get(0);
  }
  return ret;
}
 

import org.springframework.jdbc.core.JdbcTemplate; //導入方法依賴的package包/類
/**
 * Finds the single Item record having the specified
 * Description, and returns it.
 * 
 * @param description
 *          The Description of the record to find.
 * 
 * @return Item - the Item object whose id matches the
 *         specified description, or null if no match was
 *         found.
 */
public Item findByDescription(String description) {
  Item ret = null;
  String sql = "SELECT * FROM " + Item.TABLE_NAME + " WHERE description = ?";
  Object[] paramValues = { description };
  JdbcTemplate jdbc = new JdbcTemplate(getDataSource());
  List<Item> items = jdbc.query(sql, paramValues, new ItemRowMapper());
  if (items.size() > 1) {
    throw new RuntimeException("Expected 1 result from findById(), instead found " + items.size()
        + " (DB configuration error, maybe?)");
  }
  if (!items.isEmpty()) {
    ret = items.get(0);
  }
  return ret;
}
 

import org.springframework.jdbc.core.JdbcTemplate; //導入方法依賴的package包/類
private void removeDuplicateRows(final JdbcTemplate jdbcTemplate, final Long zone) {
    final List<SubjectEntity> subjects = jdbcTemplate
            .query("SELECT DISTINCT subject_identifier, attributes FROM subject", new SubjectRowMapper());
    jdbcTemplate.update("DELETE FROM subject *");
    for (SubjectEntity s : subjects) {
        jdbcTemplate.update("INSERT INTO subject (subject_identifier, attributes, "
                        + " authorization_zone_id) VALUES (?,?,?)", s.getSubjectIdentifier(), s
                        .getAttributesAsJson(),
                zone);
    }
    final List<ResourceEntity> resources = jdbcTemplate
            .query("SELECT DISTINCT resource_identifier, attributes FROM resource", new ResourceRowMapper());
    jdbcTemplate.update("DELETE FROM resource *");
    for (ResourceEntity r : resources) {
        jdbcTemplate.update("INSERT INTO resource (resource_identifier, attributes, "
                        + " authorization_zone_id) VALUES (?,?,?)", r.getResourceIdentifier(), r
                        .getAttributesAsJson(),
                zone);
    }

    final List<PolicySetEntity> policysets = jdbcTemplate
            .query("SELECT DISTINCT policy_set_id, policy_set_json FROM policy_set", new PolicySetRowMapper());
    jdbcTemplate.update("DELETE FROM policy_set *");
    for (PolicySetEntity ps : policysets) {
        SqlRowSet row = jdbcTemplate
                .queryForRowSet("SELECT * FROM policy_set WHERE policy_set_id =?", ps.getPolicySetID());
        if (row.next()) {
            jdbcTemplate.update("UPDATE policy_set SET policy_set_json = ? WHERE policy_set_id = ?",
                    ps.getPolicySetJson(), ps.getPolicySetID());
        } else {
            jdbcTemplate.update("INSERT INTO policy_set (policy_set_id, policy_set_json, "
                    + " authorization_zone_id) VALUES (?,?,?)", ps.getPolicySetID(), ps.getPolicySetJson(), zone);
        }
    }
}
 

import org.springframework.jdbc.core.JdbcTemplate; //導入方法依賴的package包/類
/**
 * Return all status changes of issues of given project.
 * 
 * @param dataSource
 *            The data source of JIRA database.
 * @param jira
 *            the JIRA project identifier.
 * @param pkey
 *            the project 'pkey'.
 * @param resultType
 *            the bean type to build in result list.
 * @param timing
 *            When <code>true</code> time spent data is fetched.
 * @param summary
 *            When <code>true</code> Summary is fetched.
 * @return status changes of all issues of given project.
 */
public <T> List<T> getChanges(final DataSource dataSource, final int jira, final String pkey, final Class<T> resultType, final boolean timing,
		final boolean summary) {
	final JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
	final RowMapper<T> rowMapper = new BeanPropertyRowMapper<>(resultType);

	// First, get all created issues (first change)
	final List<T> issues;
	final String sqlPart = ", RESOLUTION AS resolution, PRIORITY AS priority, issuestatus AS status, ASSIGNEE AS assignee,"
			+ " REPORTER AS reporter, issuetype AS type, ? AS toStatus, DUEDATE AS dueDate, created"
			+ (timing ? ", TIMESPENT AS timeSpent, TIMEESTIMATE AS timeEstimate, TIMEORIGINALESTIMATE AS timeEstimateInit" : "")
			+ (summary ? ", SUMMARY AS summary" : "") + "  FROM jiraissue WHERE PROJECT = ?";
	if (getJiraVersion(dataSource).compareTo("6.0.0") < 0) {
		// JIRA 4-5 implementation, use "pkey"
		issues = jdbcTemplate.query("SELECT ID AS id, pkey AS pkey" + sqlPart, rowMapper, STATUS_OPEN, jira);
	} else {
		// JIRA 6+, "pkey" is no more available in the 'jiraissue' table
		issues = jdbcTemplate.query("SELECT ID AS id, CONCAT(?, issuenum) AS pkey" + sqlPart, rowMapper, pkey + "-", STATUS_OPEN, jira);
	}
	return issues;
}
 

import org.springframework.jdbc.core.JdbcTemplate; //導入方法依賴的package包/類
/**
 * Return ordered custom fields by the given identifiers
 * 
 * @param dataSource
 *            The data source of JIRA database.
 * @param customFields
 *            the expected custom fields identifiers.
 * @param project
 *            Jira project identifier. Required to filter custom field agains contexts.
 * @return ordered custom fields by their identifier.
 */
public Map<Integer, CustomFieldEditor> getCustomFieldsById(final DataSource dataSource, final Set<Integer> customFields, final int project) {
	if (customFields.isEmpty()) {
		// No custom field, we save an useless query
		return new HashMap<>();
	}

	// Get map as list
	final JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
	final RowMapper<CustomFieldEditor> rowMapper = new BeanPropertyRowMapper<>(CustomFieldEditor.class);
	final List<CustomFieldEditor> resultList = jdbcTemplate
			.query("SELECT ID AS id, TRIM(cfname) AS name, DESCRIPTION AS description, CUSTOMFIELDTYPEKEY AS fieldType FROM customfield WHERE ID IN ("
					+ newIn(customFields) + ") ORDER BY id", rowMapper, customFields.toArray());

	// Make a Map of valid values for single/multi select values field
	final Map<Integer, CustomFieldEditor> result = new LinkedHashMap<>();
	addListToMapIdentifier(dataSource, resultList, result, project);
	return result;
}
 

import org.springframework.jdbc.core.JdbcTemplate; //導入方法依賴的package包/類
/**
 * Query JIRA database to collect activities of given users. For now, only the last success connection is
 * registered.
 * 
 * @param dataSource The JIRA datasource to query the user activities.
 * @param users
 *            the users to query.
 * @return activities.
 */
public Map<String, Activity> getActivities(final DataSource dataSource, final Collection<String> users) {
	final JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
	final Map<String, Activity> result = new HashMap<>();
	if (!users.isEmpty()) {
		jdbcTemplate.query("SELECT u.lower_user_name AS login, attribute_value AS value FROM cwd_user AS u"
				+ " INNER JOIN cwd_user_attributes AS ua ON ua.user_id = u.ID WHERE u.lower_user_name IN (" + newIn(users)
				+ ") AND attribute_name=?;", rs -> {
					final Activity activity = new Activity();
					activity.setLastConnection(new Date(Long.valueOf(rs.getString("value"))));
					result.put(rs.getString("login"), activity);
				}, ArrayUtils.add(users.toArray(), "lastAuthenticated"));
	}
	return result;
}
 

import org.springframework.jdbc.core.JdbcTemplate; //導入方法依賴的package包/類
/**
 * Return all components attached to an issue of a project.
 * 
 * @param dataSource
 *            The data source of JIRA database.
 * @param jira
 *            the JIRA project identifier.
 * @return all custom fields attached to an issue of a project ordered by issue. Key is the issue.
 */
public Map<Integer, Collection<Integer>> getComponentsAssociation(final DataSource dataSource, final int jira) {
	final JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
	final Map<Integer, Collection<Integer>> result = new HashMap<>();

	jdbcTemplate.query(
			"SELECT n.SOURCE_NODE_ID AS issue, n.SINK_NODE_ID AS c"
					+ " FROM nodeassociation AS n INNER JOIN jiraissue AS i ON (i.ID = n.SOURCE_NODE_ID AND i.PROJECT = ?)"
					+ " WHERE n.SOURCE_NODE_ENTITY=? AND n.SINK_NODE_ENTITY=? AND n.ASSOCIATION_TYPE=?",
			(RowCallbackHandler) rs -> result.computeIfAbsent(rs.getInt("issue"), k -> new ArrayList<>()).add(rs.getInt("c")), jira, "Issue",
			"Component", "IssueComponent");
	return result;
}
 

import org.springframework.jdbc.core.JdbcTemplate; //導入方法依賴的package包/類
/**
 * Finds All Category records in the DB.
 * 
 * @return List<Category> - the List of Category objects in the DB.
 *         Will be empty if none found.
 */
public List<Category> findAll() {
  List<Category> ret;
  JdbcTemplate jdbc = new JdbcTemplate(getDataSource());
  String sql = "SELECT * FROM " + Category.TABLE_NAME;
  ret = jdbc.query(sql, new CategoryRowMapper());
  log.info("Found " + ret.size() + " rows from query");
  return ret;
}
 

import org.springframework.jdbc.core.JdbcTemplate; //導入方法依賴的package包/類
/**
 * Finds All Item records in the DB.
 * 
 * @return List<Item> - the List of item objects in the DB.
 *         Will be empty if none found.
 */
public List<Item> findAll() {
  List<Item> ret;
  String sql = "SELECT * FROM " + Item.TABLE_NAME;
  JdbcTemplate jdbc = new JdbcTemplate(getDataSource());
  ret = jdbc.query(sql, new ItemRowMapper());
  if (ret.size() > 1) {
    log.info("Found " + ret.size() + " rows from query");
  }
  return ret;
}
 

import org.springframework.jdbc.core.JdbcTemplate; //導入方法依賴的package包/類
private Set<ZoneClientEntity> findExistingOAuthClients(final JdbcTemplate jdbcTemplate) {
    Set<ZoneClientEntity> oauthClients = new HashSet<>();
    List<ZoneClientEntity> subjectOAuthClients = jdbcTemplate
            .query("SELECT DISTINCT issuer_id, client_id FROM subject", new ZoneClientRowMapper());
    oauthClients.addAll(subjectOAuthClients);

    List<ZoneClientEntity> resourceOAuthClients = jdbcTemplate
            .query("SELECT DISTINCT issuer_id, client_id FROM resource", new ZoneClientRowMapper());
    oauthClients.addAll(resourceOAuthClients);

    List<ZoneClientEntity> policySetOAuthClients = jdbcTemplate
            .query("SELECT DISTINCT issuer_id, client_id FROM policy_set", new ZoneClientRowMapper());
    oauthClients.addAll(policySetOAuthClients);
    return oauthClients;
}
 

import org.springframework.jdbc.core.JdbcTemplate; //導入方法依賴的package包/類
@Bean
DataRepository dataRepository(JdbcTemplate jdbcTemplate) {
    return (String application, List<String> profiles, List<String> labels) ->
            jdbcTemplate.query("select * from settings",
                    (rs, rowNum) -> new DataPropertySource(
                            rs.getString("profile"),
                            rs.getString("label"),
                            new HashMap<String, Object>() {{
                                put(rs.getString("source"), rs.getString("value"));
                            }}
                    )
            );
}
 

import org.springframework.jdbc.core.JdbcTemplate; //導入方法依賴的package包/類
@Test
public void testGetDataSource() throws Exception {
    DataSourceFactory.createDataSource("minsx", "com.mysql.jdbc.Driver", "jdbc:mysql://123.206.133.24:4858/minsx?useUnicode=true&characterEncoding=utf-8&autoReconnect=true", "wssswcom", "Ss123456");
    DruidDataSource dataSource = DataSourceFactory.getDataSource("minsx");
    JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

    /*List<Map<String, Object>> result = jdbcTemplate.queryForList("select * from sys_auth");
    result.get(0).forEach((key, value) -> {
        System.out.print(key + "\t\t");
    });
    System.out.println();
    result.forEach(datas -> {
        datas.forEach((key, value) -> System.out.print(value + "\t\t"));
        System.out.println();
    });*/

    List<Auth> userList = jdbcTemplate.query("select * from sys_auth", (ResultSet rs, int rowNum) -> {
        Auth user = new Auth();
        user.setId(rs.getInt("auth_id"));
        user.setType(rs.getString("type"));
        user.setCategory(rs.getString("category"));
        user.setAuthValue(rs.getString("auth_value"));
        user.setDescription(rs.getString("description"));
        return user;
    });

    userList.forEach(user->System.out.println(user.toString()));

}
 

import org.springframework.jdbc.core.JdbcTemplate; //導入方法依賴的package包/類
/**
 * Return all custom fields matching to the given names
 * 
 * @param dataSource
 *            The data source of JIRA database.
 * @param customFields
 *            the expected custom fields names.
 * @param project
 *            Jira project identifier. Required to filter custom field against contexts.
 * @return all custom field configurations referenced in the given project and matching the required names.
 */
public Map<String, CustomFieldEditor> getCustomFields(final DataSource dataSource, final Set<String> customFields, final int project) {
	if (customFields.isEmpty()) {
		// No custom field, we save useless queries
		return new HashMap<>();
	}

	// Get map as list
	final JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
	final RowMapper<CustomFieldEditor> rowMapper = new BeanPropertyRowMapper<>(CustomFieldEditor.class);
	final List<CustomFieldEditor> resultList = jdbcTemplate
			.query("SELECT cf.ID AS id, TRIM(cf.cfname) AS name, cf.DESCRIPTION AS description, cf.CUSTOMFIELDTYPEKEY AS fieldType FROM customfield AS cf WHERE TRIM(cf.cfname) IN ("
					+ newIn(customFields) + ")", rowMapper, customFields.toArray());

	// Also add the translated items
	final List<CustomFieldEditor> resultListTranslated = jdbcTemplate.query(
			"SELECT cf.ID AS id, TRIM(cf.cfname) AS originalName, TRIM(ps.propertyvalue) AS name, cf.cfname AS originalName, cf.DESCRIPTION AS description, "
					+ "cf.CUSTOMFIELDTYPEKEY AS fieldType "
					+ "FROM customfield AS cf INNER JOIN propertyentry AS pe ON pe.ENTITY_ID = cf.ID INNER JOIN propertystring AS ps ON pe.ID = ps.ID "
					+ "WHERE pe.ENTITY_NAME=? AND PROPERTY_KEY LIKE ? AND TRIM(ps.propertyvalue) IN (" + newIn(customFields) + ")",
			rowMapper, ArrayUtils.addAll(new String[] { "CustomField", "%FR" }, customFields.toArray()));

	// Make a Map of valid values for single/multi select values field
	final Map<String, CustomFieldEditor> result = new HashMap<>();
	addListToMap(dataSource, resultList, result, project);
	addListToMap(dataSource, resultListTranslated, result, project);
	return result;
}