SSM框架学习-5

在本次学习之前先说说这次学习的课程目标：

了解Spring组建JDBC Template
能使用JDBC Template进行持久化操作
帮助自己学习Hibernate，MyBatis框架

前置条件：

JDBC
Spring IOC，Apring AOP
Mysql

1.Spring JDBC Template

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

程序员代码->JDBC Template->JDBC API->JDBC驱动->数据库

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

2.创建项目

Maven

选择没有模板的Maven项目，在配置文件中添加Mysql驱动mysql-connector-javaSpring组件spring-core,spring-beans,sproing-context,spring-aop,JDBC Template组件spring-jdbc,spring-tx。
Spring配置

beans 的配置使用Spring的jdbc管理,对于会用JDBC的应该是很熟悉了 ```

<bean class="org.springframework.jdbc.core.JdbcTemplate" id="jdbcTemplate">
    <property name="dataSource" ref="dataSource"/>
</bean> 
<context:component-scan base-package="com.snake_lvyonghao.sc"/> ```

3.JDBC Template基本使用

通过JdbcTemplate类来对数据库进行操作，通过Beans创建一个JdbcTemplate类，使用JDBC的方式对数据库进行操作,例如创建一个表：

    @org.junit.Test
    public void testExecute(){
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("spring.xml");
        JdbcTemplate jdbcTemplate = (JdbcTemplate) applicationContext.getBean("jdbcTemplate");

        jdbcTemplate.execute("create table user1(id int,name varchar(20))");
    }

update方法对数据进行增删改查操作

int update(String sql, Object[] args)
int update(String sql, Object... args)

batchUpdate方法批量增删改查操作

int [] batchUpdate(String[] sql)
int [] vatchUpdate(String sql, List<Object[] args>)

完整样如下：

private JdbcTemplate jdbcTemplate;
  {
      ApplicationContext applicationContext = new ClassPathXmlApplicationContext("spring.xml");
      jdbcTemplate = (JdbcTemplate) applicationContext.getBean("jdbcTemplate");
  }

  public void testExecute(){
      jdbcTemplate.execute("create table user1(id int,name varchar(20))");
  }


  public void testUpdate(){
      String sql = "insert into student(name,sex) values(?,?)";
      jdbcTemplate.update(sql,new Object[]{"张飞","男"});
  }


  public void testUpdate2(){
      String sql = "update student set sex=? where id=?";
      jdbcTemplate.update(sql,new Object[]{"女","1003"});
  }


  public void testBatchUpdate(){
      String[] sqls = {
              "insert into student(name,sex) values('刘备','女')",
              "insert into student(name,sex) values('关羽','男')",
              "update student set sex='女' where id=1008"
      };
      jdbcTemplate.batchUpdate(sqls);
  }


  @org.junit.Test
  public void testBatchUpdate2(){
      String sql = "INSERT into selection(student,course) values (?,?)";
      List<Object[]> list = new ArrayList<>();
      list.add(new Object[]{1009,1001});
      list.add(new Object[]{1009,1003});
      jdbcTemplate.batchUpdate(sql,list);
  }

在实际的开发应用当中，增删改的应用还是比较少的，应用的最频繁的还是查找功能

查询简单数据项获取一个

T queryForObject(String sql, Class<T> type) 
T queryForObject(String sql, Object[] args, Class<T> type) 
T queryForObject(String sql, Class<T> type,Object... arg)

查询简单数据项获取多个

返回值类型是Class类型

List<T> queryForList(String sql , Class<T> type) 
List<T> queryForList(String sql , Object[] args , Class<T> type) 
List<T> queryForList(String sql , Class<T> type,Object... arg)

查找总量，或者特定性别：

    @org.junit.Test
    public void testQuerySimple1(){
        String sql = "select count(*) from student";
        int count = jdbcTemplate.queryForObject(sql,Integer.class);
        System.out.println(count);
    }

    @org.junit.Test
    public void testQuerySimple(){
        String sql = "select name from student where sex=?";
        List<String> names = jdbcTemplate.queryForList(sql,String.class,"男");
        System.out.println(names);
    }

查询复杂对象（封装为Map）获取一个

Map queryForMap(String sql) 
Map queryForMap(String sql , Object[] args) 
Map queryForMap(String sql , Object... arg) 

