SpringMVC调用数据库的一些方法

1.返回Id的KeyHolder：

 代码使用实例：

final String sql="INSERT INTO t_user (name,sex,height,cdate) VALUES(?,?,?,?)";
KeyHolder keyHolder = new GeneratedKeyHolder();
db.update(new PreparedStatementCreator() {
    public java.sql.PreparedStatement createPreparedStatement(
     Connection conn) throws SQLException {
                int i = 0;
               PreparedStatement ps = conn.prepareStatement(sql,PreparedStatement.RETURN_GENERATED_KEYS);
               ps.setString(++i, "商建兴");
               ps.setInt(++i, 0);
               ps.setDouble(++i, 1.75);
               ps.setLong(++i,System.currentTimeMillis());
               return ps;
     }
}, keyHolder);
int id = keyHolder.getKey().intValue();

JdbcTemplate源码：

@Override
    public int update(final PreparedStatementCreator psc, final KeyHolder generatedKeyHolder)
            throws DataAccessException {

        Assert.notNull(generatedKeyHolder, "KeyHolder must not be null");
        logger.debug("Executing SQL update and returning generated keys");

        return execute(psc, new PreparedStatementCallback<Integer>() {
            @Override
            public Integer doInPreparedStatement(PreparedStatement ps) throws SQLException {
                int rows = ps.executeUpdate();
                List<Map<String, Object>> generatedKeys = generatedKeyHolder.getKeyList();
                generatedKeys.clear();
                ResultSet keys = ps.getGeneratedKeys();
                if (keys != null) {
                    try {
                        RowMapperResultSetExtractor<Map<String, Object>> rse =
                                new RowMapperResultSetExtractor<Map<String, Object>>(getColumnMapRowMapper(), 1);
                        generatedKeys.addAll(rse.extractData(keys));
                    }
                    finally {
                        JdbcUtils.closeResultSet(keys);
                    }
                }
                if (logger.isDebugEnabled()) {
                    logger.debug("SQL update affected " + rows + " rows and returned " + generatedKeys.size() + " keys");
                }
                return rows;
            }
        });
    }

注：如果不需要主键，不要使用该方法添加数据，因为这样会增加服务器负担。PreparedStatement 引用的变量都是final修饰的常量。

2.批处理 batchUpdate（sql）:
代码实例：

String[] sql = new String[3];
sql[0] = "INSERT INTO t_user (name,sex,height,cdate) VALUES('商建兴',175,NOW())";
sql[1] = "UPDATE t_user SET  name= '小胡'  WHERE id=12";
sql[3] = "INSERT INTO t_user (name,sex,height,cdate) VALUES('小华',175,NOW())";
int[] i = jdbcTemplate.batchUpdate(sql);

说明：传入值：string数组，返回int数组。 注意数组下标和数组越界

3.返回实体类 或某个类型 queryForObject

代码实例：

	public User findCust(String code ) {
		String sql = "select * from t_user where code=?";
                User u=new User();
		try {
			u = db.queryForObject(sql,BeanPropertyRowMapper.newInstance(User.class),code);
			return u;
		} catch (org.springframework.dao.EmptyResultDataAccessException e) {
			return new User();
		}
	}
返回int，方便判断是否存在：（实例判断是否有该商品）
public boolean iscunzai(int userId,String proId,String actId,String act_type){
		String issql="SELECT COUNT(1) FROM t_shopping_card WHERE user_id=? AND pro_id=? AND act_pro_id=? and act_typ=?";
			int i = db.queryForObject(issql, Integer.class,userId,proId,actId,act_type);
			return	i>0;
	}

4.直接返回 list  query  用于实体list列表

 1.   
 String sql = "select * from t_user where user_id=? GROUP BY cardId  order by join_card_time desc";
     List<User> list=db.query(sql, BeanPropertyRowMapper.newInstance(User.class),userId);
2.List<Brand> custList = jdbcTemplate.query(sql, new ProjCustRowMapper());//创建一个方法实现RowMapper ，自定义查询的返回值
public class ProjCustRowMapper implements RowMapper {
		@Override
		public Object mapRow(ResultSet rs, int n) throws SQLException {
			Customers cust = new Customers();
			cust.setCustomer_id(rs.getInt("c.customer_id"));
			cust.setName(rs.getString("c.name"));
			cust.setSex(rs.getInt("c.sex"));
			Contract contract = new Contract();
			contract.setContract_num(rs.getString("ctc.contract_num"));
			contract.setAmount(rs.getFloat("ctc.amount"));
			return cust;
		}
	}

5.返回List<Map<String, Object>> queryForList   用于只取某个单一的字段

String sql = "select help_code from t_index ";
List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
if(null!list&&list.size()>0){ //注意判断list不为空
    list.get(0).get("help_code ");
}

6.修改/添加 update

int i=jdbcTemplate.update(sql,val1，val2...);返回值是int

7.其他：execute（），queryForRowSet（），queryForInt（），queryForMap（），queryForLong（）