薪酬管理系统后台部分是采用当时非常流程的Struts框架实现，其中数据库访问采用原生的jdbc相关技术实现。由于当时项目研发时的特殊环境所致，因此大量的逻辑使用存储过程实现。用存储过程实现业务逻辑的好处是性能快，部署方便，不用停服务进行部署；但是用存储过程实现业务的逻辑的缺点同样也非常明显，例如可读性差，可维护性差。直到现在，依然还有很多业务系统的逻辑是用数据库的存储过程实现，总的来看，我个人不建议用存储过程来实现业务逻辑，因为现在能写存储过程的程序员太少了，同时存储过程的修改和debug都是需要对数据库非常熟悉的程序员才能开发。
本文档的作用只是起到抛砖引玉的作用，供广大爱好者或者相关行业工作者学习或借鉴。欢迎任何企业或个人联系企起期，我们的网址是www.iqiqiqi.cn。
bsm\src ├── bsmConfig.properties #项目相关配置文件 ├── bsm_struts.xml #struts配置文件 ├── customQuery.xml #灵活查询配置文件 ├── dbinfo.properties #数据库连接相关配置文件 ├── etc | ├── content #封装输入输出的消息 | | ├── ContentPack.java | | ├── DataPack.java | | ├── IContentOutput.java | | ├── IContentValidator.java | | ├── InputPack.java | | └── OutputPack.java | ├── database #数据库连接池配置 | | ├── DBConnectionManager.java | | ├── ParamTypes.java | | └── SqlProcedureCaller.java | ├── excel #excel相关工具包 | | ├── api | | | ├── ImportExcelData.java | | | └── ImportExcelDataLoader.java | | ├── bean | | | ├── AreaCells.java | | | ├── AreaExtendAble.java | | | ├── Cell.java | | | ├── CellArray.java | | | ├── CellSquare.java | | | ├── ChildAble.java | | | ├── Cross.java | | | ├── DataLoadAble.java | | | ├── ExcelFile.java | | | ├── Expression.java | | | ├── FamilyAbleCell.java | | | ├── Line.java | | | ├── MarkAble.java | | | ├── ParentAble.java | | | ├── Position.java | | | ├── Row.java | | | ├── Sheet.java | | | └── VisibleCells.java | | ├── loader | | | ├── ExcelDataLoader.java | | | └── LoadManager.java | | ├── service | | | ├── CellRegister.java | | | ├── DataService.java | | | ├── DataServiceImpl.java | | | ├── ExcelService.java | | | ├── ExcelServiceImpl.java | | | └── KeyWords.java | | └── util #excel相关处理 | | ├── ElementLoader.java | | ├── ExcelUtil.java | | ├── StringUtil.java | ├── exception | | ├── NoSearchResultFoundException.java | | ├── PromptException.java | | └── SessionTimeoutException.java | ├── http #封装的http调用相关包 | | ├── ByteArrayPartSource.java | | ├── ControllerThreadSocketFactory.java | | ├── DefaultProtocolSocketFactory.java | | ├── EasySSLProtocolSocketFactory.java | | ├── FilePart.java | | ├── FilePartSource.java | | ├── MultipartEntity.java | | ├── NaiveTrustManager.java | | ├── Part.java | | ├── PartBase.java | | ├── PartSource.java | | ├── Protocol.java | | ├── ProtocolSocketFactory.java | | ├── SecureProtocolSocketFactory.java | | ├── SSLProtocolSocketFactory.java | | ├── StringPart.java | | └── TimeoutController.java | ├── security #封装的安全相关的调用包 | | ├── Base64.java | | ├── Encoder.java | | └── RandomGenerator.java | └── util #常用小工具，例如字符串处理，日期处理等等 | ├── Arith.java | ├── CharacterUtils.java | ├── CharsetUtil.java | ├── DatabaseUtil.java | ├── DateJsonValueProcessor.java | ├── DateTimeUtil.java | ├── EchoData.java | ├── EchoDataPool.java | ├── EchoOracleData.java | ├── EchoOracleDataPool.java #Oracle连接池 | ├── ExcelUtil.java #封装的excel操作 | ├── Field.java | ├── FileOperate.java #封装的文件操作 | ├── FileUploadSaveAs.java | ├── HttpUtil.java | ├── InputStreamUtil.java | ├── JsonUtil.java #josn相关处理的封装 | ├── Kernel32.java | ├── LockWorkStation.java | ├── MainDemo.java | ├── MD5.java | ├── MESInterface.java | ├── NumberUtils.java | ├── ParamUtils.java | ├── RegexTestPatternMatcher.java | ├── SendMessage.java | ├── SfJsonUtil.java #SourceForge json操作封装 | ├── StringUtil.java | ├── StringUtils.java | ├── TestCallDll.java | ├── User32.java | └── UTF8CharacterEncodingFilter.java #过滤器，设置UTF-8 ├── log4j.xml #log配置文件 ├── oracleDbinfo.properties #Oracle相关配置文件 ├── qiqiqi | └── bsm | ├── action #所有业务模块的action包，接收浏览器端过来的请求 | | ├── AdjustSalaryManagementAction.java | | ├── AreaManagementAction.java | | ├── CustomQueryManagementAction.java | | ├── DepartmentExpensesFieldManagementAction.java | | ├── DepartmentManagementAction.java | | ├── DepartmentPluralismManagementAction.java | | ├── DictionaryManagementAction.java | | ├── ExpenseFieldInfoManagementAction.java | | ├── ExpenseManagementAction.java | | ├── ExpensesClaimManagementAction.java | | ├── ExpensesFieldManagementAction.java | | ├── ExpenseStatFieldManagementAction.java | | ├── ExpenseStatManagementAction.java | | ├── ExportExcelAction.java | | ├── ExportTemplateManagementAction.java | | ├── FieldInfoManagementAction.java | | ├── GlobalParameterManagementAction.java | | ├── LeaveManagementAction.java | | ├── MarkRateManagementAction.java | | ├── OperationTypeParameterManagementAction.java | | ├── PerformanceManagementAction.java | | ├── PositionManagementAction.java | | ├── PrivateTaxRateManagementAction.java | | ├── QualificationManagementAction.java | | ├── QueryManagementAction.java | | ├── RunningCostClaimManagementAction.java | | ├── RunningCostFieldManagementAction.java | | ├── RunningCostImportManagementAction.java | | ├── SalaryLevelManagementAction.java | | ├── SalaryManagementAction.java | | ├── SmsSentResultManagementAction.java | | ├── SmsTemplateManagementAction.java | | ├── UserManagementAction.java | | ├── VacationManagementAction.java | | ├── VacationReportManagementAction.java | | ├── VacationSmsTemplateManagementAction.java | | └── VacationTypeManagementAction.java | ├── dao #封装所有业务模块的数据库访问层，主体逻辑是设置参数调用存储过程 | | ├── AdjustSalaryManagementDao.java | | ├── AreaManagementDao.java | | ├── CustomQueryManagementDao.java | | ├── DepartmentExpensesFieldManagementDao.java | | ├── DepartmentManagementDao.java | | ├── DepartmentPluralismManagementDao.java | | ├── DictionaryManagementDao.java | | ├── ExpenseFieldInfoManagementDao.java | | ├── ExpenseManagementDao.java | | ├── ExpensesClaimManagementDao.java | | ├── ExpensesFieldManagementDao.java | | ├── ExpenseStatFieldManagementDao.java | | ├── ExpenseStatManagementDao.java | | ├── ExportTemplateManagementDao.java | | ├── FieldInfoManagementDao.java | | ├── GlobalParameterManagementDao.java | | ├── LeaveManagementDao.java | | ├── MarkRateManagementDao.java | | ├── OperationTypeParameterManagementDao.java | | ├── PerformanceManagementDao.java | | ├── PositionManagementDao.java | | ├── PrivateTaxRateManagementDao.java | | ├── QualificationManagementDao.java | | ├── QueryManagementDao.java | | ├── RunningCostClaimManagementDao.java | | ├── RunningCostFieldManagementDao.java | | ├── RunningCostImportManagementDao.java | | ├── SalaryLevelManagementDao.java | | ├── SalaryManagementDao.java | | ├── SmsSentResultManagementDao.java | | ├── SmsTemplateManagementDao.java | | ├── UserManagementDao.java | | ├── VacationManagementDao.java | | ├── VacationReportManagementDao.java | | ├── VacationSmsTemplateManagementDao.java | | └── VacationTypeManagementDao.java | └── service #封装所有业务的服务层 | ├── AdjustSalaryManagementService.java | ├── AreaManagementService.java | ├── CustomQueryManagementService.java | ├── DepartmentExpensesFieldManagementService.java | ├── DepartmentManagementService.java | ├── DepartmentPluralismManagementService.java | ├── DictionaryManagementService.java | ├── ExpenseFieldInfoManagementService.java | ├── ExpenseManagementService.java | ├── ExpensesClaimManagementService.java | ├── ExpensesFieldManagementService.java | ├── ExpenseStatFieldManagementService.java | ├── ExpenseStatManagementService.java | ├── ExportTemplateManagementService.java | ├── FieldInfoManagementService.java | ├── GlobalParameterManagementService.java | ├── LeaveManagementService.java | ├── MarkRateManagementService.java | ├── OperationTypeParameterManagementService.java | ├── PerformanceManagementService.java | ├── PositionManagementService.java | ├── PrivateTaxRateManagementService.java | ├── QualificationManagementService.java | ├── QueryManagementService.java | ├── RunningCostClaimManagementService.java | ├── RunningCostFieldManagementService.java | ├── RunningCostImportManagementService.java | ├── SalaryLevelManagementService.java | ├── SalaryManagementService.java | ├── SmsSentResultManagementService.java | ├── SmsTemplateManagementService.java | ├── UserManagementService.java | ├── VacationManagementService.java | ├── VacationReportManagementService.java | ├── VacationSmsTemplateManagementService.java | └── VacationTypeManagementService.java ├── struts.properties #struts属性配置文件 └── struts.xml #structs配置文件
一个成熟的后台项目包含很多技术框架类的内容，例如组件库，安全处理，统一日志处理，多语言，统一异常处理，excel导入导出等等。由于此薪酬管理系统是一个完整的项目，技术框架该有的基础功能已经基本涵盖，以下从二个功能点代码进行讲解，起到抛砖引玉的作用，完整的代码学习还需要自行在项目中或者自行debug进行学习。
对应《薪酬管理系统代码讲解(前端部分)》，此处解析java端对应的后台部分。
package qiqiqi.bsm.action;
import java.io.File;
import com.opensymphony.xwork2.ActionSupport;
import etc.content.ContentPack;
import etc.content.IContentOutput;
import qiqiqi.bsm.service.UserManagementService;
import org.apache.log4j.Logger;
import org.apache.struts2.ServletActionContext;
public class UserManagementAction extends ActionSupport implements IContentOutput{
/**
*
*/
private static final long serialVersionUID = -6861570896996776616L;
public static final String TAG = "UserManagementAction";
private static final String OUTPUT = "output";
private Logger log = Logger.getLogger(UserManagementAction.class);
private static UserManagementService service = new UserManagementService();
private ContentPack content;
private File uploadFile;
private String uploadFileFileName;
public File getUploadFile() {
return uploadFile;
}
public void setUploadFile(File uploadFile) {
this.uploadFile = uploadFile;
}
public String getUploadFileFileName() {
return uploadFileFileName;
}
public void setUploadFileFileName(String uploadFileFileName) {
this.uploadFileFileName = uploadFileFileName;
}
private String output;
public String getOutput() {
return output;
}
public void setOutput(String output) {
this.output = output;
}
public String process(){
try{
content = new ContentPack();
if(!content.getInput().getIntent().equals("doLogin")){
content.checkAndSetSession();
}
String intent = (String)(content.getInput().getIntent());
log.info(TAG+".process(@intent="+intent+")");
if(intent.equals("getUserList")){
service.getUserList(content);
}
else if(intent.equals("getUserListByDepartment")){
service.getUserListByDepartment(content);
}
else if(intent.equals("addUser")){
service.addUser(content); //用户添加
}
else if(intent.equals("updateUser")){
service.updateUser(content); //用户修改
}
else if(intent.equals("deleteUser")){
service.deleteUser(content); //用户删除
}
else if(intent.equals("getUserInfo")){
service.getUserInfo(content); //得到用户信息
}
else if(intent.equals("doLogin")){
//登录处理
service.doLogin(content,ServletActionContext.getRequest());
}
else if(intent.equals("getUserMenuPermission")){
//得到当前用户的菜单权限
service.getUserMenuPermission(content,ServletActionContext.getRequest());
}
else if(intent.equals("changePassword")){
log.info(content.getSession().getString("USERID")+" 修改个人密码");
service.changePassword(content);
}
else if(intent.equals("setUserPermission")){
log.info(content.getSession().getString("USERID")+" 设置"+content.getInput().getString("USERID")+"权限");
service.setUserPermission(content);
}
else if(intent.equals("getUserPermission")){
service.getUserPermission(content);
}
else if(intent.equals("uploadUser")){
content.getInput().put("UPLOADFILE", this.getUploadFile());
content.getInput().put("UPLOADFILEFILENAME", this.getUploadFileFileName());
service.uploadUser(content);
}
else if(intent.equals("uploadBasicInfo")){
content.getInput().put("UPLOADFILE", this.getUploadFile());
content.getInput().put("UPLOADFILEFILENAME", this.getUploadFileFileName());
service.uploadBasicInfo(content);
}
else if(intent.equals("getBasicInfoList")){
service.getBasicInfoList(content);
}
else if(intent.equals("saveBasicInfo")){
service.saveBasicInfo(content);
}
else if(intent.equals("getMinOrderId")){
service.getMinOrderId(content);
}
else if(intent.equals("getVirtualUserList")){ //得到虚拟用户列表
service.getVirtualUserList(content);
}
else if(intent.equals("addVirtualUser")){
service.addVirtualUser(content); //虚拟用户添加
}
else if(intent.equals("updateVirtualUser")){
service.updateVirtualUser(content); //虚拟用户修改
}
else if(intent.equals("deleteVirtualUser")){
service.deleteVirtualUser(content); //虚拟用户删除
}
else if(intent.equals("getVirtualUserInfo")){
service.getVirtualUserInfo(content); //虚拟得到用户信息
}
else if(intent.equals("getUserListForExpenses")){
service.getUserListForExpenses(content); //根据登录用户得到所属部门的用户包括虚拟用户
}
else{
content.getOutput().setMessage("No implementation code for the Intent being requested!");
}
content.getOutput().send((IContentOutput)this);
}catch(Exception ex){
try{
content.getOutput().setException(ex).send((IContentOutput)this);
}catch(Exception e){
log.error(e);
e.printStackTrace();
};
}
return OUTPUT;
}
}从以上代码可以看出，此action对应的是用户管理的Action，可以看到所有的请求都封装到process尽显处理，这是为了简化struts的配置，否则每个action都需要配置对应的method。现在的SpringBoot框架，已经把这些相关的配置简化到注解中了。
从以上代码可以看出，不同的业务处理，通过intent进行区分。在进行实际业务的调用时，先需要校验是否存在session，当会话不存在时给出对应的提醒，提醒用户重新登录。当会话存在时，则重新设置该Session，已延长会话的保存时间。
当判断到intent操作是doLogin时，则调用服务端的doLogin进行处理。
由于本项目的业务逻辑主要是在存储过程中实现，因此Service一层是薄薄的一层。当然，有些特殊的比较复杂的逻辑，还是会在Service层中进行实现。以下是对应的doLogin 服务层代码：
/**
* 登录处理
* @param content
* @throws Exception
*/
public void doLogin(ContentPack content,HttpServletRequest request) throws Exception
{
log.debug(TAG+" - doLogin(content)");
if(content.validate()){
maDao.doLogin(content,request);
}
}从以上代码可以看出，Service这层比较薄，首先是打印了相关log，为了出错时进行跟踪提供方便。其次是对传入过来的信息进行校验，校验输入的参数是否正确，如果正确则调用对应DAO层的doLogin进行登录处理，如果校验失败，则返回对应的出错信息到前端。
对应的DAO层代码如下：
/**
* 登录处理
* @param content
* @throws Exception
*/
public void doLogin(ContentPack content,HttpServletRequest request) throws Exception
{
log.debug(TAG+" - doLogin(content)");
SqlProcedureCaller caller = new SqlProcedureCaller("SP_USERMGT_LOGIN");
caller.setParam("USERID", content.getInput().getString("USERID"),ParamTypes.STRING,false,1);
caller.setParam("PASSWORD", content.getInput().getString("PASSWORD"),ParamTypes.STRING,false,2);
caller.setParam("MESSAGE", null,ParamTypes.STRING,true,3);
caller.setParam("USERINFO", null,ParamTypes.JSONARRAY,true,0);
try{
caller.open();
caller.call();
String message = (String)(caller.getOutput("MESSAGE"));
if(message.equals("OK")) //登录成功,设置session
{
JSONArray userInfo = (org.json.JSONArray)caller.getOutput("USERINFO");
String userId = ((JSONObject)userInfo.get(0)).getString("USERID");
String userName = ((JSONObject)userInfo.get(0)).getString("USERNAME");
request.getSession().setAttribute("userid", userId);
request.getSession().setAttribute("username",userName);
}
content.getOutput().setMessage(message);
}finally{
caller.close();
}
}通过上述代码可以知道，DAO层的主要作用就是给存储过程的调用设置参数，另外根据存储过程调用返回的结果设置输出到前端的响应消息。从上面代码可以看出，登录调用的存储过程为SP_USERMGT_LOGIN，以下为对应的存储过程代码：
CREATE DEFINER=`sosb`@`%` PROCEDURE `SP_USERMGT_LOGIN`(IN v_userid varchar(200), IN v_password varchar(200), OUT v_message varchar(1000)) BEGIN DECLARE v_count int; SELECT count(*) INTO v_count FROM tb_user a WHERE a.userid = v_userid AND a.password = v_password; IF v_count > 0 THEN SELECT count(*) INTO v_count FROM tb_user_permission WHERE userid = v_userid; IF v_count > 0 THEN SET v_message = 'OK'; SELECT userid,username FROM tb_user WHERE userid = v_userid; ELSE SET v_message = '没有任何权限！请联系管理员！'; END IF; ELSE SET v_message = '不存在此用户或密码不正确!'; END IF; END
从以上存储过程代码可以看出，首先判断是否存在该用户，如果不存在则返回对应的提醒信息。如果存在该用户，则判断该用户是否有权限，如果没有任何权限则返回对应的提醒信息。如果上述校验都没有问题，则返回对应的用户记录信息。
薪资项指标是薪资的核心组成部分，前端通过添加，删除，修改调后端接口进行指标的增删改。由于这些指标会真实对应到数据库的表字段上，因此本篇文档把这个功能点拿出来讲有一定的意义。毕竟指标维护并不象常规功能点的增删改，同时业务角度来看，指标也是薪资管理的核心功能点。
前端发出请求时，都会对应到后端的action层（按照Struts的标准），以下为对应的action层代码：
package qiqiqi.bsm.action;
import com.opensymphony.xwork2.ActionSupport;
import etc.content.ContentPack;
import etc.content.IContentOutput;
import qiqiqi.bsm.service.FieldInfoManagementService;
import org.apache.log4j.Logger;
public class FieldInfoManagementAction extends ActionSupport implements IContentOutput{
/**
*
*/
private static final long serialVersionUID = -1976078359098869671L;
public static final String TAG = "FieldInfoManagementAction";
private static final String OUTPUT = "output";
private Logger log = Logger.getLogger(FieldInfoManagementAction.class);
private static FieldInfoManagementService service = new FieldInfoManagementService();
private ContentPack content=new ContentPack();
private String output;
public String getOutput() {
return output;
}
public void setOutput(String output) {
this.output = output;
}
public String process(){
try{
content = new ContentPack();
content.checkAndSetSession();
String intent = (String)(content.getInput().getIntent());
log.info(TAG+".process(@intent="+intent+")");
if(intent.equals("getFieldInfoList")){
service.getFieldInfoList(content);
}
else if(intent.equals("addFieldInfo")){
service.addFieldInfo(content); //指标添加
}
else if(intent.equals("updateFieldInfo")){
service.updateFieldInfo(content); //指标修改
}
else if(intent.equals("deleteFieldInfo")){
service.deleteFieldInfo(content); //指标删除
}
else if(intent.equals("getFieldInfoInfo")){
service.getFieldInfoInfo(content); //得到指标信息
}
else{
content.getOutput().setMessage("No implementation code for the Intent being requested!");
}
content.getOutput().send((IContentOutput)this);
}catch(Exception ex){
try{
content.getOutput().setException(ex).send((IContentOutput)this);
}catch(Exception e){
log.error(e);
e.printStackTrace();
};
}
return OUTPUT;
}
}通过上述代码可以看到，action层根据传过来的intent，判断是执行哪部分业务逻辑，当intent为addFieldInfo时，则是添加薪资项指标；当intent为updateFieldInfo时则是修改薪资项指标；当intent为deleteFieldInfo时则是进行指标项删除；当intent为getFieldInfoInfo时，则是获得薪资项指标相关信息。
action调用对应的servcie层进行处理，以下是对应的service层代码：
package qiqiqi.bsm.service;
import qiqiqi.bsm.dao.FieldInfoManagementDao;
import org.apache.log4j.Logger;
import etc.content.ContentPack;
public class FieldInfoManagementService{
static private final String TAG = "FieldInfoManagementService";
static private Logger log = Logger.getLogger(FieldInfoManagementService.class);
static private FieldInfoManagementDao maDao = new FieldInfoManagementDao();
public void getFieldInfoList(ContentPack content)throws Exception{
maDao.getFieldInfoList(content);
}
/**
* 添加指标
* @param content
* @throws Exception
*/
public void addFieldInfo(ContentPack content) throws Exception
{
log.debug(TAG+" - addFieldInfo(content)");
if(content.validate()){
maDao.addFieldInfo(content);
}
}
/**
* 修改指标
* @param content
* @throws Exception
*/
public void updateFieldInfo(ContentPack content) throws Exception
{
log.debug(TAG+" - updateFieldInfo(content)");
if(content.validate()){
maDao.updateFieldInfo(content);
}
}
/**
* 删除指标
* @param content
* @throws Exception
*/
public void deleteFieldInfo(ContentPack content) throws Exception
{
log.debug(TAG+" - deleteFieldInfo(content)");
if(content.validate()){
maDao.deleteFieldInfo(content);
}
}
/**
* 得到指标信息
* @param content
* @throws Exception
*/
public void getFieldInfoInfo(ContentPack content) throws Exception
{
log.debug(TAG+" - getFieldInfoInfo(content)");
if(content.validate()){
maDao.getFieldInfoInfo(content);
}
}
}以上service的实现包括五个函数，一个为得到指标列表，一个为添加指标，另一个为修改指标，还有一个为删除指标，最后一个为得到指标的详细信息。与登录的service一样，此service层为很薄的一层，具体的业务逻辑都是在存储过程中实现的，我们先一起来看看对应的DAO层代码：
package qiqiqi.bsm.dao;
import org.apache.log4j.Logger;
import org.json.JSONArray;
import etc.content.ContentPack;
import etc.database.ParamTypes;
import etc.database.SqlProcedureCaller;
public class FieldInfoManagementDao {
public static final String TAG = "FieldInfoManagementDao";
private Logger log = Logger.getLogger(FieldInfoManagementDao.class);
public void getFieldInfoList(ContentPack content) throws Exception{
log.debug(TAG+" - getFieldInfoList(content)");
SqlProcedureCaller caller = new SqlProcedureCaller("SP_FIELDINFO_MGT_GETFIELDINFOLIST");
caller.setParam("FLAG", content.getInput().getString("FLAG"),ParamTypes.STRING,false,1);
caller.setParam("MESSAGE", null,ParamTypes.STRING,true,2);
caller.setParam("FIELDINFOLIST", null,ParamTypes.JSONARRAY,true,0);
try{
caller.open();
caller.call();
String message = (String)(caller.getOutput("MESSAGE"));
content.getOutput().setMessage(message);
if(message.equals("OK")){
//get item list rowset
JSONArray rtnList = (JSONArray)caller.getOutput("FIELDINFOLIST");
content.getOutput().put("FIELDINFOLIST",rtnList);
}
}finally{
caller.close();
}
}
/**
* 给SalaryManagementService用
* @param flag
* @return
* @throws Exception
*/
public JSONArray getFieldInfoList(String flag) throws Exception{
log.debug(TAG+" - getFieldInfoList(flag)");
JSONArray rtnList=null;
SqlProcedureCaller caller = new SqlProcedureCaller("SP_FIELDINFO_MGT_GETFIELDINFOLIST");
caller.setParam("FLAG", flag,ParamTypes.STRING,false,1);
caller.setParam("MESSAGE", null,ParamTypes.STRING,true,2);
caller.setParam("FIELDINFOLIST", null,ParamTypes.JSONARRAY,true,0);
try{
caller.open();
caller.call();
String message = (String)(caller.getOutput("MESSAGE"));
if(message.equals("OK")){
//get item list rowset
rtnList = (JSONArray)caller.getOutput("FIELDINFOLIST");
}
return rtnList;
}finally{
caller.close();
}
}
/**
* 根据指标名称得到对应的字段名称
* @param fieldName
* @return
* @throws Exception
*/
public String getFieldCodeByName(String fieldName) throws Exception{
log.debug(TAG+" - getFieldCodeByName(flag)");
String fieldCode = "";
SqlProcedureCaller caller = new SqlProcedureCaller("SP_FIELDINFO_MGT_GETFIELDCODE");
caller.setParam("FIELDNAME", fieldName,ParamTypes.STRING,false,1);
caller.setParam("MESSAGE", null,ParamTypes.STRING,true,2);
caller.setParam("FIELDCODE", null,ParamTypes.STRING,true,3);
try{
caller.open();
caller.call();
String message = (String)(caller.getOutput("MESSAGE"));
if(message.equals("OK")){
//get item list rowset
fieldCode = (String)(caller.getOutput("FIELDCODE"));
}
else{
fieldCode = "NONE"; //没找到对应的字段
}
return fieldCode;
}finally{
caller.close();
}
}
/**
* 根据code得到数据类型
* @param fieldName
* @return
* @throws Exception
*/
public String getFieldTypeByCode(String fieldCode) throws Exception{
log.debug(TAG+" - getFieldTypeByCode(flag)");
String fieldType = "";
SqlProcedureCaller caller = new SqlProcedureCaller("SP_FIELDINFO_MGT_GETFIELDTYPE");
caller.setParam("FIELDCODE", fieldCode,ParamTypes.STRING,false,1);
caller.setParam("MESSAGE", null,ParamTypes.STRING,true,2);
caller.setParam("FIELDTYPE", null,ParamTypes.STRING,true,3);
try{
caller.open();
caller.call();
String message = (String)(caller.getOutput("MESSAGE"));
if(message.equals("OK")){
//get item list rowset
fieldType = (String)(caller.getOutput("FIELDTYPE"));
}
else{
fieldType = "Z"; //没找到对应的字段
}
return fieldType;
}finally{
caller.close();
}
}
/**
* 添加指标
* @param content
* @throws Exception
*/
public void addFieldInfo(ContentPack content) throws Exception
{
log.debug(TAG+" - addFieldInfo(content)");
SqlProcedureCaller caller = new SqlProcedureCaller("SP_FIELDINFO_MGT_ADD");
caller.setParam("CODE", content.getInput().getString("CODE"),ParamTypes.STRING,false,1);
caller.setParam("NAME", content.getInput().getString("NAME"),ParamTypes.STRING,false,2);
caller.setParam("TYPE", content.getInput().getString("TYPE"),ParamTypes.STRING,false,3);
caller.setParam("LENGTH", content.getInput().getString("LENGTH"),ParamTypes.STRING,false,4);
caller.setParam("PRECISE", content.getInput().getString("PRECISE"),ParamTypes.STRING,false,5);
caller.setParam("ENABLEFLAG", content.getInput().getString("ENABLEFLAG"),ParamTypes.STRING,false,6);
caller.setParam("CANEXPORT", content.getInput().getString("CANEXPORT"),ParamTypes.STRING,false,7);
caller.setParam("CANDISPLAY", content.getInput().getString("CANDISPLAY"),ParamTypes.STRING,false,8);
caller.setParam("ISBASIC", content.getInput().getString("ISBASIC"),ParamTypes.STRING,false,9);
caller.setParam("COMMENTS", content.getInput().getString("COMMENTS"),ParamTypes.STRING,false,10);
caller.setParam("FORMULA", content.getInput().getString("FORMULA"),ParamTypes.STRING,false,11);
caller.setParam("ISINCOME", content.getInput().getString("ISINCOME"),ParamTypes.STRING,false,12);
caller.setParam("ISRELATIONTOLEVEL", content.getInput().getString("ISRELATIONTOLEVEL"),ParamTypes.STRING,false,13);
caller.setParam("ISRELATIONTOPOSITION", content.getInput().getString("ISRELATIONTOPOSITION"),ParamTypes.STRING,false,14);
caller.setParam("ISDEFAULTHIDDEN", content.getInput().getString("ISDEFAULTHIDDEN"),ParamTypes.STRING,false,15);
caller.setParam("ISRELATIONTOTAX", content.getInput().getString("ISRELATIONTOTAX"),ParamTypes.STRING,false,16);
caller.setParam("ORDERID", content.getInput().getString("ORDERID"),ParamTypes.STRING,false,17);
caller.setParam("ISRELATIONTOMARK", content.getInput().getString("ISRELATIONTOMARK"),ParamTypes.STRING,false,18);
caller.setParam("FORMULANAME", content.getInput().getString("FORMULANAME"),ParamTypes.STRING,false,19);
caller.setParam("XMLCONTENT", content.getInput().getPackXmlString("CONTENT"),ParamTypes.STRING,false,20);
caller.setParam("MESSAGE", null,ParamTypes.STRING,true,21);
caller.setParam("ISROUND", content.getInput().getString("ISROUND"),ParamTypes.STRING,false,22);
caller.setParam("DOUPDATESALARYLEVEL", content.getInput().getString("DOUPDATESALARYLEVEL"),ParamTypes.STRING,false,23);
try{
caller.open();
caller.call();
String message = (String)(caller.getOutput("MESSAGE"));
content.getOutput().setMessage(message);
}finally{
caller.close();
caller=null;
}
}
/**
* 修改指标
* @param content
* @throws Exception
*/
public void updateFieldInfo(ContentPack content) throws Exception
{
log.debug(TAG+" - updateFieldInfo(content)");
SqlProcedureCaller caller = new SqlProcedureCaller("SP_FIELDINFO_MGT_UPDATE");
caller.setParam("CODE", content.getInput().getString("CODE"),ParamTypes.STRING,false,1);
caller.setParam("NAME", content.getInput().getString("NAME"),ParamTypes.STRING,false,2);
caller.setParam("TYPE", content.getInput().getString("TYPE"),ParamTypes.STRING,false,3);
caller.setParam("LENGTH", content.getInput().getString("LENGTH"),ParamTypes.STRING,false,4);
caller.setParam("PRECISE", content.getInput().getString("PRECISE"),ParamTypes.STRING,false,5);
caller.setParam("ENABLEFLAG", content.getInput().getString("ENABLEFLAG"),ParamTypes.STRING,false,6);
caller.setParam("CANEXPORT", content.getInput().getString("CANEXPORT"),ParamTypes.STRING,false,7);
caller.setParam("CANDISPLAY", content.getInput().getString("CANDISPLAY"),ParamTypes.STRING,false,8);
caller.setParam("ISBASIC", content.getInput().getString("ISBASIC"),ParamTypes.STRING,false,9);
caller.setParam("COMMENTS", content.getInput().getString("COMMENTS"),ParamTypes.STRING,false,10);
caller.setParam("FORMULA", content.getInput().getString("FORMULA"),ParamTypes.STRING,false,11);
caller.setParam("ISINCOME", content.getInput().getString("ISINCOME"),ParamTypes.STRING,false,12);
caller.setParam("ISRELATIONTOLEVEL", content.getInput().getString("ISRELATIONTOLEVEL"),ParamTypes.STRING,false,13);
caller.setParam("ISRELATIONTOPOSITION", content.getInput().getString("ISRELATIONTOPOSITION"),ParamTypes.STRING,false,14);
caller.setParam("ISDEFAULTHIDDEN", content.getInput().getString("ISDEFAULTHIDDEN"),ParamTypes.STRING,false,15);
caller.setParam("ISRELATIONTOTAX", content.getInput().getString("ISRELATIONTOTAX"),ParamTypes.STRING,false,16);
caller.setParam("ORDERID", content.getInput().getString("ORDERID"),ParamTypes.STRING,false,17);
caller.setParam("ISRELATIONTOMARK", content.getInput().getString("ISRELATIONTOMARK"),ParamTypes.STRING,false,18);
caller.setParam("FORMULANAME", content.getInput().getString("FORMULANAME"),ParamTypes.STRING,false,19);
caller.setParam("XMLCONTENT", content.getInput().getPackXmlString("CONTENT"),ParamTypes.STRING,false,20);
caller.setParam("MESSAGE", null,ParamTypes.STRING,true,21);
caller.setParam("ISROUND", content.getInput().getString("ISROUND"),ParamTypes.STRING,false,22);
caller.setParam("DOUPDATESALARYLEVEL", content.getInput().getString("DOUPDATESALARYLEVEL"),ParamTypes.STRING,false,23);
try{
caller.open();
caller.call();
String message = (String)(caller.getOutput("MESSAGE"));
content.getOutput().setMessage(message);
}finally{
caller.close();
caller=null;
}
}
/**
* 删除指标
* @param content
* @throws Exception
*/
public void deleteFieldInfo(ContentPack content) throws Exception
{
log.debug(TAG+" - deleteFieldInfo(content)");
SqlProcedureCaller caller = new SqlProcedureCaller("SP_FIELDINFO_MGT_DELETE");
caller.setParam("CODE", content.getInput().getString("CODE"),ParamTypes.STRING,false,1);
caller.setParam("MESSAGE", null,ParamTypes.STRING,true,2);
try{
caller.open();
caller.call();
String message = (String)(caller.getOutput("MESSAGE"));
content.getOutput().setMessage(message);
}finally{
caller.close();
caller=null;
}
}
/**
* 得到指标信息
* @param content
* @throws Exception
*/
public void getFieldInfoInfo(ContentPack content) throws Exception
{
log.debug(TAG+" - getFieldInfoInfo(content)");
SqlProcedureCaller caller = new SqlProcedureCaller("SP_FIELDINFO_MGT_GETINFO");
caller.setParam("CODE", content.getInput().getString("CODE"),ParamTypes.STRING,false,1);
caller.setParam("MESSAGE", null,ParamTypes.STRING,true,2);
caller.setParam("FIELDINFOLIST", null,ParamTypes.JSONARRAY,true,0);
try{
caller.open();
caller.call();
String message = (String)(caller.getOutput("MESSAGE"));
content.getOutput().setMessage(message);
if(message.equals("OK")){
//get item list rowset
JSONArray rtnList = (JSONArray)caller.getOutput("FIELDINFOLIST");
content.getOutput().put("FIELDINFOLIST",rtnList);
}
}finally{
caller.close();
}
}
}如以上代码所示，DAO层的主要任务就是调用对应的存储过程，存储过程的参数值及位置需要在DAO层进行指定。以下用添加指标的存储过程以及删除指标的存储过程进行代码逻辑讲解。
CREATE PROCEDURE `SP_FIELDINFO_MGT_ADD`(
IN v_CODE VARCHAR(30),
IN v_NAME VARCHAR(100),
IN v_TYPE VARCHAR(6),
IN v_LENGTH VARCHAR(6),
IN v_PRECISE VARCHAR(6),
IN v_ENABLEFLAG VARCHAR(6),
IN v_CANEXPORT VARCHAR(6),
IN v_CANDISPLAY VARCHAR(6),
IN v_ISBASIC VARCHAR(6),
IN v_COMMENTS VARCHAR(500),
IN v_FORMULA VARCHAR(1000),
IN v_ISINCOME VARCHAR(6),
IN v_ISRELATIONTOLEVEL VARCHAR(6),
IN v_ISRELATIONTOPOSITION VARCHAR(6),
IN v_ISDEFAULTHIDDEN VARCHAR(6),
IN v_ISRELATIONTOTAX VARCHAR(6),
IN v_ORDERID VARCHAR(20),
IN v_ISRELATIONTOMARK VARCHAR(6),
IN v_FORMULANAME VARCHAR(2000),
IN v_xmlcontent VARCHAR(20000),
OUT v_message VARCHAR(1000),
IN v_isround VARCHAR(6),
IN v_doupdatesalarylevelformula CHAR(1))
BEGIN
DECLARE v_count int;
DECLARE i int(4) DEFAULT 1;
DECLARE s_salarylevelid varchar(20);
DECLARE s_formulacount int;
DECLARE s_formula varchar(1000);
DECLARE s_formulaname varchar(2000);
DECLARE v_sql varchar(1000);
DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN
ROLLBACK;
SET v_message = '存储过程 SP_FIELDINFO_MGT_ADD 发现异常';
END;
SET autocommit = 0;
START TRANSACTION;
SELECT count(*)
INTO v_count
FROM tb_FIELD_INFO
WHERE code = v_code or name=v_name;
IF v_count > 0
THEN
SET v_message = concat('已经存在', v_name, '指标编码');
ELSE
INSERT INTO bsm.tb_field_info(code,
name,
type,
length,
precise,
enableflag,
canexport,
candisplay,
isbasic,
comments,
formula,
isincome,
isrelationtolevel,
isrelationtoposition,
isdefaulthidden,
isrelationtotax,
orderid,
isrelationtomark,
formulaname,isround)
VALUES (v_code,
v_name,
v_type,
v_length,
v_precise,
v_enableflag,
v_canexport,
v_candisplay,
v_isbasic,
v_comments,
v_formula,
v_isincome,
v_isrelationtolevel,
v_isrelationtoposition,
v_isdefaulthidden,
v_isrelationtotax,
v_orderid,
v_isrelationtomark,
v_formulaname,v_isround);
IF v_isrelationtolevel = '1' and v_doupdatesalarylevelformula='1'
THEN
/**插入对应关联到工资级别的公式*/
SET s_formulacount =
ExtractValue(
v_xmlcontent,
'count(/CONTENT/DATA/SALARYLEVELFORMULALIST)');
WHILE i <= s_formulacount
DO
SET s_salarylevelid =
ExtractValue(
v_xmlcontent,
concat('/CONTENT/DATA/SALARYLEVELFORMULALIST[',
i,
']/SALARYLEVELID'));
SET s_formula =
ExtractValue(
v_xmlcontent,
concat('/CONTENT/DATA/SALARYLEVELFORMULALIST[',
i,
']/FORMULA'));
SET s_formulaname =
ExtractValue(
v_xmlcontent,
concat('/CONTENT/DATA/SALARYLEVELFORMULALIST[',
i,
']/FORMULANAME'));
INSERT INTO tb_salary_level_formula(salarylevelid,
fieldcode,
formula,
formulaname)
VALUES (s_salarylevelid,
lower(v_code),
s_formula,
s_formulaname);
SET i = i + 1;
END WHILE;
END IF;
END IF;
IF v_count = 0
THEN
IF v_type = '1'
THEN
/*数值型*/
SET v_sql =
concat('ALTER TABLE bsm.tb_salary_information ADD ',
lower(v_code),
' NUMERIC(',
v_length,
',',
v_precise,
') DEFAULT 0 COMMENT ''',
v_name,
''';');
ELSEIF v_type = '2'
THEN
/*字符型*/
SET v_sql =
concat('ALTER TABLE bsm.tb_salary_information ADD ',
lower(v_code),
' VARCHAR(',
v_length,
') COMMENT ''',
v_name,
''';');
END IF;
SET @sql = v_sql;
PREPARE v_statement FROM @sql;
EXECUTE v_statement;
DEALLOCATE PREPARE v_statement;
END IF;
COMMIT;
IF v_message IS NULL OR v_message = ''
THEN
SET v_message = 'OK';
END IF;
END如上述添加指标的存储过程，首先判断该指标编码是否存在，如果存在则返回对应提醒信息，如果不存在则添加该指标；接着判断该指标是否关联到工资序列，如果关联到工资序列，则通过读取xml节点循环的方式获得对应的工资序列公式，把所有关联到工资序列的值添加到表tb_salary_level_formula中；再山洪判断该指标项是字符型还是数字型，如果是数值型，则在tb_salary_information表中添加该字段并设置默认值为0,同时设置对应的长度和精度，如果是字符型，则在tb_salary_information表中添加字符型字段；一切无误后返回OK到DAO层，如果有任何问题，则会进行回滚，并返回异常信息到对应的DAO层。
CREATE PROCEDURE `SP_FIELDINFO_MGT_DELETE`(IN v_code VARCHAR(20),
OUT v_message VARCHAR(1000))
BEGIN
DECLARE v_sql varchar(1000);
DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN
ROLLBACK;
SET v_message = '存储过程 SP_FIELDINFO_MGT_DELETE 发现异常';
END;
SET autocommit = 0;
START TRANSACTION;
DELETE FROM tb_field_info
WHERE code = v_code;
/**删除对应的工资序列里面的公式*/
DELETE FROM tb_salary_level_formula where fieldcode = v_code;
SET v_sql =
concat('ALTER TABLE bsm.tb_salary_information DROP ',
lower(v_code),
';');
SET @sql=v_sql;
PREPARE v_statement FROM @sql;
EXECUTE v_statement;
deallocate prepare v_statement;
COMMIT;
SET v_message = 'OK';
END如SP_FIELDINFO_MGT_DELETE存储过程代码所示，首先在tb_field_info中删除对应的指标，如果没有问题则删除tb_salary_level_formula表中对应的工资序列公式数据，再然后调用动态sql删除tb_salary_information对应的字段。一切无误后返回OK到DAO调用层，如果遇见异常信息，首先通过ROLLBACK进行回滚，其次返回异常信息到DAO层。
扫码关注不迷路！！！
郑州升龙商业广场B座25层
service@iqiqiqi.cn
联系电话：400-8049-474
联系电话：187-0363-0315
联系电话：199-3777-5101
