InquireDB.java

Go to the documentation of this file.

package p2pserver;

import java.sql.*;
import java.util.ArrayList;

public class InquireDB
{
    
    private Connection c;
    private Statement s;
    private boolean speak;

    public InquireDB(boolean disperr, String db, String user, String pswd)
    {
        super();
        speak = disperr;
        c = p2pserver.Connecttodbms.connect(speak,db,user,pswd);
        s  = p2pserver.Connecttodbms.createStat(speak,c);
    }

    public boolean putFileList(ArrayList l)
    {
        for(int i=0;i<l.size();i++)
            {
                ArrayList ll = (ArrayList)l.get(i);
                int j = -1;
                
                String query = "INSERT INTO "+p2pserver.PrepareDB_user.tab2()+
                                " VALUES("+
                                            ll.get(++j).toString()+","+
                                            "'"+ll.get(++j).toString()+"',"+
                                            "'"+ll.get(++j).toString()+"',"+
                                            ll.get(++j).toString()+","+
                                            ll.get(++j).toString()+","+
                                            ll.get(++j).toString()+","+
                                            "'"+ll.get(++j).toString()+"',"+
                                            ll.get(++j).toString()+","+
                                            ll.get(++j).toString()+","+
                                            "default"+
                                       ");";
                //if(speak) p2putility.Util.logServer(query);
                boolean ok = p2pserver.Connecttodbms.noRSetQuery(speak,s,query);
                                                    
                if(!ok) return false;
            }
        return true;
    }

    public boolean delFileListBy(String ckey, String name, String path, String id,
                                 String isdir, String size, String cdate, String rights,
                                 String state, String idate
                                )
    {
        String query = "DELETE FROM "+p2pserver.PrepareDB_user.tab2();
        
        boolean bef =false;
        
        if(ckey!=null){         query +=" WHERE ckey="+ckey; bef = true;}
        
        if(bef&&(name!=null))   query += " AND name='"+name+"'";
        else if(name!=null){    query += " WHERE name='"+name+"'"; bef=true;}
        
        if(bef&&(path!=null))   query += " AND path='"+path+"'";
        else if(path!=null){    query += " WHERE path='"+path+"'"; bef=true;}
        
        if(bef&&(id!=null))     query += " AND id="+id;
        else if(id!=null){      query += " WHERE id="+id; bef=true;}
        
        if(bef&&(isdir!=null))  query += " AND isdir="+isdir;
        else if(isdir!=null){   query += " WHERE isdir="+isdir; bef=true;}
        
        if(bef&&(size!=null))   query += " AND size="+size;
        else if(size!=null){    query += " WHERE size="+size; bef=true;}
        
        if(bef&&(cdate!=null))  query += " AND cdate='"+cdate+"'";
        else if(cdate!=null){   query += " WHERE cdate='"+cdate+"'"; bef=true;}
        
        if(bef&&(rights!=null)) query += " AND rights="+rights;
        else if(rights!=null){  query += " WHERE rights="+rights; bef=true;}
        
        if(bef&&(state!=null))  query += " AND state="+state;
        else if(state!=null){   query += " WHERE state="+state; bef=true;}
        
        if(bef&&(idate!=null))  query += " AND idate='"+idate+"'";
        else if(idate!=null){   query += " WHERE idate='"+idate+"'"; bef=true;}
        
        query += ";";

        //if(speak) p2putility.Util.logServer(query);
        return p2pserver.Connecttodbms.noRSetQuery(speak,s,query);
    }

    public long nextCkeyFileList() {
        long ret = 0;
        try {
            ResultSet r = p2pserver.Connecttodbms.yesRSetQuery(speak, s, "SELECT MAX(ckey) AS c FROM " + p2pserver.PrepareDB_user.tab2());

            if (r.next())
                ret = r.getLong("c");

            if(speak) p2putility.Util.logServer("Count file: "+(ret+1));

            p2pserver.Connecttodbms.closeRSet(speak, r);

        } catch (SQLException ex) {
            if(speak) p2putility.Util.logServer("nextCkeyFileList: "+ex);
        }
        return (ret+1);
    }

