📄 database.java
字号:
package ma.bmce.tools;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.NumberFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleTypes;
import oracle.sql.CHAR;
import oracle.sql.DATE;
import oracle.sql.NUMBER;
public class DataBase {
public Connection connection(){
Connection connect = null;
try{
Class.forName("oracle.jdbc.driver.OracleDriver" );
connect = (Connection) DriverManager.getConnection("jdbc:oracle:thin:@192.42.172.3:1523:pcardg", "BMCE_DEV", "pcard001" );
System.out.println("Connexion OK !!" );
}
catch (ClassNotFoundException ex)
{
System.out.print("Classe Introuvable " + ex.getMessage());
}
catch (SQLException ex)
{
System.out.print("Connexion Impossible " + ex.getMessage());
}
finally
{
System.out.print("!!" );
}
return connect;
}
public static List<String> GET_CARD_INFO1 (String p_card_number){
int returnVal=-3;
String p_code_reponse=null;
DATE p_datcrt=null;
String p_codbqe=null;
String p_prp_bqecpt=null;
String p_codagence=null;
String p_nomprenom=null;
String p_adrs1=null;
String p_adrs2=null;
String p_adrs3=null;
Date p_datdebut=null;
Date p_datfin =null;
String p_etat=null;
CHAR p_etatlsn=null;
String p_ribport=null;
CHAR p_vip=null;
CHAR p_renew=null;
String p_typcrt=null;
String p_typecarte=null;
String p_catsoc=null;
String p_nordr=null;
CHAR p_upstat=null;
String p_titre=null;
String p_ville=null;
Connection con = null;
OracleCallableStatement oraPreStat=null;
try {
con = new DataBase().connection();
oraPreStat = (oracle.jdbc.OracleCallableStatement) con.prepareCall("BEGIN ? := PCRD_BMCE_JAVA_TOOLS.GET_CARD_INFO(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);END;");
oraPreStat.setString(2,p_card_number);
oraPreStat.registerOutParameter(1,OracleTypes.INTEGER);
oraPreStat.registerOutParameter(3,OracleTypes.VARCHAR);
oraPreStat.registerOutParameter(4,OracleTypes.DATE);
oraPreStat.registerOutParameter(5,OracleTypes.VARCHAR);
oraPreStat.registerOutParameter(6,OracleTypes.VARCHAR);
oraPreStat.registerOutParameter(7,OracleTypes.VARCHAR);
oraPreStat.registerOutParameter(8,OracleTypes.VARCHAR);
oraPreStat.registerOutParameter(9,OracleTypes.VARCHAR);
oraPreStat.registerOutParameter(10,OracleTypes.VARCHAR);
oraPreStat.registerOutParameter(11,OracleTypes.VARCHAR);
oraPreStat.registerOutParameter(12,OracleTypes.DATE);
oraPreStat.registerOutParameter(13,OracleTypes.DATE);
oraPreStat.registerOutParameter(14,OracleTypes.VARCHAR);
oraPreStat.registerOutParameter(15,OracleTypes.CHAR);
oraPreStat.registerOutParameter(16,OracleTypes.VARCHAR);
oraPreStat.registerOutParameter(17,OracleTypes.CHAR);
oraPreStat.registerOutParameter(18,OracleTypes.CHAR);
oraPreStat.registerOutParameter(19,OracleTypes.VARCHAR);
oraPreStat.registerOutParameter(20,OracleTypes.VARCHAR);
oraPreStat.registerOutParameter(21,OracleTypes.VARCHAR);
oraPreStat.registerOutParameter(22,OracleTypes.VARCHAR);
oraPreStat.registerOutParameter(23,OracleTypes.CHAR);
oraPreStat.registerOutParameter(24,OracleTypes.VARCHAR);
oraPreStat.registerOutParameter(25,OracleTypes.VARCHAR);
oraPreStat.executeUpdate();
p_code_reponse = (String) oraPreStat.getString(3);
p_datcrt= oraPreStat.getDATE(4);
p_codbqe=(String) oraPreStat.getString(5);
p_prp_bqecpt =(String) oraPreStat.getString(6);
p_codagence= (String) oraPreStat.getString(7);
p_nomprenom=(String) oraPreStat.getString(8);
p_adrs1=(String) oraPreStat.getString(9);
p_adrs2=(String) oraPreStat.getString(10);
p_adrs3=(String) oraPreStat.getString(11);
p_datdebut=(Date)oraPreStat.getDATE(12).dateValue();
System.out.println("la date="+p_datdebut);
p_datfin =(Date)oraPreStat.getDATE(13).dateValue();
p_etat =(String) oraPreStat.getString(14);
p_etatlsn=oraPreStat.getCHAR(15);
p_ribport=(String) oraPreStat.getString(16);
p_vip =oraPreStat.getCHAR(17);
p_renew=oraPreStat.getCHAR(18);
p_typcrt=(String) oraPreStat.getString(19);
p_typecarte=(String) oraPreStat.getString(20);
p_catsoc=(String) oraPreStat.getString(21);
p_nordr=(String) oraPreStat.getString(22);
p_upstat=oraPreStat.getCHAR(23);
p_titre=(String) oraPreStat.getString(24);
p_ville =(String) oraPreStat.getString(25);
returnVal =oraPreStat.getInt(1);
System.out.println("La valeur de retour est: "+returnVal);
} catch (SQLException e) {
e.printStackTrace();
} finally{
try { con.close(); }
catch (Exception e) {
e.printStackTrace();}}
/*
* remplire la liste de retour
*/
List<String> retour=new ArrayList<String>();
//0
retour.add(""+returnVal);
//1
retour.add(p_code_reponse);
//2
retour.add(""+p_datcrt);
//3
retour.add(p_codbqe);
//4
retour.add(p_prp_bqecpt);
//5
retour.add(p_codagence);
//6:nomprenom
retour.add(p_nomprenom );
//7
retour.add(p_adrs1);
//8
retour.add(p_adrs2);
//9
retour.add(p_adrs3);
//10: date de debut
retour.add(""+p_datdebut);
//11: date de fin
retour.add(""+p_datfin);
//12: statut carte
retour.add(p_etat);
//13
retour.add(""+p_etatlsn);
//14
retour.add(p_ribport);
//15
retour.add(""+p_vip);
//16
retour.add(""+p_renew);
//17
retour.add(p_typcrt);
//18: type carte
retour.add(p_typecarte);
//19
retour.add(p_catsoc);
//20
retour.add(p_nordr);
//21
retour.add(""+p_upstat);
//22
retour.add(p_titre);
//23
retour.add(p_ville);
return retour;
}
public List<String> GET_CARD_LIST1(String p_account_number){
int returnVal=-3;
String p_code_reponse=null;
NUMBER p_nombre_carte = null;
String p_card_number_1=null;
String p_card_number_2 =null;
String p_card_number_3=null;
String p_card_number_4=null;
String p_card_number_5=null;
Connection con = null;
OracleCallableStatement oraPreStat=null;
try {
con = new DataBase().connection();
oraPreStat = (oracle.jdbc.OracleCallableStatement) con.prepareCall("BEGIN ? := PCRD_BMCE_JAVA_TOOLS.GET_CARD_LIST(?,?,?,?,?,?,?,?);END;");
oraPreStat.setString(2,p_account_number);
oraPreStat.registerOutParameter(1,OracleTypes.INTEGER);
oraPreStat.registerOutParameter(3,OracleTypes.VARCHAR);
oraPreStat.registerOutParameter(4,OracleTypes.NUMBER);
oraPreStat.registerOutParameter(5,OracleTypes.VARCHAR);
oraPreStat.registerOutParameter(6,OracleTypes.VARCHAR);
oraPreStat.registerOutParameter(7,OracleTypes.VARCHAR);
oraPreStat.registerOutParameter(8,OracleTypes.VARCHAR);
oraPreStat.registerOutParameter(9,OracleTypes.VARCHAR);
oraPreStat.executeUpdate();
p_code_reponse = (String) oraPreStat.getString(3);
NumberFormat nf = NumberFormat.getInstance();
p_nombre_carte=oraPreStat.getNUMBER(4);
p_card_number_1= (String) oraPreStat.getString(5);
p_card_number_2= (String) oraPreStat.getString(6);
p_card_number_3= (String) oraPreStat.getString(7);
p_card_number_4= (String) oraPreStat.getString(8);
p_card_number_5= (String) oraPreStat.getString(9);
returnVal=oraPreStat.getInt(1);
} catch (SQLException e) {
e.printStackTrace();
} finally{
try { con.close(); }
catch (Exception e) {
e.printStackTrace();}}
List<String> retour=new ArrayList<String>();
retour.add(""+returnVal);
retour.add(""+p_nombre_carte);
retour.add(p_card_number_1);
retour.add(p_card_number_2);
retour.add(p_card_number_3);
retour.add(p_card_number_4);
retour.add(p_card_number_5);
System.out.println("le retour est :"+retour.get(0));
/*System.out.println("toto="+p_nombre_carte);
for(int i=0;i<retour.size();i++)
{
System.out.println(retour.get(i));
}*/
return retour;
}
public static void main(String[] args) throws ParseException
{
DateFormat df = new SimpleDateFormat("dd-MM-yyyy");
try
{
Date today = df.parse("20-12-2005");
System.out.println("Today = " + df.format(today));
} catch (ParseException e)
{
e.printStackTrace();
}
}
public static List<String> GET_ACCOUNT_INFO1(String p_account_number){
String p_code_reponse =null;
String p_devise=null;
Date p_date_ouverture=null;
String p_raison_sociale=null;
String p_complement_rs=null;
String p_adresse1_residence=null;
String p_adresse2_residence=null;
String p_ville_residence=null;
CHAR p_separateur=null;
String p_pays_courrier=null;
CHAR p_courrier_garde=null;
String p_tel_domicile =null;
String p_code_postal=null;
String p_ville_courrier=null;
String p_espace=null;
String p_racine_charge_clientele=null;
CHAR p_situation_matrimoniale=null;
String p_num_ide=null;
CHAR p_attribut_res=null;
String p_type_client=null;
String p_capital=null;
String p_dirigeant=null;
String p_fonction_dirigeant=null;
String p_statut_compte=null;
String p_code_secteur_act=null;
CHAR p_code_segment =null;
String p_contentieux=null;
CHAR p_espace_1=null;
int returnVal=-3;
Connection con = null;
OracleCallableStatement oraPreStat=null;
try {
con = new DataBase().connection();
oraPreStat = (oracle.jdbc.OracleCallableStatement) con.prepareCall("BEGIN ? := PCRD_BMCE_JAVA_TOOLS.GET_ACCOUNT_INFO(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);END;");
oraPreStat.setString(2,p_account_number);
oraPreStat.registerOutParameter(1,OracleTypes.INTEGER);
oraPreStat.registerOutParameter(3,OracleTypes.VARCHAR);
oraPreStat.registerOutParameter(4,OracleTypes.VARCHAR);
oraPreStat.registerOutParameter(5,OracleTypes.DATE);
oraPreStat.registerOutParameter(6,OracleTypes.VARCHAR);
oraPreStat.registerOutParameter(7,OracleTypes.VARCHAR);
oraPreStat.registerOutParameter(8,OracleTypes.VARCHAR);
oraPreStat.registerOutParameter(9,OracleTypes.VARCHAR);
oraPreStat.registerOutParameter(10,OracleTypes.VARCHAR);
oraPreStat.registerOutParameter(11,OracleTypes.CHAR);
oraPreStat.registerOutParameter(12,OracleTypes.VARCHAR);
oraPreStat.registerOutParameter(13,OracleTypes.CHAR);
oraPreStat.registerOutParameter(14,OracleTypes.VARCHAR);
oraPreStat.registerOutParameter(15,OracleTypes.VARCHAR);
oraPreStat.registerOutParameter(16,OracleTypes.VARCHAR);
oraPreStat.registerOutParameter(17,OracleTypes.VARCHAR);
oraPreStat.registerOutParameter(18,OracleTypes.VARCHAR);
oraPreStat.registerOutParameter(19,OracleTypes.CHAR);
oraPreStat.registerOutParameter(20,OracleTypes.VARCHAR);
oraPreStat.registerOutParameter(21,OracleTypes.CHAR);
oraPreStat.registerOutParameter(22,OracleTypes.VARCHAR);
oraPreStat.registerOutParameter(23,OracleTypes.VARCHAR);
oraPreStat.registerOutParameter(24,OracleTypes.VARCHAR);
oraPreStat.registerOutParameter(25,OracleTypes.VARCHAR);
oraPreStat.registerOutParameter(26,OracleTypes.VARCHAR);
oraPreStat.registerOutParameter(27,OracleTypes.VARCHAR);
oraPreStat.registerOutParameter(28,OracleTypes.CHAR);
oraPreStat.registerOutParameter(29,OracleTypes.VARCHAR);
oraPreStat.registerOutParameter(30,OracleTypes.CHAR);
oraPreStat.executeUpdate();
p_code_reponse = (String) oraPreStat.getString(3);
p_devise = (String) oraPreStat.getString(4);
p_date_ouverture=(Date) oraPreStat.getDATE(5).dateValue();
System.out.println("la date est : "+p_date_ouverture);
p_raison_sociale = (String) oraPreStat.getString(6);
p_complement_rs = (String) oraPreStat.getString(7);
p_adresse1_residence = (String) oraPreStat.getString(8);
p_adresse2_residence = (String) oraPreStat.getString(9);
p_ville_residence = (String) oraPreStat.getString(10);
p_separateur= oraPreStat.getCHAR(11);
p_pays_courrier = (String) oraPreStat.getString(12);
p_courrier_garde= oraPreStat.getCHAR(13);
p_tel_domicile = (String) oraPreStat.getString(14);
p_code_postal = (String) oraPreStat.getString(15);
p_ville_courrier = (String) oraPreStat.getString(16);
p_espace = (String) oraPreStat.getString(17);
p_racine_charge_clientele = (String) oraPreStat.getString(18);
p_situation_matrimoniale = oraPreStat.getCHAR(19);
p_num_ide = (String) oraPreStat.getString(20);
p_attribut_res= oraPreStat.getCHAR(21);
p_type_client = (String) oraPreStat.getString(22);
p_capital = (String) oraPreStat.getString(23);
p_dirigeant = (String) oraPreStat.getString(24);
p_fonction_dirigeant = (String) oraPreStat.getString(25);
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -