java - 需要有关使用 SQL 和 java 程序的分离度的帮助

Question

我正在尽最大努力，但我现在迷失了。我知道这是可怕的代码，但是如果您真的雄心勃勃，有人可以尝试帮助我解决语法和总体问题吗？谢谢。

这是 SQL 架构:

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)
);

<小时/>

这是迄今为止我写得非常糟糕的程序:

<小时/>

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"));

Answer 1

你非常需要一个DAO 。另请查看 this thread .