databaseaccess.java

This is a complete but simple application of Video rental system written in java. It uses GUI and MS

import java.sql.*;
import javax.swing.*;
import java.util.*;

/**
 * Performs the database access
 */
public class DatabaseAccess
{ 
  // DatabaseAccess ODBC
  // Driver JDBC/ODBC constant
  final static private String drv = "sun.jdbc.odbc.JdbcOdbcDriver"; // DB driver
  
  // Path(URL) DB constant
  final static private String URL = "jdbc:odbc:VideoRentalStore"; // DB alias
  
  public Connection connection; // DB connection object
  
  /**
   * + connect(): boolean =================================================================
   * ------------------------------------
   * Opens a database connection returning a boolean value that indicates whether
   *the connection was established or not.
   */
  public boolean connect()
  {
    boolean isConnected = true;
    
    try
    {
      Class.forName(drv);
      
      connection = DriverManager.getConnection(URL, "", ""); 
    }
    catch(java.lang.ClassNotFoundException er1)
    {
      System.out.println(er1.getMessage());
      
      isConnected = false;
    }
    catch(SQLException ex)
    {
      System.out.println(ex.getMessage());
      
      isConnected = false;
    }
    return isConnected;
  }

  /**
   * + disconnect() =======================================================================
   * ------------------------------------
   * Terminates the database connection.
   */
  public void disconnect()
  {
    try
    {
      connection.close();
    }
    catch(SQLException ex)
    {
      System.out.println(ex.getMessage());
    }
  }

  /**
   * + checkExist(par1: String): int ========================================================
   * ------------------------------------
   * Receives (in par1) a complement to form a Query 
   * [SELECT COUNT(*) FROM] of type [table WHERE condition] 
   * and returns the total of records found
   */
  public int checkExist(String queryComplement)
  {
    int value = 0;
    
    try
    {
      Statement stm = connection.createStatement();
      
      ResultSet rs = stm.executeQuery("select count(*) from " + queryComplement);
      
      rs.next();
      
      value = rs.getInt(1);
      
      rs.close();
      
      stm.close();
    }
    catch(SQLException ex)
    {
      System.out.println("Query problems: select count(*) from " + queryComplement);
    }
    
    return value;
  }     

  /**
   * + retFields(par1: String[], par2: String, par3: String) ==============================
   * ------------------------------------
   * Receives complements of a given query of type
   * SELECT [par2] FROM [par3] and returns the array defined (in par1)
   * the columns of a record from this query
   */
  public void retFields(String[] complements, String query1, String query2)
  {
    try
    {
      Statement stm = connection.createStatement();
      
      ResultSet rs = stm.executeQuery("select " + query1 + " from " + query2);
      
      if(rs.next())
      {
        for (int i = 0; i < complements.length; i++)
       {
          complements[i] = rs.getString(i + 1);
       }
      }
      
      rs.close();
      
      stm.close();
    }
    catch(SQLException ex)
      {
      System.out.println("Query problems: select " + query1 + " From " + query2);
    }
  }     

  /**
   * + executeSQL(par1: String): int =========================================================
   * ------------------------------------
   * Receives a SQL query (in par1) that can be: 
   *   INSERT INTO table (cmp1, cmp2) VALUES (val1, val2)
   *   UPDATE table SET cmp1 = val1 WHERE condi玢o
   *   DELETE FROM table WHERE condition
   * and returns an integer value with the total of rows affected
   */
  public int executeSQL(String query)
  {
    int value = 0;
    
    try
    {
      Statement stm = connection.createStatement();
      
      value = stm.executeUpdate(query);
      
      stm.close();
    }
    catch(SQLException ex)
    {
      System.out.println("Query problems " + query);
    }
    
    return value;
  }

  /**
   * + returnCode(par1: String): String ===================================================
   * ------------------------------------
   * Returns a single code based on a query sent through par1
   */
  public String returnCode(String query)
  {
    String cmp = "";
    
    Statement stm;
    
    try
    {
      stm = connection.createStatement();
      
      ResultSet rs = stm.executeQuery("select " + query);
      
      if(rs.next())
      {
        cmp = rs.getString(1);
      }
    }
    catch(SQLException ex)
    {
      System.out.println("Query problems: select " + query);
    }
    
    return cmp;
  }

  /**
   * + loadList(par1: JList, par2, par3: String) ==========================================
   * ------------------------------------
   * Loads an object of type JList (received in par1)
   * based on a complement of query of type
   * SELECT [par2] FROM [par3].
   */
  public void loadList(JList list, String query1, String query2)
  {
    try
    {
      Vector row = new Vector();
      
      Statement stm = connection.createStatement();
      
      ResultSet rs = stm.executeQuery("select " + query1 + " from " +  query2);

      while(rs.next())
      {
        row.addElement(rs.getString(1));
      }
      
      list.setListData(row); 
      
      rs.close();
      
      stm.close();
    }
    catch(SQLException ex)
    {
      System.out.println("Query problems: select " + query1 + " from " + query2);
    }
  }    

  /**
   * + loadComboBox(par1: JComboBox, par2, par3: String) =====================================
   * ------------------------------------
   * Loads an object of type JComboBox (passed by par1)
   * based on the complement of a give query of type
   * SELECT [par2] FROM [par3].
   */
  public void loadComboBox(JComboBox combo, String query1, String query2)
  {
    try
    {
      Statement stm = connection.createStatement();
      
      ResultSet rs = stm.executeQuery("select " + query1 + " from " + query2);

      combo.removeAllItems();
      
      while(rs.next())
      {
        combo.addItem(rs.getString(1));
      }
      
      rs.close();
      
      stm.close();
    }
    catch(SQLException ex)
    {
      System.out.println("Query problems: select " + query1 + " from " + query2);
    }
  }

}