查询复杂对象（封装为Map）获取多个,其实就是把每条信息封装到Map当中再把多条Map信息封装到List当中去。
```
List<Map<String,Object>> queryForList(String sql) List<Map<String,Object>> queryForList(String sql , Object[] args) List<Map<String,Object>> queryForList(String sql , Object... arg)
```
获取一个学生id等于固定值的信息，获取所有学生信息：

    @org.junit.Test
    public void testQueryMap(){
        String sql = "select * from student where id=?";
        Map<String,Object> stu = jdbcTemplate.queryForMap(sql,1008);
        System.out.println(stu);
    }

    @org.junit.Test
    public void testQuerMap2(){
        String sql = "select * from student";
        List<Map<String,Object>> stu = jdbcTemplate.queryForList(sql);
        System.out.println(stu);
    }

查询复杂对象（封装为实体对象）RowMapper接口获取一个

T queryForObject(String sql,RowMapper<T> mapper) 
T queryForObject(String sql,Object[] args,RowMapper<T> mapper) 
T queryForObject(String sql,RowMapper<T> mapper,Object... arg)

查询复杂对象（封装为实体对象）RowMapper接口获取多个

List<T> query(String sql,RowMapper<T> mapper) 
List<T> query(String sql,Object[] args,RowMapper<T> mapper) 
List<T> query(String sql,RowMapper<T> mapper,Object... arg)

下面演示一个封装实体类，其中的RowMapper接口为了简化操作我们会提前写一个私有类让他去继承RowMapper接口。

  @org.junit.Test
  public void testQueryEntity1(){
      String sql = "select * from student where id=?";
      Student student = jdbcTemplate.queryForObject(sql,new StudentRowMapper(),1012);
      System.out.println(student);
  }

  @org.junit.Test
  public void testQueryEntity2(){
      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 student1 = new Student();
          student1.setId(resultSet.getInt("id"));
          student1.setName(resultSet.getString("name"));
          student1.setSex(resultSet.getNString("sex"));
          student1.setBorn(resultSet.getDate("born"));
          return student1;
      }
  }

4.JDBC Template持久层示例

持久层要写我们的StudentDao，CourseDao，SelectionDao首先我们先写出接口：

package com.snake_lvyonghao.sc.dao;

import com.snake_lvyonghao.sc.entity.Selection;

import java.util.List;
import java.util.Map;

public interface SelectionDao {
    void insert(List<Selection> selections);
    void delete(int sid,int cid);
    List<Map<String,Object>> selectByStudent(int sid);
    List<Map<String,Object>> selectByCourse(int cid);
}

-----------------------------------------

package com.snake_lvyonghao.sc.dao;

import com.snake_lvyonghao.sc.entity.Student;

import java.util.List;

public interface StudentDao {
    void insert(Student student);
    void update(Student student);
    void delete(int id);
    Student select(int id);
    List<Student> selectAll();
}
-------------------------------

package com.snake_lvyonghao.sc.dao;

import com.snake_lvyonghao.sc.entity.Course;

import java.util.List;

public interface CourseDao {
    void insert(Course course);
    void update(Course course);
    void delete(int id);
    Course select(int id);
    List<Course> selectAll();
}

然后实现StudentCourse的DaoImpl，这样我们就可以把数据库中的表和类一一对应，表中的列和类中的成员一以对应。

package com.snake_lvyonghao.sc.dao.impl;

import com.snake_lvyonghao.sc.dao.StudentDao;
import com.snake_lvyonghao.sc.entity.Student;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

public class StudentDaoImpl implements StudentDao {
    private JdbcTemplate jdbcTemplate;
    {
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("spring.xml");
        jdbcTemplate = (JdbcTemplate) applicationContext.getBean("jdbcTemplate");
    }

    @Override
    public void insert(Student student) {
        String sql = "insert into student(name,sex,born) values(?,?,?)";
        jdbcTemplate.update(sql,student.getName(),student.getSex(),student.getBorn());
    }

    @Override
    public void update(Student student) {
        String sql = "update student set name=?,sex=?,born=? where id=?";
        jdbcTemplate.update(sql,student.getName(),student.getSex(),student.getBorn(),student.getId());
    }

    @Override
    public void delete(int id) {
        String sql = "delete from student where id=?";
        jdbcTemplate.update(sql,id);
    }

    @Override
    public Student select(int id) {
        String sql = "select * from student where id=?";
        return jdbcTemplate.queryForObject(sql,new StudentRowMapper(),id);
    }

    @Override
    public List<Student> selectAll() {
        String sql = "select * from student";
        return jdbcTemplate.query(sql,new StudentRowMapper());
    }

