0

I have a class that contains methods that connect to a database, run a query, and assigns data from the database into variables.

The purpose of this Java program is to scan a database that houses information on security threats for client's servers. When the query is run it should return selected data fields from 2 tables (target_stats and attack_stats) upon reading that the number of attacks (target_stats.num_attacks) is above 0.

I can successfully retrieve data from the target table but I get null values from the attacker table and I know that the field is not null in the database.

My question: can anyone detect an error, most likely a logical one, in my query that is causing the unfavorable results? I am a beginner programmer and fairly new to SQL.

Also, I am in the process of learning setters and getters in this program, all of the retrieved data is to be used in another class but I am testing and learning with just the target field for now. Perhaps that is causing an issue?

UPDATE I rant he query in MySQL query browser and get the same unfavorable result, so the problem is just in my query logic. I have done some research in queries and joins but must still be lacking suitable knowledge.

My code (hopefully I post it correctly in format):

public class Database {

String details = null;
int threat_level = 0;
ResultSet rslt = null;

private String target2;

Connection con;
public void createConnection() {

    //Establish a connection
    try {
        con = DriverManager.getConnection("sensitiveInformation");
    } catch (SQLException e) {

        e.printStackTrace();
    }
    System.out.println("Database connected");
}

public ResultSet getData() {

    String query =  "SELECT target_stats.server_id, target_stats.target, target_stats.threat_level, target_stats.client_id, attack_stats.attacker, attack_stats.num_this_attack " +
            "       FROM target_stats " +
            "       LEFT OUTER JOIN attack_stats " +
            "       ON target_stats.target = attack_stats.target " +
            "       WHERE target_stats.num_attacks > '0' " +
            "       AND target_stats.interval_id>'2'";
    Statement stmt = null;
    try {
        stmt = con.createStatement();
    } catch (SQLException e) {

        e.printStackTrace();
    }

    try {
        rslt = stmt.executeQuery(query);
    } catch (SQLException e) {

        e.printStackTrace();
    }

    return rslt;    

}

public void process() {

    try {


        String server_id = rslt.getString("server_id");
        target2 = rslt.getString("target");
        threat_level = rslt.getInt("threat_level");
        int client_id = rslt.getInt("client_id");
        String attacker = rslt.getString("attacker");
        String num_this_attack = rslt.getString("num_this_attack");
        details = "Target IP: " + target2 + " Server ID: " + server_id + " Client ID: " + client_id + " Threat Level: " + threat_level  + " Attacker IP: " + attacker + " Number of attacks: " + num_this_attack;


        System.out.println(details);


    } catch (SQLException e) {

        e.printStackTrace();
    }
}
public String getTraget2() {
    return target2;
}

}

Update: I should mention that I have a while loop in another class that properly reads through each record.

Update 2: Here is the main class, I am not concerned with the GUI elements right now:

public class MainDisplay extends JFrame {

static Toolkit tk = Toolkit.getDefaultToolkit();
static int Width = (int)tk.getScreenSize().getWidth();
static int Height = (int)tk.getScreenSize().getHeight();

public static JFrame frame = new JFrame();
public static JLayeredPane lpane = new JLayeredPane();
public static String target;

public static void main (String[] args) { new MainDisplay();

}

public MainDisplay() {

frame.setPreferredSize(new Dimension(Width, Height));
frame.setLocation(0,0);
frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
frame.setUndecorated(true);

//NoRisk Run = new NoRisk();
//Run.NoThreat();


ThreatPanel Run = new ThreatPanel();
Database Data = new Database();

//Create Connection to Database and run query

Data.createConnection();
Data.getData();
    try {
        while(Data.rslt.next()){
            Data.process();
            Run.TargetServer = Data.getTraget2();
            System.out.print(Data.getTraget2()); //for testing purposes
            Run.ShowThreats();
            try {
                Thread.sleep(1000*10); // sleeps for ten seconds
            } catch (InterruptedException e) {

                e.printStackTrace();
            }

        }
    } catch (SQLException e) {

        e.printStackTrace();
    }

//Run.ShowThreats();

frame.setLayout(new BorderLayout());
frame.add(lpane, BorderLayout.CENTER);
lpane.setBounds(0,0, Width, Height);






frame.pack();
frame.setVisible(true);

} }

0

3 Answers 3

1

Did you try to execute the same query in database? You mentioned it should return some results but did you try it in database before putting in the codes? If the database is not returning the desirable result, we will look into the query statement, for example instead of left outer join you might have to use inner join.

Sign up to request clarification or add additional context in comments.

4 Comments

I just did and I got the same unfavorable results. I guess the problem is just int he query itself then?
Great. Now we narrowed the problem. So you have to troubleshoot the query, most of the time it's the join problem as I pointed out. Unless you share the tables here, we cant' really help you out. Of course, you have to be careful when sharing sensitive information, the attacker might be anyone in this community :)
Unfortunately I can't share the exact tables because it contains sensitive. However I was about to share the table layout and omit the sensitive information when I ran into another problem. I am using MySQL Query Browser and am able to view the data in target_stats table but when I go to the attack_stats table, it is blank. It's peculiar because I can see in the target_stats table that there are a few servers under threat and have viewed this information the other day. hmm...
Looks like it's entirely a different issue now. The attacker wipe off the data :) Once the data is back, try to play with different type of joins which suit your data.
1

The field in attacker table may be not null, but when you have LEFT OUTER JOIN - you will have null values in a query when there is no corresponding data in attack_stats table - please check that.

The query looks ok - try with LEFT INNER JOIN - then you will have no rows visible where there are no corresponding data in attack_stats table.

Comments

0

Pat, Can you post your main method, from where you are calling this code. Also you should iterate over resultset to move the cursors.

while(rslt.next())
{
 String server_id = rslt.getString("server_id");
        target2 = rslt.getString("target");
        threat_level = rslt.getInt("threat_level");
        int client_id = rslt.getInt("client_id");
        String attacker = rslt.getString("attacker");
        String num_this_attack = rslt.getString("num_this_attack");
        details = "Target IP: " + target2 + " Server ID: " + server_id + " Client ID: " + client_id + " Threat Level: " + threat_level  + " Attacker IP: " + attacker + " Number of attacks: " + num_this_attack;


        System.out.println(details);

}

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.