dbmanager.java

This is a project used to find a corresondin location from place

/*
 * DBManager.java
 *
 * Created on Jan 23, 2008, 2:27:19 AM
 *
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package com.horizongroup.mcompanion.web.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.*;
/**
 *
 * @author Administrator
 */
public class DBManager 
{
    private Connection connection;
    private Statement statement;
    public DBManager() 
    {
        try 
        {
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://localhost:4000/mcompanion";
            connection = DriverManager.getConnection(url, "mcompanion", "mars");
            statement = connection.createStatement();
            System.out.println("Connection Successful !");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public boolean savePreferences(String preference, String value, String type)
    {
        String query = "INSERT INTO userpreference(preferencename, preference, type) VALUES('"+preference+"', '"+value+"', '"+type+"')";
        try{
            getStatement().executeUpdate(query);
            return true;
        }catch(Exception e){
            e.printStackTrace();
        }
        return false;
    }
    
    
    public String loadPreferences()
     {
        String preferences = "";
        try {
            ResultSet rs = getStatement().executeQuery("SELECT id, preferencename, preference FROM userpreference") ; //WHERE preferencename ='" + prefereceName + "' ");
            while (rs.next()) {
                preferences +=  rs.getString(2)+ ":" + rs.getString(3)+":";
                preferences +="#";
            }
            return preferences;
        } catch (Exception e) {
            e.printStackTrace();
            return preferences;
      }
    }
    
    public boolean checkUser(String userId, String password) 
    {
        try 
        {
            ResultSet rs = getStatement().executeQuery("select * from user_login where loginname='"+userId.trim()+"' and password='"+password.trim()+"'");
            System.out.println("Checking User : "+userId+" : "+password);
            boolean res =  rs.next();
            System.out.println(res);
            return res;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }
    public String findUserName(String userId) 
    {
        String userName = "Guest";
        try {
            ResultSet rs = getStatement().executeQuery("SELECT firstname, middlename, lastname FROM user_details WHERE firstname='" + userId + "' ");
            if (rs.next()) {
                userName = rs.getString(1) + " " + rs.getString(2) + " " + rs.getString(3);
            }
            return userName;
        } catch (Exception e) {
            e.printStackTrace();
            return userName;
        }
    }
    
    public String findUserMessages(String userId){
        System.out.println("Fetching Messages of "+userId);
        String messages = "No Messages !";
        try {
            ResultSet rs = getStatement().executeQuery("SELECT id, subject, content FROM user_messages WHERE user_id='" + userId + "' ");
            if (rs.next()) {
                messages = rs.getString(1) + ":" + rs.getString(2);
            }
            return messages;
        } catch (Exception e) {
            e.printStackTrace();
            return messages;
        }
    }
    public boolean deleteObject(String tableName, String property, String value) {
        try {
            int id=Integer.parseInt(value);
            String query1="select address_id from "+tableName+" where "+ property+"=";
             if (value instanceof String) {
                query1 +=id;
            } else {
                query1 += "" + id + "";
            }
            System.out.println("Query1="+query1);
            String query = "DELETE FROM " + tableName + " WHERE " + property + "=";
            if (value instanceof String) {
                query +=id;
            } else {
                query += "" + id + "";
            }
            System.out.println("query="+query);
            getStatement().execute(query);
            
            ResultSet rs=getStatement().executeQuery(query1);
            int aid=0;
            while(rs.next())
            {
                System.out.println("inside while");
                 aid=Integer.parseInt(rs.getString(1));
                
            }
            String query2="delete from address where address_id="+aid;
            System.out.println("Address query"+query2);
            getStatement().execute(query2);
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }

    }
    
    public ArrayList findListByLikeCondition(String tableName, String[] returnValues, String[] params, Object[] values) {
        
        ArrayList resultList = new ArrayList();
        if (params.length != values.length) {
            System.err.println("Invalid Criteria-Value Pair!");
        }


        String query = "SELECT * FROM "+tableName;

       /* if (returnValues != null) {
            for (int j = 0; j < returnValues.length; j++) {
                query += returnValues[j];
                if (j != returnValues.length - 1) {
                    query += ",";
                }
            }
        } else {
            query += "* ";
        }
*/
        
       // query += " FROM " + tableName;

        if (params.length > 0) {
            query += " WHERE ";
        }
               
        for (int i = 0; i < params.length; i++) {
            if (values[i] != null) {
                if (values[i] instanceof String) {
                    query += params[i] + " like" + " '%" + values[i] + "%' ";
                } else {
                    query += params[i] + "=" + " " + values[i] + " ";
                }

                if (i != (params.length - 1)) {
                    query += " AND ";
                }
            }
        }
        try {
            System.err.println("Executing Query : " + query);
            ResultSet rs = getStatement().executeQuery(query);            
            int index = 0;
            while (rs.next()) {
                Object[] objectValues= new Object[returnValues.length];
                for (int k = 0; k < returnValues.length; k++) {
                    objectValues[k] = rs.getString(returnValues[k]);                    
                }
                resultList.add(index++,objectValues);
            }
        } catch (Exception exception) {
            exception.printStackTrace();
            return new ArrayList();
        }
        for(int i=0; i<resultList.size(); i++){
            System.out.println(resultList.get(i));
        }
        return resultList;
    }

    
public String findAllAirports(){
        String airports = "";
        int count = 0;
        System.out.println("Fetching Airports");        
        try {
            ResultSet rs = getStatement().executeQuery("SELECT * FROM airport");
            while (rs.next()) {  
                count++;
                airports += rs.getString(1) + ":" + rs.getString(2)+":"+rs.getString(3)+":"+rs.getString(4)+":"+rs.getString(5)+":"+rs.getString(6);                
                airports += "#";
            }
            airports+=count;
            System.out.println("Returning Airports : "+airports);
            return airports;
        } catch (Exception e) {
            e.printStackTrace();
            return airports;
        }
    }
    
    public String findSchedulesByDateFromTo(Timestamp fromTime, Timestamp toTime, String airportFrom, String airportTo){
       System.out.println(fromTime);
        String flightSchedules = "";
        int count = 0;
        System.out.println("Fetching Flight Schedules ...");        
        try {
            ResultSet rs = getStatement().executeQuery("select * from flight_schedule where departure_time between '"+fromTime+"' and '"+toTime+"' and departure_airport_code='"+airportFrom+"' and arrival_airport_code='"+airportTo+"'");
            while (rs.next()) {  
                count++;
                flightSchedules += rs.getString(1) + "*" + rs.getString(2)+"*"+rs.getString(3)+"*"+rs.getString(4)+"*"+rs.getString(5)+"*"+rs.getString(6)+"*"+rs.getInt(7)+"*"+rs.getInt(8)+"*"+rs.getInt(9)+"*";                
                flightSchedules += "#";
            }
            flightSchedules+=count;
            System.out.println("Returning Flight Schedules : "+flightSchedules);
            return flightSchedules;
        } catch (Exception e) {
            e.printStackTrace();
            return flightSchedules;
        }
    }

    public Statement getStatement() {
        return statement;
    }

    public Connection getConnection() {
        return connection;
    }
   
}