/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package jdbc.beszamolo.p810;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

/**
 *
 * @author kiraly4
 */
public class Beszamolo {

    private Connection conn = null;
    private CallableStatement cs = null;
    private Statement s = null;
    private ResultSet rs = null;
    private String url = "jdbc:oracle:thin:@193.6.5.42:1521:info";
    private String user = "fh4koe";
    private String pass = "fh4koe";


    public Beszamolo()
    {
        connect();
    }

    private void connect(){
     try {

        conn = DriverManager.getConnection(url, user, pass);
     } catch(Exception e) {
        System.err.println(e.getMessage());
       }
     }

    public void disconnect()
    {
        try{
            conn.close();
        }catch(Exception e){
             System.err.println(e.getMessage());
        }
    }

    public int insert(int isbn, String sz_kod, String kategoria, int ar)
    {
        String statement = "INSERT INTO konyv VALUES (?,?,?,?)";

        try{
        cs = conn.prepareCall(statement);
        cs.setInt(1, isbn);
        cs.setString(2,sz_kod);
        cs.setString(3,kategoria);
        cs.setInt(4,ar);
        cs.executeQuery();
        return 1;
        }catch(Exception e )
        {
            System.err.println(e.getMessage());
            return -1;
        }finally{
            try{
            cs.close();
            }catch(Exception e){
                System.err.println(e.getMessage());
            }
        }
    }

    public int select()
    {
        String statement = "SELECT * FROM konyv";
        int count = 0;

        try{
            s = conn.createStatement();
            s.executeQuery(statement);
            rs = s.getResultSet();

            System.out.println("ISBN \t SZKOD \t KAT \t AR");
            while(rs.next())
            {
                count++;
                System.out.println(rs.getInt("isbn")+" \t "+rs.getString("sz_kod")+" \t "+rs.getString("kategoria")+" \t "+rs.getInt("ar"));

            }
            System.out.println("------------------");


            return count;
        }catch(Exception e)
        {
            System.err.println(e.getMessage());
            return -1;
        }finally{
            try{
            rs.close();
            }catch(Exception e){
                System.err.println(e.getMessage());
            }
        }
    }

    public String callProcedure(int isbn)
    {
        String statement = "call konyv_keres(?,?)";
        try{
            cs = conn.prepareCall(statement);
            cs.setInt(1, isbn);
            cs.registerOutParameter(2, java.sql.Types.CHAR);
            cs.executeQuery();
            String azon = cs.getString(2);
            return azon;

        }catch(Exception e)
        {
            System.err.println(e.getMessage());
            return "";
        }finally{
            try{
            cs.close();
            }catch(Exception e){
                System.err.println(e.getMessage());
            }
        }
    }

    public int callFunction(String kategoria)
    {
        String statement = "? = call kat_osszar(?)";
        try{
            cs = conn.prepareCall(statement);
            cs.registerOutParameter(1, java.sql.Types.NUMERIC);
            cs.setString(2, kategoria);
            cs.executeQuery();
            int osszar = cs.getInt(1);
            return osszar;

        }catch(Exception e)
        {
            System.err.println(e.getMessage());
            return -1;
        }finally{
            try{
            cs.close();
            }catch(Exception e){
                System.err.println(e.getMessage());
            }
        }
    }

    public static void main(String args[])
    {
        Beszamolo besz = new Beszamolo();

        besz.insert(109,"C111","T", 3000);
        besz.select();
        System.out.println("callProcedure: "+besz.callProcedure(109));
        System.out.println("callFunction: "+besz.callFunction("S"));

        besz.disconnect();

    }
}