Spring-JDBC的查询操作

1、查询指定user_id的数量

public int queryAccountUserId(int userId) {
        String sql="select count(*) from tb_account where user_id=?";
        int count=jdbcTemplate.queryForObject(sql,Integer.class,userId);
        return count;
    }

2、查询账户的详情信息

public Account queryAccountId(int accountId) {
        String sql="select * from tb_account where account_id=?";
        Account account=jdbcTemplate.queryForObject(sql,(ResultSet rs, int i) ->{
            Account acc=new Account();
            acc.setAccountId(accountId);
            acc.setAccountName(rs.getString("account_name"));
            acc.setAccountType(rs.getString("account_type"));
            acc.setMoney(rs.getDouble("money"));
            acc.setRemark(rs.getString("remark"));
            acc.setCreateTime(rs.getDate("create_time"));
            acc.setUpdateTime(rs.getDate("update_time"));
            acc.setUserId(rs.getInt("user_id"));
            return acc;
        },accountId);
        return account;
    }

3、进行多条件的查询操作

public List<Account> queryAccountByParam(Integer userId, String accountName, String accountType, String createTime) {
        String sql="select * from tb_account where user_id=?";
        //定义一个参数列表
        List<Object> param=new ArrayList<>();
        param.add(userId);

        //账户名称
        if(StringUtils.isNoneBlank(accountName)){
            //拼接sql语句
            sql +=" and account_name like concat('%',?,'%')";
            //设置参数
            param.add(accountName);
        }

        //账户类型
        if(StringUtils.isNoneBlank(accountType)){
            //拼接sql语句
            sql +=" and account_type=?";
            //设置参数
            param.add(accountType);
        }
        //创建时间
        if(StringUtils.isNoneBlank(createTime)){
            //拼接sql语句
            sql +=" and create_time<?";
            //设置参数
            param.add(createTime);
        }

        Object[] obj=param.toArray();


        List<Account> accountList=jdbcTemplate.query(sql,obj,(ResultSet rs, int i) ->{
            Account acc=new Account();
            acc.setAccountId(rs.getInt("account_id"));
            acc.setAccountName(rs.getString("account_name"));
            acc.setAccountType(rs.getString("account_type"));
            acc.setMoney(rs.getDouble("money"));
            acc.setRemark(rs.getString("remark"));
            acc.setCreateTime(rs.getDate("create_time"));
            acc.setUpdateTime(rs.getDate("update_time"));
            acc.setUserId(rs.getInt("user_id"));
            return acc;
        });
        return accountList;
    }