安卓的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也一并上传,看看你们需要哪个,需要那个没去下载使用吧,