淘客必须做网站优化培训课程

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

　　spring data access第一个helloword案例:

 使用java程序实现访问配置

1.导包

2.测试案例

　　　　@Testpublic 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.xsdhttp://www.springframework.org/schema/contexthttp://www.springframework.org/schema/context/spring-context.xsdhttp://www.springframework.org/schema/aophttp://www.springframework.org/schema/aop/spring-aop.xsdhttp://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 {@Autowiredprivate JdbcTemplate jdbcTemplate;@Testpublic 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 {@Autowiredprivate 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>>();@Overridepublic 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>>();@Overridepublic 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>();@Overridepublic 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>();@Overridepublic 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的返回值会根据第二个参数来对返回进行区分

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

　　

@Overridepublic 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()) {//没有数据  并不会报错  只是会返回为nullresults.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);}