soccerleaguedao.java

在线足球社区源代码

package web.domain;

import java.sql.*;

/**
 * This Data Access Object performs database operations on League objects.
 */
public class SoccerLeagueDAO{
	/**
	 * This method retrieves a League object from the database.
	 */
	public League selectLeague(String year, String season) throws SQLException{
		Connection conn = null;
		League league = null;

		try{
			conn = getConnection();
			
			// Create SQL SELECT statement
			PreparedStatement stmt = conn.prepareStatement("SELECT * FROM League WHERE year=? AND season=?");
			
			// Initialize statement and execute the query
			stmt.setString(1, year);
			stmt.setString(2, season);
			ResultSet results = stmt.executeQuery();
			
			// Iterator over the query results
			if(results.next()) {
				// Create and fill-in the League object
				league =
					new League(
							results.getInt("LID"),
							results.getString("year"),
							results.getString("season"),
							results.getString("title"));
			}

		}finally{
			if(conn != null) conn.close();
		}

		return league;
	}
		
	/**
	 * This method inserts the League into the database.
	 */
	public League insert(League league) throws SQLException{
		Connection conn = null;

		try{
			conn = getConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("select LeagueID.NEXTVAL from dual");
			rs.next();
			league.setLid(rs.getInt(1));
			stmt.close();
			
			String sql =
				"INSERT INTO League (LID, year, season, title) "
				+ "VALUES (?, ?, ?, ?)";
			
			// Create SQL INSERT statement
			PreparedStatement insert_stmt = conn.prepareStatement(sql);
			
			// Add data fields
			insert_stmt.setInt(1, league.getLid());
			insert_stmt.setString(2, league.getYear());
			insert_stmt.setString(3, league.getSeason());
			insert_stmt.setString(4, league.getTitle());
			
			// Execute SQL INSERT statement
			insert_stmt.executeUpdate();
		}finally{
			if(conn != null) conn.close();
		}
		
		return league;
	}

	public Player selectPlayer(String name) throws SQLException{
		Connection conn = null;
		Player player = null;

		try{
			conn = getConnection();
			
			// Create SQL SELECT statement
			PreparedStatement stmt = conn.prepareStatement("SELECT * FROM Player WHERE name=?");
			
			// Initialize statement and execute the query
			stmt.setString(1, name);
			ResultSet results = stmt.executeQuery();
			
			// Iterator over the query results
			if(results.next()) {
				// Create and fill-in the League object
				player =
					new Player(
							results.getInt("PID"),
							results.getString("name"),
							results.getString("address"),
							results.getString("city"),
							results.getString("province"),
							results.getString("postalCode"));
			}

		}finally{
			if(conn != null) conn.close();
		}

		return player;
	}

	/**
	 * This method inserts the Player into the database.
	 */
	public Player insert(Player player) throws SQLException{
		Connection conn = null;
		
		try{
			conn = getConnection();
			
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("select PlayerID.NEXTVAL from dual");
			rs.next();
			player.setPid(rs.getInt(1));
			stmt.close();
			
			String sql =
				"INSERT INTO Player (PID, NAME, ADDRESS, CITY, PROVINCE, POSTALCODE) "
				+ "VALUES (?, ?, ?, ?, ?, ?)";
			// Create SQL INSERT statement
			PreparedStatement insert_stmt = conn.prepareStatement(sql);
			
			// Add data fields
			insert_stmt.setInt(1, player.getPid());
			insert_stmt.setString(2, player.getName());
			insert_stmt.setString(3, player.getAddress());
			insert_stmt.setString(4, player.getCity());
			insert_stmt.setString(5, player.getProvince());
			insert_stmt.setString(6, player.getPostalCode());
			
			// Perform the SQL INSERT
			insert_stmt.executeUpdate();
		}finally{
			if(conn != null) conn.close();
		}
		
		return player;
		
	}

	/*
	 * 根据传入的参数，在数据库的表中插入一条记录
	 */
	public void insert(Registration reg) throws SQLException{
		Connection conn = null;
		
		try{
			conn = getConnection();
			
			String sql =
				"INSERT INTO Registration (LID, PID, DIVISION) " + "VALUES (?, ?, ?)";
			PreparedStatement stmt = conn.prepareStatement(sql);
			
			stmt.setInt(1, reg.getLeague().getLid());
			stmt.setInt(2, reg.getPlayer().getPid());
			stmt.setString(3, reg.getDivision());
			stmt.executeUpdate();
		}finally{
			if(conn != null) conn.close();
		}		
	}
	/*
	 * 初始化Connection 对象
	 */
    public Connection getConnection() throws SQLException{
        String url="jdbc:oracle:thin:@localhost:1521:ORCL";
        String userName="scott";
        String password="tiger";
        
        DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
        Connection conn = DriverManager.getConnection(url, userName, password);
            
        return conn;
    }
	
	
}