Spring学习之JDBCTemplate

创建一个JDBC的项目

1.创建数据库图片说明
2.创建一个Maven的webapp模板
在pom文件中导入依赖：

MySQL驱动
Spring组件（core.beans.context.aop）

JDBCTemplate（jdbc，tx）spring下的
在resources文件夹中写入applicationContext文件写入相关配置：

<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:context="http://www.springframework.org/schema/context"
    xmlns:aop="http://www.springframework.org/schema/aop"
    xmlns:tx="http://www.springframework.org/schema/tx"
    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="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
     <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
     <property name="url" value="jdbc:mysql://localhost:3306/selection_course?useUnicode=true&characterEncoding=utf-8"/>
     <property name="username" value="root"/>
     <property name="password" value="root"/>
 </bean>
 <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
     <property name="dataSource" ref="dataSource"/>
 </bean>
 <context:component-scan base-package="com.imooc.sc"/>
</beans>

DataSource是配置数据库连接的相关配置
JDBCTemplate的配置

2.JDBCTemplate基本使用

execute方法：执行sql语句

@org.junit.Test
public void textExecute() {
   ApplicationContext applicationContext = new ClassPathXmlApplicationContext("spring.xml");
   JdbcTemplate jdbcTemplate = (JdbcTemplate) applicationContext.getBean("jdbcTemplate");
   jdbcTemplate.execute("create table user1(id int,name varchar (20))");
}

update与batchUpdate方法：
update：对数据进行增删改
int update(String sql,Object[] args)
int update(String sql,Object... args)

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

    public void textUpdate2() {
        String sql = "update student set sex =? where id=?";
        jdbcTemplate.update(sql,"女",1);
    }

batchUpdate:批量增删改操作:
int[] batchUpdate(String[] sql)
int[] batchUpdate(String sql,List<Object[]> args)

public void testBatchUpdate() {
        String[] sqls = {
                "insert into student(name,sex) values('关羽','女')",
                "insert into student(name,sex) values('刘备','女')",
                "update student set sex ='男' where id=1"
        };
        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[]{1,1001});
        list.add(new Object[]{1,1003});

        jdbcTemplate.batchUpdate(sql,list);
    }

query与queryXXX方法

查询简单数据项

public void testQuerySimple1() {
   String sql = "select count(*) from student";
   Integer count = jdbcTemplate.queryForObject(sql, Integer.class);
   System.out.println(count);
}
public void testQuerySimple2() {
   String sql = "select name from student where sex = ?";
   List<String> list = jdbcTemplate.queryForList(sql, String.class, "女");
   for (String s:list ) {
       System.out.println(s);
   }
}

2、查询复杂对象（封装为Map）

获取一个
Map queryForMap（String sql，Object... args）

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

  }

获取多个
List<Map<String,Object>> queryForList(String sql,Object... arg)

public void testQueryMap2() {
      String sql = "select * from student";
      List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);
      System.out.println(maps);

  }

3.查询复杂对象（封装为对象）重点掌握

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)</t></t></t>

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

      System.out.println(student);
  }

获取多个（封装为实体对象）
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)</t></t></t></t></t></t>

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

      System.out.println(list);
  }

出于降低代码重复性的问题，不要用匿名内部类的形式

private class StudentRowMapper implements RowMapper<Student> {
      @Override
      public Student mapRow(ResultSet rs, int rowNum) throws SQLException {
          Student stu = new Student();
          stu.setSex(rs.getString("sex"));
          stu.setBorn(rs.getDate("born"));
          stu.setName(rs.getString("name"));
          stu.setId(rs.getInt("id"));
          return stu;
      }
  }