spring之jdbcTemplate

" class="org.springframework.jdbc.core.JdbcTemplate"> <!-- 注入数据源 --> <property name="dataSource" ref="driverManagerDataSource"></property> </bean> </beans>

　　测试方法;

@RunWith(SpringJUnit4ClassRunner.class)  //整合junit4
@ContextConfiguration(locations="classpath:applicationContext.xml")//使用注解加载配置文件
public class SpringJDBCTest {
	@Autowired
	private JdbcTemplate jdbcTemplate;
	@Test
	public void test01(){
		String sql="insert into t_user values(2,‘张三‘,123,‘男‘)";
		jdbcTemplate.execute(sql);
		
	}
}

　　CRUD操作

　　　　创建表

CREATE DATABASE springtest;
USE springtest;
CREATE TABLE t_user(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
age INT,
sex VARCHAR(20)
)
INSERT INTO t_user VALUES(NULL,‘tom‘,20,‘男‘);
INSERT INTO t_user VALUES(NULL,‘fox‘,30,‘男‘);
INSERT INTO t_user VALUES(NULL,‘tony‘,40,‘男‘);

创建表

　　　　cud操作

@RunWith(SpringJUnit4ClassRunner.class)  //整合junit4
@ContextConfiguration(locations="classpath:applicationContext.xml")//使用注解加载配置文件
public class SpringJDBCTest {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Test    //添加数据
    public void insertData(){
        //String sql="insert into t_user values(4,‘张三‘,123,‘男‘)";
        //jdbcTemplate.execute(sql);
        String sql="insert into t_user values(?,?,?,?)";
        jdbcTemplate.update(sql,5,"王五",66,"女");
        
    }
    @Test    //删除数据
    public void deleteData(){
        //String sql="delete from  t_user where id=4 ";
        //jdbcTemplate.execute(sql);
        String sql="delete from  t_user where id=? ";
        jdbcTemplate.update(sql,5);
    }
    @Test    //修改数据
    public void updateData(){
        /*String sql="update t_user set name=‘李四‘ where id=3 ";
        jdbcTemplate.execute(sql);*/
        String sql="update t_user set name=? where id=? ";
        jdbcTemplate.update(sql,"jj",1);
    }
}

增删改

 

　　　　　查询操作

对没有封装的数据进行查询

@Test    //查询简单的一格数据
    public void seachOne3(){
        List<Map<String, Object>> list = jdbcTemplate.queryForObject("select * from t_user",new RowMapper<List<Map<String,Object>>>() {
            List<Map<String,Object>> list=new ArrayList<Map<String,Object>>();
            @Override
            public List<Map<String,Object>> mapRow(ResultSet rs, int rowNum) throws SQLException {
                System.out.println(rowNum);
                System.out.println("------");
                Map<String,Object> map=null;
                while(rs.next()){
                    map=new HashMap<String,Object>();
                    map.put("id",rs.getInt("id"));
                    map.put("name",rs.getString("name"));
                    map.put("age",rs.getInt("age"));
                    map.put("sex",rs.getString("sex"));
                    list.add(map);
                }
                return list;
            }
        });
        System.out.println(list);
    }

QueryForObject

 

@Test    //查询简单的一格数据
    public void seachOne3(){
        List<Map<String, Object>> list = jdbcTemplate.query("select * from t_user",new RowMapper<Map<String,Object>>() {
            List<Map<String,Object>> list=new ArrayList<Map<String,Object>>();
            @Override
            public Map<String,Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
                Map<String,Object> map=new HashMap<String,Object>();
                    map.put("id",rs.getInt("id"));
                    map.put("name",rs.getString("name"));
                    map.put("age",rs.getInt("age"));
                    map.put("sex",rs.getString("sex"));
                return map;
            }
        });
        System.out.println(list);
    }

query

对封装的数据进行查询

@Test    //查询简单的一格数据
    public void seachOne2(){
        List<User> list = jdbcTemplate.queryForObject("select * from t_user",new RowMapper<List<User>>() {
            List<User> list=new ArrayList<User>();
            @Override
            public List<User> mapRow(ResultSet rs, int rowNum) throws SQLException {
                //System.out.println(rs.getRow());
                    //do {
                        User u=new User();
                        u.setId(rs.getInt("id"));
                        u.setName(rs.getString("name"));
                        u.setAge(rs.getInt("age"));
                        u.setSex(rs.getString("sex"));
                        list.add(u);
                    //} while (rs.next());
                    return list;
            }
        });
        System.out.println(list);
    }

queryForObjcet

 

@Test    //查询简单的一格数据
    public void seachOne4(){
        List<User> list = jdbcTemplate.query("select * from t_user",new RowMapper<User>() {
            List<User> list=new ArrayList<User>();
            @Override
            public User mapRow(ResultSet rs, int rowNum) throws SQLException {
                        User u=new User();
                        u.setId(rs.getInt("id"));
                        u.setName(rs.getString("name"));
                        u.setAge(rs.getInt("age"));
                        u.setSex(rs.getString("sex"));
                        
                    return u;
            }
        });
        System.out.println(list);
    }

query

 

query 和queryForObject的区别

query是底层会对查询出来的数据进行处理,比如查询结果为空会返回为空,并不会抛异常;query的返回值会根据第二个参数来对返回进行区分

@Override
	public void query(String sql, RowCallbackHandler rch) throws DataAccessException {   
		query(sql, new RowCallbackHandlerResultSetExtractor(rch));
	}

	@Override
	public <T> List<T> query(String sql, RowMapper<T> rowMapper) throws DataAccessException { 返回值为T的集合
		return query(sql, new RowMapperResultSetExtractor<T>(rowMapper));
	}

　　

@Override
	public List<T> extractData(ResultSet rs) throws SQLException {
		List<T> results = (this.rowsExpected > 0 ? new ArrayList<T>(this.rowsExpected) : new ArrayList<T>());
		int rowNum = 0;//设置为第零行开始
		while (rs.next()) {//没有数据  并不会报错  只是会返回为null
			results.add(this.rowMapper.mapRow(rs, rowNum++));
		}
		return results;
	}

　　queryForObject在查询时候如果没有查询出数据则会抛出异常  因为会对results进行判断   如果为空则会抛出异常.

public static <T> T requiredSingleResult(Collection<T> results) throws IncorrectResultSizeDataAccessException {
		int size = (results != null ? results.size() : 0);
		if (size == 0) {
			throw new EmptyResultDataAccessException(1);
		}
		if (results.size() > 1) {
			throw new IncorrectResultSizeDataAccessException(1, size);
		}
		return results.iterator().next();
	}

 　　Bean查询  类似于beanUtils对于javabean类进行数据赋值

public class BeanPropertyRowMapper<T> implements RowMapper<T>

　　

@Test	//使用bean来进行查询数据
	public void seachBean(){
		List<User> list = jdbcTemplate.query("select * from t_user",new BeanPropertyRowMapper<User>(User.class));
		System.out.println(list);
	}

　　

 

spring之jdbcTemplate

标签：ati   encoding   结果   F12   技术   none   配置文件   增删改   根据