概述以及准备工作

什么JDBCTemplate

  1. Spring框架对JDBC进行了封装,使用JDBCTemplate可以很方便的实现对数据库的操作

    准备工作

  2. 引入相关的依赖

    1.    <!--事务-->
    2.              <dependency>
    3.          <groupId>org.springframework</groupId>
    4.          <artifactId>spring-tx</artifactId>
    5.          <version>5.2.9.RELEASE</version>
    6.      </dependency>
    7.              <!--JDBCTemplate-->
    8.              <dependency>
    9.          <groupId>org.springframework</groupId>
    10.          <artifactId>spring-jdbc</artifactId>
    11.          <version>5.2.9.RELEASE</version>
    12.      </dependency>
    13.              <!--连接池-->
    14.      <dependency>
    15.          <groupId>com.alibaba</groupId>
    16.          <artifactId>druid</artifactId>
    17.          <version>1.1.16</version>
    18.      </dependency>
    19.              <!--mysql驱动-->
    20.      <dependency>
    21.          <groupId>mysql</groupId>
    22.          <artifactId>mysql-connector-java</artifactId>
    23.          <version>8.0.20</version>
    24.      </dependency>
    25.              <!--整合Thymlef或者Mybatis-->
    26.      <dependency>
    27.          <groupId>org.springframework</groupId>
    28.          <artifactId>spring-orm</artifactId>
    29.          <version>5.0.2.RELEASE</version>
    30.      </dependency>

    2.在spring的配置文件中配置连接池

    1.      <!--数据库连接池-->
    2.      <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
    3.          <property name="driverClassName" value="com.mysql.cj.jdbc.Driver"/>
    4.          <property name="url" value="jdbc:mysql://47.93.182.140:3306/qingcheng_goods?serverTimezone=GMT%2B8&amp;useSSL=false"/>
    5.          <property name="username" value="root"/>
    6.          <property name="password" value="123456"/>
    7.      </bean>

    3.配置JDBCTemplate对象,注入DataSource

    1. <!--JDBCTemplate-->
    2.      <bean id="JdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
    3.          <!--注入dataSource-->
    4.          <property name="dataSource" ref="dataSource"/>
    5.      </bean>

    4.创建service类,创建dao类,在dao注入JdbcTemplate对象
    配置文件

    1. <!--开启组件扫描-->
    2.      <context:component-scan base-package="com.ranin"></context:component-scan>

    Service

    1. @Service
    2. public class BookServiceImpl implements BookService {
    3.  //注入dao
    4.  @Resource
    5.  private BookDao bookDao;
    6. }

    Dao

    1. @Repository
    2. public class BookDaoImpl implements BookDao {
    4.  //注入JdbcTemplate
    5.  @Resource
    6.  private JdbcTemplate jdbcTemplate;
    7. }

    JDBCTemplate操作数据库

    添加

    1.对应数据库创建实体类
    2.编写service和dao
    1.在dao进行数据库添加操作
    2.调用JdbcTemplate对象里面update方法实现update(String sql,Object args)
    (1)里面有两个参数
    (2)第一个参数:sql语句
    (3)第二个参数:可变参数,设置sql语句的值

    1. @Repository
    2. public class BookDaoImpl implements BookDao {
    4.  //注入JdbcTemplate
    5.  @Resource
    6.  private JdbcTemplate jdbcTemplate;
    8.  //添加的方法
    9.  public void addUser(User user) {
    10.      String sql = "insert into t_user values(?,?,?)";
    11.      //创建可变参数数组
    12.      Object[] args = {user.getUser_id(),user.getUsername(),user.getUstatus()};
    13.      int update = jdbcTemplate.update(sql,args);
    14.      if(update == 1){
    15.          System.out.println("插入成功");
    16.      }
    17.  }
    18. }

    测试添加

    1.  @Test
    2.  public void test1(){
    3.      ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");
    4.      BookService bookService = context.getBean("bookServiceImpl", BookServiceImpl.class);
    5.      User user = new User();
    6.      user.setUser_id(1);
    7.      user.setUsername("张三");
    8.      user.setUstatus("好");
    9.      bookService.addBook(user);
    10.  }

    JdbcTemplate操作数据库(修改和删除)

    在service中创建两个方法一个修改一个删除

    1.  //修改方法
    2.  public void updateBook(User user) {
    3.      String sql = "update t_user set username=?,ustatus=? where user_id=?";
    4.      Object[] args = {user.getUsername(),user.getUstatus(),user.getUser_id()};
    5.      int update = jdbcTemplate.update(sql, args);
    6.      if(update == 1){
    7.          System.out.println("修改成功");
    8.      }
    9.  }
    11.  //删除方法
    12.  public void deleteBook(Integer uid) {
    13.      String sql = "delete from t_user where user_id = ?";
    14.      int update = jdbcTemplate.update(sql, uid);
    15.      if(update == 1)
    16.          System.out.println("删除成功");
    17.  }

    测试

    1.  @Test
    2.  public void test2(){
    3.      ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");
    4.      BookService bookService = context.getBean("bookServiceImpl", BookServiceImpl.class);
    5.      //修改
    6.      User user = new User();
    7.      user.setUser_id(1);
    8.      user.setUsername("张3");
    9.      user.setUstatus("a");
    10.      bookService.updateUser(user);
    11.      bookService.deleteBook(1);
    12.  }

    JdbcTemplate操作数据库(查询)

    查询返回某个值

    1.查询表里有多少条数据,返回是某个值
    2.使用JDBCTemplate实现查询返回某个值代码
    queryForObject(String sql.Class requireedType)
    1.有两个参数
    2.第一个参数:sql语句
    3.第二个参数:返回类型Class

    1.  @Test
    2.  public void test3(){
    3.      ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");
    4.      BookService bookService = context.getBean("bookServiceImpl", BookServiceImpl.class);
    5.      bookService.findCount();
    6.  }

    查询返回对象

    1.场景:查询图书详情
    2.JdbcTemplate实现查询返回对象
    queryForObject(String sql,RowMapper rowMapper,Object….args)
    1.有三个参数
    2.第一个参数:sql语句
    3.第二个参数:RowMapper,是接口,返回不同类型的数据,使用这个接口里面实现类完成数据封装
    4.第三个参数:sql语句中的值

    1. User user = jdbcTemplate.queryForObject(sql,new BeanPropertyRowMapper<User>(User.class),id);

    查询返回集合

    1.场景:查询图书列表分页
    2.调用JDBCTemplate方法实现查询返回集合
    query(String sql.RowMapper rowMapper,Object..args)
    1.有三个参数
    2.第一个参数:sql语句
    3.第二个参数:RowMapper
    4.第三个参数:sql语句中的值

    1. List<User> list = jdbcTemplate.query(sql,new BeanPropertyRowMapper<User>(User.class));

    批量操作

    批量添加

    1.批量操作:操作表里面多条数据
    2.JdbcTemplate实现批量添加操作
    batchUpdate(String sql,List batchArgs)
    1.有两个参数
    2.第一个参数:sql语句
    3.第二个参数:List集合,表示添加多条记录数据

    1.  //批量添加
    2.  public void batchAddUser(List<Object[]> batchArgs) {
    3.      String sql = "insert into t_user values(?,?,?)";
    4.      int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs);
    5.      System.out.println(ints);
    6.  }

    测试

    1. @Test
    2. public void test5(){
    3.  ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");
    4.  BookService bookService = context.getBean("bookServiceImpl",BookServiceImpl.class);
    5.  List<Object[]> batchArgs = new ArrayList<>();
    6.  Object[] o1 = {2,"java","a"};
    7.  Object[] o2 = {3,"c","c"};
    8.  Object[] o3 = {4,"c++","c"};
    9.  batchArgs.add(o1);
    10.  batchArgs.add(o2);
    11.  batchArgs.add(o3);
    12.  bookService.batchAdd(batchArgs);
    13. }

    批量修改

    实现批量修改

    1. public void batchUpdate(List<Object[]> batchArgs) {
    2.      String sql = "update t_user set username=?,ustatus=? where user_id=?";
    3.      int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs);
    4.      System.out.println(ints);
    5.  }

    测试

    1. @Test
    2. public void test6(){
    3.  ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");
    4.  BookService bookService = context.getBean("bookServiceImpl",BookServiceImpl.class);
    5.  List<Object[]> batchArgs = new ArrayList<>();
    6.  Object[] o1 = {"j","a",2};
    7.  Object[] o2 = {"c++","c",3};
    8.  Object[] o3 = {"c","c",4};
    9.  batchArgs.add(o1);
    10.  batchArgs.add(o2);
    11.  batchArgs.add(o3);
    12.  bookService.batchUpdate(batchArgs);
    13. }

    批量删除

    1. public void batchDelete(List<Object[]> batchArgs) {
    2.      String sql = "delete from t_user where user_id=?";
    3.      int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs);
    4.      System.out.println(ints);
    5.  }

    测试

    1. @Test
    2. public void test7(){
    3.  ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");
    4.  BookService bookService = context.getBean("bookServiceImpl",BookServiceImpl.class);
    5.  List<Object[]> batchArgs = new ArrayList<>();
    6.  Object[] o1 = {2};
    7.  Object[] o2 = {3};
    8.  Object[] o3 = {4};
    9.  batchArgs.add(o1);
    10.  batchArgs.add(o2);
    11.  batchArgs.add(o3);
    12.  bookService.batchDelete(batchArgs);
    13. }