随时补充,有例子说明
sql无非是增删改查,一个一个来,先从删开始
##删
###1.sql带变量的
String sql ="DELETE FROM T_BASE WHERE ID=?";
return jdbcTemplate.update(sql,new Object[]{
id
}, new int[]{
java.sql.Types.VARCHAR
});
//返回值是数字
###2.删除有多个限定参数的(一个类)
public int delYearValue(YearInfo yearInfo) {
String sql ="DELETE FROM T_DATE WHERE INDUSTRY_ID=? AND COMPANY_ID=? AND YEAR=?";
return jdbcTemplate.update(sql,new Object[]{
yearInfo.getIndustryId(),
yearInfo.getCompanyId(),
yearInfo.getYear()
}, new int[]{
java.sql.Types.VARCHAR,
java.sql.Types.VARCHAR,
java.sql.Types.VARCHAR
});
}
###2.删除有多个限定参数的(非一个类)
public int delYearInfo(String year,String parkId) {
List<Object> params=new LinkedList<Object>();
String sql = "DELETE FROM T_DATA_PEOPLE WHERE YEAR = ? AND PARK_ID = ?";
params.add(year);
params.add(parkId);
return jdbcTemplate.update(sql,params.toArray());
}
##查
###1.sql不含任何变量,查询结果是list<object>
的
String sql ="SELECT ID,NAME FROM T_BASE";
return jdbcTemplate.query(sql, new RowMapper<IndustryInfo>(){
@Override
public IndustryInfo mapRow(ResultSet rs, int arg1) throws SQLException {
IndustryInfo industryInfo=new IndustryInfo();
industryInfo.setId(rs.getString("ID"));
industryInfo.setName(rs.getString("NAME"));
return industryInfo;
}});
}
//返回结果是list<industryInfo>
###2.1.sql带多个变量,查询结果是list<object>
的
List<Object> params = new ArrayList<Object>();
String sql =" SELECT ID, NAME FROM T_BASE WHERE NAME LIKE ? ";
params.add("%"+searchValue+"%");
return jdbcTemplate.query(sql, new RowMapper<CompanyInfo>() {
@Override
public CompanyInfo mapRow(ResultSet rs, int index) throws SQLException {
CompanyInfo companyInfo = new CompanyInfo();
companyInfo.setId(rs.getString("ID"));
companyInfo.setName(rs.getString("NAME"));
return companyInfo;
}},params.toArray());
###3.查询数量,可带可不带参数的
String sql = "SELECT COUNT(*) FROM T_BASE";
if(name != null && nName != ""){
sql += " AND NAME LIKE '%" + nName + "%'";
}
return jdbcTemplate.queryForObject(sql, null, Integer.class);
//返回的是条数count
###3.查询数量,带多个参数的
List<Object> params = new ArrayList<Object>();
String sql = "select count(*) from T_BASE_INDUSTRY where NAME = ? and id = ?";
params.add(name);
params.add(id);
int total = jdbcTemplate.queryForObject(sql, params.toArray(),Integer.class);
return total;
##新增
###1.新增数据带多个参数(废话)
public int addIndustryInfo(IndustryInfo ind) {
String sql ="INSERT INTO T_BASE (ID,NAME) VALUES(?,?)";
return jdbcTemplate.update(sql, new Object[]{
ind.getId(),
ind.getName()
}, new int[]{
java.sql.Types.VARCHAR,
java.sql.Types.VARCHAR
});
}
##改
###1.带多个参数(一个类)
public int editIndustryInfo(IndustryInfo ind) {
String sql = "UPDATE T_BASE_INDUSTRY SET NAME = ? WHERE ID = ?";
return jdbcTemplate.update(sql, new Object[]{
ind.getName(),
ind.getId()
}, new int[]{
java.sql.Types.VARCHAR,
java.sql.Types.VARCHAR
});
}