/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package Utils;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author TAM LONG
 */
public class JDBCUtil implements Serializable {

    public JDBCUtil() {
    }

    public  String createConnectionString(String dbName, String user, String pass) {
        String conString = "jdbc:sqlserver://PHUCNT;databaseName="
                + dbName + ";user=" + user + ";password=" + pass;
        return conString;
    }

    public  int CUDQuery(String conString, String sqlQuery) {
        Connection con = null;
        Statement stmt = null;
        int rows = 0;
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            con = DriverManager.getConnection(conString);
            stmt = con.createStatement();
            rows = stmt.executeUpdate(sqlQuery);
            return rows;
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            try {
                stmt.close();
                con.close();;
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }
        return 0;
    }
    
    public boolean insert(String conString, String query, Object[] params) {
        Connection con = null;
        PreparedStatement pstm = null;
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            con = DriverManager.getConnection(conString);
            pstm = con.prepareStatement(query);
            for (int i = 0; i < params.length; i++) {
                pstm.setObject(i + 1, params[i]);
            }
            int result = pstm.executeUpdate();
            if (result > 0) {
                return true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (pstm != null) {
                    pstm.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return false;
    }

    public List<Object[]> getAll(String conString, String sql) throws SQLException {
        Connection con = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            con = DriverManager.getConnection(conString);
            pstm = con.prepareStatement(sql);
            rs = pstm.executeQuery();
            ResultSetMetaData rsm = rs.getMetaData();
            int cols = rsm.getColumnCount();
            List<Object[]> list = new ArrayList<Object[]>();
            while (rs.next()) {
                Object[] row = new Object[cols];
                for (int i = 0; i < cols; i++) {
                    row[i] = rs.getObject(i + 1);
                }
                list.add(row);
            }
            return list;

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }

                if (pstm != null) {
                    pstm.close();
                }

                if (con != null) {
                    con.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return null;
    }
   
}