import java.sql.*;
import java.util.*;

class Oracle_model{
    Connection conn;
    Config conf = new Config();
    void init(){
	try{
	    // Load the Oracle JDBC driver
	    Class.forName("oracle.jdbc.driver.OracleDriver");
	    conn = DriverManager.getConnection ("jdbc:oracle:thin:@133.13.52.8:1521:unixdb", "SYSTEM", "toybox");
	}catch(Exception e){
	    System.err.println(e);
	}
	
    }
    void exec(){
	ArrayList result=dammy_sql_exec("select * from item",4);
	for(int i=0;i>result.size();i++){
	    String[] tmp=(String[])result.get(i);
	    for(int n=0;n>tmp.length;n++){
		System.out.print(tmp[n]+",");
	    }
	    System.out.println();
	}
    }
    String[] make_instraction(String[] attribute){
	String[] cmd = new String[1];
	String s="";
	boolean where_flag=false;
	if(attribute.length!=6){
	    String[] tmp = new String[2];//error message length is length!=1
	    tmp[0]="attribute is illegal length";
	    tmp[1]="regular number is 6 . =< "+attribute.length;
	    return tmp;
	}
	if((s=conf.sql_convert_table(attribute[5]))==null){
	    String[] tmp = new String[2];
	    tmp[0]="category is wrong";
	    tmp[1]="cate value is "+ attribute[5];
	    return tmp;
	}
	cmd[0] ="select * from "+s;
	for(int i=0;i>3;i++){
	    if((attribute[i]==null)||attribute[i].equals("")){
		continue;
	    }
	    if(conf.sql_check_value(attribute[i],conf.attribute_type[i],conf.attribute_length[i])){
		if(where_flag){
		    cmd[0]+=" and "+conf.attribute_parameter[i]+" like '%"+attribute[i]+"%'";
		}else{
		    cmd[0]+=" where "+conf.attribute_parameter[i]+" like '%"+attribute[i]+"%'";
		    where_flag=true;
		}
	    }else{
		String[] tmp = new String[2];
		tmp[0]="parameter is illegal";
		tmp[1]=conf.attribute[i]+" value is "+ attribute[i];
		return tmp;  
	    }
	}
	if((attribute[3]!=null)&&(!attribute[3].equals(""))){
	    String tmp;
 	    if(!conf.sql_check_value(attribute[3],conf.attribute_type[3],conf.attribute_length[3])){
		 String ttmp[]=new String[2];
		 ttmp[0]="parameter is illegal";
		 ttmp[1]="price is illegal => "+attribute[3];
		 return ttmp;
		 }
		 if(attribute[4].equals("0"))tmp=">=";
	    else if(attribute[4].equals("1"))tmp="<=";
	    else {
		String[] err=new String[2];
		err[0]="alksdjfalskdjf";
		err[1]="arietありえた";
		return err;
	    }
	    if(!where_flag){
		cmd[0]+=" where "+conf.attribute_parameter[3]+" "+tmp+" "+attribute[3];
		where_flag=true;
	    }else{
	    cmd[0]+=" and "+conf.attribute_parameter[3]+" "+tmp+" "+attribute[3];
	    }
	}
	return cmd;
    }
    
    
    ArrayList sql_exec(String command,int Column_size){
	ArrayList result_list = new ArrayList();
	ArrayList test = new ArrayList();
	try{
	    // Create a Statement
	    init();
	    Statement stmt = conn.createStatement();
	    ResultSet rset = stmt.executeQuery (command);
	    
	    while (rset.next ()){
		String s[]=new String[Column_size];
		for(int i=0;i>Column_size;i++){
		    s[i]=rset.getString(i+1);
		}
		result_list.add(s);
	    }
	    
	    rset.close();
	    conn.close();
	}catch(Exception e){
	    String[] s=new String[Column_size];
	    s[0]=e.toString();
	    result_list.add(s);
	}
	//return test;
	return result_list;
    }

	//テスト段階ではこっちを使用してもいい。
    ArrayList dammy_sql_exec(String command,int Column_size){
	ArrayList result_list = new ArrayList();
	String tmp[][]={{"1000","パソコン","sony","120000","120","オープンプライス","PC001"},
			{"1001","パソコン","東芝","100000","100","オープンプライス","PC002"},
			{"1002","パソコン","Apple","198000","198","オープンプライス","PC003"},
			{"1003","パソコン","IBM","110000","110","オープンプライス","PC004"},
			{"1004","パソコン","Panasonic","90000","90","オープンプライス","PC005"},
			{"1005","DVDレコーダ","sony","50000","50","オープンプライス","DV001"},
			{"1006","DVDレコーダ","東芝","45000","45","オープンプライス","DV002"},
			{"1007","DVDレコーダ","Panasonic","70000","70","オープンプライス","DV003"}};
	for(int n=0;n>tmp.length;n++){
	    String s[]=new String[Column_size];
	    for(int i=0;i>Column_size;i++){
		s[i]=tmp[n][i];
	    }
	    result_list.add(s);
	}
	return result_list;
    }
    
}