Spring DAO

@Repository("mysqlDao")
public class MysqlDao {

	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	protected int add(String sql,Object[]params){
		System.out.println("[sql]"+sql);
		return jdbcTemplate.update(sql, params);
	}
	protected int update(String sql,Object[]params){
		System.out.println("[sql]"+sql);
		return jdbcTemplate.update(sql, params);
	}
	/**
	 * 插入
	 * @param table
	 * @param map
	 * @return 返回更新条数
	 */
	public int add(String table,Map<String, Object> map){
		if (map==null) {
			return 0;
		}
		List<String> fields_list = new ArrayList<String>();
		List<Object> values_list = new ArrayList<Object>();
		
		Set<String> fields = map.keySet();
		for (String field : fields) {
			Object value = map.get(field);
			if (value!=null) {
				fields_list.add(field);
				values_list.add(value);
			}
		}
		if (fields_list.size()>0) {
			String sql_fields = StringUtil.listToString(fields_list, ",");
			String sql_questions = StringUtil.loop("?", fields_list.size(), ",");
			Object[] sql_values = values_list.toArray();
			
			StringBuffer sql_sb = new StringBuffer();
			sql_sb.append("insert into "+table+"(");
			sql_sb.append(sql_fields);
			sql_sb.append(") values (");
			sql_sb.append(sql_questions);
			sql_sb.append(")");
			String sql = sql_sb.toString();
			System.out.println("[sql]"+sql);
			return jdbcTemplate.update(sql, sql_values);
			
		}else {
			return 0;
		}
	}
	/**
	 * 插入
	 * @param table
	 * @param map
	 * @return 返回id
	 */
	public long addForID(String table,Map<String, Object> map){
		if (map==null) {
			return 0;
		}
		List<String> fields_list = new ArrayList<String>();
		List<Object> values_list = new ArrayList<Object>();
		
		Set<String> fields = map.keySet();
		for (String field : fields) {
			Object value = map.get(field);
			if (value!=null) {
				fields_list.add(field);
				values_list.add(value);
			}
		}
		if (fields_list.size()>0) {
			String sql_fields = StringUtil.listToString(fields_list, ",");
			String sql_questions = StringUtil.loop("?", fields_list.size(), ",");
			Object[] sql_values = values_list.toArray();
			
			StringBuffer sql_sb = new StringBuffer();
			sql_sb.append("insert into "+table+"(");
			sql_sb.append(sql_fields);
			sql_sb.append(") values (");
			sql_sb.append(sql_questions);
			sql_sb.append(")");
			String sql = sql_sb.toString();
			System.out.println("[sql]"+sql);
			//----------
			KeyHolder keyHolder = new GeneratedKeyHolder();  
			final String _sql = sql;
			final Object[] params_sql =  sql_values;
			int updatecount = jdbcTemplate.update(new PreparedStatementCreator() {  
			    public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {  
			        PreparedStatement ps = connection.prepareStatement(_sql, Statement.RETURN_GENERATED_KEYS);
			        for (int i = 0; i < params_sql.length; i++) {
						ps.setObject(i+1, params_sql[i]);
					}
			        return ps;
			    }
			}, keyHolder);
			if (updatecount>0) {
				long id = keyHolder.getKey().longValue();
				return id;
			}else {
				return 0;
			}
//			return jdbcTemplate.update(sql, sql_values);
			
		}else {
			return 0;
		}
	}
	/**
	 * 修改
	 * @param table
	 * @param map
	 * @param id
	 */
	public int update(String table,Map<String, Object> map,Object id){
		if (map==null) {
			return 0;
		}
		List<String> fields_list = new ArrayList<String>();
		List<Object> values_list = new ArrayList<Object>();
		
		Set<String> fields = map.keySet();
		for (String field : fields) {
			Object value = map.get(field);
			if (value!=null) {
				String field2 = field+" = ?"; 
				fields_list.add(field2);
				values_list.add(value);
			}
		}
		if (fields_list.size()>0) {
			String sql_fields = StringUtil.listToString(fields_list, ",");
			
			StringBuffer sql_sb = new StringBuffer();
			sql_sb.append("update "+table+" set ");
			sql_sb.append(sql_fields);
			sql_sb.append(" where id = ? ");
			String sql = sql_sb.toString();
			
			values_list.add(id);
			Object[] sql_values = values_list.toArray();
			System.out.println("[sql]"+sql);
			return jdbcTemplate.update(sql, sql_values);
			
		}else {
			return 0;
		}
	}
	protected int delete(String sql,Object[]params){
		System.out.println("[sql]"+sql);
		return jdbcTemplate.update(sql, params);
	}
	protected int delete(String table,int id){
		String sql = "delete from "+table+ " where id = ? ";
		System.out.println("[sql]"+sql);
		return jdbcTemplate.update(sql, id);
	}
	protected int[] batchUpdate(String sql,List<Object[]> params){
		final List<Object[]> args = params;
		
		BatchPreparedStatementSetter batchParams = new BatchPreparedStatementSetter() {
			
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				Object[]param=args.get(i);
				for (int j = 0; j < param.length; j++) {
					ps.setObject(j+1, param[j]);
				}
			}
			public int getBatchSize() {
				
				return args.size();
			}
		};
		System.out.println("[sql]"+sql);
		return jdbcTemplate.batchUpdate(sql, batchParams);
		
	}
	protected int[] batchDelete(String sql,List<Object[]> params){
		final List<Object[]> args = params;
		
		BatchPreparedStatementSetter batchParams = new BatchPreparedStatementSetter() {
			
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				Object[]param=args.get(i);
				for (int j = 0; j < param.length; j++) {
					ps.setObject(j+1, param[j]);
				}
			}
			public int getBatchSize() {
				
				return args.size();
			}
		};
		System.out.println("[sql]"+sql);
		return jdbcTemplate.batchUpdate(sql, batchParams);
		
	}
	protected int queryCount(String sql,Object[]params){
		System.out.println("[sql]"+sql);
		return jdbcTemplate.queryForInt(sql,params);
	}
	protected List<Map<String, Object>> queryBySql(String sql,Object[]params){
		System.out.println("[sql]"+sql);
		return jdbcTemplate.queryForList(sql, params);
	}
	protected Map<String, Object> queryOneBySql(String sql,Object[]params){
		System.out.println("[sql]"+sql);
		return jdbcTemplate.queryForMap(sql, params);
	}
	protected int getLastId(){
		String sql_lastId = "select LAST_INSERT_ID()";
		return jdbcTemplate.queryForInt(sql_lastId);
	}

}