第一种:查询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语句,?号分隔符 值数组)
spring jdbc template 自己的笔记
最新推荐文章于 2024-04-17 04:58:23 发布