Jump to content


Check out our Community Blogs

Register and join over 40,000 other developers!


Recent Status Updates

View All Updates

Photo
- - - - -

MySQL Connections

mysql connection mysql_connect

  • Please log in to reply
11 replies to this topic

#1 chili5

chili5

    CC Mentor

  • Expert Member
  • PipPipPipPipPipPipPipPip
  • 3038 posts
  • Programming Language:Java, C#, PHP, JavaScript, Ruby, Transact-SQL
  • Learning:C, Java, C++, C#, PHP, JavaScript, Ruby, Transact-SQL, Assembly, Scheme, Haskell, Others

Posted 27 August 2009 - 05:27 AM

Connecting to MySQL with Java


A lot of businesses build there applications using MySQL to manage the database. Java is a common language for database access. Before you can connect to the database you need to get the MySQL connector and add it to your CLASSPATH

You can download the MySQL connector here: MySQL Official web site.

Most of the details are in connecting to the database. I find it makes sense to create a database connection class that you can use over and over again. This is one of the first things I did in my project was define a way that I can connect to a database. I created a class that managed how I connect to the database, and provided methods for me to send queries to the database.

There are two types of queries that I needed:

Queries that return a value (ex. SELECT)
Queries that do not return a value (ex. UPDATE)

These queries are treated differently in the Java language.

First I define the class body:

class db {
	
}

Next I need to define the member variables that I need.

private Connection conn;
private static Statement s;
private String sUser;
private String sPass;
private String sDB;

The conn object holds a connection to the database. The Statement object is needed to send queries to the database.
Then the three strings are the variables we need to connect to a database. These are the user name, the password and the database name. I made statement static so I can send queries from any form without playing pass the buck with my database connection. This works since the user must enter the program on the main form where I create the connection.

I made this class for my project but I made it so I could use this class for any database not just a specific one. With that in mind, we need to see the constructor.

public db(String sUsername, String sPassword, String sDB) {
        this.sPass = sPassword;
        this.sUser = sUsername;
        this.sDB = sDB;
        createConn();
}

We pass in the username, password and the database name. Then we create a connection with the createConn method.

This method handles a lot of details that aren't really that important. I wrote this method once and just forgot about it.

private void createConn() {
        // create Connection
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();

            // a connection is used to connect to a database ;)
            // you need a database URL with the database name, username, password and database
            this.conn = DriverManager.getConnection("jdbc:mysql:///" + this.sDB, this.sUser, this.sPass);
            s = conn.createStatement(); // this statement is used to execute queries
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

First we have to load the driver which we downloaded as the MySQL connector. Then we create a connection using the DriverManager class. Note, that the URL is used to connect to a MySQL database. Thus, this class only works with MySQL and I probably should have named it MySQLConn.

Now we create a statement that we can use to execute queries. If anything goes wrong an exception is thrown and data is outputted. This isn't good error handling because it won't stop the program if there is an error. That is one thing that I did not do a good job of with the last project was handling errors. I tried to prevent errors from even occurring but if something happens, it doesn't do much about it.

The statement object is what we need to send queries.

Now I define a method that is used to execute queries that return a value.

public ResultSet results(String sQuery) throws SQLException {
        // This method is used to execute SQL statements that return a result.
        // such as SELECT
        
        ResultSet r = null;
        r = s.executeQuery(sQuery);
        return r;

    }

This method takes a query, uses the statement to execute the query and returns the ResultSet of the executed query.

public void update(String sQuery) throws SQLException {
        /**
         * This method is used with statements that do not return a result.
         * Such as insert, delete and update.
         */
        s.executeUpdate(sQuery);


    }

This method is what I used to update parts of the database. It doesn't return anything hence the void method. It also forces the caller to handle the exception. Thus my code has a lot of try... catch blocks. This method can also be used to delete data but I never used it for that purpose. You cannot delete anything in my program.

That is the database connection class and it handles everything that I could ever possibly need to do.


Now let us look at the Result Set.

The ResultSet object is used with select queries. You can think of this object as a 2D map. Each column that you ask for from the select query is one entry in the map.

So this query:

SELECT name, age FROM people

Returns a result set where each row contains 2 entries name and age. Then you can use a getString method or getInt method to access the results. There is a get method for every Java data type.

You can also use the result set to update the database but I will go into that later. I didn't do that in my project, because I didn't know about it.

Now let us take a look at how to output the results of this query.

while (rs.next()) {
	System.out.println("Name: " + rs.getString("name") + " Age: " + rs.getInt("age"));
}

Think of rs as an iterator that starts off before the result set and rs.next() moves to the next item. This method returns false, when rs is pointing just after the last result.

That is all that is to it.


Using the Database class


db dbConn = new db("root","pwd","db");

String sQuery = "SELECT name, age FROM people";

try {
	ResultSet rs = dbConn.results(sQuery);

	while (rs.next()) {
		System.out.println("Name: " + rs.getString("name") + " Age: " + rs.getInt("age"));
	}

	sQuery = "UPDATE people SET age = age+1 WHERE name='James'";
	dbConn.update(sQuery);
} catch (SQLException ex) {
	System.out.println("Error accessing query.");
}	


Easy. My class probably should have handled the exceptions though.

More on the result set class later.

Hope this helps! :)
  • 3

