Java 数据库访问类(Oracle数据库)

分类:Java     发布:2019-08-09     来源:本站     浏览:1264 次

//--------------------------------------------------------------------------------
// 文件描述:JavaOracle数据库访问辅助类
// 文件作者:全体开发人员
// 特别说明:需下载ojdbc6.jar组件,下载地址:http://file.qingshanboke.com/File/Preview/cc24f9fcd07346ce9fc098aacbec0a0a
// 创建日期:2020-02-22
//--------------------------------------------------------------------------------

package com.rc.common;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

import javax.sql.RowSet;
import javax.sql.rowset.CachedRowSet;
import javax.sql.rowset.RowSetFactory;
import javax.sql.rowset.RowSetProvider;

import org.json.JSONException;
import org.json.JSONObject;

public final class OracleHelper {
	private String _connection;
	private String _userName;
	private String _password;

	/**
	 * 构造函数,传入数据库地址和账户信息
	 * 
	 * @param connection 如:jdbc:oracle:thin:@192.168.100.22:1521:orcl
	 * @param userName   如:SCHONLINESER
	 * @param password   如:SCHONLINESER
	 */
	public OracleHelper(String connection, String userName, String password) {
		_connection = connection;
		_userName = userName;
		_password = password;
	}

	/**
	 * 取得数据库连接
	 */
	private Connection getConnection() {
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			java.sql.Connection conn = DriverManager.getConnection(_connection, _userName, _password);
			return conn;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}

	/**
	 * 关闭数据库连接
	 */
	private void close(Connection conn, PreparedStatement pstm, ResultSet rs) {
		try {
			if (conn != null) {
				conn.close();
			}
			if (pstm != null) {
				pstm.close();
			}
			if (rs != null) {
				rs.close();
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		}
	}

	/**
	 * 执行单个数据库操作 Insert,Update,Delete
	 * 
	 * @return 成功执行的记录数
	 */
	public Integer Update(String sql, String[] params) {
		Connection conn = null;
		PreparedStatement pstm = null;

		try {
			conn = getConnection();
			pstm = conn.prepareStatement(sql);
			if (params != null) {
				for (int i = 0; i < params.length; i++) {
					pstm.setString(i + 1, params[i]);
				}
			}
			return pstm.executeUpdate();

		} catch (Exception e) {
			e.printStackTrace();
			return -1;
		} finally {
			close(conn, pstm, null);
		}
	}

	/**
	 * 执行多个数据库操作,包含事务处理功能
	 * 
	 * @return 如果事务执行成功返回1,如果事务执行不成功返回0
	 */
	public Integer Update(String[] sqls, String[][] params) {
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = getConnection();
			// 禁止自动提交事务
			conn.setAutoCommit(false);
			for (int i = 0; i < sqls.length; i++) {
				pstm = conn.prepareStatement(sqls[i]);
				if (params != null) {
					for (int j = 0; j < params[i].length; j++) {
						pstm.setString(j + 1, params[i][j]);
					}
				}
				pstm.executeUpdate();
			}
			conn.commit();
			return 1;
		} catch (Exception e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (Exception e2) {
				e2.printStackTrace();
			}
			return 0;
		} finally {
			close(conn, pstm, null);
		}
	}

	/**
	 * 执行SQL语句,返回JSON结果(不带参数)
	 */
	public String QueryJson(String sql) {
		Connection conn = getConnection();
		Statement stmt;
		try {
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			String json = resultSetToJson(rs);
			rs.close();
			stmt.close();
			conn.close();
			return json;
		} catch (SQLException e) {
			e.printStackTrace();
			return e.getMessage();
		}
	}

