spring jdbc template 自己的笔记

最新推荐文章于 2024-04-17 04:58:23 发布

shenjianxz

最新推荐文章于 2024-04-17 04:58:23 发布

阅读量718

点赞数

分类专栏： javaEE

本文链接：https://blog.csdn.net/shenjianxz/article/details/51316016

版权

javaEE 专栏收录该内容

33 篇文章 1 订阅

订阅专栏

第一种：查询list 通过反射自动映射对应实体,表字段和实体要相对应(简单)
Object[] params = new Object[]{guestId};
int[] types = new int[]{Types.INTEGER};
List<LinkMan> linkMansList = getJdbcTemplate().query(selectSql,params,new BeanPropertyRowMapper(LinkMan.class));
if(!linkMansList.isEmpty()){
    return linkMansList;
}
第二种：查询list（第二种实体表不对应）(尽量相对应！)
String selectSql[] = GuestSqlStatements.getByGuestInfoOfPage( guestType, searchParamVo, userInfo);
List<GuestViewVo> listAll;
Object[] params = new Object[]{page.getPage().getCurrentPage(), page.getPage().getPageSize()};
int[] types = new int[]{Types.INTEGER, Types.INTEGER};
listAll = getJdbcTemplate().query(selectSql[0], params, types, new RowMapper<GuestViewVo>() {
    @Override
    public GuestViewVo mapRow(ResultSet rs, int i) throws DataAccessException, SQLException {
        GuestViewVo vo = new GuestViewVo();
        vo.setCityName(rs.getString("city")); //用户城市
        vo.setBaseRegionName(rs.getString("baseRegion"));//用户区域
        vo.setName(rs.getString("name")); //名称
        vo.setType(rs.getInt("type")); //客源类型
        vo.setRegionName(rs.getString("region")); //意向区域
        vo.setTradingArea(rs.getString("tradingArea")); //意向商圈
        vo.setSquare(rs.getInt("minSquare") + "-" + rs.getInt("maxSquare")); //意向面积
        vo.setMonthly(rs.getInt("minMonthly") + "-" + rs.getInt("maxMonthly")); //意向每月租金
        vo.setTotalPrice(rs.getFloat("minTotalPrice") + "-" + rs.getInt("maxTotalPrice")); //总价格
        vo.setId(rs.getInt("id"));
        return vo;
    }
});

第三中种 查询单列，单条数据 如果为空 会抛出EmptyResultDataAccessException错误
getJdbcTemplate().queryForObject(selectSql, Date.class);

第四种 查询所有返回list<map,object>集合类型（如没有实体接受返回表记录，表中的列名，简单）
List<Map<String,Object>> userInfoPages =  getJdbcTemplate().queryForList(sql.toString(), params, types);

以上是 笔者感觉用的比较常用的查询； 下面写写 怎么保存
KeyHolder keyHolder=new GeneratedKeyHolder(); (spring jdbc中返回插入的ID需要这个)
getJdbcTemplate().update(new PreparedStatementCreator() {
    @Override
    public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
        StringBuilder sql=new StringBuilder();
        sql.append(String.format("insert into %s(" +
                "job_num,realName,gender,birthday," +
                "id_num,birthplace,education," +
                "seniority,header_img," +
                "group_id,role_id," +
                "job_grade_id,phone_num," +
                "email,STATUS," +
                "u_password,city_id," +
                "is_manage," +
                "entry_date,departure_date," +
                "job_status) " +
                "values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",PlatformConstants.MAPPING_TABLE_USERINFO));
                PreparedStatement pstat=connection.prepareCall(sql.toString());
                pstat.setString(1,userInfo.getJobNum());
                pstat.setString(2,userInfo.getRealName());
                pstat.setInt(3,userInfo.getGender());
                pstat.setDate(4,new java.sql.Date(userInfo.getBirthday().getTime()));
                pstat.setString(5,userInfo.getIdNum());
                pstat.setString(6,userInfo.getBirthplace());
                pstat.setInt(7,userInfo.getEducation());
                pstat.setString(8,userInfo.getSeniority());
                pstat.setString(9,userInfo.getHeaderImg());
                pstat.setInt(10,userInfo.getGroupId().intValue());
                pstat.setInt(11,userInfo.getRoleId().intValue());
                pstat.setInt(12,userInfo.getJobGradeId().intValue());
                pstat.setLong(13, userInfo.getPhoneNum().longValue());
                pstat.setString(14,userInfo.getEmail());
                pstat.setInt(15,userInfo.getStatus());
                pstat.setString(16,userInfo.getuPassword());
                pstat.setInt(17,userInfo.getCityId());
                pstat.setInt(18,userInfo.getIsManage());
                pstat.setDate(19,new java.sql.Date(userInfo .getEntryDate().getTime()));
                pstat.setDate(20,new java.sql.Date(userInfo.getDepartureDate().getTime()));
                pstat.setInt(21,userInfo.getJobStatus());
                return pstat;
    }
},keyHolder);
return keyHolder.getKey().intValue();
更新 操作直接简单的
getJdbcTemplate().update（字符串sql语句，？号分隔符 值数组）