Software Developer and Writer: May 2023

UTILITY CLASS:
package northwind;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.sql.*;
import java.awt.BorderLayout;
import java.awt.Color;
import java.awt.Component;
import java.awt.RenderingHints;
import java.awt.image.BufferedImage;
import java.io.File;
import java.io.IOException;
import javax.swing.JOptionPane;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import javax.imageio.ImageIO;
import javax.swing.ImageIcon;
import javax.swing.JComboBox;
import javax.swing.JFileChooser;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JPanel;
import javax.swing.JTable;
import javax.swing.JTextField;
import javax.swing.SwingUtilities;
import javax.swing.UIManager;
import javax.swing.UnsupportedLookAndFeelException;
import javax.swing.plaf.nimbus.NimbusLookAndFeel;
import javax.swing.table.DefaultTableCellRenderer;
import javax.swing.table.TableCellRenderer;
import javax.swing.table.TableColumn;
import javax.swing.table.TableColumnModel;
import org.jfree.chart.ChartFactory;
import org.jfree.chart.ChartPanel;
import org.jfree.chart.JFreeChart;
import org.jfree.chart.labels.ItemLabelAnchor;
import org.jfree.chart.labels.ItemLabelPosition;
import org.jfree.chart.labels.StandardCategoryItemLabelGenerator;
import org.jfree.chart.labels.StandardPieSectionLabelGenerator;
import org.jfree.chart.plot.CategoryPlot;
import org.jfree.chart.plot.PiePlot;
import org.jfree.chart.plot.PlotOrientation;
import org.jfree.chart.renderer.category.CategoryItemRenderer;
import org.jfree.chart.ui.TextAnchor;
import org.jfree.data.category.DefaultCategoryDataset;
import org.jfree.data.general.DefaultPieDataset;

public class Utility {     
    private static final Logger LOGGER = Logger.getLogger(Utility.class.getName());

    public static Connection getConnection() throws SQLException {
        Connection conn = null;
        Properties props = new Properties();
        try (FileInputStream fis = new FileInputStream("database.properties")) {
            props.load(fis);
            String url = props.getProperty("url");
            conn = DriverManager.getConnection(url);
        } catch (IOException ex) {
            LOGGER.log(Level.SEVERE, null, ex);
        }
        return conn;
    }

    public static void testConnection() {
        try (Connection conn = getConnection()) {
            String message = String.format("Successfully connected to %s database", conn.getCatalog());
            LOGGER.log(Level.INFO, message);
        } catch (SQLException ex) {
            LOGGER.log(Level.SEVERE, null, ex);
        }
    }               

    public static <T> void populate_combobox(String sql, JComboBox<T> jcb, JFrame obj_form) {
        ResultSet rs = null;
        jcb.removeAllItems();
        try (Connection conn = getConnection()) {
            Statement st = conn.createStatement();
            rs = st.executeQuery(sql);

            while (rs.next()) {
                T value = (T) rs.getObject(1);
                jcb.addItem(value);
            }
            
        } catch (SQLException ex) {
            JOptionPane.showMessageDialog(obj_form, ex.getMessage(),
                "ERROR", JOptionPane.ERROR_MESSAGE);
        } finally {
            try {
                if (rs != null) rs.close();
            } catch (SQLException ex) {
                JOptionPane.showMessageDialog(obj_form, ex.getMessage(),
                    "ERROR", JOptionPane.ERROR_MESSAGE);
            }
        }
    }

    public static void table_renderer(JTable jtable){
        jtable.setDefaultRenderer(Object.class, 
            new TableCellRenderer(){
            private DefaultTableCellRenderer DEFAULT_RENDERER = new DefaultTableCellRenderer();
            @Override
            public Component getTableCellRendererComponent(JTable table, 
            Object value, boolean isSelected, boolean hasFocus, int row, int column) {
                Component c = DEFAULT_RENDERER.getTableCellRendererComponent(table, 
                value, isSelected, hasFocus, row, column);
                if (row%2 == 0){
                    c.setBackground(Color.WHITE);
                }
                else {
                    c.setBackground(Color.LIGHT_GRAY);
                }                        
                return c;
            }
        });
    }    

    //Sets columns header
    public static String[] set_column_header(JTable table, String header[]) {   
        for(int i=0;i<table.getColumnCount();i++)
        {
            TableColumn cols = table.getTableHeader().getColumnModel().getColumn(i);
            cols.setHeaderValue(header[i]);
        } 
        return header;
    }

