package br.com.phpbrasil.artigo;

import java.sql.*;

public class ConexaoDB {
    public static Connection getConexao() {
        Connection con = null;
        try {
            String driver = "org.gjt.mm.mysql.Driver";
            String user = "root";
            String password = "psntmoyc";
            String host = "jdbc:mysql://localhost:3306/phpbrasil_artigo";
		
            Class.forName(driver);
            con = DriverManager.getConnection(host,user,password);
        } catch (SQLException ex) {
            System.out.println(ex);
        } catch (Exception ex) {
            System.out.println(ex);
        }
        return con;
    }

    public static void close(Connection con){
        try {
            con.close();
        } catch(Exception ex){
            System.out.println(ex);
        }
    }
}

CREATE TABLE `pt_usuario` (
  `id_usuario` int(10) unsigned NOT NULL auto_increment,
  `usuario` varchar(32) NOT NULL default '0',
  `senha` varchar(20) NOT NULL default '0',
  `ativo` char(3) NOT NULL default '0',
  PRIMARY KEY  (`id_usuario`,`usuario`)
) TYPE=INNODB;

CREATE TABLE `menu` (
  `id_menu` int(10) unsigned NOT NULL auto_increment,
  `nome_link` varchar(255) NOT NULL default '0',
  `path_link` varchar(255) NOT NULL default '0',
  `ativo` char(3) NOT NULL default '0',
  PRIMARY KEY  (`id_menu`)
) TYPE=INNODB;

CREATE TABLE `permissao` (
  `id_menu` int(10) NOT NULL default '0',
  `id_usuario` int(10) NOT NULL default '0',
  `boolean_permissao` char(3) NOT NULL default '0',
  PRIMARY KEY  (`id_menu`,`id_usuario`)
) TYPE=INNODB;

INSERT INTO pt_usuario(usuario,senha,ativo) VALUES("phpbrasil","YXJ0aWdv","sim");
INSERT INTO pt_usuario(usuario,senha,ativo) VALUES("timao","dGltYW8=","sim");
INSERT INTO pt_usuario(usuario,senha,ativo) VALUES("chapolin","Y2hhcG9saW4=","sim");

INSERT INTO menu(nome_link, path_link, ativo) VALUES("Segurança","seguranca.php","sim");
INSERT INTO menu(nome_link, path_link, ativo) VALUES("Corinthians","corinthians.php","sim");
INSERT INTO menu(nome_link, path_link, ativo) VALUES("Chaves e Chapolin","prasempre_ch.php","sim");

INSERT INTO permissao(id_menu, id_usuario, boolean_permissao) VALUES("1","1","sim");
INSERT INTO permissao(id_menu, id_usuario, boolean_permissao) VALUES("2","1","sim");
INSERT INTO permissao(id_menu, id_usuario, boolean_permissao) VALUES("3","1","sim");

INSERT INTO permissao(id_menu, id_usuario, boolean_permissao) VALUES("1","2","nao");
INSERT INTO permissao(id_menu, id_usuario, boolean_permissao) VALUES("2","2","sim");
INSERT INTO permissao(id_menu, id_usuario, boolean_permissao) VALUES("3","2","nao");

INSERT INTO permissao(id_menu, id_usuario, boolean_permissao) VALUES("1","3","nao");
INSERT INTO permissao(id_menu, id_usuario, boolean_permissao) VALUES("2","3","nao");
INSERT INTO permissao(id_menu, id_usuario, boolean_permissao) VALUES("3","3","nao");

    public static void main(String php[]) {
        final Connection connection = ConexaoDB.getConexao();
        try {
            String pt_usuario = "Mostra os usuarios da Tabela PT_USUARIO\n";
            PreparedStatement preparedStatement = connection.prepareStatement("SELECT usuario, senha FROM pt_usuario");
            ResultSet resultSet = preparedStatement.executeQuery();
            int i = 0;
            while(resultSet.next()){
                pt_usuario += "USUARIO: "+resultSet.getString(1) + "\n";
                i++;
            }
            if (i == 0){
                pt_usuario = "Não foi encontrado nemhum valor.";
            }
            preparedStatement.close();
            resultSet.close();
            System.out.println(pt_usuario);
        } catch(SQLException e) {
            System.out.println(e);
        } finally {
            ConexaoDB.close(connection);
        }
    }