1.定义一个存储过程(不带参数)
使用sql语句定义一个存储过程挺简单的,参考这一篇博文https://blog.youkuaiyun.com/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查看这个存储过程保存与否;
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了;