src/database/SQLHelper.java

   1 package database;
   2
   3 import java.sql.Connection;
   4 import java.sql.ResultSet;
   5 import java.sql.Statement;
   6
   7 import javax.naming.Context;
   8 import javax.naming.InitialContext;
   9 import javax.sql.DataSource;
  10
  11 public class SQLHelper {
  12
  13         private Connection SQLConnection;
  14         private DataSource datasource;
  15
  16         public SQLHelper() throws Exception
  17         {
  18                 // Obtain our environment naming context
  19                 Context initCtx = new InitialContext();
  20                 Context envCtx = (Context) initCtx.lookup("java:comp/env");
  21
  22                 // Look up our data source
  23                 datasource = (DataSource) envCtx.lookup("jdbc/REMOTE");
  24         }
  25
  26         public void openDB() throws Exception
  27         {
  28                 SQLConnection = datasource.getConnection();
  29         }
  30
  31         public ResultSet retrieve(String query)
  32         {
  33                 ResultSet rs = null;
  34                 try {
  35                         Statement stmt = SQLConnection.createStatement();
  36                         rs = stmt.executeQuery(query);
  37                 } catch (Exception e) {
  38                         e.printStackTrace();
  39                         System.out.println("Query: " + query);
  40                 }
  41                 return rs;
  42         }
  43
  44         public ResultSet retrieveRW(String query)
  45         {
  46                 ResultSet rs = null;
  47                 try {
  48                         Statement stmt = SQLConnection.createStatement(java.sql.ResultSet.TYPE_SCROLL_SENSITIVE,
  49                     java.sql.ResultSet.CONCUR_UPDATABLE);
  50                         rs = stmt.executeQuery(query);
  51                 } catch (Exception e) {
  52                         System.out.println("Exception: " + e.toString());
  53                         System.out.println("Query: " + query);
  54                 }
  55                 return rs;
  56         }
  57
  58         public int update(String query)
  59         {
  60                 int count = 0;
  61                 try {
  62                         Statement stmt = SQLConnection.createStatement();
  63                         count = stmt.executeUpdate(query);
  64                 } catch (Exception e) {
  65                         System.out.println("Exception: " + e.toString());
  66                         System.out.println("Query: " + query);
  67                 }
  68                 return count;
  69         }
  70
  71         public void execute(String query)
  72         {
  73                 try {
  74                         Statement stmt = SQLConnection.createStatement();
  75                         stmt.execute(query);
  76                 } catch (Exception e) {
  77                         System.out.println("Exception: " + e.toString());
  78                         System.out.println("Query: " + query);
  79                 }
  80         }
  81
  82         public void closeDB()
  83         {
  84                 try {
  85                         SQLConnection.close();
  86                 }
  87                 catch (Exception e)
  88                 {
  89                         System.out.println("Exception: " + e.getMessage());
  90                 }
  91         }
  92 }