SQL connection : can't connect to database in java

Question

I have added JAR file "mysql-connector-java-5.1.36-bin" and have created database "UserScore" and under that table "ScoreSheet".I have added a row in ScoreSheet (Name,Score) value ('Kamal',40) to verify but the code throws a lot of exceptions on connection.I am using Eclipse + Xampp.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class SQLDriver{
    public static void main(String[] args){
        try{
            //Accessing driver from jar file
            Class.forName("com.mysql.jdbc.Driver");

            //Get connection through creating a variable 'myConn'
            Connection myConn = DriverManager.getConnection("jdbc:mysql://localhost:3306/UserScores");

            //Create statement
            PreparedStatement statement = myConn.prepareStatement("select * from 'scoresheet'");

            //Execute SQL Query
            ResultSet result = statement.executeQuery();

            //Process the result set
            while(result.next()){
                System.out.println(result.getString(1)+" "+result.getString(2));
            }
        }
        catch(Exception e){
            e.printStackTrace();
        }
    }
}

`but the code throws a lot of exceptions on connection`. Please mention those — mustangDC, Jul 27 '15 at 17:52
There is an error in your query. I think you meant `select * from scoresheet;`. — pietv8x, Jul 27 '15 at 17:52
https://www.dropbox.com/s/4al4gaaayyrs755/Screenshot%202015-07-27%2023.36.54.png?dl=0 — Kamal Vidhani, Jul 27 '15 at 18:07

score 0 · Answer 1 · answered Jul 27 '15 at 17:57

0

Your query should specify Scoresheet without '
Specify the username and password when calling DriverManager.getConnection
The second row in your table does not appear to be of type string. Invoke result.getInt(2) rather than result.getString(2)

answered Jul 27 '15 at 17:57

Emile Pels

3,837
1
15
23

Haven't set any username/password to my database and I followed your first and last point but it didn't work out. – Kamal Vidhani Jul 27 '15 at 18:29
@KamalVidhani Judging by the screenshot you posted, it appears MySQL is not running. – Emile Pels Jul 27 '15 at 18:32
"MySQL is not running" means? – Kamal Vidhani Jul 27 '15 at 18:38
@KamalVidhani Exactly what it says. You can start it like this, assuming you're on a Windows system: https://dev.mysql.com/doc/refman/5.0/en/windows-start-command-line.html – Emile Pels Jul 27 '15 at 18:41

score 0 · Accepted Answer · answered Jul 27 '15 at 18:22

0

I always do it like this:

// Hostname
private static final String dbHost = "127.0.0.1";

// Port -- Standard: 3306
private static final String dbPort = "3306";

// Database name
private static String database = "database"; //

// Database user
private static final String dbUser = "root";

// Datenbankpasswort
private static final String dbPassword = "";

private Statement s;


public void Connect() {
    try {
        Class.forName("java.sql.Driver"); // load driver
        Connection conn = DriverManager.getConnection("jdbc:mysql://" + dbHost + ":"
                + dbPort + "/" + database + "?" + "user=" + dbUser + "&"
                + "password=" + dbPassword); // try to connect with your attributes 
        s = conn.createStatement();
    } catch (ClassNotFoundException e) { // 
        l.error("Driver not found " + e);
    } catch (SQLException e) {
        l.error("Connect not possible" + e);
    }
}

answered Jul 27 '15 at 18:22

L4B0MB4

151
1
11

I replaced localhost to 127.0.0.1 but didn't work out, rest is logically same I guess. – Kamal Vidhani Jul 27 '15 at 18:42
was there an exception ? – L4B0MB4 Jul 27 '15 at 18:45
lots....but I think the number got reduced...so I am sharing screenshot link: https://www.dropbox.com/s/bwr4m3mefd2yn7p/Screenshot%202015-07-28%2000.23.59.png?dl=0 – Kamal Vidhani Jul 27 '15 at 18:53
okay the problem is that your user is not allowed to connect to the database. you have to allow the connection. you can do this with phpmyadmin or with sql commands : http://stackoverflow.com/questions/10236000/allow-all-remote-connections-mysql for example – L4B0MB4 Jul 27 '15 at 19:03
Thanks user4139197. I created a new user with custom privileges. – Kamal Vidhani Jul 28 '15 at 16:53

SQL connection : can't connect to database in java

2 Answers2