dbconnection.java

this code explains about the database connection establishment.

package com.wwr.commons;

import java.sql.*;
import java.util.logging.Level;
import java.util.logging.Logger;

public class DBConnection {

    Connection conn = null;
    Statement stmt;
    ResultSet rs,rsCall = null;
    CallableStatement cstmt = null;

    /** Creates a new instance of dbConnection */
    public DBConnection() throws Exception {

        //......... This URL has been set to chek the code with ORACLE on 132.   on 31 OCT.
        //Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        //Class.forName("oracle.jdbc.driver.OracleDriver");      
        //conn= DriverManager.getConnection("jdbc:odbc:MCMP","admin","admin123");
        //conn=DriverManager.getConnection("jdbc:oracle:thin:@192.168.5.134:1521:scott","scott","tiger");
        //  DriverManager.registerDriver(new com.microsoft.jdbc.sqlserver.SQLServerDriver());
       // conn=DriverManager.getConnection("jdbc:microsoft:sqlserver://192.168.5.65:1433;DatabaseName=mCampaign","SQLAdmin","admin123");

        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        //Establish a connection
       conn = DriverManager.getConnection("jdbc:sqlserver://123.238.36.202;DatabaseName=wwr", "wwr", "worldwide");
       
                //Anup:- 123.237.7.42
                // Tushar :- 192.168.0.122
                // Tushar new :- 123.238.36.202
       
        // conn = DriverManager.getConnection("jdbc:sqlserver://192.168.5.127:1433;DatabaseName=mCampaign", "sa", "admin123");

    // System.out.println("Connected ");
    //conn=DriverManager.getConnection("jdbc:microsoft:sqlserver://192.168.5.57:1433;DatabaseName=mCampaign2","admin","admin123");
    }

    // .........................All methods are  synchronised on 29-10-2007 by Ranjeet.
    public ResultSet execQuery(String strSQL) throws Exception {
        try {
            //  System.out.println("IN SELECT :::  "+strSQL);
            stmt = conn.createStatement();//ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);//
            rs = stmt.executeQuery(strSQL);

        //System.out.println("IN DBCONN  FETCH SIZE>>>"+rs.getFetchSize());

        } catch (SQLException sqlExcep) {
            System.out.println("SQL Exception       " + sqlExcep);
        //logDumper.logWrite("Codeflow.txt",sqlExcep); //------changed on 17-10-07
        }
        //System.out.println("RETURNING RESULTSET FROM DB CON WITH VALUE AS : : : "+rs);
        return rs;
    }

    public ResultSet execDMLQuery(String strSQL) throws Exception {
        try {
             System.out.println("execdMLQuery:::::IN SELECT :::  "+strSQL);
            stmt = conn.createStatement();//ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            rs = stmt.executeQuery(strSQL);
        //System.out.println("IN DBCONN  FETCH SIZE>>>"+rs.getFetchSize());

        } catch (SQLException sqlExcep) {
            System.out.println("SQL Exception       " + sqlExcep);
        //logDumper.logWrite("Codeflow.txt",sqlExcep); //------changed on 17-10-07
        }
        //System.out.println("RETURNING RESULTSET FROM DB CON WITH VALUE AS : : : "+rs);
        return rs;
    }

    public ResultSet execQueryTest(String strSQL) throws Exception {
        try {
            //  System.out.println("IN SELECT execQueryTest:::  "+strSQL);
            stmt = conn.createStatement();
            rs = stmt.executeQuery(strSQL);

        //System.out.println("IN DBCONN  FETCH SIZE>>>"+rs.getFetchSize());

        } catch (SQLException sqlExcep) {
            System.out.println("SQL Exception       " + sqlExcep);
        //logDumper.logWrite("Codeflow.txt",sqlExcep); //------changed on 17-10-07
        }
        //System.out.println("RETURNING RESULTSET FROM DB CON WITH VALUE AS : : : "+rs);
        return rs;
    }

    public int execUpdate(String strSQL) throws SQLException {

        //  System.out.println("IN EXEC UPDATE  :::  "+strSQL);
        stmt = conn.createStatement();//ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
        int rowsUpdated = stmt.executeUpdate(strSQL);
        stmt.close();
        return rowsUpdated;

    }

    public int update(String strSQL) throws SQLException {
        // System.out.println("IN UPDATE ::: "+strSQL);
        stmt = conn.createStatement();//ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
        int rowsUpdated = stmt.executeUpdate(strSQL);
        // System.out.println("DBConnection Executed---------------------------------------");
        stmt.close();
        return rowsUpdated;

    }

    public int update_RO(String strSQL) throws SQLException {
        System.out.println("IN UPDATE ::: " + strSQL);
        stmt = conn.createStatement();//ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

        int rowsUpdated = stmt.executeUpdate(strSQL);
        // System.out.println("DBConnection Executed---------------------------------------");
        stmt.close();
        return rowsUpdated;

    }

    public CallableStatement exe_callable_statement(String  query){
        CallableStatement cstmt=null;
        try {
            cstmt = conn.prepareCall(query);
        } catch (SQLException ex) {
            ex.printStackTrace();
            Logger.getLogger(DBConnection.class.getName()).log(Level.SEVERE, null, ex);
        }
         return cstmt;
    }
    
    public void close() throws SQLException {
        if (stmt != null) {
            stmt.close();
        }
        if (conn != null) {
            conn.close();
        }

    }
    
    
    
    
    
    public CallableStatement call(String query){
        try{
            cstmt = conn.prepareCall (query);           
                    
        }catch(Exception e){
            e.printStackTrace();
        }
         return cstmt;
    }

    public static void main(String[] args) {
        try {
            DBConnection dbc = new DBConnection();
            // String qq="SELECT On_Date FROM Results123 WHERE DATEDIFF(day, 2007-10-01, 2007-10-25 )";
//            String qq = "select top(10)* from tbl_sms_subscription";//"SELECT DATEDIFF(dd,'01 October 2007','24 October 2007') from Results123";
            //  String qq="select * from sonal where On_DateOn_Time between convert(datetime, '2007/10/12') and convert(datetime, '2009/10/14');" ;
            // String qq="insert into sonal values(convert(datetime, '14/10/2007'))";
            // int i=dbc.execUpdate(qq);
            //  System.out.println("-----------"+i);
            
            CallableStatement cs = dbc.call("{call getLatestContentStories(?,?)}");
             cs.setString(1,"Ramayan");
            cs.setString(2,"9320257080");
            ResultSet rs = cs.executeQuery();
            while(rs.next()){
                System.out.println(rs.getString("description"));
    
            }
//            ResultSet rs = dbc.execQueryTest(qq);
//            if(rs.next()) {
//                System.out.println(rs.getString(1));

//            } 
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }
    // Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance();
    //  Class.forName("org.postgresql.Driver");
    // conn = DriverManager.getConnection("jdbc:microsoft:sqlserver://localhost:1433", "", "");
    // conn =  DriverManager.getConnection("jdbc:postgresql://localhost/MCMP","","");
}