Spring JDBCTemplet 基本使用

一、查询，以及返回值得处理 

1 JDBCTeamplet查询出来的数据，如果名字和实体名字一样，则直接映射为实体 ，用BeanPropertyRowMapper

返回对象实体参数：

   ParameterizedBeanPropertyRowMapper.newInstance(Customer.class)

   new BeanPropertyRowMapper(Customer.class)  

 

 

 例如：根据条件查询，因为查询出来的字段与实体属性相同，则直接用BeanPropertyRowMapper返回即可

	public List<Person> findByCondition(Person person) {
 
		String sql = "select id,user_name userName ,age,tel from person where user_name like ?";
		RowMapper  rowMapper = new PersonRowMapper();
		return super.query(sql,  new Object[]{ "%"+person.getUserName()+"%"} ,   new BeanPropertyRowMapper( Person.class) );
	}

 2 如果返回的字段与实体的属性不一样，则需要自己定义一个实现RowMapper接口的类

如：1） 查询方法 

      

	public List<Person> findAll() {
		String sql = " select  id,user_name  ,age,tel  from person  ";
		RowMapper  rowMapper = new PersonRowMapper();
		//super.query(sql, new Object[]{areaId,"%"+schoolName+"%"} , new PersonRowMapper())
		return (List<Person>) super.query(sql,new PersonRowMapper());
	}

    2） 实现RowMapper接口的类 

	private static final class 	PersonRowMapper implements RowMapper{

		@Override
		public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
		 
			Person person = new Person();
			person.setAge( rs.getInt("age"));
			person.setId(rs.getInt("id"));
			person.setTel( rs.getInt("tel"));
			person.setUserName(rs.getString("user_name"));
			return person;
		}
		
		
	}

 

 3、查询返回自增的主键（Mysql测试）

  如：利用回调实现

	public Integer createReturnKey(final Person person) {
		
		final String sql = "INSERT INTO person (  user_name,age ,tel )VALUES(  ?,?,?)";
		
		KeyHolder keyHolder = new GeneratedKeyHolder();
		super.update(new PreparedStatementCreator (){
			@Override
			public PreparedStatement createPreparedStatement(Connection con)
					throws SQLException {
				PreparedStatement ps = con.prepareStatement(sql);
				ps.setObject(1, person.getUserName() );
				ps.setObject(2, person.getAge() );
				ps.setObject(3,  person.getTel() );
				return ps;
			}
		}, keyHolder) ; 
		return keyHolder.getKey().intValue();
	}

 

    2 增加返回插入条数

	public Integer create(Person person) {
		
		String sql = "INSERT INTO person (  user_name,age ,tel )VALUES(  ?,?,?)";
		Object[] obj = {person.getUserName(),person.getAge(),person.getTel()};
		return super.update(sql, obj );
		
	}

 二、删除 

  

	public Integer delete(int id ) {
		
		String sql = "delete from person where id=?";
		Object[] obj = {id};
		return super.update(sql, obj );
	}

 

三、更新 

	public Integer update(Person person) {
		String sql = "update person set user_name=?,age=? ,tel=? where id=? ";
		Object[] obj={person.getUserName(),person.getAge(),person.getTel(),person.getId() };
		return super.update(sql,  obj );
		
	}