spring jdbc 使用

一般操作

 

@Repository
public class UserDao {
	
	@Autowired
	private 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获得角色id
public 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);