本文共 3380 字,大约阅读时间需要 11 分钟。
使用sql语句定义一个存储过程挺简单的,参考这一篇博文https://blog.csdn.net/qq_44973159/article/details/91491985
create procedure ccgc as select * from t_student 而使用java并不 那么简单了,,,如下package java617存储过程;import java.sql.*;//定义一个不带参数的存储过程public class test { private static ResultSet rs=null; private static Connection dbConn=null; private static PreparedStatement stmt=null; public static void main(String[] args) { String driverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver"; String dbURL = "jdbc:sqlserver://localhost:1433 ;Databasename =studentDB;" +"integratedSecurity=true"; String sql ="create procedure ccgc" + " as select * from t_student" ; //在这里定义好存储过程 try{ Class.forName(driverName); dbConn = DriverManager.getConnection(dbURL); //连接url stmt =dbConn.prepareStatement(sql); //将存储过程写入到数据库 stmt.executeUpdate(); //对数据库的信息进行更新 System.out.println("连接完成"); } catch (Exception e){ System.out.println("连接未完成"); e.printStackTrace(); } }}
出现过的问题:在直接用连接sql语句中的 DriverManager.getConnection(dbURL); 在编译运行的过程中报错,空指针异常
在进sql server查看这个存储过程保存与否;定义存储过程后,怎么调用呢?用sql语句很简单,
直接exec+name即可,那用java呢?如下:package java617存储过程;//调用不带参数的存储过程import java.sql.*;public class testDemo { private static ResultSet rs=null; private static Connection dbConn=null; private static PreparedStatement stmt=null; //======对所有的值进行初始化,避免空指针异常====== public static void main(String[] args) { String driverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver"; String dbURL = "jdbc:sqlserver://localhost:1433 ;Databasename =studentDB;" +"integratedSecurity=true"; //String sql ="create procedure ccgc" + " as select * from t_student" ; String sql = "{call ccgc} "; // ==== 调用存储过程 ==== try{ Class.forName(driverName); dbConn = DriverManager.getConnection(dbURL); //stmt =dbConn.prepareStatement(sql); CallableStatement cstmt = dbConn.prepareCall(sql); //===调用sql语句(调用存储过程的语句) rs = cstmt.executeQuery(); // === 接收sql返回的内容 while (rs.next()) { System.out.print("学号"+rs.getString(1)); System.out.print("姓名"+rs.getString(2)); System.out.print("性别"+rs.getString(3)); System.out.print("出生日期"+rs.getString(4)); System.out.println("班级编号"+rs.getString(5)); //====== 遍历数据库中的数据并打印输出 ===== // === 在这输出语句可以用一条输出语句输出,单一出口原则=== } System.out.println("连接完成"); } catch (Exception e){ System.out.println("连接未完成"); e.printStackTrace(); } }}
package java617存储过程;import java.sql.*;//============定义一个带参数的存储过程===========public class test1 { private static ResultSet rs=null; private static Connection dbConn=null; private static PreparedStatement stmt=null; public static void main(String[] args) { String driverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver"; String dbURL = "jdbc:sqlserver://localhost:1433 ;Databasename =studentDB;" +"integratedSecurity=true"; String sql ="create proc ccgc3 @sno char(10) " + " as select cno,grade,sno from t_score where sno =@sno" ; try{ Class.forName(driverName); dbConn = DriverManager.getConnection(dbURL); stmt =dbConn.prepareStatement(sql); stmt.executeUpdate(); System.out.println("连接完成"); } catch (Exception e){ System.out.println("连接未完成"); e.printStackTrace(); } }}
我们只需要将不带参数的存储过程的代码,把定义存储过程的sql语句改成带参的语句就ok了;