可以在多个类方法中使用相同的BasicDataSource，Connection，Statement和ResultSet对象吗? [英] Is it ok to use same BasicDataSource, Connection, Statement and ResultSet Object in multiple class methods.?

问题描述

我有下面的代码，该代码使用BasicDataSource，Sql Connection，Statement和ResultSet的静态对象.下面的代码工作正常，但是我只想知道使用这种编码实践的安全性.或如何优化以下代码，使其变得更稳定，更可靠.

I have below code which uses static objects of BasicDataSource, Sql Connection, Statement and ResultSet. The code below is working fine, but i just want to know about the safety of using these kinds of coding practices. or how can i optimize the below code so that it can become more stable and can reliable.

public class Testing {
     static BasicDataSource bds = DBConnection.getInstance().getBds();
     static Connection con = null;
     static PreparedStatement stmt = null;
     static ResultSet rs = null;

    private void show() {
        try {
            con = bds.getConnection();
            stmt = con.prepareStatement("SELECT * FROM users");
            rs = stmt.executeQuery();
            if(rs.next()) {
                System.out.println(rs.getString("firstname") + " " + rs.getString("lastname"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    private void display() {
        try {
            con = bds.getConnection();
            stmt = con.prepareStatement("SELECT * FROM agent_cities");
            rs = stmt.executeQuery();
            while(rs.next()) {
                System.out.println(rs.getString("city_name"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    private void add() {
        try {
            con = bds.getConnection();
            stmt = con.prepareStatement("UPDATE users SET firstname = 'shsh' WHERE id = 2");
            stmt.executeUpdate();
            System.out.println("updated successfully");
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    public static void main(String[] args) {
        Testing t = new Testing();
        t.show();
        t.display();
        t.add();
    }
}

先谢谢了.请分享您可以打破上述代码的案例，并对其安全性提出疑问.

Thanks in advance. Do share your cases on which you can break above code and question about its safety.

更新:仅更新以确保没有人可以使用我在上面的程序中使用的静态字段，因为在部署到开发服务器上时，上面的程序包含错误.

Update : Updating only to ensure that no one should use static fields as i have used in above program because above program contains bug when deployed on dev server.

在大型系统上使用上述代码后，我发现了该错误.一个月前，我对上面的代码没有任何问题，并且工作正常，但今天我发现了该错误.

After using above code on large systems i found the bug. one month ago i had no problem with the above code and it was working fine but today i found the bug.

错误:

在击中我的API 6-7次后，它在第8次击中时停止给出响应.我真的不知道为什么，也不知道程序中存在漏洞.但是现在，当我接受答案时，我更改了源代码，并开始在代码中使用尝试资源并删除了静态字段.

After hitting my APIs 6-7 times it stopped giving response at 8th hit. i really don't know why and have no idea about loop holes present in program. But now as i have accepted the answer i changed my source code and started using try-with resources in my code and removed static fields.

但是我仍然很想知道我在上面的代码中发现的错误.无法响应并在7-8次API命中后挂起.请分享您对此的想法.我正在使用apache tomcat 8.5.32服务器.预先感谢.

But i am still curious to know about the bug that i found in the above code. that doesn't gives response and hangs after 7-8 API hits. Please share your thoughts on this. i'm using apache tomcat 8.5.32 server. Thanks in advance.

推荐答案

最好使用 try-with-resources .即使在引发异常或内部返回时，这也会自动关闭Connection，Statement和ResultSet.

Better use try-with-resources. This automatically closes Connection, Statement and ResultSet, even when an exception was raised, or on an inner return.

    String sql = "UPDATE users SET firstname = ? WHERE id = ?";
    try (Connection con = bds.getConnection();
            PreparedStatement stmt = con.prepareStatement()) {
        stmt.setString(1, "shsh");
        stmt.setLong(2, 2);
        stmt.executeUpdate();
        System.out.println("updated successfully");
    }

    String sql = "SELECT city_name FROM agent_cities";
    try (Connection con = bds.getConnection();
            PreparedStatement stmt = con.prepareStatement()) {
        try (ResultSet rs = stmt.executeQuery()) {
            while(rs.next()) {
                System.out.println(rs.getString("city_name"));
            }
        }
    }

这对于垃圾收集更好.防止rs2，rs3异常.允许多用户并发，例如在服务器应用程序中.自行查询.而 static 甚至更具有全局变量的风格.

This is better for garbage collection. Prevents unnice rs2, rs3. Allows multi-user concurrency, like in a server application. Calls that query themselves. And static is even more in the style of global variables.

这篇关于可以在多个类方法中使用相同的BasicDataSource，Connection，Statement和ResultSet对象吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！