    private class StudentRowMapper implements RowMapper<Student> {
        @Override
        public Student mapRow(ResultSet resultSet, int i) throws SQLException {
            Student student1 = new Student();
            student1.setId(resultSet.getInt("id"));
            student1.setName(resultSet.getString("name"));
            student1.setSex(resultSet.getNString("sex"));
            student1.setBorn(resultSet.getDate("born"));
            return student1;
        }
    }
}
----------------------------------
package com.snake_lvyonghao.sc.dao.impl;

import com.snake_lvyonghao.sc.dao.CourseDao;
import com.snake_lvyonghao.sc.entity.Course;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

public class CourseDaoImpl implements CourseDao {

    private JdbcTemplate jdbcTemplate;
    {
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("spring.xml");
        jdbcTemplate = (JdbcTemplate) applicationContext.getBean("jdbcTemplate");
    }

    @Override
    public void insert(Course course) {
        String sql = "insert into course(name,score) values (?,?)";
        jdbcTemplate.update(sql,course.getName(),course.getScore());
    }

    @Override
    public void update(Course course) {
        String sql = "update course set name=?,score=? where id=?";
        jdbcTemplate.update(sql,course.getName(),course.getScore(),course.getId());
    }

    @Override
    public void delete(int id) {
        String sql = "delete from course where id=?";
        jdbcTemplate.update(sql,id);
    }

    @Override
    public Course select(int id) {
        String sql = "select * from course where id=?";
        return jdbcTemplate.queryForObject(sql,new CourseRowMapper(),id);
    }

    @Override
    public List<Course> selectAll() {
        String sql = "select * from course";
        return jdbcTemplate.query(sql,new CourseRowMapper());
    }

    private class CourseRowMapper implements RowMapper<Course> {
        @Override
        public Course mapRow(ResultSet resultSet, int i) throws SQLException {
            Course course = new Course();
            course.setId(resultSet.getInt("id"));
            course.setName(resultSet.getString("name"));
            course.setScore(resultSet.getInt("score"));
            return course;
        }
    }
}

-------------------------------------------

package com.snake_lvyonghao.sc.dao.impl;

import com.snake_lvyonghao.sc.dao.SelectionDao;
import com.snake_lvyonghao.sc.entity.Selection;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class SelectionDaoImpl implements SelectionDao {

    private JdbcTemplate jdbcTemplate;
    {
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("spring.xml");
        jdbcTemplate = (JdbcTemplate) applicationContext.getBean("jdbcTemplate");
    }

    @Override
    public void insert(List<Selection> selections) {
        String sql = "insert into selection values(?,?,?,?)";
        List<Object[]> list = new ArrayList<>();
        for (Selection selection : selections){
            Object[] args = new Object[4];
            args[0] = selection.getSid();
            args[1] = selection.getCid();
            args[2] = selection.getSelTime();
            args[3] = selection.getScroe();
            list.add(args);
        }
        jdbcTemplate.batchUpdate(sql,list);
    }

    @Override
    public void delete(int sid,int cid) {
        String sql = "delete from selection where student=? and where course=?";
        jdbcTemplate.update(sql,sid,cid);
    }

    @Override
    public List<Map<String, Object>> selectByStudent(int sid) {
        String sql = "select se.*,stu.name sname,cou.name  cname from selection se " +
                "left join student stu on se.student=stu.id" +
                "left join course cou on se.course=cou.id" +
                "where student=?";

        return jdbcTemplate.queryForList(sql,sid);
    }

    @Override
    public List<Map<String, Object>> selectByCourse(int cid) {
        String sql = "select se.*,stu.name sname,cou.name  cname from selection se " +
                "left join student stu on se.student=stu.id" +
                "left join course cou on se.course=cou.id" +
                "where course=?";

        return jdbcTemplate.queryForList(sql,cid);
    }
}

5.总结

JDBC Template是Spring框架对JDBC操作的封装，简单、灵活但不够强大。
实际应用中还需要和其它ORM框架混合使用
本节的内容主要是熟悉JDBC Template使用，和理解为什么要去使用对象-关系相连接的ORM框架，为日后的学习打下基础 SSM框架源码地址

SSM框架学习-5

JDBC Template

SSM框架学习-5

1.Spring JDBC Template

2.创建项目

3.JDBC Template基本使用

4.JDBC Template持久层示例

5.总结

CATALOG

FEATURED TAGS

FRIENDS