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

时间:2021-11-16 14:06:27

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

1.新建SQLSERVER数据库:java_conn_test

2.新建表:tb_User

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

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

4>做好准备工作之后新建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;
}

}

5>执行存储过程:

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("查询失败");
}
}
}
}

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