Execute Access Query in Resultset Java

Question

I am trying to execute count query on MDB file, and want to store it to a local variable.

I am getting error "Type mismatch: cannot convert from boolean to int" if assign the output directly.

While trying to use result set I am getting similar error as well "Type mismatch: cannot convert from boolean to ResultSet"

Here's the code:

String connectionString ="jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)}; DBQ=C:\\test\\TestEJFolder\\BWC_Ejournal.mdb;";

        DriverManager.getConnection(connectionString, "", "");

        Connection conn = DriverManager.getConnection(connectionString, "", "");
        Connection conn1 = DriverManager.getConnection(connectionString, "", "");
        Connection conn2 = DriverManager.getConnection(connectionString, "", "");

        String sql = "SELECT * FROM Events";
        String dt = "SELECT TOP 1 [Date] FROM Events";
        String count = "SELECT COUNT(ID) FROM Events";

        Statement cmd = conn.createStatement(); 
        Statement cmd1 = conn1.createStatement();   
        Statement cmd2 = conn2.createStatement();   

        cmd.execute(sql);
        cmd1.execute(dt);
        cmd2.execute(count);

        ResultSet rc = cmd2.execute(count);

        int r_count = cmd2.execute(count);

Need help to fix this.

Answer 1

Bad code in too many ways. Start reading the JDBC tutorial.

Try something like this:

ResultSet rc = cmd2.execute(count);
int r_count = 0;
while (rc.next()) {
    r_count = rc.getInt(1);
}

You aren't closing any of your JDBC resources, which will only come to grief.

You should externalize your connection information.

I'd encapsulate more of your stuff into small, isolated methods rather than jumbling multiple statements together this way.

You ought to think about a well-defined, interface-based persistence layer.

Make those SQL queriesprivate static final String. No need for them to be local.

I'd recommend that you try something more like this. Start with an interface:

package persistence;

import java.util.List;
import java.util.Map;

/**
 * EventDao
 * @author Michael
 * @link http://stackoverflow.com/questions/5016730/creating-a-dsn-less-connection-for-ms-access-within-java
 * @since 6/25/13 5:19 AM
 */
public interface EventDao {
    List<Map<String, Object>> findAllEvents();
}

Then write an implementation:

package persistence;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * EventDaoImpl
 * @author Michael
 * @link http://stackoverflow.com/questions/17213307/execute-access-query-in-resultset-java/17213356?noredirect=1#comment25072519_17213356
 * @since 6/25/13 5:15 AM
 */
public class EventDaoImpl implements EventDao {

    private static final String DEFAULT_URL = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)}; DBQ=C:\\test\\TestEJFolder\\BWC_Ejournal.mdb;";
    private static final String SQL_FIND_ALL_EVENTS = "SELECT * FROM Events";

    private Connection connection;

    public EventDaoImpl(Connection connection) {
        this.connection = connection;
    }

    @Override
    public List<Map<String, Object>> findAllEvents() {
        List<Map<String, Object>> results = new ArrayList<Map<String, Object>>();
        Statement st = null;
        ResultSet rs = null;
        try {
            st = this.connection.createStatement();
            rs = st.executeQuery(SQL_FIND_ALL_EVENTS);
            results = map(rs);
        } catch (SQLException e) {
            e.printStackTrace();  
            throw new RuntimeException(e);
        } finally {
            close(rs);
            close(st);
        }
        return results;
    }

    public static Connection createConnection(String driver, String url, String username, String password) throws ClassNotFoundException, SQLException {
        Class.forName(driver);
        if ((username == null) || (password == null) || (username.trim().length() == 0) || (password.trim().length() == 0)) {
            return DriverManager.getConnection(url);
        } else {
            return DriverManager.getConnection(url, username, password);
        }
    }

    public static void close(Connection connection) {
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


    public static void close(Statement st) {
        try {
            if (st != null) {
                st.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void close(ResultSet rs) {
        try {
            if (rs != null) {
                rs.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void rollback(Connection connection) {
        try {
            if (connection != null) {
                connection.rollback();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static List<Map<String, Object>> map(ResultSet rs) throws SQLException {
        List<Map<String, Object>> results = new ArrayList<Map<String, Object>>();
        try {
            if (rs != null) {
                ResultSetMetaData meta = rs.getMetaData();
                int numColumns = meta.getColumnCount();
                while (rs.next()) {
                    Map<String, Object> row = new HashMap<String, Object>();
                    for (int i = 1; i <= numColumns; ++i) {
                        String name = meta.getColumnName(i);
                        Object value = rs.getObject(i);
                        row.put(name, value);
                    }
                    results.add(row);
                }
            }
        } finally {
            close(rs);
        }
        return results;
    }

    public static List<Map<String, Object>> query(Connection connection, String sql, List<Object> parameters) throws SQLException {
        List<Map<String, Object>> results = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = connection.prepareStatement(sql);

            int i = 0;
            for (Object parameter : parameters) {
                ps.setObject(++i, parameter);
            }
            rs = ps.executeQuery();
            results = map(rs);
        } finally {
            close(rs);
            close(ps);
        }
        return results;
    }
}