DDL(Create，Drop，ALTER)常用execute方法
insert，修改update，删除delete常用update方法
nd.esp.service.lifecycle.daos.common
查询query和queryForxxx方法
jdbc+NamedParameterJdbcTemplate
jdbcTemple用法
重要
/1.查询一行数据并返回int型结果
jdbcTemplate.queryForInt("select count(*) from test");
//2. 查询一行数据并将该行数据转换为Map返回
jdbcTemplate.queryForMap("select * from test where name='name5'");
//3.查询一行任何类型的数据，最后一个参数指定返回结果类型
jdbcTemplate.queryForObject("select count(*) from test", Integer.class);
//4.查询一批数据，默认将每行数据转换为Map
jdbcTemplate.queryForList("select * from test");
//5.只查询一列数据列表，列类型是String类型，列名字是name
jdbcTemplate.queryForList("
select name from test where name=?", new Object[]{"name5"}, String.class);
//6.查询一批数据，返回为SqlRowSet，类似于ResultSet，但不再绑定到连接上
SqlRowSet rs = jdbcTemplate.queryForRowSet("select * from test");
简单的queryForxxx方法
String querySql = "select distinct rr.source_uuid as cid,tm.identifier as tmid";
querySql += " FROM resource_relations rr INNER JOIN chapters c ON rr.source_uuid=c.identifier";
querySql += " INNER JOIN ndresource tm ON c.teaching_material=tm.identifier";
querySql += " WHERE rr.enable=1 and rr.res_type='chapters' AND rr.resource_target_type='"
+ resType + "'";
querySql += " AND rr.target='" + resId + "'";
querySql += " AND tm.primary_category='teachingmaterials' AND tm.enable=1";
final Map<String, String> map = new HashMap<String, String>();
defaultJdbcTemplate.query(querySql,
new RowMapper<Map<String, String>>() {
@Override
public Map<String, String> mapRow(ResultSet rs, int rowNum)
throws SQLException {
map.put(rs.getString("cid"), rs.getString("tmid"));
return null;
}
});
return map;
没有防注入，直接query实现
Map<String, Object> params = new HashMap<String, Object>();
params.put("schoolId", schoolId);
if (StringUtils.hasText(resType)) {
params.put("resType", resType);
}
if (StringUtils.hasText(queryDate)) {
params.put("queryDate", queryDate);
}
String querySql = sqlStringBuffer.toString();
LOG.info("sql语句为" + querySql);
final List<HourDataModel> resultList = new ArrayList<HourDataModel>();
NamedParameterJdbcTemplate namedJdbcTemplate = new NamedParameterJdbcTemplate(
defaultJdbcTemplate);
namedJdbcTemplate.query(querySql, params, new RowMapper<String>() {
@Override
public String mapRow(ResultSet rs, int rowNum) throws SQLException {
HourDataModel hdm = new HourDataModel();
hdm.setHour(rs.getString("hour"));
hdm.setData(rs.getInt("data"));
resultList.add(hdm);
return null;
}
});
sql查询的时候要防止注入,用NamedParameterJdbcTemplate namedJdbcTemplate = new NamedParameterJdbcTemplate(
defaultJdbcTemplate);来做
@Override
public boolean updatePreview(String resType, String resId, Map<String,String> preview) {
JdbcTemplate jdbcTemplateInUse = jdbcTemplate;
if (IndexSourceType.QuestionType.getName().equals(resType) || IndexSourceType.SourceCourseWareObjectType.equals(resType)) {
jdbcTemplateInUse = questionJdbcTemplate;
}
String sql = "UPDATE ndresource SET preview='" + ObjectUtils.toJson(preview) + "' WHERE identifier = '" + resId + "'";
LOG.info(jdbcTemplateInUse.toString() + "; preview更新sql:"+sql);
// contributeRepository.getEntityManager().createNativeQuery(sql).executeUpdate();
jdbcTemplateInUse.execute(sql);
return true;
}
update代码（包地址nd.esp.service.lifecycle.daos.ResLifecycle.v06.imp），这个没得防注入，就是直接写好的sql语句直接执行
public int insert(T model, String tableName, boolean isIncludeAutoIncrementField) {
StringBuilder sqlStringBuilder = new StringBuilder("INSERT INTO " + tableName + "");
List<Object> paramsList = new ArrayList<Object>();
try {
StringBuilder keyStringBuilder = new StringBuilder(" (");
StringBuilder valueStringBuilder = new StringBuilder(" (");
for (Field field : getDeclaredFieldsIncludeSuperClasses(model.getClass())) {
field.setAccessible(true);
Object fieldValue = field.get(model);
if (fieldValue == null)
continue;
if (fieldValue instanceof Collection<?> || fieldValue instanceof Array)
fieldValue = JSONArray.toJSONString(fieldValue);
else if (fieldValue instanceof Enum)
fieldValue = fieldValue.toString();
if (!field.isAnnotationPresent(Column.class))
continue;
if (isIncludeAutoIncrementField == false &&
field.isAnnotationPresent(AutoIncrement.class))
continue;
Column annotation = field.getAnnotation(Column.class);
String columnName = annotation.name();
keyStringBuilder.append(" `" + columnName + "`,");
valueStringBuilder.append(" ?,");
paramsList.add(fieldValue);
}
keyStringBuilder.setCharAt(keyStringBuilder.length() - 1, ')');
valueStringBuilder.setCharAt(valueStringBuilder.length() - 1, ')');
sqlStringBuilder.append(keyStringBuilder.toString());
sqlStringBuilder.append(" VALUES ");
sqlStringBuilder.append(valueStringBuilder.toString());
} catch (IllegalArgumentException | IllegalAccessException e) {
logger.warn("BaseDao.insert-IllegalArgument： " + e.getMessage());
}
sqlStringBuilder.append(";");
// return jdbcTemplate.update(sqlStringBuilder.toString());
KeyHolder keyHolder = new GeneratedKeyHolder();
final String sql = sqlStringBuilder.toString();
final Object[] params = paramsList.toArray();
jdbcTemplate.update(new PreparedStatementCreator() {
@Override
public PreparedStatement createPreparedStatement(
Connection con) throws SQLException {
PreparedStatement preparedStatement = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
for (int i = 0; i < params.length; i++) {
preparedStatement.setObject(i+1, params[i]);
}
return preparedStatement;
}
}, keyHolder);
// Get newly inserted id.
Number key = keyHolder.getKey();
// If auto generated key is null, return 1.
return key == null ? 1 : key.intValue();
}
有防止注入的update，他是用匿名类来做的（在这个包内nd.esp.service.lifecycle.daos.common）
重点学习（这个包nd.esp.service.lifecycle.daos.teachingmaterial.v06.impl）中有关jdbc代码
public void batchSave(final List<String> coverageList, final String userId){
if(coverageList != null && coverageList.size() > 0){
String sql = "insert into " + TABLE_POSTFIX + "(user_id, coverage,create_time) values (?,?,?) "
+ "on duplicate key update create_time = ?";
jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps, int i) throws SQLException {
String coverage = coverageList.get(i);
ps.setString(1, userId);
ps.setString(2, coverage);
ps.setTimestamp(3, new Timestamp(System.currentTimeMillis()));
ps.setTimestamp(4, new Timestamp(System.currentTimeMillis()));
}
@Override
public int getBatchSize() {
return coverageList.size();
}
});
}
}
批量插入batchUpdate，insert的防注入的（这个包nd.esp.service.lifecycle.daos.usercoveragemapping.v06.impl）
public void batchDelete(final List<String> coverageList, final String userId){
if(coverageList != null && coverageList.size() > 0){
String sql = "DELETE FROM " + TABLE_POSTFIX + " WHERE user_id = ? AND coverage = ?";
jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps, int i) throws SQLException {
String coverage = coverageList.get(i);
ps.setString(1, userId);
ps.setString(2, coverage);
}
@Override
public int getBatchSize() {
return coverageList.size();
}
});
}
}
批量删除 batchUpdate （nd.esp.service.lifecycle.daos.usercoveragemapping.v06.impl）
public List<String> findUserCoverageList(String userId) {
List<Object> args = new ArrayList<Object>();
StringBuffer sql = new StringBuffer();
sql.append(" SELECT coverage FROM " + TABLE_POSTFIX + " WHERE user_id = ? ");
args.add(userId);
if(StringUtils.isNotBlank(userId)){
return this.jdbcTemplate.queryForList(sql.toString(), String.class, args.toArray());
}else{
return new ArrayList<String>();
}
}
查找的，简单防注入（这个包nd.esp.service.lifecycle.daos.usercoveragemapping.v06.impl）
