创建存储过程查询

创建存储过程查询

DELIMITER $$    // 将标准分隔符 - 分号(;)更改为：$$, 将sql整体传递
CREATE PROCEDURE `getCamreaByCivi`(
	IN civi VARCHAR(255)
	)
BEGIN
	SELECT `NAME`,CAMERAID, FONLINE, PTZTYPE FROM vs_camera WHERE CIVILCODE = civi;
END $$
DELIMITER ;  // 改回来

CALL getCamreaByCivi("650000"); // sql调用

注意：输入的参数名不能和WHERE子句中的CIVILCODE同名，否则相当于WHERE 1=1

java中调用存储过程

// 获取到civilcode对应的摄像头信息
jdbcTemplate.getJdbcOperations().execute(new CallableStatementCreator() {
    @Override
    public CallableStatement createCallableStatement(Connection con) throws SQLException {
        String procedure = "{call getCamreaByCivi(?)}";
        CallableStatement cs = con.prepareCall(procedure);
        cs.setString(1, parentCivilcode);// 设置输入参数的值
        return cs;
    }
}, new CallableStatementCallback() {
    public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
        cs.execute();
        ResultSet rs = cs.getResultSet();
        while (rs.next()) {
            String status = "";
            if (rs.getString("FONLINE").equals("1")) {
                status = "1";
            } else if (rs.getString("FONLINE").equals("0")) {
                status = "-1";
            }
            VsSimpleTree cameraTree = new VsSimpleTree(3, rs.getString("CAMERAID"), parentCivilcode,
                    rs.getString("NAME"), "3", new ArrayList<>(), status, floor+1, Integer.parseInt(rs.getString("PTZTYPE")));
            resultList.add(cameraTree);
        }
        return resultList; // 获取输出参数的值
    }
});