JDBC with MySQL

Question

I'm working on getting my database to talk to my Java programs. What do I need to get started?

Having already read through (and been thoroughly confused, something that does not happen often) with some other turorials I figured I'd best ask here. How do I import a jar file from the local directory? Can someone give me a quick and dirty sample program using the JDBC?

Following this answer gives me this.

SqlTest.java:16: cannot find symbolsymbol  : class Connectionlocation: class SqlTest        Connection conn = DriverManager.getConnection( "jdbc:mysql://localhost:3306/projects", "user", "123" );     ^SqlTest.java:16: cannot find symbolsymbol  : variable DriverManagerlocation: class SqlTest     Connection conn = DriverManager.getConnection( "jdbc:mysql://localhost:3306/projects", "root", "123" );                       ^SqlTest.java:17: cannot find symbolsymbol  : class Statementlocation: class SqlTest      Statement st = conn.createStatement();      ^SqlTest.java:18: cannot find symbolsymbol  : class ResultSetlocation: class SqlTest        ResultSet rs = st.executeQuery( "select * from table" );        ^4 errors

I'm on OS X if that makes any difference.

Now I'm getting a rather stupendous error:

Exception in thread "main" com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: 
Communications link failureThe last packet sent successfully to the server was 0 
milliseconds ago. The driver has not received any packets from the server.  at 
sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)   at 
sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.
java:27)    at java.lang.reflect.Constructor.newInstance(Constructor.java:513)  at 
com.mysql.jdbc.Util.handleNewInstance(Util.java:409)    at 
com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1122)   at 
com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2260) at 
com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:787)   at 
com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:49)  at 
sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)   at 
sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
    at 
sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.
java:27)    at java.lang.reflect.Constructor.newInstance(Constructor.java:513)  at 
com.mysql.jdbc.Util.handleNewInstance(Util.java:409)    at 
com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:357)  at 
com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:285)  at 
java.sql.DriverManager.getConnection(DriverManager.java:582)    at 
java.sql.DriverManager.getConnection(DriverManager.java:207)    at 
SqlTest.main(SqlTest.java:22)Caused by: 
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failureThe 
last packet sent successfully to the server was 0 milliseconds ago. The driver has not 
received any packets from the server.   at 
sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)   at 
sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
    at 
sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.
java:27)    at java.lang.reflect.Constructor.newInstance(Constructor.java:513)  at 
com.mysql.jdbc.Util.handleNewInstance(Util.java:409)    at 
com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1122)   at 
com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:344) at 
com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2181) ... 12 moreCaused 
by: java.net.ConnectException: Connection refused   at 
java.net.PlainSocketImpl.socketConnect(Native Method)   at 
java.net.PlainSocketImpl.doConnect(PlainSocketImpl.java:333)    at 
java.net.PlainSocketImpl.connectToAddress(PlainSocketImpl.java:195) at 
java.net.PlainSocketImpl.connect(PlainSocketImpl.java:182)  at 
java.net.SocksSocketImpl.connect(SocksSocketImpl.java:432)  at 
java.net.Socket.connect(Socket.java:529)    at 
java.net.Socket.connect(Socket.java:478)    at java.net.Socket.<init>
(Socket.java:375)   at java.net.Socket.<init>(Socket.java:218)  at 
com.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:256)    at 
com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:293) ... 13 more

Contents of the test file:

import com.mysql.jdbc.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class SqlTest
{
    public static void main(String [] args) throws Exception
    {
        // Class.forName( "com.mysql.jdbc.Driver" ); // do this in init
        // // edit the jdbc url 
        Connection conn = DriverManager.getConnection( "jdbc:mysql://localhost:3306/projects?user=user1&password=123");
        // Statement st = conn.createStatement();
        // ResultSet rs = st.executeQuery( "select * from table" );

        System.out.println("Connected?");
    }
}

Answer 1

Have a look at Sun's JDBC tutorial. It's not specifically about MySQL, but the idea of JDBC is that it's database agnostic - it doesn't matter which database you use, you just have to use the appropriate JDBC driver and connection string.

For MySQL, you can download the JDBC driver here. It consists of a JAR file which you need to put in your classpath. See the documentation of the driver, which also contains examples of how to use it.

Answer 2

What do I need to get started? a JDBC driver, depending on what SQL / Database you're using. e.g: MS SQL use jtds http://jtds.sourceforge.net/

How do I import a jar file from the local directory? Add it to classpath, using -cp option or in MANIFEST.MF file in your jar add Class-Path: thejdbcdriver.jar

Answer 3

Download MySQL-JDBC-Type-4-Treiber (i.g. 'mysql-connector-java-5.1.11-bin.jar' from 'mysql-connector-java-5.1.11.zip') at Mysql.

You need to inculde the driver jar during compile- and runtime in your classpath.

Class.forName( "com.mysql.jdbc.Driver" ); // do this in init
// edit the jdbc url 
Connection conn = DriverManager.getConnection( "jdbc:mysql://MyDbComputerNameOrIP:3306/myDatabaseName", username, password );
Statement st = conn.createStatement();
ResultSet rs = st.executeQuery( "select * from table" );

Answer 4

I might be barking up the wrong tree here, but your exception seems to indicate your MySQL server isn't available.

Exception in thread "main" com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failureThe last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server. at...

What happens if you try (from the terminal)

mysql -u username -p

You will be prompted for the password associated with the username. After you give the correct password does the mysql client connect?

You may have to start MySQL from the Preferences if not. You can also set it to run at startup.

Answer 5

So, you have a

CommunicationsException: Communications link failure

I'm quoting from this answer which also contains a step-by-step MySQL+JDBC tutorial:

If you get a SQLException: Connection refused or Connection timed out or a MySQL specific CommunicationsException: Communications link failure, then it means that the DB isn't reachable at all. This can have one or more of the following causes:

1. IP address or hostname in JDBC URL is wrong.
2. Hostname in JDBC URL is not recognized by local DNS server.
3. Port number is missing or wrong in JDBC URL.
4. DB server is down.
5. DB server doesn't accept TCP/IP connections.
6. DB server has run out of connections.
7. Something in between Java and DB is blocking connections, e.g. a firewall or proxy.
   
   

To solve the one or the other, follow the following advices:

1. Verify and test them with ping.
2. Refresh DNS or use IP address in JDBC URL instead.
3. Verify it based on my.cnf of MySQL DB.
4. Start the DB.
5. Verify if mysqld is started without the --skip-networking option.
6. Restart the DB and fix your code accordingly that it closes connections in finally.
7. Disable firewall and/or configure firewall/proxy to allow/forward the port.