spring之jdbcTemplate

spring的另一个功能模块data access对于数据库的支持

　　spring data access第一个helloword案例:

 使用java程序实现访问配置

1.导包

2.测试案例

　　　　@Test
	public void test01(){
		DriverManagerDataSource dataSource=new DriverManagerDataSource();
		dataSource.setDriverClassName("com.mysql.jdbc.Driver");
		dataSource.setUrl("jdbc:mysql:///springtest");
		dataSource.setUsername("root");
		dataSource.setPassword("123");
		JdbcTemplate jdbcTemplate=new JdbcTemplate();
		jdbcTemplate.setDataSource(dataSource);
		String sql="insert into t_user values(1,'张三',123,'男')";
		jdbcTemplate.execute(sql);
		
	}

　　把创建对象交给spring来控制,这样就需要在applicationContext.xml文件中配置数据访问

applicationContext.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:c="http://www.springframework.org/schema/c"
	xmlns:p="http://www.springframework.org/schema/p"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:context="http://www.springframework.org/schema/context"
   xsi:schemaLocation="http://www.springframework.org/schema/beans 
						http://www.springframework.org/schema/beans/spring-beans.xsd
						http://www.springframework.org/schema/context
						http://www.springframework.org/schema/context/spring-context.xsd
						http://www.springframework.org/schema/aop
						http://www.springframework.org/schema/aop/spring-aop.xsd
						http://www.springframework.org/schema/tx 
						http://www.springframework.org/schema/tx/spring-tx.xsd">
	<!-- 创建数据源 -->
	 <bean id="driverManagerDataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
	 	<!-- 配置数据库驱动 -->
		<property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
		<property name="url" value="jdbc:mysql:///springtest"></property>
		<property name="username" value="root"></property>
		<property name="password" value="123"></property>
	</bean>
	
	<bean id="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);
    }

@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);
    }

对封装的数据进行查询

@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);
    }

@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 和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);
	}