package dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import dao.AccountDao;
import entity.Account;
import util.JdbcUtil;
public class AccountDaoImpl implements AccountDao{

	private Integer  getOid(String seq) throws Exception{
		Connection conn=null;
		Statement stm=null;
		ResultSet rs=null;
		try{
			conn=JdbcUtil.getConnection();
			stm=conn.createStatement();
			String sql="select "+seq+".nextval from dual";      	 
			rs=stm.executeQuery(sql);
			rs.next();
			return rs.getInt(1);
		}finally{
			JdbcUtil.release(rs, stm, null);
		}
	}
	public void insertAccount(Account c) throws Exception {
		// TODO Auto-generated method stub
		Connection conn=null;
		PreparedStatement pstm=null;
		try{
			conn=JdbcUtil.getConnection();
			conn.setAutoCommit(false);
			String sql="insert into jdbc_account values (?,?,?,?,?,?,?)";
			pstm=conn.prepareStatement(sql);
			int id=getOid("jdbc_account_seq");
			pstm.setInt(1, id);
			pstm.setString(2, c.getCardId());
			pstm.setString(3, c.getPassword());
			pstm.setString(4, c.getName());
			pstm.setString(5, c.getPersonId());
			pstm.setDouble(6, c.getBalance());
			pstm.setInt(7, c.getAccountType());
			pstm.executeUpdate();
			c.setId(id);
		}finally{
			JdbcUtil.release(null, pstm, null);
		}
	}
	
	public void deleteAccount(String cardId) throws Exception {
		// TODO Auto-generated method stub
		Connection conn=null;
		PreparedStatement pstm=null;
		try{
			conn=JdbcUtil.getConnection();
			conn.setAutoCommit(false);
			String sql="delete from jdbc_account where cardId=?";
			pstm=conn.prepareStatement(sql);
			pstm.setString(1, cardId);
			pstm.executeUpdate();
		}finally{
			JdbcUtil.release(null, pstm, null);
		}
	}

	public void update(Account c) throws Exception {
		// TODO Auto-generated method stub
		Connection conn=null;
		PreparedStatement pstm=null;
		try{
			conn=JdbcUtil.getConnection();
			conn.setAutoCommit(false);
			String sql="update jdbc_account set password=?,name=?,personId=?,balance=? where cardId=?";
			pstm=conn.prepareStatement(sql);
			pstm.setString(1,c.getPassword());
			pstm.setString(2, c.getName());
			pstm.setString(3, c.getPersonId());
			pstm.setDouble(4, c.getBalance());
			pstm.setString(5, c.getCardId());
			pstm.executeUpdate();
		}finally{
			JdbcUtil.release(null, pstm, null);
		}
	}
	
	public Account queryByCardId(String cardId)  {
		// TODO Auto-generated method stub
		Connection conn=null;
		PreparedStatement pstm=null;
		ResultSet rs=null;
		Account c=null;
		try{
			conn=JdbcUtil.getConnection();
			String sql="select id,cardId,password,name,personId,balance,accountType from jdbc_account where cardId=?";
			pstm=conn.prepareStatement(sql);
			pstm.setString(1, cardId);
			rs=pstm.executeQuery();
			if(rs.next()){
				c=new Account();
				c.setId(rs.getInt(1));
				c.setCardId(rs.getString(2));
				c.setPassword(rs.getString(3));
				c.setName(rs.getString(4));
				c.setPersonId(rs.getString(5));
				c.setBalance(rs.getDouble(6));
				c.setAccountType(rs.getInt(7));
			}
		}finally{
			JdbcUtil.release(rs, pstm, null);
			return c;
		}
		
	}

	public Account queryById(long id) {
		// TODO Auto-generated method stub
		Connection conn=null;
		PreparedStatement pstm=null;
		ResultSet rs=null;
		Account c=null;
		try{
			conn=JdbcUtil.getConnection();
			String sql="select id,cardId,password,name,personId,balance,accountType from jdbc_account where id=?";
			pstm=conn.prepareStatement(sql);
			pstm.setLong(1, id);
			rs=pstm.executeQuery();
			if(rs.next()){
				c=new Account();
				c.setId(rs.getInt(1));
				c.setCardId(rs.getString(2));
				c.setPassword(rs.getString(3));
				c.setName(rs.getString(4));
				c.setPersonId(rs.getString(5));
				c.setBalance(rs.getDouble(6));
				c.setAccountType(rs.getInt(7));
			}
		}finally{
			JdbcUtil.release(rs, pstm, null);
			return c;
		}
	}

	public List queryByPersonId(String personId) {
		// TODO Auto-generated method stub
		Connection conn=null;
		PreparedStatement pstm=null;
		ResultSet rs=null;
		List<Account> l=null;
		try{
			conn=JdbcUtil.getConnection();
			String sql="select id,cardId,password,name,personId,balance,accountType from jdbc_account where personId=?";
			pstm=conn.prepareStatement(sql);
			pstm.setString(1, personId);
			rs=pstm.executeQuery();
			l=new ArrayList<Account>();
			while(rs.next()){
				Account c=new Account();
				c.setId(rs.getInt(1));
				c.setCardId(rs.getString(2));
				c.setPassword(rs.getString(3));
				c.setName(rs.getString(4));
				c.setPersonId(rs.getString(5));
				c.setBalance(rs.getDouble(6));
				c.setAccountType(rs.getInt(7));
				l.add(c);
			}
		}finally{
			JdbcUtil.release(rs, pstm, null);
			return l;
		}
	}

}