package com.cargo.db;

import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.DriverManager;
import java.sql.SQLException;

/**
 *class explain:Database connection
 *set up name:	crazyadept
 *set up time:	11/22/2004
 */
public class Conn {
    private static Connection con;
    private Statement stmt;
    private ResultSet rs;
    private static final String drivername = "com.microsoft.jdbc.sqlserver.SQLServerDriver";
    private static final String url = "jdbc:microsoft:sqlserver://127.0.0.1:1433;DatabaseName=db_Cargo;user=sa;password=;";

    /**
     *method explain:   Get Connection DataBase
     *modify time:	12/20/2004
     */
    public static synchronized Connection getCon() throws Exception {
        try {
            Class.forName(drivername);
            con = DriverManager.getConnection(url);
            return con;
        } catch (SQLException e) {
            System.err.println(e.getMessage());
            throw e;
        }
    }

    /**
     *output parameter:	in order select in SQL
     *modify time:	12/20/2004
     */
    public Statement getStmtread() {
        try {
            con = getCon();
            stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                                       ResultSet.CONCUR_READ_ONLY);
            return stmt;
        } catch (Exception e) {
            System.err.println(e.getMessage());
            e.printStackTrace();
        }
        return null;
    }

    /**
     *output parameter: Data
     *input parameter:  SQL select sentence
     *modify time:      11/29/2004
     */
    public ResultSet getRs(String sql) {
        try {
            stmt = getStmtread();
            rs = stmt.executeQuery(sql);
            return rs;
        } catch (Exception e) {
            System.err.println(e.getMessage());
            System.err.println(sql);
            e.printStackTrace();
        }
        return null;
    }

    /**
     *output parameter: not in order select in str SQL
     *modify time:      11/29/2004
     */
    public Statement getStmt() {
        try {
            con = getCon();
            stmt = con.createStatement();
            return stmt;
        } catch (Exception e) {
            System.err.println(e.getMessage());
            e.printStackTrace();
        }
        return null;
    }

    /**
     *method explain:   Close DataBase Connection
     *modify time:      11/29/2004
     */
    public synchronized void close() {
        try {
            if (rs != null) {
                rs.close();
                rs = null;
            }
        } catch (Exception e) {
            System.err.println(e.getMessage());
            e.printStackTrace();
        }
        try {
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
        } catch (Exception e) {
            System.err.println(e.getMessage());
            e.printStackTrace();
        }
        try {
            if (con != null) {
                con.close();
                con = null;
            }
        } catch (Exception e) {
            System.err.println(e.getMessage());
            e.printStackTrace();
        }
    }

    /**
     *input parameter:  DataBase table name
     *output parameter: count = -2 ;Exception
     *		        count = -1 ;Error
     *			count = 0  ;Nothing
     *			count > 0  ;Right
     *modify time:	11/29/2004
     */
    public int getRowCount(String strSql) {
        int intCount = 0;
        try {
            stmt = getStmtread();
            rs = stmt.executeQuery("SELECT COUNT(*) FROM " + strSql);
            if (rs.next()) {
                intCount = rs.getInt(1);
            } else {
                intCount = -1;
            }
        } catch (Exception e) {
            intCount = -2;
            System.err.println("SELECT COUNT(*) FROM " +strSql);
            System.err.println(e.getMessage());
        } finally {
            close();
            return intCount;
        }
    }

    /**
     *input parameter:	insert SQL
     *output parameter:	count = -2 >Exception
     *			count = 0  >nothing
     *			count > 1  >right
     *modify time:	11/29/2004
     */
    public int insert(String sql) {
        int count = 0;
        stmt = getStmt();
        try {
            count = stmt.executeUpdate(sql);
        } catch (Exception e) {
            count = -2;
            System.err.println(sql);
            System.err.println(e.getMessage());
        } finally {
            close();
            return count;
        }
    }

    /**
     *input parameter:	update SQL
     *output parameter:	count = -2 >Exception
     *			count = 0  >nothing
     *			count > 1  >right
     *modify time:	11/29/2004
     */
    public int update(String sql) {
        int count = 0;
        stmt = getStmt();
        try {
            count = stmt.executeUpdate(sql);
        } catch (Exception e) {
            count = -2;
            System.err.println(sql);
            System.err.println(e.getMessage());
        } finally {
            close();
            return count;
        }
    }

    /**
     *input parameter:	delete SQL
     *output parameter:	count = -2 >Exception
     *			count = 0  >nothing
     *			count > 1  >right
     *modify time:	11/29/2004
     */
    public int delete(String sql) {
        int count = 0;
        stmt = getStmt();
        try {
            count = stmt.executeUpdate(sql);
        } catch (Exception e) {
            count = -2;
            System.err.println(sql);
            System.err.println(e.getMessage());
        } finally {
            close();
            return count;
        }
    }
}