动感大挪移下载,义务兵优待金,地理学习方法
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也一并上传,看看你们需要哪个,需要那个没去下载使用吧,
如对本文有疑问，请在下面进行留言讨论，广大热心网友会与你互动！！ 点击进行留言回复
数据库SQL---数据库、基本表、视图、索引的定义、修改、删除
在 Azure CentOS VM 中配置 SQL Server 2019 AG - (上)
在 Azure CentOS VM 中配置 SQL Server 2019 AG - (下)
网友评论