首页 > 代码库 > JDBC之存储过程

JDBC之存储过程

2024-08-14 16:41:59   221人阅读

存储过程的语法创建就不说了，这里这篇博客 就挺详细了http://blog.sina.com.cn/s/blog_52d20fbf0100ofd5.html。

1. Java代码调用没有参数的存错过程

　　首显示创建一个个存储过程：

DELIMITER $$USE `jdbcdemo`$$DROP PROCEDURE IF EXISTS `selectAll`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `selectAll`()BEGINSELECT * FROM boy; 得到所有的数据    END$$DELIMITER ;

 其次在代码中调用这个存储过程，当然首先还是获得数据库的连接。 获取连接在上一片博客中http://www.cnblogs.com/xlurenjia/p/5901983.html。

Java 中的代码

public List<Boy> querayProcedure(){        List<Boy> list = new ArrayList<>();        Connection conn = DBUtil.getConnection();        try {            CallableStatement statement = conn.prepareCall("call selectAll()"); 调用存储过程            statement.execute();            ResultSet rs = statement.getResultSet();  // 获得结果集，其余操作就跟平常的操作都一样了            while (rs.next()) {                String name = rs.getString("user_name");                Boy gd = new Boy();                gd.setUser_name(name);                list.add(gd);            }        } catch (SQLException e) {            // TODO Auto-generated catch block            e.printStackTrace();        }                return list;    }

2. 有带IN参数的存储过程

DELIMITER $$USE `jdbcdemo`$$DROP PROCEDURE IF EXISTS `select_filter`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `select_filter`(IN sp_name VARCHAR(20))BEGIN    IF sp_name IS NULL OR sp_name = ‘‘ THEN      SELECT * FROM boy;        ELSE      IF LENGTH(sp_name) = 11 AND SUBSTRING(sp_name, 1, 1)=1 THEN        SELECT * FROM boy WHERE mobile = sp_name;      ELSE        SELECT * FROM boy WHERE user_name LIKE CONCAT(‘%‘, sp_name, ‘%‘);      END IF;    END IF;    END$$DELIMITER ;

Java中的代码

public List<Boy> querayProcedureFilter(String sp_name){        List<Boy> list = new ArrayList<>();        Connection conn = DBUtil.getConnection();        try {            CallableStatement statement = conn.prepareCall("call select_filter(?)");            statement.setString(1, sp_name); //将参数穿进去            statement.execute();            ResultSet rs = statement.getResultSet();            while (rs.next()) {                String name = rs.getString("user_name");                Boy gd = new Boy();                gd.setUser_name(name);                list.add(gd);            }        } catch (SQLException e) {            // TODO Auto-generated catch block            e.printStackTrace();        }                return list;    }

3. 带有OUT的存储过程

DELIMITER $$

USE `jdbcdemo`$$

DROP PROCEDURE IF EXISTS `select_count`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `select_count`( OUT sp_count INT)
BEGIN
SELECT COUNT(1)INTO sp_count FROM boy;
END$$

DELIMITER ;

Java中代码

public int querayCountByProceureOut(){            int count = 0;        try {            Connection conn = DBUtil.getConnection();            CallableStatement cs = conn.prepareCall("call select_count(?)");            cs.registerOutParameter(1, Types.INTEGER); // 注册你要获得的结果，执行之后获得结果            cs.execute();            count = cs.getInt(1);        } catch (SQLException e) {            // TODO Auto-generated catch block            e.printStackTrace();        }                return count;    }

 

JDBC之存储过程


联系
我们