dao.java

在线足球社区

package com.pandahome.struts.soccerLeagueDao;

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

import com.pandahome.struts.domain.League;
import com.pandahome.struts.domain.Player;
import com.pandahome.struts.domain.Registration;

import oracle.jdbc.driver.OracleDriver;

public class Dao {
	private Connection con;

	private void getConnection() {
		try {
			DriverManager.registerDriver(new OracleDriver());
			con = DriverManager.getConnection(
					"jdbc:oracle:thin:@127.0.0.1:1521:ORCL", "scott", "tiger");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	private void closeConnection() {
		if (con != null) {
			try {
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	public Player selectPlayer(String name) {
		Player player = new Player();
		getConnection();
		try {
			PreparedStatement pstmt = con
					.prepareStatement("SELECT * FROM player WHERE name=?");
			pstmt.setString(1, name);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				player.setAddress(rs.getString("address"));
				player.setCity(rs.getString("city"));
				player.setName(name);
				player.setPid(Integer.parseInt(rs.getString("pid")));
				player.setPostalCode(rs.getString("postalCode"));
				player.setProvince(rs.getString("province"));
				return player;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();

		} finally {
			closeConnection();
		}
		return null;

	}

	public League selectLeague(String season, String year) {
		League league = new League();
		getConnection();
		try {
			PreparedStatement pstmt = con
					.prepareStatement("SELECT * FROM league WHERE year=? AND season=?");
			pstmt.setString(1, year);
			pstmt.setString(2, season);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				league.setLid(Integer.parseInt(rs.getString("lid")));
				league.setSeason(season);
				league.setTitle(rs.getString("title"));
				league.setYear(year);
				return league;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();

		} finally {
			closeConnection();
		}
		return null;

	}

	public League insert(League league) throws SQLException {
		getConnection();
		try {
			Statement stmt = con.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 = con.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 {
			closeConnection();
		}

		return league;
	}

	public Player insert(Player player) throws SQLException {
		getConnection();
		try {
			Statement stmt = con.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 = con.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 {
			closeConnection();
		}

		return player;

	}

	public void insert(Registration reg) throws SQLException {
		getConnection();
		try {
			String sql = "INSERT INTO Registration (LID, PID, DIVISION) "
					+ "VALUES (?, ?, ?)";
			PreparedStatement stmt = con.prepareStatement(sql);

			stmt.setInt(1, reg.getLeague().getLid());
			stmt.setInt(2, reg.getPlayer().getPid());
			stmt.setString(3, reg.getDivision());
			stmt.executeUpdate();
		} finally {
			closeConnection();
		}
	}
}