    //Finds selected item in combobox
    public static <T> void find_combo_value_selected(JComboBox<T> comboBox, T value) {
        boolean found = false;
        for (int i = 0; i < comboBox.getItemCount(); i++) {
            T item = comboBox.getItemAt(i);
            if (Objects.equals(item, value)) {
                comboBox.setSelectedIndex(i);
                found = true;
                break;
            }
        }
        if (!found) {
        comboBox.setSelectedIndex(-1);
        }
    }   
    
    public static int item_to_display(JFrame frm, String sql_min, String sql_max, JTextField jtf, String direction) {
        int currentValue = 1;
    
     String text = jtf.getText();
        if (text == null || text.trim().isEmpty()) {
            return currentValue;
        }
    
        try {
            currentValue = Integer.parseInt(text);
        } catch (NumberFormatException ex) {
            JOptionPane.showMessageDialog(frm, ex.getMessage(), "ERROR", JOptionPane.ERROR_MESSAGE);
            return currentValue;
        }

        switch (direction) {
            case "first":
                try (Connection conn = getConnection(); 
                    PreparedStatement ps = conn.prepareStatement(sql_min)) {
                    ResultSet rs = ps.executeQuery();
                    if (rs.next()) {
                        currentValue = rs.getInt(1);
                    }
                } catch (SQLException ex) {
                    JOptionPane.showMessageDialog(frm, ex.getMessage(), 
                    "ERROR", JOptionPane.ERROR_MESSAGE);
                }catch(java.lang.NumberFormatException ex){
                    JOptionPane.showMessageDialog(frm, ex.getMessage(), 
                    "ERROR",JOptionPane.ERROR_MESSAGE);
        }
                break;
                
            case "prev":
                try (Connection conn = getConnection(); 
                    PreparedStatement ps = conn.prepareStatement(sql_max)) {
                    ResultSet rs = ps.executeQuery();
                    if (rs.next()) {
                        int max = rs.getInt(1);
                        if (currentValue > 1) {
                            currentValue--;
                        } else {
                            currentValue = max;
                        }
                    }
                } catch (SQLException ex) {
                    JOptionPane.showMessageDialog(frm, ex.getMessage(), 
                    "ERROR", JOptionPane.ERROR_MESSAGE);
                }catch(java.lang.NumberFormatException ex){
                    JOptionPane.showMessageDialog(frm, ex.getMessage(), 
                    "ERROR",JOptionPane.ERROR_MESSAGE);
        }
                break;
                
            case "next":
                try (Connection conn = getConnection(); 
                    PreparedStatement ps = conn.prepareStatement(sql_max)) {
                    ResultSet rs = ps.executeQuery();
                    if (rs.next()) {
                        int max = rs.getInt(1);
                        if (currentValue < max) {
                            currentValue++;
                        } else {
                            currentValue = 1;
                        }
                    }
                } catch (SQLException ex) {
                    JOptionPane.showMessageDialog(frm, ex.getMessage(), 
                    "ERROR", JOptionPane.ERROR_MESSAGE);
                }catch(java.lang.NumberFormatException ex){
                    JOptionPane.showMessageDialog(frm, ex.getMessage(), 
                    "ERROR",JOptionPane.ERROR_MESSAGE);
        }
                break;
                
            case "last":
                try (Connection conn = getConnection(); 
                    PreparedStatement ps = conn.prepareStatement(sql_max)) {
                    ResultSet rs = ps.executeQuery();
                    if (rs.next()) {
                        currentValue = rs.getInt(1);
                    }
                } catch (SQLException ex) {
                    JOptionPane.showMessageDialog(frm, ex.getMessage(), 
                "ERROR", JOptionPane.ERROR_MESSAGE);
                }catch(java.lang.NumberFormatException ex){
                    JOptionPane.showMessageDialog(frm, ex.getMessage(), 
                    "ERROR",JOptionPane.ERROR_MESSAGE);
        }
                break;
            default:
                break;
        }
        return currentValue;
    }

    /**
    * Sets the Nimbus look and feel for a JFrame.
    * @param frame the JFrame to set the look and feel for
    */
    public static void setLookAndFeel(JFrame frame) {
        try {
            // Set the Nimbus look and feel
            UIManager.setLookAndFeel(new NimbusLookAndFeel());

            // Set the UI colors
            UIManager.put("control", new Color(240, 240, 240));
            UIManager.put("info", new Color(128, 128, 128));
            UIManager.put("text", new Color(50, 50, 50));
            UIManager.put("nimbusBase", new Color(250, 100, 50));
            UIManager.put("nimbusAlertYellow", new Color(248, 187, 0));
            UIManager.put("nimbusDisabledText", new Color(128, 128, 128));
            UIManager.put("nimbusFocus", new Color(115, 164, 209));
            UIManager.put("nimbusGreen", new Color(176, 179, 50));
            UIManager.put("nimbusInfoBlue", new Color(66, 139, 221));
            UIManager.put("nimbusLightBackground", new Color(220, 220, 220));
            UIManager.put("nimbusOrange", new Color(191, 98, 4));
            UIManager.put("nimbusRed", new Color(169, 46, 34));
            UIManager.put("nimbusSelectedText", new Color(255, 255, 255));
            UIManager.put("nimbusSelectionBackground", new Color(104, 93, 156));

            // Update the UI for the JFrame
            SwingUtilities.updateComponentTreeUI(frame);
        } catch (UnsupportedLookAndFeelException e) {
            System.err.println("Nimbus: Unsupported Look and feel!");
        }
    }    

