Java-mysql connection

[Kligham]

Hi,

I'm reading a book about mysql and java atm. The book is a bit old (from 2003). Now when I want to try to execute an example, netbeans gives me 2 errors:

package mysql;

import java.sql.*;

public class Hello {
    Connection connection;
    private void displaySQLErrors(SQLException e) {
        System.out.println("SQLException: " + e.getMessage());
        System.out.println("SQLState: " + e.getSQLState());
        System.out.println("VendorError: " + e.getErrorCode());
    }

    public Hello() {
        try {
            [COLOR="Red"]Class.forName("com.mysql.jdbc.Driver").newInstance();[/COLOR]
        }
        [COLOR="red"]catch (SQLException e) {[/COLOR]
            System.err.println("Unable to find and load driver");
            System.exit(1);
        }
    }

    public void connectToDB() {
        try {
            connection = DriverManager.getConnection(
            "jdbc:mysql://localhost:3306/test?user=root&password=o94gcd2rx");
        }
        catch(SQLException e) {
            displaySQLErrors(e);
        }
    }

    public void executeSQL() {
        try {
            Statement statement = connection.createStatement();
            ResultSet rs = statement.executeQuery("SELECT * FROM data WHERE voornaam = Peter");
            while (rs.next()) {
                System.out.println(rs.getString("voornaam"));
            }
        rs.close();
        statement.close();
        connection.close();
        }
        catch(SQLException e) {
            displaySQLErrors(e);
        }
    }

    public static void main(String[] args) {
        Hello hello = new Hello();
        hello.connectToDB();
        hello.executeSQL();
    }
}

The errors that netbeans give me, can be seen on the 2 pics in the attachments
.

Grtz

[PhHein]

Uhm, those are pretty clear error messages. "Must be caught or declared to be thrown" - do it. "is never thrown" - so why catch it?

EDIT: Maybe you should put that book away for a while and start with the very basics of Java.

[Kligham]

I found in the java docs api the following for ClassforName:

Throws:
LinkageError - if the linkage fails
ExceptionInInitializerError - if the initialization provoked by this method fails
ClassNotFoundException - if the class cannot be located

Does this mean I need to catch these three exceptions after the ClassforName try block and delete the SQLException catch block?


Grtz

[PhHein]

Yup, or you just catch a common super class or you declare them to be thrown.

But you should really get the basics first. Read this: http://java.sun.com/docs/books/tutorial/index.html

[Tolls]

Also, there's no need to call newInstance().
Class.forName() is sufficient to load the class, thus removing the need for catching the InstantiationException. If the book you're using has that as a line I'd question how good it is, frankly.

[Kligham]

The book is from 2003. It was the only I found about java and mysql. If there is another one, you can post it here or a link to a good website where there is a good java-mysql connection example. The most examples on the internet uses that newinstance, however they are mostly also a couple of years old.

[Tolls]

Well there's always the JDBC tutorial at Sun.

But, even in 2003 there was no need to call newInstance().

[chirag.jain18]

Replace the code :
<code>

try {
Class.forName("com.mysql.jdbc.Driver").newInstance ();
}
catch (SQLException e) {
System.err.println("Unable to find and load driver");
System.exit(1);
}



</code>
with this code:

<code>

try {
Class.forName("com.mysql.jdbc.Driver");
}
catch (Exception e) {
System.err.println("Unable to find and load driver");
System.exit(1);
}

</code>

it will work now.

[masijade]

However, that is bad habit. It takes a bit of experience to know when you should simply use an all encompassing "Exception" or "Throwable" catch as oppossed to catching individual exceptions, and, seemingly, the OP is not that advanced yet, and so this is not good advice, at this point.

[Kligham]

However, that is bad habit. It takes a bit of experience to know when you should simply use an all encompassing "Exception" or "Throwable" catch as oppossed to catching individual exceptions, and, seemingly, the OP is not that advanced yet, and so this is not good advice, at this point.

How do I know when I need to use "Exception" or a indidual exceptions? How about this example?

[masijade]

Like I said, it comes with experience. There are times where you want to catch RuntimeExceptions (which Exception will do), but most times you don't, There are times when Exceptions can actually be handled and recovered from, but for that you need to handle different Exceptions differently, so Exception is not good, there are other times where it doesn't matter what happened, you just want to log and exit/continue, then Exception is okay. But you need to learn this, and you need to be able to apply that knowledge to indiviual situations. There is no "rule-of-thumb", except maybe "never catch Exception/Throwable, always catch specific Exceptions".

[Kligham]

I do have 2 more questions after reading the JDBC tutorial:
1) There are 2 ways to establish the connection: either with drivermanager or datasource. So basically, which do I use? They say that datasource is preferred, but I haven't seen any example on the internet with datasource.

2)When do I use statement and when do I use the Preparedstatement?

Grtz

[JosAH]

I do have 2 more questions after reading the JDBC tutorial:
1) There are 2 ways to establish the connection: either with drivermanager or datasource. So basically, which do I use? They say that datasource is preferred, but I haven't seen any example on the internet with datasource.

2)When do I use statement and when do I use the Preparedstatement?

Grtz

A DataSource is implemented by a driver vendor and you can't instantiate one with a 'new' operator or your own factory or whatever. You have to ask a JNDI implementation for one; for now better stick to the Driver mechanism.

If you want to construct your String representation of your SQL query with String fiddling better use a PreparedStatement; it protects you against SQL injection and it saves you the burden to get all those single and double quotes right.

kind regards,

Jos

[chirag.jain18]

If you use Statement, the SQL stetement is parsed every time the query is executed, If you use PreparedStatement, parsing is done only once.
So if you are executing same query many times (in for or while loop) with different parameters values, use PreparedStatement, as it will give better performance.

[Tolls]

The better guide is as Jos says. If you have to fiddle with Strings then the odds are that you should be using a PreparedStatement.

In general you shouldn't concatenate your parameters into the query. It's not good in terms of SQL injection, or in terms of performance on the database.

The only real exception to that is lopsided flags. That is, your table has a flag (say STATE) in which one value is significantly more represented than others (there are fewer "active" than "complete" for example). In this case it might be better, in terms of execution on the db, to concatenate that one in rather than bind it. This, though, is something that comes from analysing your queries and your data, and is by no means a rule.

[JosAH]

I think this link is appropriate here ;-)

kind regards,

Jos

[Tolls]

hehehehe.
:)