spring jdbc 使用

@Repositorypublic class UserDao {@Autowiredprivate JdbcTemplate jdbcTemplate;private String sqlColumn = "user_id,account, password, user_name, org_id, cert_id, idcard_num, user_email, user_phone, key_id, vcert_sn, remark";private int count;//查询用户public List<UserVo> getUser(int start, int end, String searchValue, String org_id,String orderCol,String orderDir){String sql ="select "+sqlColumn+" from user_info where (account like ? or user_name like ?) and org_id like ? limit ?,?"Object[] params = {"%"+searchValue+"%","%"+searchValue+"%","%"+org_id+"%",start,end};@SuppressWarnings({ "unchecked",  "rawtypes" })List<UserVo> user_list = jdbcTemplate.query(sql, params,new BeanPropertyRowMapper(UserVo.class));return user_list;}//根据id查询用户public UserVo getUser(UserVo userVo) {String sql = "select "+ sqlColumn +" from user_info  where user_id = ?";Object[] params = {userVo.getUser_id()};@SuppressWarnings({ "unchecked",  "rawtypes" })List<UserVo> user_list = jdbcTemplate.query(sql, params,new BeanPropertyRowMapper(UserVo.class));return user_list.get(0);}//查询用户总条数public int getUserCount(String org_id, String searchValue) {String sql = "select count(1) from user_info where org_id like ? and (account like ? or user_name like ?)";Object[] params = {"%"+org_id+"%","%"+searchValue+"%","%"+searchValue+"%"};count = jdbcTemplate.queryForObject(sql, params,Integer.class);return count;}//添加用户public int addUser(UserVo userVo) {String sql = "insert into user_info("+sqlColumn+") values(?,?,?,?,?,?,?)";Object[] params = {userVo.getUser_id(),userVo.getAccount(),userVo.getPassword(),userVo.getUser_name(),userVo.getRemark()};count = jdbcTemplate.update(sql, params);return count;}//修改用户public int userUpdate(UserVo userVo) {String sql = "update user_info set account = ?,user_name = ?,org_id = ? where user_id = ?";Object[] params = {userVo.getAccount(),userVo.getUser_name(),userVo.getOrg_id(),userVo.getUser_id()};count = jdbcTemplate.update(sql,params);return count;}//删除用户public int deleteUser(String id) {String sql = "delete from user_info where user_id = ?";Object[] params = {id};count = jdbcTemplate.update(sql,params);return count;}｝

其他常用写法

//根据服务id获得角色idpublic List<String> getRoleIdByUserId(String user_id) {String sql = "select role_id from user_role where user_id = ?";Object[] params = {user_id};List<String> roleid_list = jdbcTemplate.queryForList(sql,params,String.class);return roleid_list;}//查询机构public List<OrgVo> getOrg(OrgVo orgVo){StringBuffer sql = new StringBuffer();sql.append("select "+sqlColumn+", remark from org_info where 1=1");List<Object> params_list = new ArrayList<Object>();//参数列表//属性非空则作为查询条件if(StringUtils.isNotEmpty(orgVo.getOrg_id())){sql.append(" and org_id = ?");params_list.add(orgVo.getOrg_id());}if(StringUtils.isNotEmpty(orgVo.getOrg_code())){sql.append(" and org_code = ?");params_list.add(orgVo.getOrg_code());}if(StringUtils.isNotEmpty(orgVo.getRemark())){sql.append(" and remark = ?");params_list.add(orgVo.getRemark());}Object[] params = new Object[params_list.size()];//参数列表list 赋值给 参数数组[]for (int i = 0; i < params_list.size(); i++) {params[i] = params_list.get(i);}List<OrgVo> org_list = queryList(sql.toString(),params);return org_list;}//其他查询结果集方式@SuppressWarnings({ "unchecked", "unused", "rawtypes" })List<OrgVo> org_list = jdbcTemplate.query(sql, params,new BeanPropertyRowMapper(OrgVo.class));@SuppressWarnings("unused")List<Map<String, Object>> org_list = jdbcTemplate.queryForList(sql,params);List<String> roleid_list = jdbcTemplate.queryForList(sql,params,String.class);