Oracle-JDBC SQLHelper

最新推荐文章于 2023-05-01 09:58:07 发布

光何

最新推荐文章于 2023-05-01 09:58:07 发布

阅读量675

点赞数

CC 4.0 BY-SA版权

分类专栏： I数据库文章标签： jdbc

本文链接：https://blog.youkuaiyun.com/GuangHEultimate/article/details/52647947

I数据库专栏收录该内容

8 篇文章

订阅专栏

本文介绍了一个Java数据库操作实用类库，包括连接资源管理、配置参数读取、执行DQL查询、DML更新及批处理等核心功能，并提供了执行存储过程的方法。该类库通过配置文件读取数据库连接信息，简化了常见的数据库操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1,SQLHelper.java

package dao;

import java.io.InputStream;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;
/**
 *
 * @author guanghe
 */
public class SQLHelper
{
    //定义连接资源
    private static Connection ct = null;
    private static PreparedStatement ps = null;
    private static CallableStatement cs = null;
    private static ResultSet rs = null;

    //定义配置参数
    private static String driver = null;
    private static String url = null;
    private static String username = null;
    private static String password = null;

    private static Properties pp = null;
    private static InputStream is = null;

    //读取配置参数，加载驱动
    static
    {
        try
        {
            pp = new Properties();
            is = SqlHelper.class.getClassLoader().getResourceAsStream("./newsmgr/dao/db.properties");
            pp.load(is);
            driver = pp.getProperty("driver");
            url = pp.getProperty("url");
            username = pp.getProperty("username");
            password = pp.getProperty("password");
            Class.forName(driver);
        }
        catch (Exception e)
        {
            e.printStackTrace();
            System.exit(0);
        }
        finally
        {
            try
            {
                is.close();
            }
            catch (Exception e)
            {
                e.printStackTrace();
            }
            is = null;
        }
    }

    //获取连接
    public static Connection getConnection()
    {
        try
        {
            ct = DriverManager.getConnection(url, username, password);
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
        return ct;
    }
    
    public static PreparedStatement getPs()
    {
        return ps;
    }
    
    public static ResultSet getRs()
    {
        return rs;
    }

    //执行DQL查询
    public static ResultSet executeQuery(String sql, String[] parameters)
    {
        try
        {
            ct = getConnection();
            ps = ct.prepareStatement(sql);
            if (parameters != null)
            {
                for (int i = 0; i < parameters.length; i++)
                {
                    ps.setString(i + 1, parameters[i]);
                }
            }
            rs = ps.executeQuery();
        }
        catch (Exception e)
        {
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
        }
        return rs;
    }

    //执行DML更新
    public static int executeUpdate(String sql, String[] parameters)
    {
        try
        {
            ct = getConnection();
            ps = ct.prepareStatement(sql);
            if (parameters != null)
            {
                for (int i = 0; i < parameters.length; i++)
                {
                    ps.setString(i + 1, parameters[i]);
                }
            }
            return ps.executeUpdate();
        }
        catch (Exception e)
        {
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
        }
        finally
        {
            close(ct, ps, rs);
        }
    }

    //执行DML批处理
    public static int executeUpdate2(String[] sql, String[][] parameters)
    {
        try
        {
            ct = getConnection();
            ct.setAutoCommit(false);

            for (int i = 0; i < sql.length; i++)
            {

                if (null != parameters[i])
                {
                    ps = ct.prepareStatement(sql[i]);
                    for (int j = 0; j < parameters[i].length; j++)
                    {
                        ps.setString(j + 1, parameters[i][j]);
                    }
                    return ps.executeUpdate();
                }

            }
            ct.commit();
        }
        catch (Exception e)
        {
            e.printStackTrace();
            try
            {
                ct.rollback();
            }
            catch (Exception e1)
            {
                e1.printStackTrace();
            }
            throw new RuntimeException(e.getMessage());
        }
        finally
        {
            close(ct, ps, rs);
        }
        return 0;
    }
    
    //执行存储过程
    public static CallableStatement callPro1(String sql, String[] parameters)
    {
        try
        {
            ct = getConnection();
            cs = ct.prepareCall(sql);
            if (parameters != null)
            {
                for (int i = 0; i < parameters.length; i++)
                {
                    cs.setObject(i + 1, parameters[i]);
                }
            }
            cs.execute();
        }
        catch (Exception e)
        {
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
        }
        finally
        {
            close(ct, cs, rs);
        }
        return cs;
    }

    //执行高级存储过程
    public static CallableStatement callPro2(String sql, String[] inparameters,
            Integer[] outparameters)
    {
        try
        {
            ct = getConnection();
            cs = ct.prepareCall(sql);
            if (inparameters != null)
            {
                for (int i = 0; i < inparameters.length; i++)
                {
                    cs.setObject(i + 1, inparameters[i]);
                }
            }
            if (outparameters != null)
            {
                for (int i = 0; i < outparameters.length; i++)
                {
                    cs.registerOutParameter(inparameters.length + 1 + i, outparameters[i]);
                }
            }
            cs.execute();
        }
        catch (Exception e)
        {
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
        }
        finally
        {
            close(ct, cs, rs);
        }
        return cs;
    }
    
    //关闭所有资源连接
    public static void close(Connection ct, Statement ps, ResultSet rs)
    {
        if (rs != null)
        {
            try
            {
                rs.close();
            }
            catch (Exception e)
            {
                e.printStackTrace();
            }
            rs = null;
        }
        if (ps != null)
        {
            try
            {
                ps.close();
            }
            catch (Exception e)
            {
                e.printStackTrace();
            }
            ps = null;
        }
        if (null != ct)
        {
            try
            {
                ct.close();
            }
            catch (Exception e)
            {
                e.printStackTrace();
            }
            ct = null;
        }
    }
}

2,db.properties

<pre name="code" class="plain">driver = oracle.jdbc.driver.OracleDriver
url = jdbc:oracle:thin:@127.0.0.1:1521:orcl
username = scott
password = tiger