» »

SQL IN JAVA

SQL IN JAVA

bojan1 ::

Zdravo imam en problem in ne vem kako bi ga rešil. Imamm dva SQL stavka:

1.
select lp.PON_KODA, lp.LPON_VREDNOST, x.LPON_VREDNOST_PASSW
from LASTPONUDNIKA as lp
inner join PONUDNIK as p on p.PON_KODA=lp.PON_KODA
inner join DEFLASTNOSTI as dl on dl.DLAS_ID=lp.DLAS_ID
inner join (select lp.PON_KODA, lp.LPON_VREDNOST as LPON_VREDNOST_PASSW
from LASTPONUDNIKA as lp
inner join PONUDNIK as p on lp.PON_KODA=p.PON_KODA
inner join DEFLASTNOSTI as dl on lp.DLAS_ID=dl.DLAS_ID
where lp.DLAS_ID = 173 and p.ELO_KODA='001'
) as x on x.PON_KODA=lp.PON_KODA
where (lp.DLAS_ID = 74) and p.ELO_KODA='001' and lp.LPON_VREDNOST LIKE 'i' and x.LPON_VREDNOST_PASSW LIKE '

Ta querry vrne:
PON_KODA LPON_VREDNOST LPON_VREDNOST_PASSW
1 bojan@fsdfs.si bojan
2 fdfd@dfa.si krneki
5 miha@fdsf.si krneki
6 miha@fdfd.si krneki fds
7 sergej@etrust.si krkr
8 miha@fd.si krneki dfsdfs

2.
declare @PONUD varchar(20)
set @PONUD= '1'
select klas.KLA_KODAPARENT, klas.KLA_KODA, klas.ELO_KODA, klas.KLA_NAZIV, dlas.DLAS_NAZIV, dlas.DLAS_LABELA, lkpon.LKPON_VREDNOST
from LASTKLASIFPON as lkpon
left outer join KLASIFPON as kpon on kpon.KPON_ID=lkpon.KPON_ID
left outer join KLASIFIKACIJA as klas on klas.KLA_KODA=kpon.KLA_KODA
left outer join DEFLASTNOSTI as dlas on dlas.ELO_KODA=klas.ELO_KODA and dlas.DLAS_ID=lkpon.DLAS_ID
left outer join PONUDNIK as p on p.PON_KODA=kpon.PON_KODA
where 1=case when @PONUD is null then 1 when @PONUD='' then 1 when kpon.PON_KODA = @PONUD then 1 else 0 end

Ta pa vrne:
DLAS_VREDNOST
KLA_KODAPARENT KLA_KODA ELO_KODA KLA_NAZIV DLAS_NAZIV DLAS_LABELA
1.03 1.03.07 003 Z3 znamka Proizvodac: BMW
1.03 1.03.07 003 Z3 model Model: Z3
1.03 1.03.07 003 Z3 tip Tip: 2.4
1.03 1.03.07 003 Z3 cena Cena: 40000
1.03 1.03.07 003 Z3 l etnik Proizveden: 2003
1.03 1.03.07 003 Z3 modleto Modelno leto:
1.03 1.03.07 003 Z3 registracija Registracija:
1.03 1.03.07 003 Z3 starost Starost:

Zdej mam pa problem v Javi pri izpisu rezultatov iz baze
Java dokument pa zgleda takole: (v njem sta vključena ta dva SQL - la)

