/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Classes;

/**
 *
 * @author indunilperamuna
 */

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Enumeration;
import java.util.Hashtable;

public class DbClass{
  
  private String _sql;
  private static  Connection conn;
  
  public static void main(String[] args) {
   
   
  }
  
  private  void connectDB(){
      String url = "jdbc:mysql://localhost:3306/";
      String dbName = "starrent";
      String driver = "com.mysql.jdbc.Driver";
      String userName = "root"; 
      String password = "toor";
      try {
      Class.forName(driver).newInstance();
      conn = DriverManager.getConnection(url+dbName,userName,password);
        //System.out.println("Connected to the Databasese");
      
      } catch (Exception e) {
          //System.out.println("Error Connecting to Database");
      }
  }
  
  public  void closeDB(){
      try{
          if (!conn.isClosed()){
          conn.close();
          //System.out.println("Connection Close");
          }else{
              //System.out.println("Connection Allready Closed");
          }
      }catch(SQLException e){}
  }
  
  public void setQuery(String sql){
      _sql = sql;
  }
  
  public ResultSet loadResults(){
      connectDB();
      ResultSet rs = null;
      try{
      Statement s = conn.createStatement();
      rs = s.executeQuery(_sql);
      }catch(SQLException e){
        System.err.println ("Error message: " + e.getMessage ());
        System.err.println ("Error number: " + e.getErrorCode ());
      }
      
      return rs;     
      
  }
  //example
  /*DbClass Db = new DbClass();
        String sql = "Select * From tbl_customers";
        Db.setQuery(sql);
        ResultSet rs = Db.loadResults();
        try{
        while ( rs.next()){
              txtcusname.setText(rs.getString("customer_name"));
        }
        Db.closeDB();
        }catch(SQLException e){}*/
  
  public Integer insert(String tablename, Hashtable data){
      Integer key = 0;
      
      String query = "INSERT INTO "+ tablename +" (";
            
      Enumeration e = data.keys();
      Integer i = 1;
      while( e.hasMoreElements()){
          if( i != data.size()){
          query += e.nextElement() + ", ";
          i++;
          }else{
            query += e.nextElement() + " ";  
          }
      }
       
      query += ") Values (";
      e = data.keys();
      i = 1;
      while (e.hasMoreElements()){
          if( i != data.size()){
          query += data.get(e.nextElement()) + ", ";
          i++;
          }else{
            query += data.get(e.nextElement());
          }
          
        
      }
      
      query += ")";                   
      connectDB();
      try{
          PreparedStatement pstmt = conn.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
          pstmt.executeUpdate();
          ResultSet keys = pstmt.getGeneratedKeys();
          
          keys.next();
          key = keys.getInt(1);
          keys.close();                              
          
          closeDB();
          
      }catch(SQLException ex){
         System.err.println ("Error message: " + ex.getMessage ());
         System.err.println ("Error number: " + ex.getErrorCode ());
      }
      return key;
      
  }
  //example
  /*
  Hashtable data = new Hashtable();
  data.put("customer_name", "'"+ txtcusname.getText().toString()+ "'");
  data.put("customer_address", "'"+ txtcusaddress.getText().toString() + "'");
  data.put("customer_email", "'"+ txtcusemail.getText().toString() + "'");
  data.put("customer_phone", "'"+ txtcusphone.getText().toString() + "'");
  data.put("customer_mobile", cusmobile);
  data.put("customer_nic", "'"+ txtcusnic.getText().toString() + "'");
  
  DbClass Db = new DbClass();
  int id = Db.insert("tbl_customers", data);
  System.out.println(id);*/
  
  public boolean update(String tablename, Hashtable data, String Where){
      
      boolean status = false;
      String query = "UPDATE "+ tablename +" SET ";
            
      Enumeration e = data.keys();
      Integer i = 1;
      while( e.hasMoreElements()){
          if( i != data.size()){
          String col = e.nextElement().toString();
          query += col + "="+ data.get(col) +", ";
          i++;
          }else{
            String col = e.nextElement().toString();
          query += col + "="+ data.get(col) +" ";
          }
      }
       
      query += " Where " + Where;
      //System.out.println(query);
      connectDB();
      try{
          
          Statement s = conn.createStatement();
          s.executeUpdate(query);         
          closeDB();
          status = true;
          
      }catch(SQLException ex){
         System.err.println ("Error message: " + ex.getMessage ());
         System.err.println ("Error number: " + ex.getErrorCode ());
      }
      
      return status;
  }
  //example
  /*
  Hashtable data = new Hashtable();
        data.put("customer_name", "'Indunil'");
        data.put("customer_address", "'Pelawatte'");
        boolean statues = Db.update("tbl_customers", data, "customer_id=1");
        status will false if error occured*/
  
 
  public boolean delete(String tablename, String Where){
      
      boolean status =  false;
      
      String query = "DELETE FROM " + tablename + " WHERE " + Where;
      connectDB();
      try{
          Statement s = conn.createStatement();
          s.executeUpdate(query);
          status =  true;
          closeDB();
      }catch(SQLException e){
         System.err.println ("Error message: " + e.getMessage ());
         System.err.println ("Error number: " + e.getErrorCode ());
         
      }
         
      return status;
             
  }
  //example
  //boolean del = Db.delete("tbl_customers", "customer_id = 1");
  
  public boolean isExists(String tablename, String tablecolumn, String value){
      
      boolean status = false;
      String query = "SELECT "+ tablecolumn + " FROM "  + tablename + " WHERE " + tablecolumn + "= "+value;
      connectDB();
      try{
          Statement s = conn.createStatement();
          ResultSet rs = s.executeQuery(query);
          while(rs.next()){
              status = true;
          }
          closeDB();               
      }catch(SQLException e){
         System.err.println ("Error message: " + e.getMessage ());
         System.err.println ("Error number: " + e.getErrorCode ()); 
      }
      return status;
  }
  //example
  //boolean chkemail = Db.isExists("tbl_customers", "customer_email", "'iperamuna@gmail.com'");
  
  public String isExists(String tablename, String tablecolumn, String value, String retcolumn){
      
      String retvalue = null;
      String query = "SELECT "+ retcolumn + " FROM "  + tablename + " WHERE " + tablecolumn + " = "+value;
      connectDB();
      try{
          Statement s = conn.createStatement();
          ResultSet rs = s.executeQuery(query);
          while(rs.next()){
              retvalue = rs.getString(retcolumn);
          }
          closeDB();               
      }catch(SQLException e){
         System.err.println ("Error message: " + e.getMessage ());
         System.err.println ("Error number: " + e.getErrorCode ()); 
      }
      
      return retvalue;
  }
  //example
  //String chkemail = Db.isExists("tbl_customers", "customer_email", "'iperamuna@gmail.com'", "customer_mobile");
    
  
}