Need help with Degrees of Separation using SQL & java program

I'm trying my best but I am lost right now. I know that this is horrible code but can someone just try to help me with the syntax and the overall problem if you're really ambitious. Thank you.

This is the SQL schema:

create table Person(
  id int primary key,
  name varchar(255) not null
);
create table Band(
  id int primary key,
  name varchar(255),
  style varchar(255)
);
create table memberOf(
  person int references Person(id),
  band int references Band(id开发者_开发技巧),
  primary key(person, band)
);

Here is my horribly written program so far:

import java.sql.*;

public class DegreeNumber {

public static void degreesOfSeparation(int origin) throws Exception {
    //Loads driver.
    Class.forName("com.mysql.jdbc.Driver");
    // Makes connection to server
    Connection c = DriverManager.getConnection
        ("jdbc:mysql://cursa.ccs.neu.edu/test");

    //Create a new prepared Statement that makes a 
        temporary table that can hold people in bands

    PreparedStatement temp = c.prepareStatement
        ("create temporary table CurrentBand (
                id int primary key);
            ");

    //Execute query, creates table above
    temp.execute();

    //Create prepared statement to insert people of band into table
    PreparedStatement insert = c.prepareStatement
        ("insert into CurrentBand (id)
            select m.musician
            from memberOf m
            where ? = m.band;");

    //Get Bands in table
    PreparedStatement getBands = c.prepareStatement
        ("select b.id
        from Bands b");
    ResultSet  bands = getBands.executeQuery();

    int bandCount = 1;
    //Sets parameter to first band on list
    insert.setInt(1, bands.getInt(bandCount));

    //Execute bands being inserted
    insert.execute();

    //Gives back ResultSet with band listed
    PreparedStatement returnCurrentBand = c.prepareStatement
        ("select * from CurrentBand");

    //Execute to give back CurrentBand records
    ResultSet currentBand = returnCurrentBand.executeQuery();

    //Creates table to hold musicians
    PreparedStatement createDegree = c.prepareStatement
        ("create temporary table Degrees(
            id int,
            name varchar(255),
            degree int,
            primary key (id))");

    //Execute to create table
    createDegree.execute();

    //Insert original Person into table Degrees
    PreparedStatement insertOrig = c.prepareStement
        ("insert into Degrees (id, name, degree)
            select p.id, p.name, 0
            from Person p
            where p.id = ?");

    insertOrig.setInt(1, origin);

    insertOrig.execute();

    int count = 1;

    //If there are still bands left on list, 
    while {(!bands.isAfterLast()){
        PreparedStatement thisRound = c.prepareStatement
            ("insert into Degrees (id, name, degree) 
                select p.id, p.name, ?
                from Person p, memberOf m, currentBand c
                where p.id = m.musician
                    and m.band = c.id");

        thisRound.setInt (1, count);
        count++;
        PreparedStatement truncate = c.prepareStatement("truncate table currentBand");
        truncate.execute();
        bandCount++;
        insert.execute();
        bands.next();
        }


    //means all bands have been gone through,  find unique people, make new table & sort and print out

        PreparedStatement createFinal = c.prepareStatement
            ("create table Final (
                name varchar(255), 
                degree int, 
                primary key (name, degree))");
        createFinal.execute();

        PreparedStatement makeFinal = c.prepareStatement(
            ("insert into Final (name, degree)
                select unique (d.name, d.degree)
                from Degrees d
                sort by degree asc, name asc");
        makeFinal.execute();

        ResultSet final = c.prepareStatement("select * from Final").executeQuery();

        while (final.next()) {
                System.out.println("Musician Name " + final.getString("name") +
                               " Degree " + rs.getInt("degree"));

You terribly need a DAO. Also take a look at this thread.

The problems you are encountering are mostly due to poor abstraction and design. It's good that you realize that this isn't a great piece of code. To give you a bit of incite about exactly why this is bad, let's consider the following problems with the class you presented.

• It's not Object-Oriented. It's merely a procedure statically tucked into a class.
• It throws the generic Exception. We won't get much of a chance to handle errors outside of its scope.
• Every time you use this function (I wouldn't even call it a method), you make a new connection to the database. That's pretty costly.
• Tables in your database aren't well represented as first-class objects in your Java program. They suffer from weak abstraction and can't have their details encapsulated. This is mostly what's contributing to your feeling that this is poor code.
• This isn't a very testable function. Break this function into many methods, and pass parameters to each of them to get their work done.
• It's long, making it rather incomprehensible. Aim for methods of 5-15 lines.
• It uses temporary tables. That'll kill efficiency when you can well work in number.
• The problem isn't well documented. I understand from the code that it has to do with bands and people, but how does this relate to degrees of freedom? Make the problem and solution obvious.

I feel that if you address these critical areas, you can refactor towards a better solution.