#2 Guest_Jordan_*

Guest_Jordan_*
  • Guest

Posted 27 August 2009 - 05:55 AM

Very useful tutorial! +rep
  • 0

#3 Shaddix

Shaddix

    CC Addict

  • Just Joined
  • PipPipPipPipPip
  • 102 posts

Posted 27 August 2009 - 06:18 AM

nice tutorial, I will be playing a bit with it, seems interesting :D
  • 0

#4 chili5

chili5

    CC Mentor

  • Expert Member
  • PipPipPipPipPipPipPipPip
  • 3038 posts
  • Programming Language:Java, C#, PHP, JavaScript, Ruby, Transact-SQL
  • Learning:C, Java, C++, C#, PHP, JavaScript, Ruby, Transact-SQL, Assembly, Scheme, Haskell, Others

Posted 27 August 2009 - 06:30 AM

Thanks! :D If you need help, you know what to do. :)

lol Jordan you forgot to click +rep. I don't care that much but you forgot. :P
  • 0

#5 Shaddix

Shaddix

    CC Addict

  • Just Joined
  • PipPipPipPipPip
  • 102 posts

Posted 27 August 2009 - 07:19 AM

ok, stupid question to start with, but by adding the connector to your classpath, you would have to do that at every client you run your program at, right?

is there a way to avoid that?
  • 0

#6 WingedPanther73

WingedPanther73

    A spammer's worst nightmare

  • Moderator
  • 17757 posts
  • Location:Upstate, South Carolina
  • Programming Language:C, C++, PL/SQL, Delphi/Object Pascal, Pascal, Transact-SQL, Others
  • Learning:Java, C#, PHP, JavaScript, Lisp, Fortran, Haskell, Others

Posted 27 August 2009 - 07:35 AM

Looking at that, it seems like you could probably create an interface for all your database classes, to unify how you connect with databases. Very nice! +rep
  • 0

Programming is a branch of mathematics.
My CodeCall Blog | My Personal Blog

My MineCraft server site: http://banishedwings.enjin.com/


#7 chili5

chili5

    CC Mentor

  • Expert Member
  • PipPipPipPipPipPipPipPip
  • 3038 posts
  • Programming Language:Java, C#, PHP, JavaScript, Ruby, Transact-SQL
  • Learning:C, Java, C++, C#, PHP, JavaScript, Ruby, Transact-SQL, Assembly, Scheme, Haskell, Others

Posted 27 August 2009 - 08:26 AM

ok, stupid question to start with, but by adding the connector to your classpath, you would have to do that at every client you run your program at, right?

is there a way to avoid that?


Actually you don't have to. When you build your program, you can include the mysqlconnector with your jar. That is what I do with my program. The client then just needs to have MySQL running with the appropriate database setup with the appropriate user name and password.

Looking at that, it seems like you could probably create an interface for all your database classes, to unify how you connect with databases. Very nice! +rep


What do you mean?
  • 0

#8 John

