mysql jdbc 存储过程工具类

ProcedureUtil

package com.example.hutool;

import org.hamcrest.beans.PropertyUtil;

import java.io.*;
import java.sql.*;
import java.util.*;

/**
 * 存储过程相关工具
 * 说明:
 * 使用原生jdbc
 * 当前仅支持mysql
 * procedure.properties 中填写url,user,passwd,driver 这四个参数
 */
public class ProcedureUtil {

    private static String url = null;
    private static String user = null;
    private static String passwd = null;
    private static String driver = null;

    static {
        //初始化jdbc连接参数
        Properties properties = null;
        try {
            properties = new Properties();
            InputStream in = PropertyUtil.class.getClassLoader().getResourceAsStream("procedure.properties");
            assert in != null;
            BufferedReader bf = new BufferedReader(new InputStreamReader(in));
            properties.load(bf);
            in.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        assert properties != null;
        url = properties.getProperty("url");
        user = properties.getProperty("user");
        passwd = properties.getProperty("passwd");
        driver = properties.getProperty("driver");
    }

    /**
     * 运行
     * 查看已保存在数据库的存储过程
     *
     * @param procedureName 存储过程名称
     * @param inputParams   参数
     * @return
     */
    public static ProcedureExecuteResult call(String procedureName, List<Object> inputParams) {
        List<List<Map>> resultList = new ArrayList<>();
        Connection connection = null;
        CallableStatement cstmt = null;
        try {
            Class.forName(driver);
            connection = DriverManager.getConnection(url, user, passwd);
            //string增加单引号
            List<Object> sqlParams = new ArrayList<>();
            for (Object inputParam : inputParams) {
                if(inputParam instanceof String){
                    inputParam = "\'"+inputParam+"\'";
                    sqlParams.add(inputParam);
                }else{
                    sqlParams.add(inputParam);
                }
            }
            String sql = "CALL " + procedureName + "("+ parseListToStr(sqlParams) +");";
            cstmt = connection.prepareCall(sql);
            ResultSet resultSet = cstmt.executeQuery();
            boolean oprFlg = cstmt.execute();
            // 外循环获取结果集的个数
            while (oprFlg) {
                List<Map> result = new ArrayList<>();
                resultSet = cstmt.getResultSet();
                // 内循环获取每个结果集的记录
                while (resultSet.next()) {
                    ResultSetMetaData rsmd = resultSet.getMetaData();
                    int columnCount = rsmd.getColumnCount();
                    Map map = new HashMap();
                    for (int i = 0; i < columnCount; i++) {
                        map.put(rsmd.getColumnName(i + 1).toLowerCase(), resultSet.getObject(i + 1));
                    }
                    result.add(map);
                }
                resultList.add(result);
                oprFlg = cstmt.getMoreResults();
            }
        } catch (SQLException | ClassNotFoundException se) {
            se.printStackTrace();
        } finally {
            try {
                if (null != connection) {
                    connection.close();
                }
                if (null != cstmt) {
                    cstmt.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return new ProcedureExecuteResult(true, 0, null,null,resultList);
    }


    private static <T> String parseListToStr(List<T> list){
        StringBuffer sb = new StringBuffer();
        if(null != list) {
            for(int i=0;i<=list.size()-1;i++){
                if(i<list.size()-1){
                    sb.append(list.get(i) + ",");
                }else {
                    sb.append(list.get(i));
                }
            }
        }
        return sb.toString();
    }

    /**
     * 查询
     * 查看已保存在数据库的存储过程
     *
     * @param procedureName 存储过程名称
     * @return
     */
    public static ProcedureExecuteResult show(String procedureName) {
        String definition = "";
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            Class.forName(driver);
            connection = DriverManager.getConnection(url, user, passwd);
            preparedStatement = connection.prepareStatement("SHOW CREATE PROCEDURE " + procedureName + ";");
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                definition = resultSet.getString("Create Procedure");
            }
        }catch (SQLException se) {
            return new ProcedureExecuteResult(false, se.getErrorCode(), se.getMessage(),null,null);
        }catch (ClassNotFoundException cnfe){
            cnfe.printStackTrace();
        } finally {
            try {
                if (null != connection) {
                    connection.close();
                }
                if (null != preparedStatement) {
                    preparedStatement.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return new ProcedureExecuteResult(true, 0, null,definition,null);
    }

    /**
     * 创建存储过程
     *
     * @param sentence 创建存储过程的语句
     * @return SQL执行结果
     */
    public static ProcedureExecuteResult create(String sentence) {
        // TODO 存储过程语句的风险校验,如DROP,DELETE
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            Class.forName(driver);
            connection = DriverManager.getConnection(url, user, passwd);
            preparedStatement = connection.prepareStatement(sentence);
            preparedStatement.execute();
        }catch (SQLException se) {
            return new ProcedureExecuteResult(false, se.getErrorCode(), se.getMessage(),null,null);
        }catch (ClassNotFoundException cnfe){
            cnfe.printStackTrace();
        } finally {
            try {
                if (null != connection) {
                    connection.close();
                }
                if (null != preparedStatement) {
                    preparedStatement.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return new ProcedureExecuteResult(true, 0, null,null,null);
    }

    /**
     * 修改存储过程
     *
     * @param sentence 创建存储过程的语句
     * @return SQL执行结果
     */
    public static ProcedureExecuteResult update(String procedureName,String sentence) {
        // TODO 存储过程语句的风险校验,如DROP,DELETE
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        String sourceDefinition = null;
        try {
            Class.forName(driver);
            connection = DriverManager.getConnection(url, user, passwd);
            //暂存原来的语句
            preparedStatement = connection.prepareStatement("SHOW CREATE PROCEDURE " + procedureName + ";");
            ResultSet resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                sourceDefinition = resultSet.getString("Create Procedure");
            }
            //删除原存储过程
            preparedStatement = connection.prepareStatement("DROP PROCEDURE " + procedureName + ";");
            preparedStatement.execute();
            //保存新的存储过程
            preparedStatement = connection.prepareStatement(sentence);
            preparedStatement.execute();
        }catch (SQLException se) {
            //修改失败回退
            try {
                if (null != connection) {
                    preparedStatement = connection.prepareStatement(sourceDefinition);
                    preparedStatement.execute();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return new ProcedureExecuteResult(false, se.getErrorCode(), se.getMessage(),null,null);
        }catch (ClassNotFoundException cnfe){
            cnfe.printStackTrace();
        } finally {
            try {
                if (null != connection) {
                    connection.close();
                }
                if (null != preparedStatement) {
                    preparedStatement.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return new ProcedureExecuteResult(true, 0, null,null,null);
    }

    /**
     * 删除存储过程
     *
     * @param procedureName 存储过程名称
     * @return SQL执行结果
     */
    public static ProcedureExecuteResult drop(String procedureName) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            Class.forName(driver);
            connection = DriverManager.getConnection(url, user, passwd);
            preparedStatement = connection.prepareStatement("DROP PROCEDURE " + procedureName + ";");
            preparedStatement.execute();
        } catch (SQLException se) {
            return new ProcedureExecuteResult(false, se.getErrorCode(), se.getMessage(),null,null);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            try {
                if (null != connection) {
                    connection.close();
                }
                if (null != preparedStatement) {
                    preparedStatement.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return new ProcedureExecuteResult(true, 0, null,null,null);
    }


}

ProcedureExecuteResult

package com.example.hutool;

import java.util.List;
import java.util.Map;

class ProcedureExecuteResult {
    private Boolean success;
    private int errCode;
    private String reason;
    private String definition;
    private List<List<Map>> result;

    public ProcedureExecuteResult() {
    }

    public ProcedureExecuteResult(Boolean success, int errCode, String reason, String definition, List<List<Map>> result) {
        this.success = success;
        this.errCode = errCode;
        this.reason = reason;
        this.definition = definition;
        this.result = result;
    }

    public Boolean getSuccess() {
        return success;
    }

    public void setSuccess(Boolean success) {
        this.success = success;
    }

    public int getErrCode() {
        return errCode;
    }

    public void setErrCode(int errCode) {
        this.errCode = errCode;
    }

    public String getReason() {
        return reason;
    }

    public void setReason(String reason) {
        this.reason = reason;
    }

    public List<List<Map>> getResult() {
        return result;
    }

    public void setResult(List<List<Map>> result) {
        this.result = result;
    }

    public String getDefinition() {
        return definition;
    }

    public void setDefinition(String definition) {
        this.definition = definition;
    }
}

procedure.properties

url=jdbc:mysql://127.0.0.1:3306/ccgc?characterEncoding=utf8&useOldAliasMetadataBehavior=true&allowMultiQueries=true
user=user
passwd=passwd
driver=com.mysql.jdbc.Driver

测试用例

package com.example.hutool;

import com.alibaba.fastjson.JSONObject;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.stereotype.Component;
import org.springframework.test.context.junit4.SpringRunner;

import java.sql.Array;
import java.sql.SQLException;
import java.util.Arrays;

@SpringBootTest
@RunWith(SpringRunner.class)
public class Test {

    @org.junit.Test
    public void call(){
        ProcedureExecuteResult procedureExecuteResult = ProcedureUtil.call("test_ccgc3", Arrays.asList("张三"));
        System.out.println(JSONObject.toJSONString(procedureExecuteResult));
    }

    @org.junit.Test
    public void show(){
        ProcedureExecuteResult procedureExecuteResult = ProcedureUtil.show("test_ccgc3");
        System.out.println(JSONObject.toJSONString(procedureExecuteResult));
    }

    @org.junit.Test
    public void create(){
        ProcedureExecuteResult procedureExecuteResult = ProcedureUtil.create("CREATE DEFINER=`root`@`%` PROCEDURE `test_ccgc3`(IN `str` varchar(10))\n" +
                "BEGIN\n" +
                "  SELECT * FROM `user` WHERE `name` = str;\n" +
                "  SELECT * FROM `user` WHERE `name` = str;\n" +
                "  SELECT * FROM `user` WHERE `name` = str;\n" +
                "END");
        System.out.println(JSONObject.toJSONString(procedureExecuteResult));
    }

    @org.junit.Test
    public void update(){
        ProcedureExecuteResult procedureExecuteResult = ProcedureUtil.update("test_ccgc3","CREATE DEFINER=`root`@`%` PROCEDURE `test_ccgc3`(IN `str` varchar(10))\n" +
                "BEGIN\n" +
                "  SELECT * FROM `user` WHERE `name` = str;\n" +
                "  SELECT * FROM `user` WHERE `name` = str;\n" +
                "  SELECT * FROM `user` WHERE `name` = str;\n" +
                "  SELECT * FROM `user` WHERE `name` = str;\n" +
                "END");
        System.out.println(JSONObject.toJSONString(procedureExecuteResult));
    }

    @org.junit.Test
    public void delete(){
        ProcedureExecuteResult procedureExecuteResult = ProcedureUtil.drop("test_ccgc3");
        System.out.println(JSONObject.toJSONString(procedureExecuteResult));
    }

}

评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值