import java.io.IOException;
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.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;

public class JdbcUtils {
    private static volatile JdbcUtils jdbcUtils = null;
    private static Connection conn = null;
    private static ResultSet rs = null;
    private static PreparedStatement ps = null;
    static String url = null;
    static String user = null;
    static String password = null;
    static {
        Properties p = new Properties();
        try {
            p.load(JdbcUtils.class.getClassLoader().getResourceAsStream(
                    "com/mysql/properties/mysql.properties"));
            url = p.getProperty("url");
            user = p.getProperty("user");
            password = p.getProperty("password");
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    private JdbcUtils() {
        getConn();
    }

    private void getConn() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(url, user, password);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    

    public static JdbcUtils newInstance() {
        if (jdbcUtils == null) {
            synchronized (JdbcUtils.class) {
                if (jdbcUtils == null) {
                    jdbcUtils = new JdbcUtils();
                }
            }
        }
        return jdbcUtils;
    }

    public int insert(String sql, Object... obj) {
        int result = 0;
        try {
            ps = conn.prepareStatement(sql);
            int index = 1;
            for (Object o : obj) {
                ps.setObject(index++, o);
            }
            result = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return -1;
        } finally {
            close();
        }
        return result;
    }

    public int Delete(String sql, Object... obj) {
        int result = 0;
        try {
            ps = conn.prepareStatement(sql);
            int index = 1;
            for (Object o : obj) {
                ps.setObject(index++, o);
            }
            result = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return -1;
        } finally {
            close();
        }
        return result;
    }

    public int update(String sql, Object... obj) {
        int result = 0;
        try {
            ps = conn.prepareStatement(sql);
            int index = 1;
            for (Object o : obj) {
                ps.setObject(index++, o);
            }
            result = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return -1;
        } finally {
            close();
        }
        return result;
    }

    public List<Map<String, Object>> select(String sql, Object... obj) {
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        try {
            ps = conn.prepareStatement(sql);
            int index = 1;
            for (Object o : obj) {
                ps.setObject(index++, o);
            }
            rs = ps.executeQuery();
            ResultSetMetaData rsd = rs.getMetaData();
            int length = rsd.getColumnCount();
            // System.out.println(length);
            while (rs.next()) {
                Map<String, Object> m = new HashMap<String, Object>();
                for (int i = 1; i <= length; i++) {
                    m.put(rsd.getColumnName(i), rs.getObject(i));
                }
                // System.out.println(m.size());
                list.add(m);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {

        }
        return list;
    }

    private void close() {
        try {
            if (rs != null) {
                rs.close();
            }
            if (ps != null) {
                ps.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

}