How to get specific id's from different MySQL Tables

Question

I know it sounds awful but I couldn't find a way to express what I need.I am currently constructing a class that checks for a basketball team if the same ticket was "hit' in the same event by getting the id's from 2 different tables. My DB consists of 3 Tables.The 1st is the 'tickets' table in which the user gives the name,the year and the ticket name. The 2nd is the 'events' table where the user gives the basketball event and the year. Now the last table is where I am confused.( I have already checked if the same event occurs in the same year cause it is impossible in a Championship to play 2 times on the same stadium).I have not checked if the same ticket appears on the same event and that is what I am trying to do.

The 3rd table is the 'check' table. I have 3 integer variables( id, ticket_id, event_id) and I want to get the ticket_id from tickets table and 1st column( which is the auto-incremented id) and do the same for the event_id from the events table(1st column is id of course).

Then I want to check and make sure they do not match more than 1 time and return a boolean value.

This is what I have came up so far.

P.S Sorry for the long post but I am so confused about this...

import java.sql.ResultSet;
import java.sql.SQLException;

public class check {

private int ticket_id,event_id;

public int getTicket_id() {
    return ticket_id;
}

public void setTicket_id(int ticket_id) {
    this.ticket_id = ticket_id;
}

public int getEvent_id() {
    return event_id;
}

public void setEvent_id(int event_id) {
    this.event_id = event_id;
}

public check(int ticket_id, int event_id) {
    super();
    this.ticket_id = ticket_id;
    this.event_id = event_id;
}

public boolean checkExistance_3 (){ 

String errMsg = "";
// class that connects with the MySQL DB
ServerConn sc = new ServerConn();
String sql3 = "SELECT Count(*) AS RowCount FROM events INNER JOIN tickets on tickets.id = events.id WHERE tickets.id = '" + ticket_id  + "' AND events.id =" + event_id ;   
 // HOW TO GIVE VALUES AT ticket_id and event_id??? this is my problem
 // I dont now if a need a SELECT with rs.next() because it would return all id's!

 // class that executes queries       
 ResultSet rs3 = sc.dbQuery(sql3);

 if (errMsg.equals(""))
   {
    try {
    rs3.next();
    int rows = rs3.getInt("RowCount");
    } catch (SQLException case1) {
    case1.printStackTrace();
    } catch (Exception case2) {
    case2.printStackTrace();}
    return true;
      }   
    return false;
 }

}

I chose not to put how the other 4 classes work cause it would be more confusing. In case another class is needed to clarify things, I will add it on my post.

Tickets Table

Events Table

I created a class that saves the id's on the 3rd table but the variables in the sql and sql_1 statements cannot be resolved cause they belong to different classes. Is it possible to inherite variables from different classes?

public String SaveId() {

String errMsg2 = "";


if ( errMsg2.equals("") ) {
   try
   {

        ServerConn sc = new ServerConn();
        // sql_game and ticketNumber appear on other classes. Can I inherit them?
        // wont find the variables, error.
        String sql = "SELECT (id) FROM events WHERE decsrp = '" + sql_game + "'";   
        String sql_1 = "SELECT (id) FROM tickets WHERE ticket = '" + ticketNumber + "'";
        ResultSet rs = sc.dbQuery(sql);
        ResultSet rs_1 = sc.dbQuery(sql_1);
        boolean allowed = true;
        while (rs.next())
               allowed = false;
        while (rs_1.next())
               allowed = false;
        if (allowed == true)
{
    try {
        Connection cn = sc.OpenDatabase();
        PreparedStatement ps2 = cn.prepareStatement("INSERT INTO elenxos (event_id,ticket_id) VALUES (?,?)");
        ps2.setInt(1,this.event_id);
        ps2.setInt(2,this.ticket_id);
        ps2.executeUpdate();
        JOptionPane.showMessageDialog(null,"Valid Ticket");
        } catch (SQLException case1) {
            case1.printStackTrace();
        } catch (Exception case2){
           case2.printStackTrace();}
        }
         else if (allowed == false)
            errMsg2 += "Impossible to use the same ticket 2 times!!";
        }catch (SQLException e)
        {
            System.out.println("8");
        }
         catch (Exception e)
        {
            System.out.println("9");  
        }
    } else {
            errMsg2 += "FAILURE!!" ;
           }
        return errMsg2;
    }

Answer 1

It appears that you are wrestling with how to execute a query that will have different query values each time you execute the query. You should use PreparedStatement to handle this situation. This will allow you to use the same statement, once created, and supply it with different values. In addition, your database queries could be improved if you are simply trying to see if a duplicate occurs.

Consider the following:

package com.example.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class Check {
    // Class fields
    private PreparedStatement check;
    private String checkQuery = "Select Count(*)"
            + "  from events inner join tickets on tickets.id = events.id"
            + " where tickets.id = ? " + "   and events.id = ? ";

    // Object fields
    private int ticket_id;
    private int event_id;

    public Check(int ticket_id, int event_id) {
        super();
        this.ticket_id = ticket_id;
        this.event_id = event_id;

    }

    public int getTicket_id() {
        return ticket_id;
    }

    public void setTicket_id(int ticket_id) {
        this.ticket_id = ticket_id;
    }

    public int getEvent_id() {
        return event_id;
    }

    public void setEvent_id(int event_id) {
        this.event_id = event_id;
    }

    public boolean checkExistance_3(Connection sc) {
        boolean returnValue = false;
        // TODO previously established db connnection passed performance reasons

        try {
            check = sc.prepareStatement(checkQuery);
            // the first argument is the varialbe indes, the second the value to
            // supstitute
            check.setInt(1, ticket_id);
            check.setInt(2, event_id);
            ResultSet results = check.executeQuery();
            results.next();
            int countResult = results.getInt("RowCount");
            if (countResult > 1) {
                returnValue = true;
            }

        } catch (SQLException e) {
            System.out.println("Error querying database: " + e.getMessage());
        }
        return returnValue;
    }
}

Answer 2

Then I want to check and make sure they do not match more than 1 time and return a boolean value.

You don't need any checks for duplicates in Java, this stuff should always be handled on the database side.

Also you don't need a third table at all, because normally a ticket only grants you entry to a certain event. Therefore you should have a 1:n relationship.

You should just be able to use the event_id as a foreign key column in the tickets table and define the primary key as the combination of ticket_id and event_id. This will solve all your problems.