Ggtcf

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.

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"));
 
 
 

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.

It is more or less ok if we are talking about such a small program.
But there is no need to keep con, stmt and rs as a static variables, they can be declared inside a method. Also, you need to rewrite try catch finally blocks and close resources properly:

Connection con = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try 
 // your code
 catch (SQLException e) 
 e.printStackTrace();
 finally 
 try if (rs != null) rs.close(); catch (Exception e) e.printStackTrace();
 try if (stmt != null) stmt.close(); catch (Exception e) e.printStackTrace();
 try if (conn != null) conn.close(); catch (Exception e) e.printStackTrace();

As a next step you can check try-with-resources construction to clean up this code.