How do I use createQuery() Method to create JDBC Connection, using sql2o?

Question

The createQuery() method is wanting a cast to a Connection object, but seems that this method would work on a SQL2o object since it is in the package.... I am using sql2o to create my database connection; however, I do not understand why the use of .createQuery() method is wanting to be cast to a Connection object?

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;

    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;

    import java.util.ArrayList;

   import javax.servlet.ServletException;
   import javax.servlet.annotation.WebServlet;
   import javax.servlet.http.HttpServlet;
   import javax.servlet.http.HttpServletRequest;
   import javax.servlet.http.HttpServletResponse;

   import org.sql2o.Sql2o;

   import com.google.gson.Gson;
   import com.models.Person;
   import com.tools.DBUtil;
   import com.tools.DataTable;



   /**
    * Servlet implementation class SalesTeam
    */
    @WebServlet(name = "SalesTeam_Table", urlPatterns = {                   "/json/table/salesteam" })
    public class Table_SalesTeam extends HttpServlet {
    private static final long serialVersionUID = 1L;

    /**
     * @see HttpServlet#HttpServlet()
     */
    public Table_SalesTeam() {
        super();
        // TODO Auto-generated constructor stub
    }

    /**
     * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse
     *      response)
     */
    protected void doGet(HttpServletRequest request, HttpServletResponse    response)
            throws ServletException, IOException {

        // Creating an arraylist based on the Person model in com.models
        ArrayList<Person> people = new ArrayList<Person>();
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;


        String DB_URL = "jdbc:mysql://localhost:3306/salesteam";
        String USER = "root";
        String PASS = "1234 ";
        Sql2o sql2o = new Sql2o(DB_URL, USER, PASS);

        String sql =
                "SELECT empID, fName " +
                "FROM salesteam " +
                "WHERE empID = 1";


    //issue is here...
        try (Connection con = sql2o.open()){
                people = con.createQuery(sql).executeAndFetch(Person.class);

     ////////////////////////

            //Selecting every record in sales_team table
            //pst = conn.prepareStatement("select f_Name, l_Name, email,           contactNum from sales_team ORDER BY f_Name ASC "); 
            //rs = pst.executeQuery();
            while (rs.next()) {
                //Create a person object and fill fields
                Person p = new Person();
                p.setfName(rs.getString("f_Name"));
                p.setlName(rs.getString("l_Name"));
                p.setEmail(rs.getString("email"));
                p.setContact(rs.getString("contactNum"));

                //Add person to people array list
                people.add(p);
            }
        } catch (SQLException e) {

            e.printStackTrace();
        }finally {
            //Must close the database objects
            DBUtil.close(conn, pst, rs);
        }

        //Gson Library was added to WEB-INF/lib
        //Creating a new gson object to hold json
        Gson gson = new Gson();

        //Create a custom DataTable object that takes an ArrayList<Object> and   makes an object
        //Used to create correct datatable json formatting
        DataTable table = new DataTable();
        table.setData(people);

        //Creating string by converting people arraylist to json string with gson object
        //Read up on gson library for json at http://www.mkyong.com/java/how-do-convert-java-object-to-from-json-format-gson-api/
        String json = gson.toJson(table);

        //Uncomment line below and look in console for what json looks like
        System.out.println(json);

        //Write json string to response
        PrintWriter out = response.getWriter();
        out.print(json);
        out.flush();

    }

    /**
     * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse
     *      response)
     */
    protected void doPost(HttpServletRequest request, HttpServletResponse                 response)
            throws ServletException, IOException {
        // TODO Auto-generated method stub
        doGet(request, response);
    }

   }

Answer 1

Read the Javadoc carefully. sql2o#open() returns an org.sql2o.Connection, which does not inherit from java.sql.Connection. If you want the underlying JDBC Connection object you have to call org.sql2o.Connection#getJdbcConnection()

It appears sql2o is behind the times, as the more recent JDBC API includes methods (Wrapper#unwrap() and isWrapperFor()) to simplify the usage of custom implementations of JDBC classes.