JAVA使用JDBC技术操作SqlServer数据库执行存储过程

深碍√TFBOYSˉ_ 2022-07-13 07:28 297阅读 0赞

JAVA使用JDBC技术操作SqlServer数据库执行存储过程：

1.新建SQLSERVER数据库：java\_conn\_test

2.新建表：tb\_User

![20161230163324056][]

3.分别新建三个存储过程：

1>带参数的新增用户存储过程：

CREATE PROCEDURE [dbo].[p_Insert_User]
    @name nvarchar(50),
    @UserPwd nvarchar(50)
    AS
    BEGIN
    	INSERT INTO tb_User VALUES(NEWID(),@name,@UserPwd)
    END

2>不带参数的查询用户信息存储过程：

CREATE PROCEDURE [dbo].[p_Select_User]
    AS
    BEGIN
    	SELECT * FROM tb_User
    END

3>带参数有输出参数的存储过程：

CREATE PROCEDURE [dbo].[p_Select_UserCount]
    @name nvarchar(50),
    @result int output
    AS
    BEGIN
    	SELECT @result= COUNT(0) FROM tb_User WHERE @name=UserName
    END

做好准备工作之后新建java项目，导入sqljdbc.jar

package com.Project_DataBase01;
    
    import java.sql.Connection;
    import java.sql.DriverManager;
    
    public class SelectQuery {
    	
    	private Connection conn;
    	
    	/*
    	 * 创建一个返回Connection的方法
    	 */
    	public Connection getConnection(){
    		try {
    			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
    			System.out.println("数据库驱动加载成功");
    			conn=DriverManager.getConnection("jdbc:sqlserver://localhost:1433;DatabaseName=java_conn_test","sa","123456");
    			if(conn==null){
    				System.out.println("数据库连接失败");
    				System.out.println("-----------------------");
    			}else {
    				System.out.println("数据库连接成功");
    				System.out.println("-----------------------");
    			}
    		} catch (Exception e) {
    			// TODO: handle exception
    			e.printStackTrace();
    		}
    		return conn;
    	}
    
    }

执行存储过程：

package com.Project_DataBase01;
    
    import java.sql.CallableStatement;
    import java.sql.Connection;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.Statement;
    import java.sql.Types;
    
    public class StartMain {
    	
    	private static Connection conn;
    
    	public static void main(String[] args) {
    		// TODO Auto-generated method stub
    	    
    		conn=new SelectQuery().getConnection();
    		
    		GetProduseInsert();
    		
    		GetProduseSelect02();
    		
    		GetProduseSelect();
    		
    	}
    
    	/*
    	 * 执行SELECT无参数存储过程,查询数据
    	 */
    	
    	public static void GetProduseSelect(){
    		if(conn==null){
    			System.out.println("链接数据库失败");
    		}else {
    			try {
    				CallableStatement cs=conn.prepareCall("{call p_Select_User()}");
    				ResultSet rs=cs.executeQuery();
    				while (rs.next()) {
    					String name=rs.getString("UserName");
    					String pwd=rs.getString("UserPwd");
    					String UserId=rs.getString("UserId");
    					System.out.println(name+"\t"+pwd+"\t"+UserId);
    				}
    				System.out.println("查询成功");
    				System.out.println("-----------------------");
    			} catch (Exception e) {
    				// TODO: handle exception
    				System.out.println("查询失败");
    				System.out.println("-----------------------");
    			}
    		}
    	}
    	
    	/*
    	 *执行INSERT有参数存储过程,查询数据 
    	 */
    	public static void GetProduseInsert(){
    		if(conn==null){
    			System.out.println("数据库连接失败");
    		}else {
    			try {
    				CallableStatement ic=conn.prepareCall("{call p_Insert_User(?,?)}");
    				ic.setString(1, "heyangyi");
    				ic.setString(2, "123");
    				ic.execute();
    			    System.out.println("添加成功");
    			 }
    			 catch (Exception ex) {
    				 //TODO: handle exception
    				 System.out.println("添加失败");
    			 }
    		}
    	}
    
    	/*
    	 * 执行带输出参数的存储过程
    	 */
    	public static void GetProduseSelect02(){
    		if(conn==null){
    			System.out.println("数据库链接失败");
    		}else {
    			try {
    				CallableStatement sp=conn.prepareCall("{call p_Select_UserCount(?,?) }");
    				sp.setString(1,"heyangyi");
    				sp.registerOutParameter(2, Types.INTEGER);
    				sp.execute();
    				System.out.println("查询成功:"+sp.getInt(2));
    			} catch (Exception e) {
    				// TODO: handle exception
    				System.out.println("查询失败");
    			}
    		}
    	}
    }

![20161230164055158][]

[20161230163324056]: /images/20220713/876a38492fb74af6bc4a1b8c7de3a1cb.png
[20161230164055158]: /images/20220713/6dffda177dc542b7b8ca149bf2629c09.png