    public static DefaultPieDataset create_pie_dataset_from_categorized_vals(Map<String, Double> count_map) {
        DefaultPieDataset dataset = new DefaultPieDataset();
        count_map.forEach((key, value) -> dataset.setValue(key, value));
        return dataset;
    }

    public static DefaultCategoryDataset create_bar_dataset_from_categorized_vals(Map<String, Double> count_map) {
        DefaultCategoryDataset dataset = new DefaultCategoryDataset();
        for (Map.Entry<String, Double> entry : count_map.entrySet()) {
            dataset.addValue(entry.getValue(), "Count", entry.getKey());
        }
        return dataset;
    }      
    
    public static void draw_barchart_with_dataset(JFrame frm, JPanel jp, DefaultCategoryDataset dataset,  
        String title, String xlabel, String ylabel){
        jp.removeAll();
        
        try{
            //Creates a JFreeChart object
            JFreeChart chartBar = ChartFactory.createBarChart(
                title, // chart title
                xlabel, // x-axis label
                ylabel, // y-axis label
                dataset, // dataset
                    PlotOrientation.HORIZONTAL, // orientation
            false, // include legend
            true, // tooltips
            false // urls
            );
            
            chartBar.getRenderingHints().put(JFreeChart.KEY_SUPPRESS_SHADOW_GENERATION, Boolean.TRUE);
            chartBar.getRenderingHints().put(RenderingHints.KEY_ANTIALIASING, RenderingHints.VALUE_ANTIALIAS_ON);
            
            //Displays value on top of each bar
            CategoryPlot plot = (CategoryPlot) chartBar.getPlot();
            CategoryItemRenderer renderer = plot.getRenderer();
            renderer.setDefaultItemLabelGenerator(new StandardCategoryItemLabelGenerator());
            renderer.setDefaultItemLabelsVisible(true);
            ItemLabelPosition position = new ItemLabelPosition(ItemLabelAnchor.CENTER, TextAnchor.CENTER);
            renderer.setDefaultPositiveItemLabelPosition(position); 

            //Sets background color 150
            chartBar.setBackgroundPaint(new Color(150,250,250));
            chartBar.getPlot().setBackgroundPaint(new Color(255, 255, 170)); 

            //Create a ChartPanel object
            ChartPanel barchartPanel = new ChartPanel(chartBar);             
            jp.setLayout(new java.awt.BorderLayout());
            jp.add(barchartPanel, BorderLayout.CENTER);
            
            jp.validate();
        }
        catch (Exception ex){
            JOptionPane.showMessageDialog(frm, ex.getMessage(), 
                    "ERROR",JOptionPane.ERROR_MESSAGE);
        }
    }   
    
    public static void draw_piechart_with_dataset(JFrame frm, JPanel jp, DefaultPieDataset dataset, String title){
        jp.removeAll();
        
        try{            
            //Creates a JFreeChart object
            JFreeChart chartPie = ChartFactory.createPieChart(
                title, // chart title
                dataset, // dataset
            true, // include legend
            true, // tooltips
            false // urls
            );    
            
            //Sets background color
            chartPie.getPlot().setBackgroundPaint(new Color(250,250,180));

            // Sets the label generator to show percentages
            PiePlot plot = (PiePlot) chartPie.getPlot();
            plot.setLabelGenerator(new StandardPieSectionLabelGenerator("{0} ({2})",
                                                                    NumberFormat.getNumberInstance(),
                                                                    new DecimalFormat("0.0%")));            
            //Create a ChartPanel object
            ChartPanel piechartPanel = new ChartPanel(chartPie); 
            jp.setLayout(new java.awt.BorderLayout());
            jp.add(piechartPanel, BorderLayout.CENTER);
            jp.validate();
        }
        catch (Exception ex){
            JOptionPane.showMessageDialog(frm, ex.getMessage(), 
                    "ERROR",JOptionPane.ERROR_MESSAGE);
        }
    }  
    
