Advertisement
Help Keep Boards Alive. Support us by going ad free today. See here: https://subscriptions.boards.ie/.
If we do not hit our goal we will be forced to close the site.

Current status: https://keepboardsalive.com/

Annual subs are best for most impact. If you are still undecided on going Ad Free - you can also donate using the Paypal Donate option. All contribution helps. Thank you.
https://www.boards.ie/group/1878-subscribers-forum

Private Group for paid up members of Boards.ie. Join the club.

JDBC ResultSet Help

  • 14-04-2009 04:43PM
    #1
    Registered Users, Registered Users 2 Posts: 269 ✭✭


    I have a number of calls calling my databaseutility class and passing in an appropiate select statement and will return a d/b result of various different sizes and i want the result stored in a string variable to be returned as a variable to another class, however i am having to manually setup size of record how do i do that dynamically i know how but the string variable is putting me off can anyone point me in the write direction
    Want to change whats in red so i dont need to specify size of record been returned from db
    while(result.next())
    {

    queryresult=" " + resultmeta.getColumnName(1) + " " + result.getObject(1)+" "+resultmeta.getColumnName(2) + " " + result.getObject(2)+" "+resultmeta.getColumnName(3) + " " + result.getObject(3)+" ";
    }
    package ie.travel.databaseutility;
    import java.sql.*;
    import java.util.ArrayList;
    import com.mysql.jdbc.exceptions.*;
    
    public class DatabaseUtility {
    	
     private Connection con;
     
    //Main constructor will establish db connection	
    public DatabaseUtility(){
    	  String username = "root";
          String password = "";
          try {//Load D/B Driver
              Class.forName("com.mysql.jdbc.Driver");
          } catch (ClassNotFoundException e) {
              String error = "Error Loading Travel 2.0 Database Driver" + e;
              System.out.println(error);
          }
    
          try {//Establish D/B Connection
              String url = "jdbc:mysql://localhost:3306/Travel";
              con = DriverManager.getConnection(url, username, password);
              con.setAutoCommit(false);
          } catch (Exception e) {
              String error = "Error Establishing Travel 2.0 DatabaseConnection" + e;
              System.out.println(error);
          }
          
          
    }
    
    public String selectRecord(String selectstatement){
    	String queryresult=null;
    	try{
    		Statement stmt = con.createStatement();
    		ResultSet result = stmt.executeQuery(selectstatement);
    		ResultSetMetaData resultmeta = result.getMetaData();
    		//int columnCount = resultmeta.getColumnCount();
    		//String query[]=null;
            while(result.next())
            {
    		 
    		  queryresult=" " + resultmeta.getColumnName(1) + " " + result.getObject(1)+" "+resultmeta.getColumnName(2) + " " + result.getObject(2)+" "+resultmeta.getColumnName(3) + " " + result.getObject(3)+" ";
    		}
    		 
    		con.commit();
    	}catch(Exception e){
    		 try{
    			 con.rollback();
    			 return "Error Travel 2.0 has been Rolled Back" + e;
    		 }
    		 catch(SQLException ex){
    				 return "Error Travel 2.0 SQL Error" + ex;
    			 
    		 }
    	}
    	finally{
    		try{
    			con.close();
    		}catch(SQLException ex){
    		 return "Error Travel 2.0 Connection Close" + ex;
    		 }
    	}
    	
    		if (queryresult == null){
    			return "Error Record Not in Travel 2.0";
    		}
    		else {
    			
    			return queryresult;
    			
    		}
    	}
    
     public String insertRecord(String insertstatement)
     {
    	   int result;
    		try{
    			Statement stmt = con.createStatement();
    			result = stmt.executeUpdate(insertstatement);
    			con.commit();
    		}catch(Exception e){
    			 try{
    				 con.rollback();
    				 return "Error Travel 2.0 has been Rolled Back" + e;
    			 }
    			 catch(SQLException ex){
    				 return "Error Travel 2.0 SQL Error" + ex;
    			 }
    		}finally{
    			try{
    				con.close();
    				
    			}catch(SQLException ex){
    			 return "Error Travel 2.0 Connection Close" + ex;
    			 }
    		}
    		if (result!=0){
    			return "Travel 2.0 Record Entry Successfull";
    		}
    		else {
    			return "Travel 2.0 Record Entry Failure";
    		}
    		
    		
    			
     }
    	
    
    //public String updateRecord(String updatestatement){
    	
    //}
    
    public String deleteRecord(String deletestatement){
    	 int result;
    		try{
    			Statement stmt = con.createStatement();
    			result = stmt.executeUpdate(deletestatement);
    			con.commit();
    		}catch(Exception e){
    			 try{
    				 con.rollback();
    				 return "Error Travel 2.0 has been Rolled Back"+e;
    			 }
    			 catch(SQLException ex){
    				 return "Error Travel 2.0 SQL Error" + ex;
    			 }
    		}finally{
    			try{
    				con.close();
    				
    			}catch(SQLException ex){
    			 return "Error Travel 2.0 Connection Close" + ex;
    			 }
    		}
    		if (result!=0){
    			return "Travel 2.0 Record Removal Successfull";
    		}
    		else {
    			return "Travel 2.0 Record Removal Failure";
    		}
     }
    public String updateRecord(String updatestatement){
    	 int result;
    		try{
    			Statement stmt = con.createStatement();
    			result = stmt.executeUpdate(updatestatement);
    			con.commit();
    		}catch(Exception e){
    			 try{
    				 con.rollback();
    				 return "Error Travel 2.0 has been Rolled Back";
    			 }
    			 catch(SQLException ex){
    				 return "Error Travel 2.0 SQL Error" + ex;
    			 }
    		}finally{
    			try{
    				con.close();
    				
    			}catch(SQLException ex){
    			 return "Error Travel 2.0 Connection Close" + ex;
    			 }
    		}
    		if (result!=0){
    			return "Travel 2.0 Record Update Successfull";
    		}
    		else {
    			return "Travel 2.0 Record Update Failure";
    		}
    }
    }
    
    
    


Comments

  • Registered Users, Registered Users 2, Paid Member Posts: 2,032 ✭✭✭lynchie


    You mean like the following?

    for(int i=1;i<=resultmeta.getColumnCount();i++)
    queryResult+=resultmeta.getColumnName(i) + " " + result.getObject(i);


  • Registered Users, Registered Users 2 Posts: 269 ✭✭cyberwit


    Thanks forgot to put in the +


Advertisement