Spring框架学习08-Jdbc Template实现数据库操作

Spring框架学习——JDBC Template 实现数据库操作

为了简化持久化操作，Spring在JDBC API之上提供了JDBC Template组件
1. 添加依赖

  <dependencies>
    <!--测试相关-->
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.11</version>
      <scope>test</scope>
    </dependency>
    <!--Spring核心基础依赖-->
    <!-- https://mvnrepository.com/artifact/org.springframework/spring-core -->
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-core</artifactId>
      <version>5.1.5.RELEASE</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/org.springframework/spring-context -->
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-context</artifactId>
      <version>5.1.3.RELEASE</version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-beans</artifactId>
      <version>5.0.2.RELEASE</version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-expression</artifactId>
      <version>5.0.2.RELEASE</version>
    </dependency>
    <!--日志相关-->
    <dependency>
      <groupId>commons-logging</groupId>
      <artifactId>commons-logging</artifactId>
      <version>1.2</version>
    </dependency>
    <dependency>
      <groupId>log4j</groupId>
      <artifactId>log4j</artifactId>
      <version>1.2.17</version>
    </dependency>
    <dependency>
      <groupId>org.junit.jupiter</groupId>
      <artifactId>junit-jupiter-api</artifactId>
      <version>RELEASE</version>
      <scope>compile</scope>
    </dependency>
    <!-- AOP联盟依赖 -->
    <dependency>
      <groupId>aopalliance</groupId>
      <artifactId>aopalliance</artifactId>
      <version>1.0</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/org.springframework/spring-aop -->
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-aop</artifactId>
      <version>5.1.5.RELEASE</version>
    </dependency>
    <!--aspectJ相关依赖-->
    <dependency>
      <groupId>org.aspectj</groupId>
      <artifactId>aspectjweaver</artifactId>
      <version>1.9.2</version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-aspects</artifactId>
      <version>5.0.2.RELEASE</version>
    </dependency>
    <!-- Mysql驱动 -->
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.38</version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-jdbc</artifactId>
      <version>5.0.2.RELEASE</version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-tx</artifactId>
      <version>5.0.2.RELEASE</version>
    </dependency>
  </dependencies>

2、配置数据源
在applicationContext.xml文件中配置数据源

   <!-- 配置数据源-->
    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <!-- mysql数据驱动-->
        <property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
        <!--连接数据库的URL-->
        <property name="url" value="jdbc:mysql://localhost:3306/stu?useUnicode=true&amp;characterEncoding=utf8"></property>
        <!--连接数据库的用户名-->
         <property name="username" value="root"></property>
        <property name="password" value="root"></property>
    </bean>
    <!--配置JDBC模板-->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"></property>
    </bean>

3、update方法
使用update方法对数据进行增删改操作
获取JdbcTemplate对象，
添加数据：

  ApplicationContext applicationContext=new ClassPathXmlApplicationContext("applicationContext.xml");
        JdbcTemplate jdbcTemplate= (JdbcTemplate) applicationContext.getBean("jdbcTemplate");
        String sql="insert into student (name,age,sex) values(?,?,?)";
        jdbcTemplate.update(sql,new Object[]{"你好呀",20,"男"});
        System.out.println("添加成功！");

修改数据：

ApplicationContext applicationContext=new ClassPathXmlApplicationContext("applicationContext.xml");
        JdbcTemplate jdbcTemplate= (JdbcTemplate) applicationContext.getBean("jdbcTemplate");
        String sql="update student set name=?,age=?,sex=? where id=?";
        jdbcTemplate.update(sql,"红楼梦","100","女",194);
        System.out.println("修改成功！");

删除数据：

  String sql="delete from student where id=?";
        jdbcTemplate.update(sql,194);
        System.out.println("删除成功！");

批量操作数据，批量执行多SQL语句

String[] sqls = {
        "insert into book(name,author,price) values('Java基础','张三',90)",
        "insert into book(name,author,price) values('C语言','李四',80)",
        "insert into book(name,author,price) values('Web前端','王五',95)",
        "update book set name='水浒传',author='施耐庵' where id=1",
};
jdbcTemplate.batchUpdate(sqls);

批量操作数据，执行同SQL语句

String sql = "insert into book(name,author,price) values(?,?,?)";
List<Object[]> list = new ArrayList<Object[]>();
list.add(new Object[]{"HTML","Tom",90.8});
list.add(new Object[]{"CSS","Jack",88});
list.add(new Object[]{"JavaScript","Lily",89});
jdbcTemplate.batchUpdate(sql,list);

4、查询简单类型
查询单个数据

String sql = "select count(*) from book";
int count = jdbcTemplate.queryForObject(sql,Integer.class);
System.out.println(count);

查询多个数据

String sql = "select name from student where sex=?";
List<String> list = jdbcTemplate.queryForList(sql,String.class,"男");
System.out.println(list);
    输出结果：
[石光辉, 大哥, 哈哈, 法分开, 小崽子, 小陈, 你好呀, C语言, Web前端, C语言, Web前端, C语言, Web前端]

查询单个对象

String sql = "select * from student where id=?";
Map<String,Object> map = jdbcTemplate.queryForMap(sql,105);
System.out.println(map);
输出结果为：
{id=105, name=方刚发, age=29, sex=女}

查询多个对象

String sql = "select * from student";
List<Map<String,Object>> list =jdbcTemplate.queryForList(sql);
System.out.println(list);
注意：在这里查询到结果太多，这里不再显示！

6、查询复杂类型（封装为实体对象）
创建实体类

复制代码

public class Student {
    private int id;
    private String name;
    private int age;
    private String sex;
    //getter、setter、toString方法
}

复制代码
查询单个对象

String sql = "select * from student where id=?";
Student stu = jdbcTemplate.queryForObject(sql,new BeanPropertyRowMapper<Student>(Student.class),2);
System.out.println(stu);

查询多个对象

String sql = "select * from student";
List<Student> list = jdbcTemplate.query(sql,new BeanPropertyRowMapper<Student>(Student.class));
System.out.println(list);