JdbcTemplate用法

1.批量插入

public void insertCompanys(final List<YearCheck> yearChecks)
{
if (yearChecks != null && 0 != yearChecks.size())
{
StringBuffer sbf = new StringBuffer();
sbf.append("INSERT INTO COMPANY_YEAR_CHECK");
sbf.append("(cp_name,tfx,");
sbf.append("record_no,record_date,");
sbf.append("record_year,record_valid_date,");
sbf.append("state)");
sbf.append(" VALUES (?,?,?,?,?,?,?)");
jdbcTemplate.batchUpdate(sbf.toString(), new BatchPreparedStatementSetter()
{
@Override
public void setValues(java.sql.PreparedStatement ps, int i) throws SQLException
{
// TODO Auto-generated method stub
YearCheck yearCheck = yearChecks.get(i);
int j = 0;
ps.setString(++j, yearCheck.getCpName());
ps.setString(++j, yearCheck.getTfx());
ps.setString(++j, yearCheck.getRecordNo());
ps.setString(++j, yearCheck.getRecordDate());
ps.setString(++j, yearCheck.getRecordYear());
ps.setString(++j, yearCheck.getRecordValidDate());
ps.setString(++j, yearCheck.getState());
}


@Override
public int getBatchSize()
{
return yearChecks.size();
}


});
}

2.查询数量

public int getMaxNo()
{
String sql = "SELECT COUNT(*) FROM companysource";
return jdbcTemplate.queryForObject(sql, null,Integer.class);
}

3.分页查询

public List<YearCheck> getChYearCheckSource(Integer id,Integer pageSize)
{
StringBuffer sbf = new StringBuffer();
//select * from table limit (start-1)*limit,limit
sbf.append(" SELECT * FROM companysource WHERE ID >= "+id+" AND ID < "+(pageSize+id));
List<YearCheck> yearChecks = jdbcTemplate.query(sbf.toString(), new RowMapper<YearCheck>()
{
@Override
public YearCheck mapRow(ResultSet rs, int rowNum) throws SQLException
{
YearCheck yearCheck = new YearCheck();
yearCheck.setCpName(rs.getString("企业名称"));
yearCheck.setTfx(rs.getString("统一社会信用代码"));
return yearCheck;
}
});
return yearChecks;
}

4.批量更新,设置值之间不能用and要用 ,

public void updateTradeMarkSJs(final List<TradeMarkSJ> list)
{
String sql = "UPDATE TB_TRADEMARKSJ SET FLAG=?,index=?  where ANN_NUM=? and RN=?";
jdbcTemplate1.batchUpdate(sql, new BatchPreparedStatementSetter()
{
public int getBatchSize()
{
return list.size();
// 这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
}


@Override
public void setValues(PreparedStatement ps, int i) throws SQLException
{
TradeMarkSJ tradeMarkSJ = (TradeMarkSJ) list.get(i);
ps.setString(1, "1");
ps.setString(2, "2");
ps.setString(3, tradeMarkSJ.getAnnNum());
ps.setString(4, tradeMarkSJ.getRn());
}
});
}