John

    CC Mentor

  • Moderator
  • 4450 posts
  • Location:New York, NY

Posted 27 August 2009 - 08:30 AM

This is something I always wanted to do, but never felt like figuring out. :)
  • 0

#9 Shaddix

Shaddix

    CC Addict

  • Just Joined
  • PipPipPipPipPip
  • 102 posts

Posted 27 August 2009 - 08:32 AM

Actually you don't have to. When you build your program, you can include the mysqlconnector with your jar. That is what I do with my program. The client then just needs to have MySQL running with the appropriate database setup with the appropriate user name and password.


cool, would you mind explaining this a bit?
  • 0

#10 WingedPanther73

WingedPanther73

    A spammer's worst nightmare

  • Moderator
  • 17757 posts
  • Location:Upstate, South Carolina
  • Programming Language:C, C++, PL/SQL, Delphi/Object Pascal, Pascal, Transact-SQL, Others
  • Learning:Java, C#, PHP, JavaScript, Lisp, Fortran, Haskell, Others

Posted 27 August 2009 - 08:42 AM

What do you mean?


Let's say you have several database types you deal with: MySQL, SQL Server, Oracle, etc. You could create an interface so you can create an "instance" of the interface type and not worry about the database type as much... in theory :)... if I understand Java's interfaces right... which I might not.
  • 0

Programming is a branch of mathematics.
My CodeCall Blog | My Personal Blog

My MineCraft server site: http://banishedwings.enjin.com/


#11 chili5

chili5

    CC Mentor

  • Expert Member
  • PipPipPipPipPipPipPipPip
  • 3038 posts
  • Programming Language:Java, C#, PHP, JavaScript, Ruby, Transact-SQL
  • Learning:C, Java, C++, C#, PHP, JavaScript, Ruby, Transact-SQL, Assembly, Scheme, Haskell, Others

Posted 27 August 2009 - 08:52 AM

cool, would you mind explaining this a bit?


All I do is in Netbeans, I go to project properties, and then in the window that apppears I go to build. You will see a tabbed pane and there is a button that says "Add Jar/Folder" I use this to add the mysql connector to the build. When I do this and build the project, it adds the mysqlconnector to the build which I can distribute with the program.

I don't know how to do this without Netbeans. Netbeans does a lot of things for me. :)

Let's say you have several database types you deal with: MySQL, SQL Server, Oracle, etc. You could create an interface so you can create an "instance" of the interface type and not worry about the database type as much... in theory :)... if I understand Java's interfaces right... which I might not.


Um if you say so. I don't get interfaces either. We could create a base database connection class and then create an extension of the class for each database type. The only thing that would need to change is the createConn method in two places.

 this.conn = DriverManager.getConnection("jdbc:mysql:///" + this.sDB, this.sUser, this.sPass);

That line would be different. The database URL would change.

Class.forName("com.mysql.jdbc.Driver").newInstance();

That would change also. You would have to change the path to the appropriate Driver class. Then in theory, you would have a database library that would work for any database server.

This is something I always wanted to do, but never felt like figuring out. :)


:)

If I change

s = conn.createStatement(); // this statement is used to execute queries

to

s = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE); // this statement is used to execute queries

Then I can update the database without actually sending an update query. I would do something like this:

r.updateString("name", r.getString("name").toUpperCase());
r.updateRow();

The database changes and no SQL update statement. Easier to understand, less joining of strings and you don't need to worry about security issues. :)
  • 0

#12 Shaddix

Shaddix

    CC Addict

  • Just Joined
  • PipPipPipPipPip
  • 102 posts

Posted 27 August 2009 - 08:55 AM

All I do is in Netbeans, I go to project properties, and then in the window that apppears I go to build. You will see a tabbed pane and there is a button that says "Add Jar/Folder" I use this to add the mysql connector to the build. When I do this and build the project, it adds the mysqlconnector to the build which I can distribute with the program.

thanx, that helped a lot, I also use netbeans ;)

edit: seems like Netbeans comes with the mysql driver, you should check this
  • 0





Also tagged with one or more of these keywords: mysql, connection, mysql_connect

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download