package br.mp.mpf;

import java.sql.*;
import br.mp.mpf.modelo.Dados;

public class Banco {

         public static void main(String[] args) throws Exception {

             Banco banco = new Banco();
             //banco.listar();

             Dados xlsdados = new Dados();
             xlsdados.setColA("OLAmunod");
             //xlsdados.setColB("TesteB");

//           banco.limparTabela("apex_spo_sao.stg_rp_carga_tg");
             banco.add(xlsdados);
             banco.listar();

          }

         private int limparTabela(String nmTabela){
             Connection conn = null;
             Statement stmt = null;
             ResultSet rs = null;
             int qtAfetados = 0;

             try {
                 conn = obterConexao();
                 stmt = conn.createStatement();
                 String sql = "DELETE FROM "+nmTabela;
                 qtAfetados = stmt.executeUpdate(sql);
                 System.out.println("Excluídos: " + qtAfetados);

             } catch (Exception e) {
               e.printStackTrace();
             }  finally {
                    try {
                        if (rs != null){
                            rs.close();
                        }
                    } catch (SQLException sqle) {// log this
                        try {
                            if (stmt != null){
                                stmt.close();
                            }
                        } catch (SQLException sqle1) {// log this
                            try {
                                if (conn != null){
                                    conn.close();
                                }
                            } catch (SQLException sqle2)  {// log this
                                System.out.println(sqle2);
                            }
                        }
                    }
             }


             return 0;
         }

         private int add(Dados dados){

             Connection conn = null;
             Statement stmt = null;
             ResultSet rs = null;
             int qtDadosAdd = 0;


             try {
                 conn = obterConexao();
                 stmt = conn.createStatement();
                 String sql = "INSERT INTO apex_spo_sao.stg_rp_carga_tg(NM_RL) VALUES ('" + dados + "')";
                 qtDadosAdd = stmt.executeUpdate(sql);
                 System.out.println("Adicionados: " + qtDadosAdd);

             } catch (Exception e) {
               e.printStackTrace();
             }  finally {
                    try {
                        if (rs != null){
                            rs.close();
                        }
                    } catch (SQLException sqle) {// log this
                        try {
                            if (stmt != null){
                                stmt.close();
                            }
                        } catch (SQLException sqle1) {// log this
                            try {
                                if (conn != null){
                                    conn.close();
                                }
                            } catch (SQLException sqle2)  {// log this
                                System.out.println(sqle2);
                            }
                        }
                    }
             }

             return 0;
         }


         private void listar(){

             Connection conn = null;
             Statement stmt = null;
             ResultSet rs = null;

               try {
                   conn = obterConexao();
                   stmt = conn.createStatement();
                   String sql = "SELECT * FROM apex_spo_sao.stg_rp_carga_tg";
                   rs = stmt.executeQuery(sql);

                   while (rs.next()) {
                       int idCarga = rs.getInt("ID_CARGA");
                       String cdRL = rs.getString("CD_RL");
                       String nmRL = rs.getString("NM_RL");
                       System.out.println("idCarga = " + "\t" + idCarga +
                                          "\t" + "cdRL = " + "\t" + cdRL +
                                          "\t" + "nmRL = " + "\t" + nmRL);
                   }

               } catch (Exception e) {
                   e.printStackTrace();
               } finally {
                    try {
                            if (rs != null){
                                System.out.println("Sem registros");
                                rs.close();
                            }
                    } catch (SQLException sqle) {// log this
                        try {
                                if (stmt != null){
                                    stmt.close();
                                }
                        } catch (SQLException sqle1) {// log this
                                try {
                                        if (conn != null){
                                            conn.close();
                                        }
                                } catch (SQLException sqle2)  {// log this
                                    System.out.println(sqle2);
                                }
                        }
                    }
               }


         }

         private static Connection obterConexao() {
                   Connection conexao = null;
                   try {
                        Class.forName("oracle.jdbc.driver.OracleDriver");
                        conexao = DriverManager.getConnection(
                                "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=db1cluster.pgr.mpf.mp.br)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=adm.pgr.mpf.mp.br)))", "APEX_SPO_SAO_CNS", "pacee474");
                                System.out.println("Conectado");
                   } catch (ClassNotFoundException e) {
                        System.out.println ("Classe JDBC Driver nao encontrada.\n\n");
                        return null;
                        //e.printStackTrace();
                   } catch (SQLException e) {
                       System.out.println ("Erro ao se conectar com o banco de dados.\n\n");
                       return null;
                     //e.printStackTrace();
                   }
                   return conexao;
         }
}