	/**
	 * 执行SQL语句,返回JSON结果(带参数)
	 */
	public String QueryJson(String sql, String[] params) {
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = getConnection();
			pstm = conn.prepareStatement(sql);
			if (params != null) {
				for (int i = 0; i < params.length; i++) {
					pstm.setString(i + 1, params[i]);
				}
			}
			ResultSet rs = pstm.executeQuery();
			String json = resultSetToJson(rs);
			return json;
		} catch (Exception e) {
			e.printStackTrace();
			return e.toString();
		} finally {
			close(conn, pstm, null);
		}
	}

	public Object ExecuteScalar(String sql) {
		Object value = null;
		try {
			RowSet rs = Query(sql);
			if (rs != null) {
				rs.next();
				value = rs.getObject(1);
			}
			return value;
		} catch (SQLException e) {
			e.printStackTrace();
			return e.getMessage();
		}
	}

	public Object ExecuteScalar(String sql, String[] params) {
		Object value = null;
		try {
			RowSet rs = Query(sql, params);
			if (rs != null) {
				rs.next();
				value = rs.getObject(1);
				return value;
			}
		} catch (Exception e) {
			return value;
		}
		return value;
	}

	public RowSet Query(String sql) {
		Connection conn = getConnection();
		Statement stmt;
		try {
			stmt = conn.createStatement();

			ResultSet rs = stmt.executeQuery(sql);
			RowSetFactory factory = RowSetProvider.newFactory();
			CachedRowSet cachedRs = factory.createCachedRowSet();
			cachedRs.populate(rs);
			rs.close();
			stmt.close();
			conn.close();
			return cachedRs;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	public RowSet Query(String sql, String[] params) {
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = getConnection();
			pstm = conn.prepareStatement(sql);
			if (params != null) {
				for (int i = 0; i < params.length; i++) {
					pstm.setString(i + 1, params[i]);
				}
			}
			ResultSet rs = pstm.executeQuery();
			RowSetFactory factory = RowSetProvider.newFactory();
			CachedRowSet cachedRs = factory.createCachedRowSet();
			// 使用ResultSet装填RowSet
			cachedRs.populate(rs);
			rs.close();
			pstm.close();
			conn.close();
			return cachedRs;

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(conn, pstm, null);
		}
		return null;
	}

	/**
	 * 执行需要分页的数据库查询操作
	 * 
	 * @return 查询结果的离线RowSet
	 */
	public RowSet Query(String sql, String[] params, Integer pageIndex, Integer pageSize) {
		Connection conn = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		CachedRowSet crs = null;

		try {
			conn = getConnection();
			pstm = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
			if (params != null) {
				for (int i = 0; i < params.length; i++) {
					pstm.setString(i + 1, params[i]);
				}
			}
			rs = pstm.executeQuery();

			// 创建CacheRowSet
			RowSetFactory factory = RowSetProvider.newFactory();
			crs = factory.createCachedRowSet();
			crs.setPageSize(pageSize);
			crs.populate(rs, (pageIndex - 1) * pageSize + 1);

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(conn, pstm, rs);
		}

		return crs;
	}

	/**
	 * 执行查询的存储过程"{ call addUser(?,?,?,?) }"
	 * 
	 * @return 返回查询结果的RowSet集合
	 */
	public RowSet executeStoredProcedure(String dsName, String sp_name, String[] params) {
		Connection conn = null;
		CallableStatement cstm = null;
		ResultSet rs = null;
		CachedRowSet crs = null;

		try {
			conn = getConnection();
			cstm = conn.prepareCall(sp_name);
			if (params != null) {
				for (int i = 0; i < params.length; i++) {
					cstm.setString(i + 1, params[i]);
				}
			}
			rs = cstm.executeQuery();

			// 创建CacheRowSet
			RowSetFactory factory = RowSetProvider.newFactory();
			crs = factory.createCachedRowSet();
			crs.populate(rs);

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(conn, cstm, rs);
		}

		return crs;
	}

	public void setConnection(String connection) {
		this._connection = connection;
	}

	public String getUserName() {
		return _userName;
	}

	public void setUserName(String userName) {
		this._userName = userName;
	}

	public String getPassword() {
		return _password;
	}

	public void setPassword(String password) {
		this._password = password;
	}

	/**
	 * 辅助方法-将ResultSet转换为JSON字符串
	 */
	public String resultSetToJson(ResultSet rs) {
		org.json.JSONArray array = new org.json.JSONArray();
		ResultSetMetaData metaData;
		try {
			metaData = rs.getMetaData();

			int columnCount = metaData.getColumnCount();

			while (rs.next()) {
				JSONObject jsonObj = new JSONObject();

				for (int i = 1; i <= columnCount; i++) {
					String columnName = metaData.getColumnLabel(i);
					String value = rs.getString(columnName);
					try {
						jsonObj.put(columnName, value);
					} catch (JSONException e) {
						e.printStackTrace();
					}
				}
				array.put(jsonObj);
			}
			String json = array.toString();
			return json;
		} catch (SQLException e) {
			e.printStackTrace();

		}
		return "";
	}
}


//调用代码
import java.io.File;
import java.io.FileOutputStream;

import javax.sql.RowSet;

public class Main {
    public static void main(String[] args) throws Exception {
        String server = "jdbc:oracle:thin:@192.168.100.22:1521:orcl";
        String userName = "SCHONLINESER";
        String userPwd = "SCHONLINESER";
        OracleHelper db = new OracleHelper(server, userName, userPwd);

        // 1.查询集合
        RowSet rs = db.Query("select * from (select * from  docinfo order by APPLYTIME desc) where rownum<10");
        while (rs.next()) {
            String pid = rs.getString("TRANSPID");
            String name = rs.getString("TRANSPNAME");
            String unit = rs.getString("DOCOLDUNIT");
            System.out.println(name + ' ' + pid + ' ' + unit + ' ' + name);
        }

        // 2.查询单个值
        String val = db.ExecuteScalar("select transpName from docinfo where pid='511304199412130017'").toString();
        System.out.println(val);

        // 2.查询单个值(参数化)
        String val2 = db
                .ExecuteScalar("select transpName from docinfo where pid=?", new String[] { "511304199412130017" })
                .toString();
        System.out.println(val2);
        Log(val2);

        // 3.查询json
        String json = db.QueryJson("select * from (select * from  docinfo order by APPLYTIME desc) where rownum<3");
        System.out.println(json);
    }

    // 写入文本
    public static void Log(String s) {
        File file = new File(System.getProperty("user.dir") + "\\log.txt");
        FileOutputStream outputStream;
        try {
            outputStream = new FileOutputStream(file);
            outputStream.write(s.getBytes());
            outputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

    }
}





        

如果觉得文章对您有帮助,您可以对我进行打赏 ¥1.42 元(金额随机^_^,每次刷新金额不同)。

请使用支付宝扫码支付

留言评论

*称  呼:
*联系方式: 方便与您取得联系,推荐使用邮箱。
*内  容:

已有评论

暂无数据

上一篇:C# 数据库访问类(MS SQL Server)

下一篇:Java 数据库访问类(SQL Server数据库)