正文
jdbcTemplate进行CRUD，查询结果转json
提示：扫一扫查出行【扫一扫了解最新限行尾号】
复制提示
通过Spring的jdbcTemplate作为dao层的框架，将获取到的字段名，及其值，通过put放在jsonObject或jsonArray中，将json返回。
public class SpringJdbcService {
private static Logger logger = LoggerFactory.getLogger(SpringJdbcService.class, SpringJdbcService.class.getName());
@Autowired
private JdbcTemplate jdbcTemplate;
/**
* 查询
*
* @return JSONArray json数组
*/
public JSONArray getJSONArray(String sql, Object[] params) {
return getJSONArray(sql, params, false);
}
public JSONArray getJSONArray(String sql, Object[] params, final boolean toUpper) {
logger.info("get sql:" + sql);
if (params != null && params.length > 0) {
for (Object o : params) {
logger.info("value:" + o);
}
}
return jdbcTemplate.query(sql, params, new ResultSetExtractor<JSONArray>() {
@Override
public JSONArray extractData(ResultSet resultSet) throws SQLException, DataAccessException {
ResultSetMetaData rsd = resultSet.getMetaData();
int clength = rsd.getColumnCount();
JSONArray ja = new JSONArray();
String columnName;
try {
while (resultSet.next()) {
JSONObject jo = new JSONObject();
for (int i = 0; i < clength; i++) {
columnName = rsd.getColumnLabel(i + 1);
columnName = toUpper ? columnName.toUpperCase() : columnName.toLowerCase();
jo.put(columnName, resultSet.getObject(i + 1));
}
ja.put(jo);
}
} catch (Exception e) {
}
return ja;
}
});
}
public JSONObject getJSONObject(String sql) {
return getJSONObject(sql, new Object[]{});
}
/**
* 说明：查询，返回的是Json对象
*
* @return JSONObject
*/
public JSONObject getJSONObject(String sql, Object[] params) {
return getJSONObject(sql, params, false);
}
public JSONObject getJSONObject(String sql, Object[] params, final boolean toUpper) {
logger.info("save sql:" + sql);
if (params != null && params.length > 0) {
for (Object o : params) {
logger.info("value:" + o);
}
}
return jdbcTemplate.query(sql, params, new ResultSetExtractor<JSONObject>() {
@Override
public JSONObject extractData(ResultSet resultSet) throws SQLException, DataAccessException {
ResultSetMetaData rsd = resultSet.getMetaData();
int clength = rsd.getColumnCount();
String columnName;
try {
if (resultSet.next()) {
JSONObject jo = new JSONObject();
for (int i = 0; i < clength; i++) {
columnName = rsd.getColumnLabel(i + 1);
columnName = toUpper ? columnName.toUpperCase() : columnName.toLowerCase();
jo.put(columnName, resultSet.getObject(i + 1));
}
return jo;
}
} catch (Exception e) {
}
return null;
}
});
}
/**
* 说明：插入数据
*
* @param sql
* @param params
* @return
*/
public long insert(final String sql, final Object[] params) {
logger.info("save sql:" + sql);
if (params != null && params.length > 0) {
for (Object o : params) {
logger.info("value:" + o);
}
}
KeyHolder keyHolder = new GeneratedKeyHolder();
jdbcTemplate.update(new PreparedStatementCreator() {
public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
PreparedStatement ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
if (params == null) {
return ps;
}
Object op = null;
int alength = params.length;
for (int i = 0; i < alength; ++i) {
op = params[i];
StatementCreatorUtils.setParameterValue(ps, i + 1, -2147483648, op);
}
return ps;
}
}, keyHolder);
return keyHolder.getKey().longValue();
}
/**
* 添加
* Edited
*
* @param
* @return int
* <p>
* 注册保存的save1/，勿调用！！！
*/
public long save(final String sql, Object[] params) {
logger.info("save sql:" + sql);
if (params != null && params.length > 0) {
for (Object o : params) {
logger.info("value:" + o);
}
}
KeyHolder keyHolder = new GeneratedKeyHolder();
jdbcTemplate.update(new PreparedStatementCreator() {
@Override
public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
Blob b = connection.createBlob();
return ps;
}
}, keyHolder);
return keyHolder.getKey().longValue();
}
/**
* 修改
* Edited
*
* @param
* @return int
*/
public int update(String sql) {
int flag = jdbcTemplate.update(sql);
return flag;
}
/**
* 说明：更新
*
* @param sql
* @param params
* @return
*/
public int update(String sql, Object[] params) {
logger.info("save sql:" + sql);
if (params != null && params.length > 0) {
for (Object o : params) {
logger.info("value:" + o);
}
}
return jdbcTemplate.update(sql, params);
}
