Java newbie needs help with database connection

Question

I'm new to Java but even newer to java database connections. I've managed to create a database connection and query a table when I put it in the Main class. Now that I've moved it into a new class called Connection I've getting errors:

package lokate;


import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;


public class Connection {

private static Statement stmt = null;
private static ResultSet rs = null;
private static Connection con = null;

public Connection() throws SQLException {
    try {
        Class.forName("com.mysql.jdbc.Driver");
        String connectionUrl = "jdbc:mysql://localhost:3306/Lokate?" +
                               "user=root&password=";

        con = DriverManager.getConnection(connectionUrl);
        stmt = con.createStatement();

        retriveData("SELECT * FROM Users");

        int rowsEffected = 0;

    } catch (SQLException sqlEx) {
        System.out.println("SQL Exception: "+ sqlEx.toString());
    } catch (ClassNotFoundException classEx) {
        System.out.println("Class Not Found Exception: "+ classEx.toString());
    } catch (Exception Ex) {
        System.out.println("Exception: "+ Ex.toString());
    }
}



public static void retriveData(String SQL) throws Exception {
    rs = stmt.executeQuery(SQL);

    while (rs.next()) {
        System.out.println(rs.getString("fname") + " : " + rs.getString("lname"));
        }
}

}

I'm getting an error saying canno find symbol. Symbol:method createStatement() and incomparable types for con = DriveManager.....

Can anyone help?

Also, is it best practice to put the connection in the class like this then call a new object every time I want to do something with the db?

Regards,

Billy

Answer 1

Your problem is that DriverManager.getConnection returns a java.sql.Connection. Since your class is also called Connection you are getting a name clash between lokate.Connection and java.sql.Connection. You Will need to specify the fully qualified class name where ever you want to use java.sql.Connection, otherwise lokate.Connection is assumed.

Specify fully qualified class name like so:

java.sql.Connection con = null;
// ....
con = DriverManager.getConnection(connectionUrl);

Alternatively, rename your Connection class to something else and you will not get this naming conflict.

Answer 2

Connection is an existing type in the java.sql package, which is what DriverManager.getConnection returns. You have named your class as Connection too, so this is causing the confusion. The simplest way out would be to rename your class to something else and add an import java.sql.Connection; at the top.

Answer 3

I'd say your code is an example of many worst practices. Let me count the ways:

1. Your Connection class is a poor abstraction that offers nothing over and above that of java.sql.Connection.
2. If you use your class, you'll never get to take advantage of connection pooling.
3. You hard wire your driver class, your connection URL, etc. You can't change it without editing and recompiling. A better solution would be to externalize such things.
4. Printing an error message in the catch blocks is far less information than supplying the entire stack trace.
5. Your code hurts my eyes. It doesn't follow the Sun Java coding standards.
6. Your retrieveData method is utterly worthless. What will you do with all those printed statements? Wouldn't it be better to load them into a data structure or object so the rest of your code might use that information?
7. It's rowsAffected - "affect" is the verb, "effect" is the noun. Another variable that's not doing any good.

You're on the wrong track. Rethink it.

I think you'll find this code more helpful.

package persistence;

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

public class DatabaseUtils
{
    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;
    }
}

Answer 4

Hi,

all abt JDBC connection is here,

http://www.codinguide.com/2010/04/prepared-statement-sql-injection-proof.html

Regards,

Answer 5

@duffymo I would like to add one more best practice to this code.. We can include some 'Javadoc Comments' to this, though the code itself is self explanatory.