DAO

package VO;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class DAO {

	private Connection conn = null;
	private PreparedStatement psmt = null;
	private ResultSet rs = null;

	private String user_id = null;
	private String user_pw = null;
	private long money = 0;

	public long getMoney() {
		return money;
	}

	public void getConn() {//DB연결
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");

			String url = "jdbc:oracle:thin:@project-db-stu.ddns.net:1524:xe";// @project-db-stu.ddns.net:1524
			String user = "cgi_1_0418_1";// cgi_1_0418_1
			String password = "smhrd1";// smhrd1
			conn = DriverManager.getConnection(url, user, password);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			System.out.println("드라이버 로딩 실패");
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println("데이터 베이스 연결 실패");
		}
	}

	public void getClose() {//자원반납
		try {
			if (rs != null)
				rs.close();
			if (psmt != null)
				psmt.close();
			if (conn != null)
				conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println("자원반납 오류");
		}
	}
	//로그인
	public int login(String user_id, String user_pw) { // 어떤 계정에 대한 실제로 로그인을 시도하는 함수, 인자값으로 ID와 Password를 받아 login을 판단함.
		// 실제로 DB에 입력될 명령어를 SQL 문장으로 만듬.

		this.user_id = user_id;
		this.user_pw = user_pw;

		int row = 0;
		try {
			getConn();
			String SQL = "SELECT * FROM USER_list WHERE id = ? and pw =?";
			psmt = conn.prepareStatement(SQL);
			psmt.setString(1, user_id);
			psmt.setString(2, user_pw);
			row = psmt.executeUpdate();

			this.money = moneyCome();

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			getClose();
		}

		return row;
	}
	//회원가입
	public int join(Game user) {
		int row = 0;
		try {
			getConn();
			String SQL = "INSERT INTO user_list VALUES(user_no_seq.nextval,?, ?, ?)";
			psmt = conn.prepareStatement(SQL);
			psmt.setString(1, user.getUser_id());
			psmt.setString(2, user.getUser_pw());
			psmt.setString(3, user.getUser_name());
			row = psmt.executeUpdate();

			if (row > 0) {
				System.out.println("회원가입성공");
			} else {
				System.out.println("회원가입실패");
			}

			String sql1 = "select * from user_list where ID =? and PW =?";

			psmt = conn.prepareStatement(sql1);
			psmt.setString(1, user.getUser_id());
			psmt.setString(2, user.getUser_pw());
			rs = psmt.executeQuery();
			int user_no = 0;
			while (rs.next()) {
				user_no = rs.getInt("USER_NO");
			}

			
			String sql2 = "insert into user_money values (?, 10000)";
			psmt = conn.prepareStatement(sql2);
			psmt.setInt(1, user_no);
			row = psmt.executeUpdate();

			if (row > 0) {
				System.out.println("금액초기화성공");
			} else {
				System.out.println("금액초기화실패");
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			getClose();
		}
		return row;// 없음
	}
	//금액업데이트
	public void moneyUpdate(long money) {
		try {
			getConn();
			
			String sql1 = "select * from user_list where ID =? and PW =?";

			psmt = conn.prepareStatement(sql1);
			psmt.setString(1, user_id);
			psmt.setString(2, user_pw);
			rs = psmt.executeQuery();
			int user_no = 0;
			while (rs.next()) {
				user_no = rs.getInt("USER_NO");
			}
			
			String sql = "update user_money set money = ? where user_nom=?";
			psmt = conn.prepareStatement(sql);
			psmt.setLong(1, money);
			psmt.setInt(2, user_no);
			int row = psmt.executeUpdate();
			if(row>0) {
				System.out.println("게임 진행상황 저장완료!\n");
			}else {
				System.out.println("금액 업데이트 실패");
			}
		} catch (Exception e) {
			// TODO: handle exception
		}
	}
	//잔액불러오기
	public long moneyCome() {
		long money = 0;
		try {
			getConn();
			String sql = "select * from user_list where ID =? and PW =?";

			psmt = conn.prepareStatement(sql);
			psmt.setString(1, user_id);
			psmt.setString(2, user_pw);
			rs = psmt.executeQuery();
			int user_no = 0;
			while (rs.next()) {
				user_no = rs.getInt("USER_NO");
			}

			String sql1 = "select * from user_money where user_nom = ?";
			psmt = conn.prepareStatement(sql1);
			psmt.setInt(1, user_no);
			rs = psmt.executeQuery();
			while (rs.next()) {
				money = rs.getLong("MONEY");
			}

		} catch (Exception e) {
			// TODO: handle exception
		}

		return money;

	}
    //랭킹
	public ArrayList<Game> ranking() {
		ArrayList<Game> list = new ArrayList<Game>();
		try {
			getConn();
			String sql = "select rownum,name,money from ranking";
			psmt = conn.prepareStatement(sql);
			rs = psmt.executeQuery();
			while (rs.next()) {
				int rownum = rs.getInt("ROWNUM");
				  String user_name = rs.getString("NAME");
		            long money = rs.getLong("MONEY");
		            
		            
		            Game s = new Game(rownum,user_name,money);
		            
		            list.add(s);

			}
		} catch (Exception e) {
			// TODO: handle exception
		}
		return list;
	}

}