下面列出了org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource#org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate 实例代码，或者点击链接到github查看源代码，也可以在右侧发表评论。
/**
* @方法名 page1
* @功能 TODO(这里用一句话描述这个方法的作用)
* @参数 @param vo
* @参数 @return
* @返回 Page<SysDbmsTabsColsInfo>
* @author Administrator
* @throws
*/
public List<SysDbmsTabsColsInfo> page1(Pagination<SysDbmsTabsMergeInfo> vo) {
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.append(" SELECT * FROM sys_dbms_tabs_cols_info c");
stringBuilder.append(" WHERE c.uuid not IN (");
stringBuilder.append(" SELECT m.cols_uuid_1 FROM sys_dbms_tabs_merge_info m ");
if (vo.getInfo().getTableUuid1() != null && vo.getInfo().getTableUuid2() != null) {
stringBuilder.append(" WHERE m.table_uuid_1 = '" + vo.getInfo().getTableUuid1() + "'");
stringBuilder.append(" AND m.table_uuid_2 = '" + vo.getInfo().getTableUuid2() + "'");
} else {
stringBuilder.append(" WHERE 1=0");
}
stringBuilder.append(" )");
stringBuilder.append(" AND c.tabs_uuid = '" + vo.getInfo().getTableUuid1() + "'");
stringBuilder.append(" ORDER BY c.cols_order");
NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(jdbcTemplate);
List<SysDbmsTabsColsInfo> list = template.query(stringBuilder.toString(), new BeanPropertyRowMapper<>(SysDbmsTabsColsInfo.class));
return list;
}
default void upload(UploadBase64FileModification file, String digest, Map<String, String> attributes) {
LobHandler lobHandler = new DefaultLobHandler();
NamedParameterJdbcTemplate jdbc = getNamedParameterJdbcTemplate();
jdbc.getJdbcOperations().execute("insert into file_blob (id, name, content_size, content, content_type, attributes) values(?, ?, ?, ?, ?, ?)",
new AbstractLobCreatingPreparedStatementCallback(lobHandler) {
@Override
protected void setValues(PreparedStatement ps, LobCreator lobCreator) throws SQLException {
ps.setString(1, digest);
ps.setString(2, file.getName());
ps.setLong(3, file.getFile().length);
lobCreator.setBlobAsBytes(ps, 4, file.getFile());
ps.setString(5, file.getType());
ps.setString(6, Json.GSON.toJson(attributes));
}
});
}
private NamedParameterJdbcTemplate revalidateJdbc (NamedParameterJdbcTemplate jdbc) {
if (jdbc != null) {
try {
NamedParameterJdbcTemplate newJdbc = request ();
if (jdbc == jdbcTmpl) {
jdbcTmpl = newJdbc;
data.logging (Log.DEBUG, "db", "Replaced default jdbc connection");
} else {
data.logging (Log.DEBUG, "db", "Replaced local jdbc connection");
}
jdbc = newJdbc;
} catch (Exception e) {
data.logging (Log.ERROR, "db", "Failed to create replacement jdbc connection", e);
jdbc = null;
}
}
return jdbc;
}
@Test
public void testInsertion() {
NamedParameterJdbcOperations namedParameterJdbcOperations = new NamedParameterJdbcTemplate(jdbcOperations);
Map<String, Object> mapA = new HashMap<>();
mapA.put("a", "hello1");
mapA.put("b", 42);
Map<String, Object> mapB = new HashMap<>();
mapB.put("a", "hello2");
mapB.put("b", null);
Map<String, Object> mapC = new HashMap<>();
mapC.put("a", "hello3");
channels.input().send(MessageBuilder.withPayload(mapA).build());
channels.input().send(MessageBuilder.withPayload(mapB).build());
channels.input().send(MessageBuilder.withPayload(mapC).build());
Assert.assertThat(namedParameterJdbcOperations.queryForObject(
"select count(*) from messages where a = :a and b = :b", mapA, Integer.class), is(1));
Assert.assertThat(namedParameterJdbcOperations.queryForObject(
"select count(*) from messages where a = :a and b IS NULL", mapB, Integer.class), is(1));
Assert.assertThat(namedParameterJdbcOperations.queryForObject(
"select count(*) from messages where a = :a and b IS NULL", mapC, Integer.class), is(1));
}
@Autowired
DemoModeDataManagerIntegrationTest(ConfigurationRepository configurationRepository,
OrganizationRepository organizationRepository,
UserManager userManager,
NamedParameterJdbcTemplate jdbcTemplate,
EventManager eventManager,
EventRepository eventRepository,
DemoModeDataManager demoModeDataManager) {
this.configurationRepository = configurationRepository;
this.organizationRepository = organizationRepository;
this.userManager = userManager;
this.jdbcTemplate = jdbcTemplate;
this.eventManager = eventManager;
this.eventRepository = eventRepository;
this.demoModeDataManager = demoModeDataManager;
}
public JdbcDataflowTaskExecutionMetadataDao(DataSource dataSource,
DataFieldMaxValueIncrementer incrementer) {
this.incrementer = incrementer;
this.jdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
this.objectMapper = new ObjectMapper();
SimpleModule module = new SimpleModule();
module.addDeserializer(Resource.class,
new ResourceDeserializer(new AppResourceCommon(new MavenProperties(), new DefaultResourceLoader())));
this.objectMapper.registerModule(module);
this.objectMapper.addMixIn(Resource.class, ResourceMixin.class);
this.objectMapper.addMixIn(AppDefinition.class, AppDefinitionMixin.class);
this.objectMapper.addMixIn(AppDeploymentRequest.class, AppDeploymentRequestMixin.class);
this.objectMapper.configure(SerializationFeature.FAIL_ON_EMPTY_BEANS, false);
this.dataSource = dataSource;
}
public static void log(String userId) {
if ( StringUtils.isBlank(userId) ) {
log.warn("null userId");
return;
}
NamedParameterJdbcTemplate namedParameterJdbcTemplate = (NamedParameterJdbcTemplate)AppContext.getBean("namedParameterJdbcTemplate");
Map<String, Object> paramMap = new HashMap<String, Object>();
paramMap.put("oid", SimpleUtils.getUUIDStr());
paramMap.put("user", userId);
paramMap.put("cuserid", "SYS");
paramMap.put("cdate", new Date());
try {
namedParameterJdbcTemplate.update("insert into tb_sys_login_log(OID, USER, CUSERID, CDATE) values(:oid, :user, :cuserid, :cdate)", paramMap);
} catch (Exception e) {
e.printStackTrace();
log.error( e.getMessage().toString() );
}
}
public static Object[] executeQueries(NamedParameterJdbcTemplate template, List<String> queries, Map<String, Object> args) {
queries = queries.stream().map(String::trim).filter(q -> !q.isEmpty()).collect(toList());
Object[] res = new Object[queries.size()];
for (int i = 0; i < queries.size(); i++) {
try {
res[i] = executeQuery(template, args, queries.get(i));
} catch (Exception e) {
String summary = InvocationUtil.findSqlSummary(e);
if (summary != null) {
throw new N2oUserException(summary);
}
throw new N2oException("SQL:" + queries.get(i)+ " Args:" + args, e);
}
}
return res;
}
/**
* 删除指定的表, 在删除期间disable外键的检查.
*/
public static void deleteTable(DataSource h2DataSource, String... tableNames) {
NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(h2DataSource);
template.update("SET REFERENTIAL_INTEGRITY FALSE",new HashMap<String, Object>());
for (String tableName : tableNames) {
template.update("DELETE FROM " + tableName,new HashMap<String, Object>());
}
template.update("SET REFERENTIAL_INTEGRITY TRUE",new HashMap<String, Object>());
}
@Autowired
@Required
@Resource(name="namedParameterJdbcTemplate")
public void setNamedParameterJdbcTemplate(
NamedParameterJdbcTemplate namedParameterJdbcTemplate) {
this.namedParameterJdbcTemplate = namedParameterJdbcTemplate;
}
@Bean
@Profile({"!"+Initializer.PROFILE_INTEGRATION_TEST, "travis"})
public DataSource getDataSource(Environment env, PlatformProvider platform) {
if(platform == PlatformProvider.CLOUD_FOUNDRY) {
return new FakeCFDataSource();
} else {
HikariDataSource dataSource = new HikariDataSource();
dataSource.setJdbcUrl(platform.getUrl(env));
dataSource.setUsername(platform.getUsername(env));
dataSource.setPassword(platform.getPassword(env));
dataSource.setDriverClassName("org.postgresql.Driver");
dataSource.setMaximumPoolSize(platform.getMaxActive(env));
dataSource.setMinimumIdle(platform.getMinIdle(env));
dataSource.setConnectionTimeout(1000L);
log.debug("Connection pool properties: max active {}, initial size {}", dataSource.getMaximumPoolSize(), dataSource.getMinimumIdle());
// check
boolean isSuperAdmin = Boolean.TRUE.equals(new NamedParameterJdbcTemplate(dataSource)
.queryForObject("select usesuper from pg_user where usename = CURRENT_USER",
new EmptySqlParameterSource(),
Boolean.class));
if (isSuperAdmin) {
log.warn("You're accessing the database using a superuser. This is highly discouraged since it will disable the row security policy checks.");
}
//
return dataSource;
}
}
@Override
public int add(Student student) {
// String sql = "insert into student(sno,sname,ssex) values(?,?,?)";
// Object[] args = { student.getSno(), student.getName(), student.getSex() };
// int[] argTypes = { Types.VARCHAR, Types.VARCHAR, Types.VARCHAR };
// return this.jdbcTemplate.update(sql, args, argTypes);
String sql = "insert into student(sno,sname,ssex) values(:sno,:name,:sex)";
NamedParameterJdbcTemplate npjt = new NamedParameterJdbcTemplate(this.jdbcTemplate.getDataSource());
return npjt.update(sql, new BeanPropertySqlParameterSource(student));
}
/**
* releases a former requested jdbc template
*
* @param temp the jdbc instance to be released
* @return null
*/
public NamedParameterJdbcTemplate release (NamedParameterJdbcTemplate temp) {
if (temp != jdbcTmpl) {
temp = null;
}
return null;
}
/**
* @param tableName
* 方法名： buildBarOrLineType1
* 功 能： TODO(这里用一句话描述这个方法的作用)
* 参 数： @param sbWhere
* 参 数： @param type1
* 参 数： @param info
* 参 数： @param map
* 返 回： void
* 作 者 ： Administrator
* @throws
*/
private void buildBarOrLineType1(StringBuilder sbWhere, String type1, SysDbmsChartDimension info, Map<String, Object> map, String tableName) {
StringBuilder sql = new StringBuilder();
Map<String, Object> param = new HashMap<>();
sql.append("SELECT " + type1 + " AS ask,COUNT(1) AS num FROM " + tableName + " t " + " WHERE " + type1 + " IS NOT NULL " + " AND " + type1 + " <>'' " + sbWhere.toString() + " " + " GROUP BY " + type1 + " " + "ORDER BY " + type1 + " ");
NamedParameterJdbcTemplate template2 = new NamedParameterJdbcTemplate(jdbcTemplate);
List<Map<String, Object>> listMap2 = template2.queryForList(sql.toString(), param);
List<Map<String, Object>> series_data = new ArrayList<>();
List<String> legend_data = new ArrayList<>();
legend_data.add("数量");
Map<String, Object> sdata = new HashMap<>();
sdata.put("type", "tbar".equals(info.getChartType()) ? "bar" : info.getChartType());
sdata.put("name", "数量");
List<Integer> series_data_data = new ArrayList<>();
List<String> xAxis_data = new ArrayList<>();
for (Map<String, Object> map2 : listMap2) {
series_data_data.add(Integer.valueOf(map2.get("num").toString()));
xAxis_data.add(map2.get("ask").toString());
}
sdata.put("data", series_data_data);
series_data.add(sdata);
map.put("series_data", series_data);
map.put("xAxis_data", xAxis_data);
map.put("legend_data", legend_data);
map.put("chartType", info.getChartType());
}
public GenericDaoHelper(
DataSource dataSource
) {
this.dataSource = dataSource;
this.jdbcTemplate = new JdbcTemplate(dataSource);
this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
}
public Retry (String nName, DBase nDBase, NamedParameterJdbcTemplate nJdbc, T nPriv) {
name = nName;
dbase = nDBase;
jdbc = nJdbc;
priv = nPriv;
error = null;
}
@Autowired
public JdbcVisitRepositoryImpl(DataSource dataSource) {
this.jdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
this.insertVisit = new SimpleJdbcInsert(dataSource)
.withTableName("visits")
.usingGeneratedKeyColumns("id");
}
private long doQueryLong (NamedParameterJdbcTemplate jdbc, String q, Map <String, Object> packed) throws SQLException {
show ("QYL", q, packed);
Retry <Object> r = new Retry <Object> ("queryLong", this, jdbc) {
@Override
public void execute () throws SQLException {
priv = (new DBAccessSingle <Long> (data, jdbc)).query (q, packed);
}
};
if (retry (r)) {
return r.priv != null ? ((Number) r.priv).longValue () : 0L;
}
throw failure (q, r.error);
}
public List<String> findOperationsFromAllApis(List<Long> apiIds) {
Map<String, Object> params = new HashMap<>();
params.put("ids", apiIds);
StringBuilder sql = new StringBuilder(190);
sql.append("SELECT CONCAT(API.BASE_PATH, OP.PATH) ");
sql.append("FROM OPERATIONS OP ");
sql.append("INNER JOIN RESOURCES RES ON OP.RESOURCE_ID = RES.ID ");
sql.append("INNER JOIN APIS API ON RES.API_ID = API.ID ");
sql.append("WHERE API.ID IN (:ids) ");
return new NamedParameterJdbcTemplate(jdbcTemplate).queryForList(sql.toString(), params, String.class);
}
/**
* 支持miniDao语法操作的查询
*
* @param dbKey 数据源标识
* @param sql 执行sql语句，sql支持minidao语法逻辑
* @param data sql语法中需要判断的数据及sql拼接注入中需要的数据
* @return
*/
public static List<Map<String, Object>> findListByHash(final String dbKey, String sql, HashMap<String, Object> data) {
List<Map<String, Object>> list;
JdbcTemplate jdbcTemplate = getJdbcTemplate(dbKey);
//根据模板获取sql
sql = FreemarkerParseFactory.parseTemplateContent(sql, data);
NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate.getDataSource());
list = namedParameterJdbcTemplate.queryForList(sql, data);
return list;
}
/**
* 支持miniDao语法操作的查询 返回单列数据list
*
* @param dbKey 数据源标识
* @param sql 执行sql语句，sql支持minidao语法逻辑
* @param clazz 类型Long、String等
* @param data sql语法中需要判断的数据及sql拼接注入中需要的数据
* @return
*/
public static <T> List<T> findListByHash(final String dbKey, String sql, Class<T> clazz, HashMap<String, Object> data) {
List<T> list;
JdbcTemplate jdbcTemplate = getJdbcTemplate(dbKey);
//根据模板获取sql
sql = FreemarkerParseFactory.parseTemplateContent(sql, data);
NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate.getDataSource());
list = namedParameterJdbcTemplate.queryForList(sql, data, clazz);
return list;
}
/**
* 方法名： buildPie
* 功 能： TODO(这里用一句话描述这个方法的作用)
* 参 数： @param map
* 参 数： @param info
* 参 数： @param list
* 返 回： void
* 作 者 ： Administrator
* @throws
*/
public void buildPie(Map<String, Object> map, SysDbmsChartDimension info, StringBuilder sbWhere, String type1, String tableName) {
List<String> legend_data = new ArrayList<>();
List<Map<String, Object>> series_data = new ArrayList<>();
StringBuilder sql = new StringBuilder();
Map<String, Object> param = new HashMap<>();
// 默认表结构
sql.append(" select " + type1 + " as aks,count(1) as num");
sql.append(" from " + tableName + " ");
sql.append(" where 1=1 ");
sql.append(" and " + type1 + " is not null ");
sql.append(" and " + type1 + " <> '' ");
sql.append(sbWhere.toString());
sql.append(" group by " + type1);
NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(jdbcTemplate);
List<Map<String, Object>> listMap = template.queryForList(sql.toString(), param);
for (Map<String, Object> map2 : listMap) {
legend_data.add(map2.get("aks").toString());
// {value:92503371, name:'男'}
Map<String, Object> data = new HashMap<>();
data.put("value", Integer.valueOf(map2.get("num").toString()));
data.put("name", map2.get("aks").toString());
series_data.add(data);
}
map.put("legend_data", legend_data);
map.put("series_data", series_data);
map.put("chartType", info.getChartType());
}
public OrderRepresentationService(OrderRepository orderRepository,
NamedParameterJdbcTemplate jdbcTemplate,
DefaultObjectMapper objectMapper) {
this.orderRepository = orderRepository;
this.jdbcTemplate = jdbcTemplate;
this.objectMapper = objectMapper;
}
public List<Book> findByTitle(String phrase) {
NamedParameterJdbcTemplate named =
new NamedParameterJdbcTemplate(jdbcTemplate);
SqlParameterSource namedParameters
= new MapSqlParameterSource("search_phrase", phrase);
String sql = "SELECT * FROM book WHERE title = :search_phrase";
return named.query(
sql,
namedParameters,
new BeanPropertyRowMapper<>(Book.class));
}
@Autowired
public JdbcPetRepositoryImpl(DataSource dataSource, OwnerRepository ownerRepository, VisitRepository visitRepository) {
this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
this.insertPet = new SimpleJdbcInsert(dataSource)
.withTableName("pets")
.usingGeneratedKeyColumns("id");
this.ownerRepository = ownerRepository;
this.visitRepository = visitRepository;
}
@Override
public List<CandidateSnapshot> getSnapshotAfterVersion(ConfigMeta meta, Long version) {
if (version < 0) {
return Lists.newArrayList();
}
NamedParameterJdbcTemplate nameJdbc = new NamedParameterJdbcTemplate(jdbcTemplate);
MapSqlParameterSource parameter = new MapSqlParameterSource();
parameter.addValue("group_id", meta.getGroup());
parameter.addValue("version", version);
parameter.addValue("profile", meta.getProfile());
parameter.addValue("data_id", meta.getDataId());
return nameJdbc.query(SELECT_VERSION_AFTER_OPERATOR, parameter, SNAPSHOT_MAPPER);
}
private static String processCsvText(ExportDataConfig config, NamedParameterJdbcTemplate jdbcTemplate,
Map<String, Object> sqlParamMap) throws Exception {
logger.info("export-Id: " + config.getId() + " name: " + config.getName());
StringBuilder out = new StringBuilder();
out.append(config.getTitle()).append("\r\n");
List<Map<String, Object>> results = jdbcTemplate.queryForList(config.getSql(), sqlParamMap);
for (int i=0; results!=null && i<results.size(); i++) {
Map<String, Object> dataMap = results.get(i);
for (Map.Entry<String, Object> entry : dataMap.entrySet()) {
if ( entry.getValue() != null ) {
String str = "";
if (entry.getValue() instanceof byte[]) { // blob text
str = new String( (byte[])entry.getValue() , Constants.BASE_ENCODING );
} else {
str = String.valueOf( entry.getValue() );
}
if (config.isEscapeCsv()) {
//str = StringEscapeUtils.escapeCsv(str);
str = SimpleUtils.escapeCsv(str);
}
if (StringUtils.isBlank(str)) {
str = " ";
}
out.append("\"").append(str).append("\"");
} else {
out.append(" ");
}
out.append( config.getSeparateSymbol() );
}
out.append("\r\n");
}
return out.toString();
}
@Test
public void test1 () {
JdbcTaskExecutionRepository taskRepository = new JdbcTaskExecutionRepository();
taskRepository.setJdbcOperations(new NamedParameterJdbcTemplate(dataSource));
taskRepository.setObjectMapper(createObjectMapper());
JdbcJobRepository jobRepository = new JdbcJobRepository();
jobRepository.setJdbcOperations(new NamedParameterJdbcTemplate(dataSource));
jobRepository.setJobTaskRepository(taskRepository);
int pageTotal = jobRepository.getPage(1).getSize();
String id = UUIDGenerator.generate();
SimpleJob job = new SimpleJob();
job.setPipelineId("demo:1234");
job.setId(id);
job.setCreateTime(new Date());
job.setStatus(JobStatus.CREATED);
jobRepository.create(job);
Page<JobSummary> all = jobRepository.getPage(1);
Assertions.assertEquals(pageTotal+1,all.getSize());
Job one = jobRepository.getById(id);
Assertions.assertNotNull(one);
}
@Autowired
public void setDataSource(final DataSource dataSource) {
jdbcTemplate = new JdbcTemplate(dataSource);
namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
simpleJdbcInsert = new SimpleJdbcInsert(dataSource).withTableName("EMPLOYEE");
}
/**
* @方法名 findAllBySysTableInfoAndUsername
* @功能 TODO(这里用一句话描述这个方法的作用)
* @参数 @param sysDbmsTabsInfo
* @参数 @return
* @返回 List<SysDbmsTabsInfo>
* @author Administrator
* @throws
*/
public List<SysDbmsTabsInfo> findAllBySysTableInfoAndUsername(SysDbmsTabsInfo sysDbmsTabsInfo) {
// 多条件时循环查询并找出userindex都有的表
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.append("select distinct * from sys_dbms_tabs_info a ");
stringBuilder.append(" where a.uuid in ( ");
stringBuilder.append(" select b.tabs_id from sys_roles_tabs_info b ");
stringBuilder.append(" where b.role_id in (");
stringBuilder.append(" select c.roles_id from sys_user_roles_info c");
stringBuilder.append(" where c.user_id in ( ");
stringBuilder.append(" select d.uuid from sys_user_base_info d");
stringBuilder.append(" where d.user_name = '" + sysDbmsTabsInfo.getCreateUser() + "'");
stringBuilder.append(" ) and c.checked = 1");
stringBuilder.append(" ) ");
stringBuilder.append(" ) and a.delete_flag = 0");
if (sysDbmsTabsInfo.getTypeUuid() != null && !"".equals(sysDbmsTabsInfo.getTypeUuid())) {
stringBuilder.append(" and a.type_uuid = '" + sysDbmsTabsInfo.getTypeUuid() + "' ");
}
if (sysDbmsTabsInfo.getJdbcUuid() != null && !"".equals(sysDbmsTabsInfo.getJdbcUuid())) {
stringBuilder.append(" and a.jdbc_uuid = '" + sysDbmsTabsInfo.getJdbcUuid() + "' ");
}
stringBuilder.append(" order by a.tabs_order ");
NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(jdbcTemplate);
List<SysDbmsTabsInfo> tabsList = template.query(stringBuilder.toString(), new BeanPropertyRowMapper<>(SysDbmsTabsInfo.class));
// 多条件查询
return tabsList;
}