JDBC Template

• 为了简化持久操作，Spring在JDBC API之上提供了JDBC Template组件

1. 传统方式
   
2. JDBC
   

• JDBC Template提供统一的模板方法，在保留代码灵活性的基础上，尽量减少持久化代码

创建项目

Maven

• MySQL驱动(mysql-connector-java)
• Spring组件(core、beans、context、aop)
• JDBC Template(jdbc、tx)

Spring配置

• 数据源
• JDBC Template

<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
    <property name="url" value="jdbc:mysql://47.114.2.235:3306/jdbc?userUnicode=true&characterEncoding=utf-8"/>
    <property name="username" value="jdbc"/>
    <property name="password" value="jdbc"/>
</bean>
<bean id="jdbcTempate" class="org.springframework.jdbc.core.JdbcTemplate">
    <property name="dataSource" ref="dataSource"/>
</bean>

JDBC Template基本使用

execute 方法 (建立表，修改表结构)

• 创建一个user的表

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:spring.xml")
public class Test {
    @Resource(name = "jdbcTemplate")
    private JdbcTemplate jdbcTemplate;
    @org.junit.Test
    public void demo1(){
//        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("spring.xml");
//        JdbcTemplate jdbcTemplate = (JdbcTemplate)applicationContext.getBean("jdbcTemplate") ;
        jdbcTemplate.execute("create table user (id int ,name varchar (20))");
    }
}

update 与 batchUpdate 方法

• update方法
• 对数据进行增删改操作
  语法：

int update(String sql,Object[] args) //第一个参数代表String 类型的SQL语句 第二个参数代表：Object中的值是为SQL语句中的占位符进行填充 返回值是行数
int update(String sql,Object''' args) //Object''不定向参数：为SQL语句中的类容进行填充
======================================
    @org.junit.Test
    public void testupdate(){
        String sql="insert into student(name,sex ) values(?,?)";
        jdbcTemplate.update(sql, new Object[]{"张飞","男"});
    }
    @org.junit.Test
    public void testupdate1(){
        String sql="update student set sex=? where id = ? ";
        jdbcTemplate.update(sql, "人妖",1);
    }

• bachUpdate方法
• 批量增删改操作

// 语法
int[] batchUpdate(String[] sql)
@org.junit.Test
public void testBatchUdate(){
    String[] sqls={
            "insert into student(name,sex ) values('关羽','人妖')",
            "insert into student(name,sex ) values('刘备','人妖')",
            "insert into student(name,sex ) values('韩信','人妖')",
            "update student set sex='男妖' where id =  2"
    };
    jdbcTemplate.batchUpdate(sqls);
}
=======================================
int[] batchUpdate(String sql,List<Object[]> args)(执行同步SQL效率高)
@org.junit.Test
public void testBatchUpdate(){
    String sql="insert into selection(student,course) values(?,?)";
    List<Object[]> list = new ArrayList<Object[]>();
    list.add(new Object[]{5,1001});
    list.add(new Object[]{3,1002});
    jdbcTemplate.batchUpdate(sql,list);
}

query 与 queryXXX 方法

1. 获取一个：
2. T queryForObject(String sql, Class type)；
   2 T queryForObject(String sql, Object[] args, Class type);
   3.T queryForObject(String sql, Class type, Object… arg);
3. 获取多个：
4. List queryForList(String sql, Class type)；
5. List queryForList(String sql, Object[] args, Class type);
6. List queryForList(String sql, Class type, Object… arg);

@org.junit.Test
    public void testQuserySimplel(){
        String sql = "select count(*) from student";
        int count = jdbcTemplate.queryForObject(sql,Integer.class);
        System.out.println(count);
    }
    @org.junit.Test
    public void testQuserySimple(){
        String sql= "select name from student where sex= ?";
        List<String> names = jdbcTemplate.queryForList(sql,String.class,"人妖");
        System.out.println(names);
    }
    @org.junit.Test
    public void testQueryMapl(){
        String sql = "select * from student where id = ?";
        Map<String,Object> stu=jdbcTemplate.queryForMap(sql,5);
        System.out.println(stu);
    }
    @org.junit.Test
    public void testQueryMapl1(){
        String sql = "select * from student ";
        List<Map<String,Object>> stu=jdbcTemplate.queryForList(sql);
        System.out.println(stu);
    }

• 查询复杂对象（封装为实体对象）：
  —RowMapper接口
  —获取一个：
  1. T queryForObject(sql, RowMapper mapper);
  2. T queryForObject(sql, RowMapper mapper, Object … arg);
  3. T queryForObject(sql, Object[] args, RowMapper mapper);

@org.junit.Test
public void map(){
    String sql = "select * from student where id=?";
    Student stu=jdbcTemplate.queryForObject(sql, new RowMapper<Student>() {
        @Override
        public Student mapRow(ResultSet resultSet, int i) throws SQLException {
            Student student = new Student();
            student.setId(resultSet.getInt("id"));
            student.setName(resultSet.getString("name"));
            student.setSex(resultSet.getString("sex"));
            student.setBorn(resultSet.getDate("born"));

            return student;
        }
    },5);
    System.out.println(stu);
}

—获取多个

1. List query(sql, RowMapper mapper);
2. List query(sql, RowMapper mapper, Object … arg);
3. List query(sql, Object[] args, RowMapper mapper);

@org.junit.Test
public void list(){
    String sql= "select * from student";
    List<Student> students = jdbcTemplate.query(sql, new RowMapper<Student>() {
        @Override
        public Student mapRow(ResultSet resultSet, int i) throws SQLException {
            Student student = new Student();
            student.setId(resultSet.getInt("id"));
            student.setName(resultSet.getString("name"));
            student.setSex(resultSet.getString("sex"));
            student.setBorn(resultSet.getDate("born"));
            return student;
        }
    });
    System.out.println(students);
}

改进=====

@org.junit.Test
public void testlist() {
    String sql = "select * from student";
    List<Student> students = jdbcTemplate.query(sql, new StudentRowMapper());
    System.out.println(students);
}
private class StudentRowMapper implements RowMapper<Student> {
    @Override
    public Student mapRow(ResultSet resultSet, int i) throws SQLException {
        Student student = new Student();
        student.setId(resultSet.getInt("id"));
        student.setName(resultSet.getString("name"));
        student.setSex(resultSet.getString("sex"));
        student.setBorn(resultSet.getDate("born"));
        return student;
    }
}

JDBC Template持久层示列

代码下载

优缺点

优点

• 简单
• 灵活

缺点

• SQL与Java代码杂糅
• 功能不丰富

总结

• 持久化操操作特点
• 必须
• 机械性
• ORM
• 对象-关系映射

JDBC Template是Spring框架对JDBC操作的封装，简单、灵活单不够强大。
实际应用中还需要和其他ORM矿建混合使用