1.定义一个存储过程(不带参数)
使用sql语句定义一个存储过程挺简单的,参考这一篇博文/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); //连接urlstmt =dbConn.prepareStatement(sql); //将存储过程写入到数据库stmt.executeUpdate();//对数据库的信息进行更新System.out.println("连接完成");}catch (Exception e){System.out.println("连接未完成");e.printStackTrace();}}}
出现过的问题:在直接用连接sql语句中的 DriverManager.getConnection(dbURL); 在编译运行的过程中报错,空指针异常
在进sql server查看这个存储过程保存与否;
2.调用一个存储过程(不带参数)
定义存储过程后,怎么调用呢?用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();}}}
3.定义一个存储过程(含参数)
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了;
4.调用存储过程(含参数)
如果觉得《JDBC 存储过程(使用java代码编写sql 存储过程)》对你有帮助,请点赞、收藏,并留下你的观点哦!