john pfeiffer
  • Home
  • Categories
  • Tags
  • Archives

jdbc preparestatement

// 2012-04-09 johnpfeiffer

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.Properties;


public class Main
{
    String dbms;
    String host;
    String port;
    String user;
    String password;

    public static void main( String args[] )
    {
        Main m = new Main();
        Connection c = null;
        Statement stmt = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;        //cursor in the remote database = a table of data representing a database result set

        try{
            m.dbms = "mysql";
            m.host = "localhost";
            m.port = "3306";
            m.user = "root";
            m.password = "password";

            String query = "show databases;";

            c = m.getConnection();
            pstmt = c.prepareStatement( query );
            /*
              con.setAutoCommit( false );       //forces the full transaction to occur at one time
              String myUpdate = "UPDATE EMPLOYEES SET SALARY = ? WHERE ID = ?";
              pstmt = c.prepareStatement( myUpdate );
            pstmt.setBigDecimal( 1 , 123456.00 );
            pstmt.setInt( 2 , 21 );
            pstmt.executeUpdate();      //does not return a result set

            pstmt.setInt( 2 , 22 );     //employee number 22 will now have the same salary
              pstmt.executeUpdate();

              pstmt.clearParameters();
              con.commit();
             */


            rs = pstmt.executeQuery();
            ResultSetMetaData rsmeta = rs.getMetaData();
            int columnCount = rsmeta.getColumnCount();
            while( rs.next() )
            {
                for ( int i = 1 ; i <= columnCount ; i++ )  //column counting starts at 1
                {
                    String name = rs.getObject( i ).toString();
                    System.out.println( name );
                }
            }
            pstmt.close();
            rs.close();
            c.close();
        }catch( SQLException sqle )
        {       System.err.println( sqle.getMessage() );
        }
        catch( Exception e )
        {       System.err.println( e.getMessage() ) ;
        }
        finally
        {
            try{
                if( stmt != null ){     stmt.close();   }
                if( pstmt != null ){        pstmt.close();  }
                if( rs != null ){       rs.close(); }
                if( c != null ){        c.close();  }
            }catch( Exception e )
            {}
        }
    }


    protected Connection getConnection() throws SQLException
    {
    Connection conn = null;
    Properties connectionProps = new Properties();
    connectionProps.put( "user" , this.user );
    connectionProps.put( "password" , this.password );

    if( this.dbms.equals( "mysql" ) )
    {       conn = DriverManager.getConnection( "jdbc:" + this.dbms + "://" + this.host + ":" + this.port + "/", connectionProps );
    }else if( this.dbms.equals( "derby" ) )
    {//   conn = DriverManager.getConnection( "jdbc:" + this.dbms + ":" + this.dbName + ";create=true", connectionProps );
    }
    System.out.println( "DEBUG: Connected to database" );
    return conn;
    }

} //end class

  • « persistence jpa
  • Amazon s3 bucket html redirect »

Published

Apr 10, 2012

Category

java

~249 words

Tags

  • java 252
  • jdbc 3
  • preparestatement 1