spring-jdbc常用sql语句

随时补充，有例子说明

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
		});
	}