ansaurus

Question

Java - JDBC driver and MySQL database connection issues

Answer 1

A:

Can mysql accept network connections? When you connect with the "mysql" command line program, you're not doing a network connection, but when you do with JDBC you are making a network connection. Try using "-h localhost" in your mysql command line to see.

Paul Tomblin 2010-06-18 22:54:51

Yes - the database is hosted on a godaddy server and I haven't had any problems connecting to this database or others hosted on the same server with other programs. I just reinstalled windows 7 and the jdbc driver, I don't know if maybe something installed wrong? I've never had this error happen before. If it matters - I am using netbeans to build the program.

Jason 2010-06-18 23:08:58

Netbeans is immaterial. I wouldn't like being able to connect directly to a database over the Internet this way.

duffymo 2010-06-18 23:13:47

@Jason, I'd say then that @duffymo has it right and throwing random versions of the JDBC driver at the problem aren't going to help - you've got to get the one that came with that version of MySQL.

Paul Tomblin 2010-06-18 23:19:33

Answer 2

+2 A:

This may not solve it, but it tells you that someone else has had this problem.

Make sure you have the precise version of JDBC driver to match your version of MySQL.

I would strongly urge you to rewrite your code more like this. You aren't closing resources properly at all.

Adapt it to your own needs. I created a local MySQL database on my machine and added a customer table. It worked just fine.

package persistence;

import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

public class DatabaseUtils
{
    private static final String URL = "jdbc:mysql://localhost:3306/contacts";
    private static final String USERNAME = "contacts";
    private static final String PASSWORD = "contacts";

    public static final String SELECT_SQL = "select customer_id, name, street, city, state, zip, phone, url from customer order by customer_id";
    public static final String INSERT_SQL = "insert into customer(name, street, city, state, zip, phone, url) values(?,?,?,?,?,?,?)";

    public static void main(String[] args)
    {
        Connection connection = null;

        try
        {
            connection = getConnection(URL, USERNAME, PASSWORD);
            List<Map> rows = findAllCustomers(connection);

            for (Map row : rows)
            {
                System.out.println(row);                    
            }
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
        finally
        {
            close(connection);
        }
    }

    public static List<Map> findAllCustomers(Connection connection) throws SQLException
    {
        List<Map> rows = new ArrayList<Map>();
        PreparedStatement st = null;
        ResultSet rs = null;

        try
        {
            st = connection.prepareStatement(SELECT_SQL);
            rs = st.executeQuery();
            while (rs.next())
            {
                rows.add(map(rs));                
            }
        }
        finally
        {
            close(rs);
            close(st);
        }

        return rows;
    }

    private static Map<String, Object> map(ResultSet rs) throws SQLException
    {
        Map<String, Object> row = new LinkedHashMap<String, Object>();

        ResultSetMetaData meta = rs.getMetaData();

        int numColumns = meta.getColumnCount();
        for (int i = 1; i <= numColumns; ++i)
        {
            String column = meta.getColumnName(i);
            Object value = rs.getObject(i);
            row.put(column, value);
        }

        return row;
    }

    public static Connection getConnection(String url, String username, String password) throws SQLException
    {
        Driver driver = DriverManager.getDriver(url);

        DriverManager.registerDriver(driver);

        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 void rollback(Connection connection)
    {
        try
        {
            if (connection != null)
            {
                connection.rollback();
            }
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
    }
}

duffymo 2010-06-18 23:05:18

ansaurus

tags:

views:

answers:

Java - JDBC driver and MySQL database connection issues

related questions