package ng.db;
import java.sql.*;
import java.util.*;
import org.json.*;
import org.json.JSONArray;


public class DBSession implements AutoCloseable {
	private  Connection con;		//������
	private boolean auto;		//�Ƿ�Ϊ�Զ��ύ
	private Statement sql;		//���һ��ʹ�õ�Statement



	public Connection getConnection(){
		return con;
	}
	//����
	private void reset(){
		if(sql!=null){
			try{
				sql.close();
			}
			catch(Exception e){

			}
			sql=null;
		}
	}




	//��ѯ�����
	public class SelectResult{
		private Statement state;
		private boolean closed;


		public Object getFirst() throws SQLException{
			ResultSet r= state.getResultSet();
			if(r.next())
				return r.getObject(1);
			else
				return null;
		}
		//��ȡ��ǰ�Ľ��
		public ResultSet getCurrentResult() throws SQLException {
			return state.getResultSet();
		}

		public JSONObject allResultToJson() throws SQLException, JSONException{
			JSONObject obj=new JSONObject();
			int idx=1;
			do{
				org.json.JSONArray a=this.resultToJson(false);
				obj.put("Table"+idx,a);
				idx++;
			}while(this.nextResult());
			return obj;
		}



		//�ƶ�����һ�����,������ŷ���true
		public boolean nextResult() throws SQLException {
			return state.getMoreResults();
		}
		public void close(){
			if(closed==false){
				closed=true;
				try{
					state.close();
				}
				catch(Exception e){

				}
			}
		}

		//���캯��
		public SelectResult(Statement state){
			this.state=state;
			ResultSet set=null;
		}

		//����ǰ�Ľ����ֱ������JSON����
		public org.json.JSONArray resultToJson(boolean RowAsObject) throws SQLException, JSONException{
			return DBHelper.resultToJson(this.getCurrentResult(), RowAsObject);
		}

		public org.json.JSONArray resultToJson(String[] fields, boolean RowAsObject) throws SQLException, JSONException{
			return DBHelper.resultToJson(fields,this.getCurrentResult(), RowAsObject);
		}


		public List<org.json.JSONArray> allToJson(boolean RowAsObject) throws SQLException, JSONException{
			List<org.json.JSONArray> arrs=new ArrayList<JSONArray>();
			do{
				arrs.add(DBHelper.resultToJson(this.getCurrentResult(), RowAsObject));
			}while(this.nextResult());
			return arrs;
		}



	}

	//��׼�洢���̵��ã���׼�洢���̶���,���ĵ�
	public class StdCallResult extends SelectResult{
		public int ResultCode;
		public String ResultMessage;
		public StdCallResult(CallableStatement state,int ResultCode,String ResultMessage){
			super(state);
			this.ResultCode=ResultCode;
			this.ResultMessage=ResultMessage;
		}
	}



	//����һ��SQL����
	public void createSql(String sql) throws SQLException{
		reset();
		PreparedStatement result= con.prepareStatement(sql);
		this.sql=result;
	}

	public StdCallResult CallProc(String proc,Object... params) throws SQLException{
		createStdCall(proc,params.length);
		return this.stdCall(params);
	}






	public void createQueryCall(String procName,int InputParamCount) throws SQLException {
		reset();
		StringBuilder sp=new StringBuilder();
		sp.append("{call ");
		sp.append(procName);
		sp.append("(");
		for(int i=0;i<InputParamCount;i++){
			if(i==0){
				sp.append('?');
			}
			else{
				sp.append(",?");
			}
		}

		CallableStatement sql=con.prepareCall(sp.toString());
		sql.registerOutParameter(InputParamCount+1, java.sql.Types.INTEGER);
		sql.registerOutParameter(InputParamCount+2, java.sql.Types.VARCHAR);
		this.sql=sql;
	}



	//����һ����׼�洢���̵��ã�����1�Ǵ洢���̣�����2����������ĸ���
	public void createStdCall(String procName,int InputParamCount) throws SQLException {
		reset();
		StringBuilder sp=new StringBuilder();
		sp.append("{call ");
		sp.append(procName);
		sp.append("(");
		for(int i=0;i<InputParamCount;i++){
			if(i==0){
				sp.append('?');
			}
			else{
				sp.append(",?");
			}
		}
		if(InputParamCount==0)
		{
			sp.append("?,?)}");
		}
		else{
			sp.append(",?,?)}");
		}
		CallableStatement sql=con.prepareCall(sp.toString());
		sql.registerOutParameter(InputParamCount+1, java.sql.Types.INTEGER);
		sql.registerOutParameter(InputParamCount+2, java.sql.Types.VARCHAR);
		this.sql=sql;
	}

