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!