    public ArrayList getFileList(String where)
    {
        where = validate(where);
        
        ArrayList l = new ArrayList();
        
        ResultSet r = p2pserver.Connecttodbms.yesRSetQuery(speak,s,"SELECT * FROM "+p2pserver.PrepareDB_user.tab2()+where);
        
        try
        {
            int i = -1;
            while(r.next())
            {
                int j = -1;
                ArrayList ll = new ArrayList();
                ll.add(++j, r.getInt("ckey"));
                ll.add(++j, r.getString("name"));
                ll.add(++j, r.getString("path"));
                ll.add(++j, r.getInt("id"));
                ll.add(++j, r.getBoolean("isdir"));
                ll.add(++j, r.getInt("size"));
                ll.add(++j, r.getDate("cdate"));
                ll.add(++j, r.getInt("rights"));
                ll.add(++j, r.getInt("state"));
                ll.add(++j, r.getTimestamp("idate"));
                
                l.add(++i, ll);
        
            }
            
        }
        catch(Exception x)
        {
            if(speak) if(speak) p2putility.Util.logServer("InquireDB getFileList ERROR!\n"+x);
            return null;
        }
        
        p2pserver.Connecttodbms.closeRSet(speak,r);
        
        return l;   
    }

    public boolean putUserList(ArrayList l)
    {
        for(int i=0;i<l.size();i++)
            {
                ArrayList ll = (ArrayList)l.get(i);
                int j = -1;
                
                String query = "INSERT INTO "+p2pserver.PrepareDB_user.tab1()+
                                " VALUES("+
                                            ll.get(++j).toString()+","+
                                            "'"+ll.get(++j).toString()+"',"+
                                            "'"+ll.get(++j).toString()+"',"+
                                            "'"+ll.get(++j).toString()+"'"+
                                       ");";
                //if(speak) p2putility.Util.logServer(query);
                boolean ok = p2pserver.Connecttodbms.noRSetQuery(speak,s,query);
                if(!ok) return false;
            }
        return true;
    }

    public boolean delUserListBy(String id, String ip, String username, String password)
    {
        String query = "DELETE FROM "+p2pserver.PrepareDB_user.tab1();
        
        boolean bef =false;
        
        if(id!=null){ query +=" WHERE id="+id; bef = true;}
        
        if(bef&&(ip!=null))     query += " AND ip='"+ip+"'";
        else if(ip!=null){      query += " WHERE ip='"+ip+"'"; bef=true;}
        
        if(bef&&(username!=null))   query += " AND username='"+username+"'";
        else if(username!=null){    query += " WHERE username='"+username+"'"; bef=true;}
        
        if(bef&&(password!=null))   query += " AND password='"+password+"'";
        else if(password!=null){    query += " WHERE password='"+password+"'"; bef=true;}
        
        query += ";";
        
        return p2pserver.Connecttodbms.noRSetQuery(speak,s,query);
    }

    public int nextUserID(){
        try {
            ResultSet r = p2pserver.Connecttodbms.yesRSetQuery(speak, s, "SELECT MAX(id) AS i FROM " + p2pserver.PrepareDB_user.tab1());
            if(r!=null)
                if (r.next())
                    return (r.getInt("i") + 1); //next

        }catch (Exception ex){if(speak) p2putility.Util.logServer("nextUserID error: "+ex);}
        return 0;// (long)(java.lang.Math.random()*100); // id casuale tra 0 e 100
    }

    public ArrayList getUserList(String where)
    {
        where = validate(where);
        
        ArrayList l = new ArrayList();
        
        ResultSet r = p2pserver.Connecttodbms.yesRSetQuery(speak,s,"SELECT * FROM "+p2pserver.PrepareDB_user.tab1()+where);
        
        try
        {
            int i = -1;
            while(r.next())
            {
                int j = -1;
                ArrayList ll = new ArrayList();
                ll.add(++j, r.getInt("id"));
                ll.add(++j, r.getString("ip"));
                ll.add(++j, r.getString("username"));
                ll.add(++j, r.getString("password"));
                
                l.add(++i, ll);
        
            }
            
        }
        catch(Exception x)
        {
            if(speak) p2putility.Util.logServer("InquireDB getUserList ERROR!\n"+x);
            return null;
        }
        
        p2pserver.Connecttodbms.closeRSet(speak,r);
        
        return l;   
    }

    public void close()
    {
        p2pserver.Connecttodbms.closeStat(speak,s);
        p2pserver.Connecttodbms.disconnect(speak,c);
    }

    private String validate(String where){
        
        if(where==null) return "";
        
        if(where.length()<5) return "";
            
        if(!where.startsWith(" ")) return (" "+where);
            
        return where;
    }

    public boolean isOpen(){
        try
        {
            if(!c.isClosed() && !s.isClosed())
                return true;
        }
        catch(Exception sqe){
            if(speak) p2putility.Util.logServer("isOpen error: "+sqe);
            return false;
        }

        return false;
    }
}