Connection to a database

[Madhatter40]

I was wondering if I could take the following code and somehow put the connection to database in it's own class without the "public static void main(String[] args)" method. That way I could call it anytime i want to open a connection to the database.

 import java.sql.Connection;
 import java.sql.DriverManager;
 import java.sql.ResultSet;
 import java.sql.SQLException;
 import java.sql.Statement;

   public class Sample
    {
    public static void main(String[] args) throws ClassNotFoundException
     {
  // load the SQLite-JDBC driver using the current class loader
  Class.forName("org.sqlite.JDBC");

      Connection connection = null;
      try
      {
  // create a database connection
  connection = DriverManager.getConnection("jdbc:sqlite:C:\\Users\\Admin\\Documents\\Eclipse\\WorkSpace\\Database\\ProductInfo.sqlite");
  Statement statement = connection.createStatement();
  statement.setQueryTimeout(30);  // set timeout to 30 sec.

 }
catch(SQLException e)
{
  // if the error message is "out of memory", 
  // it probably means no database file is found
  System.err.println(e.getMessage());
}
finally
{
  try
  {
    if(connection != null)
      connection.close();
  }
  catch(SQLException e)
  {
    // connection close failed.
    System.err.println(e);
  }
}
  }
 }

[milovan]

did you try to use instance of this class as it is now (few modifications perhaps)?
main() is also just a method and (true, used for specific purpose but..) it is not necessary to use it.

[sibernewf]

I've used Singleton classes before to do this.
Sorry re any typos.

public class DataBaseConnectionFactory {
  private static DatabaseConnectionFactory instance = new DatabaseConnectionFactory();
  // Most of these fields I usually populate using a properties file, but you will have to figure that out if you want to use that.
  private static String URL = null;
  private static String USERNAME;
  private static String PASSWORD;
  private static String DRIVER_CLASS = "org.posgresql.Driver";  // if using Postgres
  private static String DATABASE;
  private static int PORT;
  private static String SERVER;

  private DatabaseConnectionFactory() {}

  // this method is where I would load data from a properties file.  In this example I just manually add the data
  public void loadProperties() {
    USERNAME = "database_user_username";  
    PASSWORD = "database_user_username's password";
    DATABASE = "name of the database";
    PORT = 5432; // whatever the port is for your database
    SERVER = "name of your server otherwise localhost to run locally";
    URL = "jdbc:postgresql://" + SERVER + ":" + PORT + "/" + DATABASE;  // use your database details where postgresql is
  }

  private void initialiseDatabase() {
    try {
      Class.forName(DRIVER_CLASS);
    } catch(ClassNotFoundException e) {
        // some kind of output to identify an issue 
    }
    // driver should be registered if we get here
    this.loadProperties();
  }

  private Connection createConnection() {
    Connection connection = null;
    try {
      if(URL == null) {
         initialiseDatabase();
      }
      if(connection == null) {
         connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
      }
    } catch(SQLException e) {
       // some output identifying unable to connect to database
    }
    return connection;
  }

  public static Connection getConnection() {
    return instance.createConnection();
  }
}

I forgot to add details on how to use the class.
The following are class fields in my DAO:

private connection connection;
private Statement statement;
private PreparedStatement preparedStatement;

For example my DAO has a delete method:

public void findById(int id) throws NotFoundException {
  String query = "SELECT ID from databasetablename where ID=" + id + ";";
  resultSet resultSet = null;
  // lets pretend you are finding a user with a firstname and lastname
  User user = null;

  try {
    connection = DatabaseConnectionFactory.getConnection();
    statement = connection.createStatement();
    resultSet = statement.executeQuery(query);
    if(resultSet.next()) {
       // populate your entity object
       user.setId(resultSet.getId("id"));
       user.setFirstName(resultSet.getString("firstname"));
       user.setLastName(resultSet.getString("lastname")):
    }
  } catch(SQLException e) {

  }
  finally {
    // close connection
   // close resultset
   // close statement
  }
  return user;  // which was retrieved and populated.
}

This may not work with a copy paste, but should point you in the right dHope that helps you out :)