import java.sql.*; import java.io.*; import javax.servlet.*; import javax.servlet.http.*; interface Functions{ public void Lisa(String eesnimi, String perenimi, String kaup, String kogus, String hind, HttpServletRequest request, HttpServletResponse response) throws IOException, ServletException, SQLException ; } class Ostja implements Functions{ String driver = "com.mysql.jdbc.Driver"; String database = "jdbc:mysql://193.40.81.2/siimk"; String user = "******"; String passwd = "******"; public void Lisa(String eesnimi, String perenimi, String kaup, String kogus, String hind, HttpServletRequest request, HttpServletResponse response) throws IOException, ServletException, SQLException { response.setContentType("text/html"); PrintWriter out = response.getWriter(); try{ Class.forName(driver).newInstance(); Connection con = DriverManager.getConnection(database, user, passwd); Statement st=con.createStatement(); Statement st2=con.createStatement(); ResultSet rs=st.executeQuery("SELECT * FROM klient WHERE eesnimi='"+eesnimi+"' AND perenimi='"+perenimi+"'"); if(rs.next()){ ResultSet rs2=st2.executeQuery("SELECT * FROM kaup WHERE kaup='"+kaup+"' AND hind='"+hind+"'"); if(rs2.next()){ st.executeUpdate("INSERT INTO seosed (klient, kauba_kood, kogus) VALUES ('"+rs.getInt("id")+"', '"+rs2.getInt("id")+"', '"+Abi.filtreeriHTML(kogus)+"')"); out.println("Tehing sooritatud, seos lisatud"); }else{ st.executeUpdate("INSERT INTO kaup (kaup, hind) VALUES ('"+Abi.filtreeriHTML(kaup)+"', '"+Abi.filtreeriHTML(hind)+"')"); ResultSet rs3=st2.executeQuery("SELECT * FROM kaup WHERE kaup='"+kaup+"' AND hind='"+hind+"'"); if(rs3.next()){ st.executeUpdate("INSERT INTO seosed (klient, kauba_kood, kogus) VALUES ('"+rs.getInt("id")+"', '"+rs3.getInt("id")+"', '"+Abi.filtreeriHTML(kogus)+"')"); out.println("Tehing sooritatud, kaup ja seos lisatud"); } } }else{ st.executeUpdate("INSERT INTO klient (eesnimi, perenimi) VALUES ('"+Abi.filtreeriHTML(eesnimi)+"', '"+Abi.filtreeriHTML(perenimi)+"')"); ResultSet rs4=st.executeQuery("SELECT * FROM klient WHERE eesnimi='"+eesnimi+"' AND perenimi='"+perenimi+"'"); if(rs4.next()){ ResultSet rs5=st2.executeQuery("SELECT * FROM kaup WHERE kaup='"+kaup+"' AND hind='"+hind+"'"); if(rs5.next()){ st.executeUpdate("INSERT INTO seosed (klient, kauba_kood, kogus) VALUES ('"+rs4.getInt("id")+"', '"+rs5.getInt("id")+"', '"+Abi.filtreeriHTML(kogus)+"')"); }else{ st.executeUpdate("INSERT INTO kaup (kaup, hind) VALUES ('"+Abi.filtreeriHTML(kaup)+"', '"+Abi.filtreeriHTML(hind)+"')"); ResultSet rs6=st2.executeQuery("SELECT * FROM kaup WHERE kaup='"+kaup+"' AND hind='"+hind+"'"); if(rs6.next()){ st.executeUpdate("INSERT INTO seosed (klient, kauba_kood, kogus) VALUES ('"+rs4.getInt("id")+"', '"+rs6.getInt("id")+"', '"+Abi.filtreeriHTML(kogus)+"')"); } } } } con.close(); }catch(Exception e){ e.printStackTrace(out); } } } public class pood extends HttpServlet { public void doGet(HttpServletRequest request, HttpServletResponse response) throws IOException, ServletException { response.setContentType("text/html"); PrintWriter out = response.getWriter(); //mysql draiveri setingud String driver = "com.mysql.jdbc.Driver"; String database = "jdbc:mysql://193.40.81.2/siimk"; String user = "********"; String passwd = "********"; //alustab html'i trykki out.println(""); out.println("JSP neti pood"); out.println(""); //käivitab mysql draiveri try { Class.forName(driver).newInstance(); Connection con = DriverManager.getConnection(database, user, passwd); Statement st1=con.createStatement(); Statement st2=con.createStatement(); if(request.getParameter("lisa")!=null){ Ostja uus=new Ostja(); uus.Lisa(request.getParameter("eesnimi"), request.getParameter("perenimi"), request.getParameter("kaup"), request.getParameter("kogus"), request.getParameter("hind"), request, response); } out.println("

Klientide ostud:

"); String paring= "SELECT id, eesnimi, perenimi FROM klient"; ResultSet rs2=st1.executeQuery(paring); out.println(""); out.println(""); while(rs2.next()){ out.println(""); } out.println("
KlientKaup
"+rs2.getString("eesnimi")+" "+rs2.getString("perenimi")+""); paring="SELECT kaup.kaup, kaup.id, kaup.hind, seosed.klient, seosed.kogus FROM kaup, seosed WHERE seosed.klient='"+rs2.getInt("id")+"' AND seosed.kauba_kood=kaup.id"; ResultSet rs3=st2.executeQuery(paring); while(rs3.next()){ out.println(""); } out.println("
Kaup: "+rs3.getString("kaup.kaup")+",Kogus: "+rs3.getInt("seosed.kogus")+"tk, Hind: "+rs3.getInt("seosed.kogus")*rs3.getInt("kaup.hind")+"EEK
"); con.close(); //Lisamise, otsimis form. out.println("

"); out.println("
"); out.println(""); out.println(""); out.println(""); out.println(""); out.println(""); out.println("
Eesnimi:
Perenimi:
Kaup:
Kogus:
Hind:
"); out.println(""); }catch(Exception e){ e.printStackTrace(out); } } }