JDBC查询将resultSet转为实体类返回json

背景

多个系统,每个系统都需要查数据库中的某张表,原先做的是在一个管理系统中写一个接口,其他系统前端调用展示数据,后改为每个系统写方法调用此接口,各自前端调用各自的方法,最后改为每个系统写接口自己查库,前端调各自接口.

因为每个系统都要写一套,嫌麻烦不想都建实体类,dao,service等,用了JDBC直接将结果解析为实体类返回给前端

@RestController
@RequestMapping("/demos/demo")
public class demo{
	private Log logger = LogFactory.getLog(demo.class);
	private static Pattern linePattern = Pattern.compile("_(\\w)");
	//获取内容
	@GetMapping(value = "/test", produces = "text/plain; charset=UTF-8")
	public String test(HttpServletRequest request) {
		String responseText = "";
		Connection conn = null;
		Statement stat = null;
		ResultSet resultSet = null;
		Properties dbProp = new Properties();
		Properties parProp = new Properties();
		StringBuffer sql = new StringBuffer();
		//使用ClassLoader加载properties配置文件生成对应的输入流
		InputStream dbIn = this.getClass().getClassLoader().getResourceAsStream("config/db.properties");
		InputStream parIn = this.getClass().getClassLoader().getResourceAsStream("parameters.properties");
		try {
			//使用properties对象加载输入流
			dbProp.load(dbIn);
			parProp.load(parIn);
			//获取连接数据库所需要的值
			String driver = dbProp.getProperty("jdbc.driverClassName");
			String url = dbProp.getProperty("jdbc.url");
			String username = dbProp.getProperty("jdbc.username");
			String password = dbProp.getProperty("jdbc.password");
			String appCode = parProp.getProperty("THIS_APP_CODE");
			Class.forName(driver);
			conn = DriverManager.getConnection(url, username, password);
			stat = conn.createStatement();
			//获取数据需要的参数
			SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
			String now = formatter.format(new Date());
			String userName = "test";
			String deptId = "test";
			
			//获取消息
			sql.append(" select * from test ");
			sql.append(" where state = '1' ");
			resultSet = stat.executeQuery(sql.toString());
			ResultSetMetaData md = resultSet.getMetaData();
			//处理结果集
			int columnCount = md.getColumnCount();
			List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
	        Map<String, Object> rowData = new HashMap<String, Object>();
	        while(resultSet.next()){
				rowData = new HashMap<String, Object>(columnCount);
				for (int i = 1; i <= columnCount; i++) {
					//将字段中下划线转为驼峰
					StringBuffer fieldStr = new StringBuffer();
					Matcher matcher = linePattern.matcher(md.getColumnName(i).toLowerCase());
					while (matcher.find()) {
						matcher.appendReplacement(fieldStr, matcher.group(1).toUpperCase());
					}
					matcher.appendTail(fieldStr);
					//装填数据
	                rowData.put(fieldStr.toString(), 
	                		"CLOB".equals(md.getColumnTypeName(i))?resultSet.getString(i):resultSet.getObject(i));
	            }
				list.add(rowData);
			}
			Gson gson = new Gson();
			String json = gson.toJson(list);
    		responseText = "{\"resCode\":\"0\",\"resMsg\":\"查询成功\",\"resData\":"+json+"}";
    		logger.info("当前:"+now+"-"+appCode+"-"+deptId+"-"+userName);
		} catch (Exception e) {
			logger.error("获取消息异常：",e);
			responseText = "{\"resCode\":\"1\",\"resMsg\":\"获取异常(" + FormatObject.formatObject(e.getMessage())
			+ ")\" }";
		}finally {
            if (resultSet != null){  //避免空指针异常
                //释放资源
                try {
                	resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (stat != null){  //避免空指针异常
            	//释放资源
            	try {
            		stat.close();
            	} catch (SQLException e) {
            		e.printStackTrace();
            	}
            }
            if (conn != null){   //避免空指针异常
            	//释放资源
            	try {
            		conn.close();
            	} catch (SQLException e) {
            		e.printStackTrace();
            	}
            }
        }
		return responseText;
	}
}

最后还是被要求写实体类写mapper