How to use Singleton class for to get connections multiple?

Question

I wrote a Singleton class for getting connections. However, I cannot get connections using Singleton.

I want to use my Singleton for getting several connections and to make query to database, using the connection Singleton. I always try several ways for this, and no success.

This is my Singleton class:

import java.sql.*;

public class ConnectDB {

private static Connection connect;
private static ConnectDB instance;

private ConnectDB()
{

    try {

        Class.forName("com.mysql.jdbc.Driver");
        //connect DB
        connect = DriverManager.getConnection("jdbc:mysql://ip/database","root","password");

    }

    catch(SQLException e)
    {
        System.err.println(e.getMessage());

    }

    catch(ClassNotFoundException e)
    {

        System.err.println(e.getMessage());

    }   
}

  public static ConnectDB getInstance()
  {

      if(instance == null) {

          instance = new ConnectDB();

      }

      return instance;

  }

}

Now, I get the connections:

 public class NameClass {




public void getInfoDatabase()
{   

       Connection cnn = ConnectDB.getConnection();
       PreparedStatement ps;
       ResultSet rs;

       try {

           ps = cnn.prepareStatement("select *  from tables");

           rs = ps.executeQuery();

           while(rs.next())
           {

               String tables = rs.getString("table1");
               System.out.println(tables);
           }

A multiple singleton? Kinda defeats the purpose of having a singleton... — Marc B, Jul 31 '12 at 06:01
where did you face a prblem? beside maby you cinsider writing a methode like execStmtL(Stmt) in the singleton class so you run it directly from here? — CloudyMarble, Jul 31 '12 at 06:06
Where are you calling ConnectDB.getInstance() ? Not with standing that, this implementation looks broken - I don't like the look of the static Connection Object. Shouldn't that be an instance variable of ConnectDB ? — DaveH, Jul 31 '12 at 07:51

score 9 · Accepted Answer · edited May 23 '17 at 12:09

If you want to use multiple connections efficiently you might be after a connection pool:

In software engineering, a connection pool is a cache of database connections maintained so that the connections can be reused when future requests to the database are required. Connection pools are used to enhance the performance of executing commands on a database.

Having a Singleton which will return many connections defies the purpose of the Singleton, whose task is to provide the same instance whenever it is called.

I recommend you take a look at this previous SO thread where various connection pool libraries are discussed.

score 1 · Answer 2 · answered Jun 22 '16 at 13:26

My way is this DBConnection is a Singleton class. And Usage of this is in ContactDAO class.

public class DBConnection{

    private static DBConnection instance;
    private String url="jdbc:oracle:thin:@192.168.10.32:1521:orcl";
    private String login="kit";
    private String pass="1234";

    private DBConnection(){

        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() throws SQLException {
        if (instance == null) {
            instance = new DBConnection();
            System.out.println(" Connection  - - - - - - - -  New DBConnection created");
        }
        try {
            return DriverManager.getConnection(instance.url, instance.login,instance.pass);
        } catch (SQLException e) {
            throw e;
        }
    }

    public static void close(Connection connection)
    {
        try {
            if (connection != null) {
                connection.close();
                connection=null;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

}

********* ContactDAO *************

public class ContactDAO {


        public List<Contact> findAll() {
            List<Contact> list = new ArrayList<Contact>();
            Connection c = null;
            String sql = "SELECT * FROM KIT.CONTACT";
            try {
                c = DBConnection.getConnection();
                Statement s = c.createStatement();
                ResultSet rs = s.executeQuery(sql);
                while (rs.next()) {
                    list.add(processRow(rs));
                }
            } catch (SQLException e) {
                e.printStackTrace();
                throw new RuntimeException(e);
            } finally {
                DBConnection.close(c);
            }
            return list;
        }

        public List<Contact> findByCity(String city) {
            List<Contact> list = new ArrayList<Contact>();
            Connection c = null;
            String sql = "SELECT * FROM KIT.CONTACT as e " + "WHERE UPPER(city) LIKE ? ";
            try {
                c = DBConnection.getConnection();
                PreparedStatement ps = c.prepareStatement(sql);
                ps.setString(1, "%" + city.toUpperCase() + "%");
                ResultSet rs = ps.executeQuery();
                while (rs.next()) {
                    list.add(processRow(rs));
                }
            } catch (SQLException e) {
                e.printStackTrace();
                throw new RuntimeException(e);
            } finally {
                DBConnection.close(c);
            }
            return list;
        }

        public Contact findById(int id) {
            String sql = "SELECT * FROM KIT.CONTACT WHERE id = ?";
            Contact contact = null;
            Connection c = null;
            try {
                c = DBConnection.getConnection();
                PreparedStatement ps = c.prepareStatement(sql);
                ps.setInt(1, id);
                ResultSet rs = ps.executeQuery();
                if (rs.next()) {
                    contact = processRow(rs);
                }
            } catch (Exception e) {
                e.printStackTrace();
                throw new RuntimeException(e);
            } finally {
                DBConnection.close(c);
            }
            return contact;
        }

        public Contact save(Contact contact) {
            return contact.getId() > 0 ? update(contact) : insert(contact);
        }

        public Contact insert(Contact contact) {
            Connection c = null;
            PreparedStatement ps = null;
            try {
                c = DBConnection.getConnection();
                ps = c.prepareStatement(
                        "INSERT INTO KIT.CONTACT (country, city, address, photo,fk_user) VALUES (?, ?, ?, ?, ?)",
                        new String[] { "ID" });

                ps.setString(1, contact.getCountry());
                ps.setString(2, contact.getCity());
                ps.setString(3, contact.getAddress());
                ps.setString(4, contact.getPhoto());
                ps.setInt(5, contact.getFk_user());

                ps.executeUpdate();

                ResultSet rs = ps.getGeneratedKeys();
                while(rs.next()){
                int id = rs.getInt(1);
                contact.setId(id);
                }


            } catch (Exception e) {
                e.printStackTrace();
                throw new RuntimeException(e);
            } finally {
                DBConnection.close(c);
            }
            return contact;
        }

        public Contact update(Contact contact) {
            Connection c = null;
            try {
                c = DBConnection.getConnection();
                PreparedStatement ps = c
                        .prepareStatement("UPDATE KIT.CONTACT SET country=?, city=?, address=?, photo=?  WHERE id=?");
                ps.setString(1, contact.getCountry());
                ps.setString(2, contact.getCity());
                ps.setString(3, contact.getAddress());
                ps.setString(4, contact.getPhoto());

                ps.setInt(5, contact.getId());
                ps.executeUpdate();
            } catch (SQLException e) {
                e.printStackTrace();
                System.out.println("contactDAO update exception");
                throw new RuntimeException(e);
            } finally {
                DBConnection.close(c);
            }
            return contact;
        }

        public boolean remove(int id) {
            Connection c = null;
            try {
                c = DBConnection.getConnection();
                PreparedStatement ps = c
                        .prepareStatement("DELETE FROM KIT.CONTACT WHERE id=?");
                ps.setInt(1, id);
                int count = ps.executeUpdate();
                return count == 1;
            } catch (Exception e) {
                e.printStackTrace();
                throw new RuntimeException(e);
            } finally {
                DBConnection.close(c);
            }
        }

        protected Contact processRow(ResultSet rs) throws SQLException {
            Contact contact = new Contact();
            contact.setId(rs.getInt("id"));
            contact.setCountry(rs.getString("country"));
            contact.setCity(rs.getString("city"));
            contact.setAddress(rs.getString("address"));
            contact.setPhoto(rs.getString("photo"));

            return contact;

        }







    }

score 0 · Answer 3 · answered Jul 31 '12 at 06:06

0

Its better to have a singleton class of connection pools like ComboPooledDataSource and then get multiple connections from that.

answered Jul 31 '12 at 06:06

amas

604
4
8

How to use Singleton class for to get connections multiple?

3 Answers3