	//�ô�����SQL�������ݿ⣬ִ��INSERT DELETE UPDATE��SQL��䣬 ������?ռλ����Ӧ�ı���
	public int update(Object... params) throws SQLException  {

		PreparedStatement sql=(PreparedStatement)this.sql;
		if(params!=null){

			for(int i=0;i<params.length;i++){
				sql.setObject(i+1, params[i]);
			}

		}
		return sql.executeUpdate();

	}

	public int _update(Object[] params) throws SQLException  {

		PreparedStatement sql=(PreparedStatement)this.sql;
		if(params!=null){

			for(int i=0;i<params.length;i++){
				sql.setObject(i+1, params[i]);
			}

		}
		return sql.executeUpdate();

	}

	public Object queryFirst(Object... params) throws SQLException{
		PreparedStatement sql=(PreparedStatement)this.sql;
		if(params!=null){

			for(int i=0;i<params.length;i++){
				sql.setObject(i+1, params[i]);
			}

		}
		if(sql.execute()){
			ResultSet r=new SelectResult(sql).getCurrentResult();
			if(r.next()){
				return r.getObject(1);
			}
			return null;
		}
		else
			return null;
	}


	//�ô�����SQL��ѯ���ݿ⣬ִ��SELECT ��䣬 ������?ռλ����Ӧ�ı���
	public SelectResult query(Object... params) throws SQLException{
		PreparedStatement sql=(PreparedStatement)this.sql;
		if(params!=null){

			for(int i=0;i<params.length;i++){
				sql.setObject(i+1, params[i]);
			}

		}
		if(sql.execute()){
			return new SelectResult(sql);
		}
		else
			return null;
	}




	public SelectResult _query(Object[] params) throws SQLException{
		PreparedStatement sql=(PreparedStatement)this.sql;
		if(params!=null){

			for(int i=0;i<params.length;i++){
				sql.setObject(i+1, params[i]);
			}

		}
		if(sql.execute()){
			return new SelectResult(sql);
		}
		else
			return null;
	}


	//�������һ�δ����ĵı�׼�洢���̣�����Ϊ�洢���̵��������������ֵ�DZ�׼���ý��������洢���̴���SELECT ���Ե���SelectResultʹ��
	public StdCallResult stdCall(Object...params) throws SQLException{
		CallableStatement sql=(CallableStatement)this.sql;
		if(params!=null){
			for(int i=0;i<params.length;i++){
				sql.setObject(i+1, params[i]);
			}
		}
		sql.execute();
		sql.getResultSet();
		int count= sql.getParameterMetaData().getParameterCount();
		int a1=sql.getInt(count-1);
		String a2=sql.getString(count);
		return new StdCallResult(sql,a1,a2);
	}

	//�رջỰ
	public void close(){
		try{
			reset();
			if(con!=null){
				con.close();
				con=null;
			}
		}
		catch(Exception e){

		}
	}

	//�ֶ��ύ��ʽ�£����������ύ
	public void commit() throws SQLException{
		if(!auto){
			con.commit();
		}
	}
	//�ֶ��ύ��ʽ�£����ڻع�����
	public void rollback() throws SQLException{
		if(!auto){
			con.rollback();
		}
	}


	//�Ự�Ĺ��캯��
	public DBSession(Connection con,boolean ManulCommit) throws SQLException{
		con.setAutoCommit(!ManulCommit);
		this.auto=!ManulCommit;
		this.con=con;
	}

	public DBSession(Connection con) throws SQLException{
		this(con,false);
	}


	public static DBSession createSession(String url,String user,String password,boolean ManulCommit) throws SQLException{
		Connection con=null;
		DBSession sn=null;
		try{
			con= DriverManager.getConnection(url,user,password);
			sn=new DBSession(con,ManulCommit);
			return sn;
		}
		catch(SQLException e){
			if(sn!=null)
				sn.close();
			else
			{
				if(con!=null){
					con.close();
				}
			}
			throw e;
		}
	}





}