安卓的java连接sqlserver数据库执行存储过程的步骤

• 生活家

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

自己在代码中的实际使用代码情况

package yunup.com.shikong.jdbc;
import android.text.TextUtils;
import android.util.Log;
import org.json.JSONArray;
import org.json.JSONException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import yunup.com.shikong.jt.OnSPDataSingListener;
import yunup.com.shikong.jt.OnSPSelectListener;
import yunup.com.shikong.utils.SPUtils;
import yunup.com.shikong.utils.StringUtils;
import yunup.com.shikong.utils.ThreadUtils;
/**
* Created by user on 2017/8/18.
*/
public class AdbSPUtils {
private static final String TAG = "ppppp";
private static Connection conn;
private static String CONNECTIP;
private static String CONNECTPORT;
private static String CONNECTDBNAME;
private static String CONNECTUSERNAME;
private static String CONNECTUSERPSW;
private static Connection connectionUpData;
private static ResultSet selectResultSet;
private static CallableStatement selectSp;
/*
* 创建一个返回Connection的方法
*/
public static Connection getConnection() throws ClassNotFoundException, SQLException {
CONNECTIP = SPUtils.readSPString(StringUtils.CONNECTIP);
CONNECTPORT = SPUtils.readSPString(StringUtils.CONNECTPORT);
if (!TextUtils.isEmpty(CONNECTPORT)) {
CONNECTPORT = ":" + CONNECTPORT;
}
CONNECTDBNAME = SPUtils.readSPString(StringUtils.CONNECTDBNAME);
CONNECTUSERNAME = SPUtils.readSPString(StringUtils.CONNECTUSERNAME);
CONNECTUSERPSW = SPUtils.readSPString(StringUtils.CONNECTUSERPSW);
//            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Class.forName("net.sourceforge.jtds.jdbc.Driver");
System.out.println("数据库驱动加载成功");
//conn= DriverManager.getConnection("jdbc:sqlserver://localhost:1433;DatabaseName=java_conn_test","sa","123456");
conn = DriverManager.getConnection("jdbc:jtds:sqlserver://" + CONNECTIP + CONNECTPORT + "/" + CONNECTDBNAME + "", CONNECTUSERNAME, CONNECTUSERPSW);
if (conn == null) {
Log.i(TAG, "getConnection: 数据库连接失败");
Log.i(TAG, "-----------------------");
} else {
Log.i(TAG, "getConnection: 数据库连接成功");
Log.i(TAG, "-----------------------");
}
return conn;
}
/*
* 执行SELECT无参数存储过程,查询数据
*/
public static void GetProduseSelect(String spName, OnSPSelectListener onSPSelectListener) {
ThreadUtils.runOnBackThread(() -> {
try {
conn = getConnection();
if (conn == null) {
Log.i(TAG, "GetProduseSelect:执行无参数查询存储过程 链接数据库失败");
} else {
//                CallableStatement cs = conn.prepareCall("{call p_Select_User()}");
CallableStatement cs = conn.prepareCall(spName);
selectResultSet = cs.executeQuery();
JSONArray jsonArray = AdbSqlUtils.resultSetToJsonArry(selectResultSet);
ThreadUtils.runOnUiThread(() -> {
onSPSelectListener.OnResponse(jsonArray.toString());
});
//                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) {
ThreadUtils.runOnUiThread(() -> {
onSPSelectListener.onError(e);
});
} finally {
//无论什么情况都要关闭释放内存,要按照顺序关闭
if (selectResultSet != null) {
try {
selectResultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (selectSp != null) {
try {
selectSp.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
});
}
/*
*执行INSERT有参数存储过程,查询数据
*/
public static void GetProduseInsert(String sqlName, String [] spParameter, OnSPDataSingListener onSPDataSingListener) {
ThreadUtils.runOnBackThread(()->{
if (conn == null) {
System.out.println("数据库连接失败");
} else {
try {
//                CallableStatement ic = conn.prepareCall("{call p_Insert_User(?,?)}");
conn=getConnection();
conn.setAutoCommit(false);
CallableStatement ic = conn.prepareCall(sqlName);
if (spParameter != null) {
for (int i = 0; i < spParameter.length; i++) {
ic.setString(i + 1, spParameter[i]);
}
}
//                ic.setString(1, "heyangyi");
//                ic.setString(2, "123");
//                ic.execute();
int i = ic.executeUpdate();
ThreadUtils.runOnUiThread(()->{
onSPDataSingListener.OnResponse(i);
});
Log.i(TAG, "GetProduseInsert: 执行更新存储过程成功");
} catch (Exception ex) {
ThreadUtils.runOnUiThread(()->{
onSPDataSingListener.onError(ex);
});
//TODO: handle exception
Log.i(TAG, "GetProduseInsert: 执行更新存储过程失败");
//                System.out.println("添加失败");
}
}
});
}
/***
* 执行带输出参数的存储过程
*
* @param spName      储存过程名字
* @param spParameter 存储过程参数,这个参数要对应住,顺序不能随意更改,第一个穿进来的就是存储过程对应的第一个参数
*/
public static void GetProduseSelect(String spName, String[] spParameter, OnSPSelectListener onSPSelectListener) {
ThreadUtils.runOnBackThread(() -> {
try {
conn = getConnection();
if (conn == null) {
Log.i(TAG, "GetProduseSelect02: 数据库链接失败");
return;
} else {
//                execute yq_phone_fh_no 系统管理员
String sqlcall = "{call yq_phone_fh_ok(?)}";
//                CallableStatement sp=conn.prepareCall("{call p_Select_UserCount(?,?) }");
selectSp = conn.prepareCall(spName);
if (spParameter != null) {
for (int i = 0; i < spParameter.length; i++) {
selectSp.setString(i + 1, spParameter[i]);
}
}
selectSp.execute();
selectResultSet = selectSp.getResultSet();
JSONArray jsonArray = AdbSqlUtils.resultSetToJsonArry(selectResultSet);
ThreadUtils.runOnUiThread(() -> {
onSPSelectListener.OnResponse(jsonArray.toString());
});
}
} catch (JSONException | SQLException | ClassNotFoundException e) {
e.printStackTrace();
Log.i(TAG, "GetProduseSelect02: 执行带参查询存储过程失败");
ThreadUtils.runOnUiThread(() -> {
onSPSelectListener.onError(e);
});
} finally {
//无论什么情况都要关闭释放内存,要按照顺序关闭
if (selectResultSet != null) {
try {
selectResultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (selectSp != null) {
try {
selectSp.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
});
//        catch (Exception e) {
//            e.printStackTrace();
//            Log.i(TAG, "GetProduseSelect02: 执行带参查询存储过程失败,未知异常");
//        }
}
}

由于jdbc 连接 返回的对象都是将ResultSet对象,类似java中结果集,所以使用起来不太方便,老是,容易出错,造成代码累赘,这里给出一个将ResultSet转换成我们常用的json字符串类型的方法,这样我们就可以将请求到的数据ResultSet转换成json字符串,再用监听者模式回调到主线中中,再进行处理我们就熟悉多了.

/**
* 将ResultSet  转换成JsonArray
*
* @param rs
* @return
* @throws SQLException
* @throws JSONException
*/
public static JSONArray resultSetToJsonArry(ResultSet rs) throws SQLException, JSONException {
// json数组
JSONArray array = new JSONArray();
// 获取列数
ResultSetMetaData metaData = rs.getMetaData();
int columnCount = metaData.getColumnCount();
// 遍历ResultSet中的每条数据
while (rs.next()) {
JSONObject jsonObj = new JSONObject();
// 遍历每一列
for (int i = 1; i <= columnCount; i++) {
String columnName = metaData.getColumnLabel(i);
String value = rs.getString(columnName);
jsonObj.put(columnName, value);
}
array.put(jsonObj);
}
return array;
}

我在代码中使用的是jtds-1.2.jar 这个跟jdbc类似,哪里不一样还没看,先做一个记录,这个jar包我也上传到我的csdn了,存储起来以便于以后再次的使用,还有jdbc.jar也一并上传,看看你们需要哪个,需要那个没去下载使用吧,

赞 (0)
上一篇 2022年3月21日
下一篇 2022年3月21日

相关推荐