public class PromenaOk extends si.etrust.struts.CustomTileAction {

private static Logger log = Logger.getLogger(PromenaOk.class);

/**
* @see si.etrust.struts.CustomTileAction#executeAction(ActionMapping, ActionForm, HttpServletRequest, HttpServletResponse)
*/

public String executeAction(ActionMapping mapping, ActionForm form, HttpServletRequest request, HttpServletResponse response) throws IOException, ServletException
{ //inicializacija spremenljivk
String ponudnik = request.getParameter("ponudnik");
String geslo = request.getParameter("geslo");
String ponsif = "";
//String avto = request.getParameter("avto");
if(geslo == null) {
geslo = "";
}
if(ponudnik == null) {
ponudnik = "";
}
String check = "0";
String pass = "0";

int offset = 0;
String _offsetParam = request.getParameter("offset"); if(_offsetParam !=null) offset = Integer.parseInt(_offsetParam);

//pripravim podatke iz baze

String SQLStavek_IME_PASSW = "select lp.PON_KODA, lp.LPON_VREDNOST, x.LPON_VREDNOST_PASSW ";
SQLStavek_IME_PASSW+= "from LASTPONUDNIKA as lp ";
SQLStavek_IME_PASSW+= "inner join PONUDNIK as p on p.PON_KODA=lp.PON_KODA ";
SQLStavek_IME_PASSW+= "inner join DEFLASTNOSTI as dl on dl.DLAS_ID=lp.DLAS_ID ";
SQLStavek_IME_PASSW+= "inner join (select lp.PON_KODA, lp.LPON_VREDNOST as LPON_VREDNOST_PASSW ";
SQLStavek_IME_PASSW+= "from LASTPONUDNIKA as lp ";
SQLStavek_IME_PASSW+= "inner join PONUDNIK as p on lp.PON_KODA=p.PON_KODA ";
SQLStavek_IME_PASSW+= "inner join DEFLASTNOSTI as dl on lp.DLAS_ID=dl.DLAS_ID ";
SQLStavek_IME_PASSW+= "where lp.DLAS_ID = 173 and p.ELO_KODA='001' ";
SQLStavek_IME_PASSW+= ") as x on x.PON_KODA=lp.PON_KODA ";
SQLStavek_IME_PASSW+= "where (lp.DLAS_ID = 74) and p.ELO_KODA='001' and lp.LPON_VREDNOST LIKE '"+ponudnik+"' and x.LPON_VREDNOST_PASSW LIKE '"+geslo+"'";

ValueArray oglasi_s_IME_PASSW = null;
//ValueArray oglasi_s_AVTO = null;

try
{
oglasi_s_IME_PASSW = GeneralLoader.getInstance().loadDynamic(SQLStavek_IME_PASSW, new Integer(offset), new Integer(Constants.SELECT_COMBOBOX));
} catch (SQLException e)
{
log.error("Exception at checking from oglasi.promena.LASTPONUDNIKA: "+e.getMessage());
return "databaseActionError";
}
//potrebno je spraviti rezlultate v objekt
Hashtable scri = new Hashtable();
//Če je rezultat vpisa enak nič potem ne spusti naprej
if (oglasi_s_IME_PASSW.count() == 0)
{
check = "0"; pass = "0";
scri.put("check", check);
scri.put("pass", pass);
return "usernameError";
}
//Če je rezultat vpisa vsaj nekaj, gre naprej...
else
{
//...preveri...
//...če je geslo enako geslu v bazi,potem dovoli INSERT!
if (oglasi_s_IME_PASSW.getObject(0).getAsString("LPON_VREDNOST").compareTo(ponudnik) == 0)
{
pass = "1";
scri.put("ponudnik", ponudnik);
if (oglasi_s_IME_PASSW.getObject(0).getAsString("LPON_VREDNOST_PASSW").compareTo(geslo) == 0)
{
check="1";
ponsif = oglasi_s_IME_PASSW.getObject(0).getAsString("PON_KODA");
PagedResults results = new PagedResults(scri, oglasi_s_IME_PASSW, 0, Constants.SELECT_COMBOBOX);
request.setAttribute(Constants.SEARCH_RESULTS_OBJECT, results);
}

//če ni tako ne spusti naprej
else
{
pass = "0"; check="0";

scri.put("check", check);
scri.put("pass", pass);
scri.put("ponudnik", ponudnik);
}


//Naložim SQL stavek za KLA_KODO, da dobim izpis avtomobilov in njihovih lastnosti
String SQLStavek_AVTO = "declare @PONUD varchar(20) ";
SQLStavek_AVTO += "set @PONUD= '"+ponsif+"' ";
SQLStavek_AVTO += "select klas.KLA_KODAPARENT, klas.KLA_KODA, klas.ELO_KODA, klas.KLA_NAZIV, dlas.DLAS_NAZIV, dlas.DLAS_LABELA, lkpon.LKPON_VREDNOST ";
SQLStavek_AVTO += "from LASTKLASIFPON as lkpon ";
SQLStavek_AVTO += "left outer join KLASIFPON as kpon on kpon.KPON_ID=lkpon.KPON_ID ";
SQLStavek_AVTO += "left outer join KLASIFIKACIJA as klas on klas.KLA_KODA=kpon.KLA_KODA ";
SQLStavek_AVTO += "left outer join DEFLASTNOSTI as dlas on dlas.ELO_KODA=klas.ELO_KODA and dlas.DLAS_ID=lkpon.DLAS_ID ";
SQLStavek_AVTO += "left outer join PONUDNIK as p on p.PON_KODA=kpon.PON_KODA ";
SQLStavek_AVTO += "where 1=case when @PONUD is null then 1 when @PONUD='' then 1 when kpon.PON_KODA = @PONUD then 1 else 0 end";
scri.put("ponsif", ponsif);

try {
oglasi_s_AVTO = GeneralLoader.getInstance().loadDynamic(SQLStavek_AVTO, new Integer(offset), new Integer(Constants.SELECT_LONGCOMBOBOX));
}

catch (SQLException e)
{
log.error("Exception at checking from oglasi.promena.LASTPONUDNIKA: "+e.getMessage());
return "databaseActionError";
}
//}
}}
if (check.compareTo("1") == 0) {
PagedResults results_avto = new PagedResults(scri, oglasi_s_AVTO, 0, Constants.SELECT_LONGCOMBOBOX);
//request.setAttribute("avto", results_avto);
request.setAttribute(Constants.SEARCH_RESULTS_OBJECT, results_avto);
return "success";
}
else {
return "gesloError";
}
}
}

Problem imam pri PagedResults, ker se mi zgleda rezultata od obeh SQL stavkov prevozita in mi pri izpisu pokaže prazno stran in ne rezultatov iz baze!

Upam da si bo kdo vzel čas


LP, Bojan


Vredno ogleda ...

TemaSporočilaOglediZadnje sporočilo
TemaSporočilaOglediZadnje sporočilo
»

TSQL rekurzivno

Oddelek: Programiranje
11918 (657) hamez66
»

[SQL] Pohitritev izpisa

Oddelek: Programiranje
252962 (1861) kuall
»

[SQL] teževa pri iskanju zapisov

Oddelek: Programiranje
102115 (1705) lopow
»

SQL - izpit

Oddelek: Programiranje
102045 (1069) FTad
»

SQL problem

Oddelek: Programiranje
51271 (1143) darh

Več podobnih tem