    public static <T> List<T> get_col_val_from_database(String column, String table) throws SQLException, ClassNotFoundException {
        List<T> values = new ArrayList<>();   
        String query = "SELECT " + column + " FROM " + table;
        
        try(Connection conn = getConnection();
            Statement statement = conn.createStatement();
                ResultSet resultSet = statement.executeQuery(query)){              
    
            // Iterates through the result set and add the column values to the list
            while (resultSet.next()) {
                T value = (T) resultSet.getObject(column);
                values.add(value);               
            }
        }catch (SQLException ex){
            JOptionPane.showMessageDialog(null, ex.getMessage(), 
                "ERROR",JOptionPane.ERROR_MESSAGE);
        }                    
        return values;
    }

    public static Object get_val_from_database(String tableName, String columnName, String condition, Object item) {
        Object result = null;
        String query = "SELECT " + columnName + " FROM " + tableName + " WHERE " + condition + " = ?";
        try (Connection conn = getConnection();         
            PreparedStatement ps = conn.prepareStatement(query)) {
            
            ps.setObject(1, item);
            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    result = rs.getObject(columnName);

                } else {
                    // no row found, clear the form fields
                    JOptionPane.showMessageDialog(null, "ITEM NOT FOUND");
                }
            }
        } catch (SQLException ex) {
            JOptionPane.showMessageDialog(null, ex.getMessage(), "ERROR", JOptionPane.ERROR_MESSAGE);
        }
        return result;
    } 
        
    public static Map<String, Double> categorize_values(List<? extends Number> list_vals, Map<String, ? extends Number> range_vals) {
        Map<String, Double> counts = new HashMap<>();

        // Initialize the counts map
        for (String category : range_vals.keySet()) {
            counts.put(category, 0.0);
        }

        // Loop through list_vals and categorize them into their corresponding range
        for (Number val : list_vals) {
            double dbValue = val.doubleValue();
            String category = range_vals.keySet().iterator().next(); // Default category 

            for (Map.Entry<String, ? extends Number> range : range_vals.entrySet()) {
                if (dbValue < range.getValue().doubleValue()) {
                    category = range.getKey();
                    break;
                }
            }

            // Increment the count for the corresponding category
            Double countObj = counts.get(category);
            double count = countObj != null ? countObj.doubleValue() : 0;
            counts.put(category, count + 1);
        }

        return counts;
    }        

    public static DefaultCategoryDataset create_bar_dataset(String query, String valColumn, String labelColumn){
        // Creates a DefaultCategoryDataset object
        DefaultCategoryDataset dataset = new DefaultCategoryDataset();
            
        try(Connection conn = getConnection(); Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(query)){
            
            while (rs.next()) {
                Object value = rs.getObject(valColumn);
                String label = rs.getString(labelColumn);
            
                if (value instanceof Number) {
                    dataset.addValue(((Number) value).doubleValue(), labelColumn, label);
                } else if (value instanceof String) {
                    double doubleValue = Double.parseDouble((String) value);
                    dataset.addValue(doubleValue, labelColumn, label);
                } else {
                    throw new RuntimeException("Unsupported value type: " + value.getClass().getName());
                }
            }
        } catch (Exception ex){
            JOptionPane.showMessageDialog(null, ex.getMessage(), 
                    "ERROR",JOptionPane.ERROR_MESSAGE);
        }
        return dataset;
    }

    public static DefaultPieDataset create_pie_dataset(String query, String valColumn, String labelColumn){
        // Creates a DefaultPieDataset object
        DefaultPieDataset dataset = new DefaultPieDataset();
            
        try(Connection conn = getConnection(); Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(query)){
            
            while (rs.next()) {
                Object value = rs.getObject(valColumn);
                String label = rs.getString(labelColumn);
            
                if (value instanceof Number) {
                    dataset.setValue(label, ((Number) value).doubleValue());
                } else if (value instanceof String) {
                    double doubleValue = Double.parseDouble((String) value);
                    dataset.setValue(label, doubleValue);
                } else {
                    throw new RuntimeException("Unsupported value type: " + value.getClass().getName());
                }
            }
        } catch (Exception ex){
            JOptionPane.showMessageDialog(null, ex.getMessage(), 
                    "ERROR",JOptionPane.ERROR_MESSAGE);
        }
        return dataset;
    } 

    public void read_image(JFrame jframe, JTextField jtfPhotoPath, JLabel label) {
        BufferedImage im = null;
        try {
            JFileChooser fileChooser = new JFileChooser();
            fileChooser.setCurrentDirectory(new File(System.getProperty("user.home")));
            int result = fileChooser.showOpenDialog(jframe);
            if (result == JFileChooser.APPROVE_OPTION) {
                File fileChosen = fileChooser.getSelectedFile();
                if (fileChosen.isFile() && fileChosen.canRead()) {
                    im = ImageIO.read(fileChosen);
                    jtfPhotoPath.setText(fileChosen.getAbsolutePath());
                    
                    // Display image on jLabelImage
                    ImageIcon icon = new ImageIcon(im);
                    label.setIcon(icon);
                } else {
                    throw new IOException("Selected file is not a valid image file or cannot be read.");
                }
            }
        } catch (IOException e) {
            JOptionPane.showMessageDialog(jframe, "Error reading image file: " + e.getMessage(),
                "Error", JOptionPane.ERROR_MESSAGE);
            label.setIcon(null);
            jtfPhotoPath.setText("");
        } catch (Exception e) {
            ImageIcon icon = new ImageIcon(getClass().getClassLoader().getResource("dummy.png"));
            label.setIcon(icon);
        }
    }

    public static void show_image(JLabel label, String path) {
        // Display image on JLabel control
        ImageIcon icon = new ImageIcon(path);
        label.setIcon(icon);
    }    

    public static int findMaxInt(Connection conn, String tableName, String columnName) throws SQLException {
        int max = Integer.MIN_VALUE;
        String query = "SELECT MAX(" + columnName + ") FROM " + tableName;
        try (Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(query)) {
            if (rs.next()) {
                max = rs.getInt(1);
            }
        }
        return max;
    }

    public void read_image(JFrame jframe, JLabel label) {
        BufferedImage im = null;
        try {
            JFileChooser fileChooser = new JFileChooser();
            fileChooser.setCurrentDirectory(new File(System.getProperty("user.home")));
            int result = fileChooser.showOpenDialog(jframe);
            if (result == JFileChooser.APPROVE_OPTION) {
                File fileChosen = fileChooser.getSelectedFile();
                if (fileChosen.isFile() && fileChosen.canRead()) {
                    im = ImageIO.read(fileChosen);
                    
                    // Display image on jLabelImage
                    ImageIcon icon = new ImageIcon(im);
                    label.setIcon(icon);
                } else {
                    throw new IOException("Selected file is not a valid image file or cannot be read.");
                }
            }
        } catch (IOException e) {
            JOptionPane.showMessageDialog(jframe, "Error reading image file: " + e.getMessage(),
                "Error", JOptionPane.ERROR_MESSAGE);
            label.setIcon(null);
        } catch (Exception e) {
            ImageIcon icon = new ImageIcon(getClass().getClassLoader().getResource("dummy.png"));
            label.setIcon(icon);
        }
    }   
    
    public static void selectRowById(JTable table, int id) {
        int rowCount = table.getRowCount();
        for (int row = 0; row < rowCount; row++) {
            int rowId = Integer.parseInt(table.getValueAt(row, 0).toString());
            if (rowId == id) {
                table.getSelectionModel().setSelectionInterval(row, row);
                table.scrollRectToVisible(table.getCellRect(row, 0, true));
                highlightRow(table, row, Color.RED);
                break;
            }
        }
    }

    private static void highlightRow(JTable table, int row, Color color) {
        table.setSelectionBackground(color);
        table.setSelectionForeground(Color.WHITE);
    
        TableCellRenderer headerRenderer = table.getTableHeader().getDefaultRenderer();
        TableColumnModel columnModel = table.getColumnModel();
    
        // Set custom cell renderer for each column
        for (int column = 0; column < table.getColumnCount(); column++) {
            columnModel.getColumn(column).setCellRenderer(new HighlightedRowRenderer(color));
        }
    
        // Set custom header renderer
        for (int column = 0; column < table.getColumnCount(); column++) {
            columnModel.getColumn(column).setHeaderRenderer(new BoldHeaderRenderer(headerRenderer));
        }
    }        
}
Software Developer and Writer

Friday, May 26, 2023

DATA SCIENCE USING JDBC AND SQL SERVER WITH OBJECT-ORIENTED APPROACH AND APACHE NETBEANS IDE: BALIGE PUBLISHING

Friday, May 19, 2023

DATA ANALYSIS USING JDBC AND SQL SERVER WITH OBJECT-ORIENTED APPROACH AND APACHE NETBEANS IDE: BALIGE PUBLISHING

© Copyright (2017),VIVIAN SIAHAAN,All Rights Reserved.

Official Blog | Kontak Kami

Blog Design By VIVIAN SIAHAAN

Content Design By VIVIAN SIAHAAN
