Querying a database via a constructor

Question

I'm really hopping some help from anyone who would be kind enough to help me resolve this. I've been at it since yesterday.

See, what I'm trying to do is create a database query class that I can re-use over and over again as many times as possible without having to write a query every other time I need to search or display items from the database.

I'd like to be able to pass the query specifications via a constructor.

I however, get this error:

run:
Connecting to a selected database...
Connected database successfully...
Creating statement...
org.h2.jdbc.JdbcSQLException: Data conversion error converting ; SQL statement:

This is my code so far:

import java.sql.*;

public class RsToAList {
    private final String table;
    private final String columns;
    private final String whereColumn;
    private final String equalsEntry;

    public RsToAList (String columns, String table, String whereColumn, String equalsEntry) {
        this.table = table;
        this.columns = columns;
        this.whereColumn = whereColumn;
        this.equalsEntry = equalsEntry;
    }

    // JDBC driver name and database URL
    static String JDBC_DRIVER = "org.h2.Driver";
    static String DB_URL = "jdbc:h2:file:C:/tryDb/tryDb";

    //  Database credentials
    static String USER = "sa";
    static String PASS = "";

    public static void main (String[] args) {
        RsToAList tryAndGet = new RsToAList("fullNames", "CLIENT", "postOfficeBoxNumber", "6448");
        tryAndGet.ourQueryMethod();
    }

    public void ourQueryMethod () {
        Connection conn = null;
        Statement stmt = null;

        try {
            // STEP 2: Register JDBC driver
            Class.forName(getJDBC_DRIVER());

            // STEP 3: Open a connection
            System.out.println("Connecting to a selected database...");
            conn = DriverManager.getConnection(getDB_URL(), getUSER(), getPASS());
            System.out.println("Connected database successfully...");

            // STEP 4: Execute a query
            System.out.println("Creating statement...");
            stmt = conn.createStatement();

            String sql = "SELECT " + (columns) + " FROM " + (table) + " WHERE "+ (whereColumn) +" = "+ (equalsEntry) +"";
            ResultSet rs = stmt.executeQuery(sql);

            //STEP 5: Extract data from result set
            while (rs.next()) {
                // Retrieve by column name
                String first = rs.getString(columns);

                // Display values
                System.out.print("ID: " + first);
            }

            rs.close();
        } catch (SQLException se) {
            // Handle errors for JDBC
            se.printStackTrace();
        } catch (Exception e) {
            // Handle errors for Class.forName
            e.printStackTrace();
        } finally {
            // finally block used to close resources
            try {
                if (stmt != null)
                    conn.close();
            } catch (SQLException se) {
            } // do nothing
            try {
                if (conn != null)
                    conn.close();
            } catch (SQLException se) {
                se.printStackTrace();
            } // end finally try
        } // end try
        System.out.println("Goodbye!");
    } // end main



    /**
     * @return the JDBC_DRIVER
     */
    public static String getJDBC_DRIVER() {
        return JDBC_DRIVER;
    }

    /**
     * @param aJDBC_DRIVER the JDBC_DRIVER to set
     */
    public static void setJDBC_DRIVER(String aJDBC_DRIVER) {
        JDBC_DRIVER = aJDBC_DRIVER;
    }

    /**
     * @return the DB_URL
     */
    public static String getDB_URL() {
        return DB_URL;
    }

    /**
     * @param aDB_URL the DB_URL to set
     */
    public static void setDB_URL(String aDB_URL) {
        DB_URL = aDB_URL;
    }

    /**
     * @return the USER
     */
    public static String getUSER() {
        return USER;
    }

    /**
     * @param aUSER the USER to set
     */
    public static void setUSER(String aUSER) {
        USER = aUSER;
    }

    /**
     * @return the PASS
     */
    public static String getPASS() {
        return PASS;
    }

    /**
     * @param aPASS the PASS to set
     */
    public static void setPASS(String aPASS) {
        PASS = aPASS;
    }

}

I'm a JAVA/ Programming newbie, and I'm trying to teach myself how to code from home in case my question seems too simple, or I seem to have overlooked something in my code.

Edit:

I'm not sure if this could help you help me, but this is the class that creates the database table:

//STEP 1. Import required packages
import java.sql.*;

public class JDBCExampleCreateTables {
    // JDBC driver name and database URL
    private static String JDBC_DRIVER = "org.h2.Driver";
    private static String DB_URL = "jdbc:h2:file:C:/tryDb/tryDb";

    //  Database credentials
    private static String USER = "sa";
    private static String PASS = "";

    public static void main (String[] args) {
        Connection conn = null;
        Statement stmt = null;

        try {
            //STEP 2: Register JDBC driver
            Class.forName(JDBC_DRIVER);

            //STEP 3: Open a connection
            System.out.println("Connecting to a selected database...");
            conn = DriverManager.getConnection(DB_URL, USER, PASS);
            System.out.println("Connected database successfully...");

            //STEP 4: Execute a query
            System.out.println("Creating table in given database...");
            stmt = conn.createStatement();

            String sql = "CREATE TABLE CLIENT " +
                    "(ID INT UNSIGNED NOT NULL AUTO_INCREMENT, " + 
                    " fullNames VARCHAR(255), " + 
                    " iDNumber VARCHAR(255), " + 
                    " pINNumber VARCHAR(255), " + 
                    " passportNumber VARCHAR(255), " + 
                    " postOfficeBoxNumber VARCHAR(255), " + 
                    " postalCode VARCHAR(255), " + 
                    " telephoneNumberLandline VARCHAR(255), " + 
                    " telephoneNumberMobile VARCHAR(255)) "; 

            stmt.executeUpdate(sql);
            System.out.println("Created table in given database...");

        } catch (SQLException se) {
            //Handle errors for JDBC
            se.printStackTrace();
        } catch (Exception e) {
            // Handle errors for Class.forName
            e.printStackTrace();
        } finally {
            // finally block used to close resources
            try {
                if (stmt!=null)
                    conn.close();
            } catch(SQLException se) {
            } // do nothing
            try {
                if (conn!=null)
                    conn.close();
            } catch (SQLException se) {
                se.printStackTrace();
            } // end finally try
        } // end try
        System.out.println("Goodbye!");
    } // end main
} // end

Answer 1

Although you have not shared the column type for the where condition. But most proabably you may be passing a string value to where clause but not enclosing it in single quotes:

String sql = "SELECT " + (columns) + " FROM " + (table) + " WHERE "+ (whereColumn) +" = "+ (equalsEntry) +"";

change it to:

String sql = "SELECT " + (columns) + " FROM " + (table) + " WHERE "+ (whereColumn) +" ='"+ (equalsEntry) +"'";

The other potential candidate for defect is this statementn:

String first = rs.getString(columns);

It may throw an error if any of your column type is not VARCHAR

Answer 2

Looks like a error in your sql statement.Make sure the data type you are comparing is compatible.PreparedStatement is handy instead of concatenating queries.

PreparedStatement stm = conn.prepareStatement("select * from person where name=?");
stm.setString(1,"ABC");
ResultSet rs= stm.executeQuery();