-
创建jdbc工具类
jdbc.properties信息:
DriverClasses = com.microsoft.sqlserver.jdbc.SQLServerDriver
url = jdbc:sqlserver://192.168.166.129:1433;DatabaseName=datadb
user = sa
password = 123456
charset = ISO
maxconn = 300
minconn = 50
maxusecount = 6000
maxidletime = 600
maxalivetime = 10
checktime = 3600
ecology.isgoveproj = 0
LOG_FORMAT = yyyy.MM.dd'-'hh:mm:ss
DEBUG_MODE = false
jdbc工具类:
package com.api.login.web;
import java.io.FileReader;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;
/**
* @ProjectName: HangDuProject
* @Package: com.api.ajk.impl
* @ClassName: JDBCUtils
* @Author: admin
* @Description: jdbc驱动类
* @Date: 2023/3/9 16:31
* @Version: 1.0
*/
public class JDBCUtils {
private static String url;
private static String user;
private static String passward;
private static String driver;
public String getJDBCUtils() {
return "url:"+this.url+" user:"+this.user+" passward:"+this.passward+" driver:"+this.driver;
}
/**
* 文件的读取,只需要读取一次即可拿到这些值.使用静态代码块
*/
static {
//读取资源文件,获取值。
try {
//1.创建Properties集合类.
Properties pro = new Properties();
//2.加载文件linux服务器:/usr/**********/WEB-INF/prop/jdbc.properties
pro.load(new FileReader("E:\\IDEA\\IdeaProjects\\jdbc\\jdbc\\src\\cn\\jdbc.properties"));
//3.获取数据,赋值
url = pro.getProperty("url");
user = pro.getProperty("user");
passward = pro.getProperty("password");
driver = pro.getProperty("DriverClasses");
//4.注册驱动
Class.forName(driver);
} catch (IOException | ClassNotFoundException e) {
e.printStackTrace();
}
}
/**
* 获取连接
* @return 连接对象
*/
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url,user,passward);
}
/**
* 释放资源
* @param stmt
* @param conn
*/
public static void close(Statement stmt, Connection conn) {
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 释放资源
* @param stmt
* @param conn
*/
public static void close(ResultSet rs, Statement stmt, Connection conn) {
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void close(PreparedStatement pstmt, Connection conn) {
if (pstmt != null) {
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
- 数据库帮助类<通用方法执行存储过程>
package com.api.login.web;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import java.beans.IntrospectionException;
import java.beans.Introspector;
import java.lang.reflect.InvocationTargetException;
import java.sql.*;
import java.util.*;
import org.apache.commons.beanutils.BeanUtils;
import java.beans.PropertyDescriptor;
public class DBHelper {
/**
* 执行存储过程返回JSONObject 第一行数据
* @param var1 执行存储过程
* @param var2 执行存储过程参数
* @return JSONArray
* @throws SQLException
*/
public JSONObject CallPro(String var1, List<Object> var2) throws SQLException {
Connection var3 = JDBCUtils.getConnection();
CallableStatement var4 = var3.prepareCall(var1);
JSONObject var5 = new JSONObject();
for (int i = 1; i < var2.size()+1; i++) {
var4.setString(i, (String) var2.get(i-1));//参数的输入值
}
ResultSet var6 = var4.executeQuery();
ResultSetMetaData var8 = var6.getMetaData(); //获取列集
if (var6.next()) {//获取一行数据
for (int i = 0; i < var8.getColumnCount(); i++) { //循环列
String var7 = var8.getColumnName(i+1);//通过序号获取列名,起始值为1
//rs.getString(rsMetaData.getColumnName(i+1));//通过列名获取值.如果列值为空,columnValue为null,不是字符型
var5.put(var7, var6.getString(var7));
}
}
JDBCUtils.close(var6,var4,var3);
return var5;
}
/**
* 执行存储过程返回泛型List,需要创建var3实体类
* @param var1 执行sql
* @param var2 执行sql参数
* @param var3 泛型对象类型
* @param <T> 泛型List
* @return
* @throws SQLException
* @throws InstantiationException
* @throws IllegalAccessException
* @throws IntrospectionException
* @throws InvocationTargetException
*/
public <T> List<T> CallPro1(String var1, List<Object> var2, Class var3) throws SQLException, InstantiationException, IllegalAccessException, IntrospectionException, InvocationTargetException {
Connection var4 = JDBCUtils.getConnection();
CallableStatement var5 = var4.prepareCall(var1);
List<T> var6 = new ArrayList<>();
for (int i = 1; i < var2.size()+1; i++) {
var5.setString(i, (String) var2.get(i-1));//参数的输入值
}
ResultSet var7 = var5.executeQuery();
while (var7.next()) {
T var8 = (T) var3.newInstance();
for (PropertyDescriptor var9 : Introspector.getBeanInfo(var3).getPropertyDescriptors()) {
if(!var9.getName().equals("class")) {
BeanUtils.copyProperty(var8, var9.getName(), var7.getString(var9.getName()));
}
}
var6.add(var8);
}
JDBCUtils.close(var7,var5,var4);
return var6;
}
/**
* 执行存储过程返回JSONArray,不需要创建实体类
* @param var1 执行存储过程
* @param var2 执行存储过程参数
* @return JSONArray
* @throws SQLException
*/
public JSONArray CallPro1(String var1, List<Object> var2) throws SQLException {
Connection var3 = JDBCUtils.getConnection();
CallableStatement var4 = var3.prepareCall(var1);
JSONArray var5 = new JSONArray();
for (int i = 1; i < var2.size()+1; i++) {
var4.setString(i, (String) var2.get(i-1));//参数的输入值
}
ResultSet var6 = var4.executeQuery();
ResultSetMetaData var8 = var6.getMetaData(); //获取列集
while (var6.next()) {
JSONObject var9 = new JSONObject();
for (int i = 0; i < var8.getColumnCount(); i++) { //循环列
String var7 = var8.getColumnName(i+1);//通过序号获取列名,起始值为1
//rs.getString(rsMetaData.getColumnName(i+1));//通过列名获取值.如果列值为空,columnValue为null,不是字符型
var9.put(var7, var6.getString(var7));
}
var5.add(var9);
}
JDBCUtils.close(var6,var4,var3);
return var5;
}
/**
* 执行存储过程,返回多个结果集 需要创建多个实体类,根据可选参数入参,需要和存储过程中的查询顺序对应
* @param var1 执行存储过程
* @param var2 执行存储过程参数
* @param var3 多泛型对象类型
* @param <T> 返回泛型
* @return ArrayList<List<T>>
* @throws SQLException
* @throws InstantiationException
* @throws IllegalAccessException
* @throws IntrospectionException
* @throws InvocationTargetException
*/
public <T> ArrayList<List<T>> CallPro2(String var1, List<Object> var2 , Class ...var3) throws SQLException, InstantiationException, IllegalAccessException, IntrospectionException, InvocationTargetException {
Connection var4 = null;
CallableStatement var5 = null;
ResultSet var6 = null;
List<T> var11 = new ArrayList<>();
ArrayList<List<T>> var9 = new ArrayList<>();
var4 = JDBCUtils.getConnection();
var5 = var4.prepareCall(var1);
for (int i = 1; i < var2.size()+1; i++) {
var5.setString(i, (String) var2.get(i-1));//参数的输入值
}
int i = 1;
var6=var5.executeQuery();
while (var6.next()) {
//类型的声明(可声明一个不确定的类型)
T var7 = (T) var3[i-1].newInstance();
for (PropertyDescriptor var8 : Introspector.getBeanInfo(var7.getClass()).getPropertyDescriptors()) {
if(!var8.getName().equals("class")) {
//writeLog("获取第" + i + "个结果集" + "var8.getName():" + var8.getName());
BeanUtils.copyProperty(var7, var8.getName(), var6.getString(var8.getName()));
}
}
var11.add(var7);
}
var9.add(var11);
while (var5.getMoreResults()){
i++;
var6 = var5.getResultSet();
var11 = new ArrayList<>();
while (var6.next()){
T var10 = (T) var3[i-1].newInstance();
for (PropertyDescriptor var8 : Introspector.getBeanInfo(var10.getClass()).getPropertyDescriptors()) {
if(!var8.getName().equals("class")) {
//writeLog("获取第" + i + "个结果集" + "var8.getName():" + var8.getName());
BeanUtils.copyProperty(var10, var8.getName(), var6.getString(var8.getName()));
}
}
var11.add(var10);
}
var9.add(var11);
}
JDBCUtils.close(var6,var5,var4);
return var9;
}
/**
* 执行存储过程,返回多个结果集
* @param var1 执行存储过程
* @param var2 执行存储过程参数
* @return 多个结果集 JSONObject key为表名称 value为表数据
* @throws SQLException
*/
public JSONObject CallPro2(String var1, List<Object> var2) throws SQLException {
Connection var3 = null ;
CallableStatement var4 = null;
//PreparedStatement var4 = null;
ResultSetMetaData var5 = null;
ResultSet var6 = null;
JSONObject var7 = null;
JSONArray var8 = null;
JSONObject var9 = new JSONObject();
var3 = JDBCUtils.getConnection();
//var4 = var3.prepareCall(var1);
var4 = var3.prepareCall(var1);
for (int i = 1; i < var2.size()+1; i++) {
var4.setString(i, (String) var2.get(i-1));//参数的输入值
}
var6=var4.executeQuery();
var5 = var6.getMetaData(); //获取列集
var8 = new JSONArray();
while (var6.next()) {
var7 = new JSONObject();
for (int i = 0; i < var5.getColumnCount(); i++) { //循环列
String columnName = var5.getColumnName(i+1);//通过序号获取列名,起始值为1
//writeLog("rs.getString():"+rs.getString(resultSetMetaData.getColumnName(i+1)));//通过列名获取值.如果列值为空,columnValue为null,不是字符型
var7.put(columnName, var6.getString(columnName));
}
var8.add(var7);
}
var9.put("table"+1,var8);
int j = 1;
while (var4.getMoreResults()){
j++;
var8 = new JSONArray();
var6 = var4.getResultSet();
var5 = var6.getMetaData(); //获取列集
while (var6.next()){
var7 = new JSONObject();
for (int i = 0; i < var5.getColumnCount(); i++) { //循环列
String columnName = var5.getColumnName(i+1);//通过序号获取列名,起始值为1
//writeLog("rs.getString():"+var6.getString(var5.getColumnName(i+1)));//通过列名获取值.如果列值为空,columnValue为null,不是字符型
var7.put(columnName, var6.getString(columnName));
}
var8.add(var7);
}
var9.put("table"+j,var8);
}
JDBCUtils.close(var6,var4,var3);
return var9;
}
public JSONArray CallPro3(String var1, List<Object> var2) throws SQLException {
Connection conn= JDBCUtils.getConnection();
PreparedStatement pstmt = conn.prepareStatement(var1);
for (int i = 1; i < var2.size()+1; i++) {
pstmt.setString(i, (String) var2.get(i-1));//参数的输入值
}
ResultSet rs = pstmt.executeQuery();
ResultSetMetaData var5 = pstmt.getMetaData(); //获取列集
JSONArray jsonArray = new JSONArray();
while (rs.next()) {
JSONObject jsonObject = new JSONObject();
for (int i = 0; i < var5.getColumnCount(); i++) { //循环列
String columnName = var5.getColumnName(i+1);//通过序号获取列名,起始值为1
//writeLog("rs.getString():"+var6.getString(var5.getColumnName(i+1)));//通过列名获取值.如果列值为空,columnValue为null,不是字符型
jsonObject.put(columnName, rs.getString(columnName));
}
jsonArray.add(jsonObject);
}
return jsonArray;
}
}
- 测试调用
@POST
@Path("/getPro2")
@Produces(MediaType.APPLICATION_JSON)
public JSONObject getPro() throws SQLException, IntrospectionException, InvocationTargetException, InstantiationException, IllegalAccessException {
String var1 = "{ call pro_Test(?,?,?) }";
List<Object> list = new ArrayList<>();
list.add("2");
list.add("");
list.add("");
DBHelper dbHelper = new DBHelper();
JSONObject jsonObject = dbHelper.CallPro(var1,list);
return jsonObject;
}
@POST
@Path("/getPro1")
@Produces(MediaType.APPLICATION_JSON)
public JSONArray getPro1() throws SQLException, IntrospectionException, InvocationTargetException, InstantiationException, IllegalAccessException {
String var1 = "{ call pro_Test(?,?,?) }";
List<Object> list = new ArrayList<>();
list.add("2");
list.add("");
list.add("");
DBHelper dbHelper = new DBHelper();
List<UserInfos> userInfosList = dbHelper.CallPro1(var1,list,UserInfos.class);
for (int i = 0; i < userInfosList.size(); i++) {
writeLog("userInfosList.size():"+userInfosList.get(i).toString());
}
return JSONArray.parseArray(JSON.toJSONString(userInfosList));
}
@POST
@Path("/getPro2")
@Produces(MediaType.APPLICATION_JSON)
public JSONArray getPro2() throws SQLException, IntrospectionException, InvocationTargetException, InstantiationException, IllegalAccessException {
String var1 = "{ call pro_Test(?,?,?) }";
List<Object> list = new ArrayList<>();
list.add("2");
list.add("");
list.add("");
DBHelper dbHelper = new DBHelper();
JSONArray jsonArray = dbHelper.CallPro1(var1,list);
return jsonArray;
}
@POST
@Path("/getPro3")
@Produces(MediaType.APPLICATION_JSON)
public JSONObject getPro3() throws SQLException, IntrospectionException, InvocationTargetException, InstantiationException, IllegalAccessException {
writeLog(">>>>>>>>>>>>>>.");
String var1 = "{ call pro_Test2(?,?) }";
List<Object> list = new ArrayList<>();
list.add("3");
list.add("2");
DBHelper dbHelper = new DBHelper();
ArrayList<List<Object>> userInfosList = dbHelper.CallPro2(var1,list,UserInfos.class,UserInfos2.class);
JSONObject jsonObject = new JSONObject();
for (int i = 0; i < userInfosList.size(); i++) {
for (int j = 0; j <userInfosList.get(i).size() ; j++) {
writeLog("userInfosList.get(i).size():"+userInfosList.get(i).get(j).toString());
}
writeLog(JSON.toJSONString(userInfosList.get(i)));
jsonObject.put("table"+i+1,JSON.toJSONString(userInfosList.get(i)));
}
return jsonObject;
}
@POST
@Path("/getPro4")
@Produces(MediaType.APPLICATION_JSON)
public JSONObject getPro4() throws SQLException {
writeLog(">>>>>>>>>>>>>>.");
String var1 = "{ call pro_Test2(?,?) }";
List<Object> list = new ArrayList<>();
list.add("3");
list.add("2");
for (int i = 0; i < list.size(); i++) {
writeLog(">>>>>>>>>"+i+">>>>>>>>>:"+list.get(i).toString());
}
DBHelper dbHelper = new DBHelper();
JSONObject userInfosList = dbHelper.CallPro2(var1,list);
writeLog("jsonObject: "+userInfosList.toJSONString());
return userInfosList;
}
@POST
@Path("/getCommonDataTablePro")
@Produces(MediaType.APPLICATION_JSON)
public JSONObject getCommonDataTablePro(@Context HttpServletRequest request) throws SQLException {
DBHelper var1 = new DBHelper();//数据库执行帮助类
JSONObject var2 = new JSONObject();//执行查询1返回的JSONObject
List<Object> var3 = new ArrayList();//执行查询1的入参
String var4 = "select * from uf_jcpro where id = ? ";//执行存储过程查询1
JSONObject var5 = new JSONObject();//整个方法返回的JSONObject
String var6 = "";//执行存储过程查询1
List<Object> var7 = new ArrayList();//执行查询2的入参
var3.add(Util.null2String(request.getParameter("proId")));
var2 = var1.GetModel(var4,var3);
var6 = "{ call "+var2.get("proName")+"(";
String [] var8 = Util.null2String(request.getParameter("var2")).split(",");
for (String s : var8) {
var7.add(s);
var6 += "?,";
}
var6 = var6.substring(0, var6.lastIndexOf(","));
var6+=") }";
var5 = var1.CallPro2(var6,var7);
DocDownloadCheckUtil.getDownloadfileidstr("");
return var5;
}
存储过程1
create PROCEDURE [dbo].[pro_updateHrmResourceByUserId]
@UID VARCHAR(50),
@mobile VARCHAR(50),
@email VARCHAR(50),
@regresidentplace VARCHAR(50),
@residentplace VARCHAR(50),
@policy VARCHAR(50),
@educationlevel VARCHAR(50),
@field14 VARCHAR(50),
@field15 VARCHAR(50),
@certificatenum VARCHAR(50)
as
begin
set nocount on
declare @statement_list INT;
--update hrmresource
--set email='156@qq.com'
--where mobile=1356878928
select
mobile,
email,
regresidentplace,
residentplace,
policy,
educationlevel
from hrmresource where id=7
set nocount off
end
- 存储过程2
create procedure [dbo].[pro_Test2] @id1 NVARCHAR(50), @id2 NVARCHAR(50) as begin set nocount on --exec [pro_Test] @id1,'','' insert into mytest_log (time,proName,var1,var2) values (GETDATE(),'[pro_Test2]',@id1,@id2) select id ,name ,age from mytest where id > @id1 select id ,name from mytest where id > @id2 set nocount off end --select*from mytest_log where var1='1' --exec [pro_Test2] '2','3' -- truncate table mytest_log
- 存储过程3
create procedure [dbo].[pro_Test]
@id NVARCHAR(50),
@name NVARCHAR(50),
@age NVARCHAR(50)
as
begin
set nocount on
INSERT INTO mytest_log (time,proName,var1,var2,var3) values (GETDATE(),'[pro_Test]',@id,@name,@age)
select * from mytest where id > @id
set nocount off
end
--select * from uf_jcpro where id = 2
- 存储过程4
create procedure [dbo].[pro_Test3] @id NVARCHAR(50), @name NVARCHAR(50), @age NVARCHAR(50) as begin INSERT INTO mytest_log (time,proName) values (GETDATE(),'[pro_Test3]') select * from mytest where name = @name end
注意:存储过程中如果存在 插入、修改和删除会执行失败
需要在begin和end间加入 set nocount on和 set nocount off,忽略受影响行数