在springboot中使用jdbcTemplate(3)

转自：https://www.cnblogs.com/qingshanli/p/11296048.html#_label3

获取count、sum等聚合函数返回的唯一值

/**
     * 只能接受String,Integer这种单列类型的实体,否则汇报异常
     */
    @Test
    public void queryForCount1() {
        String sql = "select count(1) from pass_user where id > ?";
        Integer count = jdbcTemplate.queryForObject(sql, Integer.class, 300000);
        LOGGER.info("[" + Thread.currentThread().getStackTrace()[1].getMethodName() + "] {}", count);
    }

获取单个记录的某一列值

/**
     * 只能接受String,Integer这种单列类型的实体,否则汇报异常
     */
    @Test
    public void queryForObject1() {
        String sql = "select NAME from pass_user where id = ?";
        String name = jdbcTemplate.queryForObject(sql, String.class, 30);//查询结果空集时会报EmptyResultDataAccessException异常
        LOGGER.info("[" + Thread.currentThread().getStackTrace()[1].getMethodName() + "] {}", name);
    }

获取单个记录的所有列值

/**
     * 可以自动进行驼峰匹配
     */
    @Test
    public void queryForObject2() {
        String sql = "select * from pass_user where id = ?";
        RowMapper<PassUser> rowMapper = new BeanPropertyRowMapper<>(PassUser.class);
        PassUser passUser = jdbcTemplate.queryForObject(sql, rowMapper, 180);//查询结果空集时会报EmptyResultDataAccessException异常
        LOGGER.info("[" + Thread.currentThread().getStackTrace()[1].getMethodName() + "] {}", passUser);
    }

获取多个记录的某一列值

/**
     * 实体类需要实现接口,覆写方法
     */
    @Test
    public void queryForList1() {
        String sql = "select name from pass_user where id < ?";
        List<String> names = jdbcTemplate.queryForList(sql, String.class, 50);//只能查询单列属性值集合
        LOGGER.info("[" + Thread.currentThread().getStackTrace()[1].getMethodName() + "] {}", names);
    }

获取多个记录的所有列值

/**
     * 实体类需要实现接口,覆写方法
     */
    @Test
    public void queryForList2() {
        String sql = "select * from pass_user where id < ?";
        List<UserEntity> userEntityList = jdbcTemplate.query(sql, new UserEntity(), 0);
        LOGGER.info("[" + Thread.currentThread().getStackTrace()[1].getMethodName() + "] {}", userEntityList);
    }

 转自：https://blog.csdn.net/weixin_40001125/article/details/88538576

JdbcTemplate查询-BeanPropertyRowMapper返回自定义对象

// query使用BeanPropertyRowMapper做映射返回对象
public static void test07() throws Exception {
    JdbcTemplate jdbcTemplate = new JdbcTemplate(DataSourceUtils.getDataSource());

    // 查询数据的SQL语句
    String sql = "SELECT * FROM product;";
    List<Product> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Product.class));

    for (Product product : list) {
        System.out.println(product);
    }
}

JdbcTemplate查询-RowMapper返回自定义对象

// query使用rowMap做映射返回一个对象
public static void test06() throws Exception {
    JdbcTemplate jdbcTemplate = new JdbcTemplate(DataSourceUtils.getDataSource());

   // 查询数据的SQL语句
   String sql = "SELECT * FROM product;";

   List<Product> query = jdbcTemplate.query(sql, new RowMapper<Product>() {
      @Override
      public Product mapRow(ResultSet arg0, int arg1) throws SQLException {
         Product p = new Product();
         p.setPid(arg0.getInt("pid"));
         p.setPname(arg0.getString("pname"));
         p.setPrice(arg0.getDouble("price"));
         return p;
      }
   });

   for (Product product : query) {
      System.out.println(product);
   }
}

JdbcTemplate查询-queryForList返回一个List集合

public static void test05() throws Exception {
   String sql = "SELECT * FROM product WHERE pid<?;";
   JdbcTemplate jdbcTemplate = new JdbcTemplate(DataSourceUtils.getDataSource());
   List<Map<String, Object>> list = jdbcTemplate.queryForList(sql, 8);
   for (Map<String, Object> map : list) {
      System.out.println(map);
   }
}

JdbcTemplate查询-queryForMap返回一个Map集合

public static void test04() throws Exception {
   String sql = "SELECT * FROM product WHERE pid=?;";
   JdbcTemplate jdbcTemplate = new JdbcTemplate(DataSourceUtils.getDataSource());
   Map<String, Object> map = jdbcTemplate.queryForMap(sql, 6);
   System.out.println(map);
}

JdbcTemplate查询-queryForObject返回String

public static void test03() throws Exception {
   String sql = "SELECT pname FROM product WHERE price=7777;";
   JdbcTemplate jdbcTemplate = new JdbcTemplate(DataSourceUtils.getDataSource());
   String str = jdbcTemplate.queryForObject(sql, String.class);
   System.out.println(str);
}