一 关联删除
BEGIN
DELETE FROM data_source WHERE ID=sourceID;
DELETE FROM source_local WHERE SOURCE_ID = sourceID;
DELETE FROM source_base WHERE SOURCE_ID = sourceID;
END;
-- CALL DATA_SOURCE_DELETE('1482976851109');//直接调用
java端
int count=0;
Connection conn = ClientPool.getConn();
CallableStatement cst = null;
String sql = "{CALL DATA_SOURCE_DELETE(?)}";
try {
cst = conn.prepareCall(sql);
cst.setString(1, id);
count = cst.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally {
if (cst != null) {
try {
cst.close();
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return count;
}
二 查询数据
CREATE PROCEDURE data_source_ces(IN source_id VARCHAR(50), OUT path VARCHAR(50))
BEGIN
SELECT SOURCE_PATH INTO path FROM data_source WHERE ID=source_id;
END;
-- DROP PROCEDURE data_source_ces;//删除存储过程
//直接调用
CALL data_source_ces('1482980738328',@path);
SET @source_id='1482980738328';
CALL data_source_ces(@source_id,@path);
SELECT @path
java端
String result = null;
Connection conn = ClientPool.getConn();
CallableStatement cst = null;
String sql = "{CALL data_source_ces(?,?)}";
try {
cst = conn.prepareCall(sql);
cst.setString(1, "1482980738328");//输入 source_id
cst.registerOutParameter(2, Types.VARCHAR);//输出 path
cst.execute();
result = cst.getString(2);//获取返回结果,即定义的输出
return result;
} catch (SQLException e) {
e.printStackTrace();
}finally {
if (cst != null) {
try {
cst.close();
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return result;
}