Lesson 6 - Programming a Database Wrapper in Java - The Database class
In the previous lesson of our Java database tutorials series, Database Wrapper in Java - Creating the Query class, we
started creating a database wrapper to simplify our work with the database and
created the Query
class. Today, we're going to create a
Database
class, which is documented, but still I'll describe some
parts of it in more detail. But before that, we'll test our Query
class.
Testing the Query class
In the Wrapper
class, we'll paste the following code into the
main()
method to test each method of the Query
class:
Query query = new Query(); query.delete("programmers").where("name = ?"); System.out.println(query.getQuery()); //Output: DELETE FROM programmers WHERE name = ? String[] columns3 = {"name","language"}; query.update("programmers").set(columns3).where("where id = ?"); System.out.println(query.getQuery()); //Output: UPDATE programmers SET name = ?,language = ? WHERE where id = ? query.select(null).from("programmers").where("language = ?"); System.out.println(query.getQuery()); //Output: SELECT * FROM programmers WHERE language = ? Object[] firstParams = {null, "Galí", 43, "PHP"}; query.insert("programmers").values(firstParams); System.out.println(query.getQuery()); //Output: INSERT INTO programmers VALUES(?,?,?,?);
Don't forget to import the Query
class!
Now let's move on to the Database
class, which holds the entire
wrapper together.
Constructor - Database Connection
In the constructor, we'll connect to the database and store this connection:
package DB; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.PreparedStatement; /** * * @author Milan Gallas */ public class Database { //connecting to the database protected Connection connection; //the Query class instance protected Query query; /** * The Database class constructor * @param db * @param userName * @param password * @throws SQLException */ public Database(String db, String userName, String password) throws SQLException{ connection = DriverManager.getConnection("jdbc:mysql://localhost/" + db, userName, password); } }
Note: If we used the wrapper in a web application instead of in a desktop one, this approach would not be appropriate because of the large number of active database connections. In this case, we should reconnect in every method using the TWR block, as you know from the previous tutorials.
A general query() method
This method will be private and it'll be an auxiliary method for the
delete()
, save()
, and update()
methods
that we'll create in the next chapters.
public class Database { //class attribute //class methods /** * * @param query * @param params * @return * @throws SQLException */ private int query(String query, Object[] params) throws SQLException{ PreparedStatement ps = connection.prepareStatement(query); if (params != null){ int index = 1; for(Object param : params){ ps.setObject(index, param); index++; } } return ps.executeUpdate(); } }
The method accepts an SQL query as the first parameter and then it accepts any number of parameters of different types.
Also note the setObject()
method of the
PreparedStatement
class. Thanks to this method, we can store values
without knowing their data type yet. This method is a rescue for us and will
make our work much easier.
The method returns the number of affected rows. The exception that the query
might throw is propagated automatically thanks to using the throws
keyword in the method header. The exception data type is
SQLException
in this case.
Deleting entries - The delete() method
public class Database { //class attribute //class methods /** * Removes data from a database table * @param table * @param requirement * @param param * @return * @throws SQLException */ public int delete(String table, String requirement, Object[] param) throws SQLException{ query = new Query(); query.delete(table) .where(requirement); return query(query.getQuery(), param); } }
We can pass a condition and parameters to the delete()
method,
and only those entries that meet our criteria are deleted.
Exceptions are also thrown automatically in this method and we leave them to be caught by the class from which the method will be called.
The method then returns the number of affected rows. We then use this value
to determine if the query has been successful. This return value is also
returned by the save()
and update()
methods! It's
important to remember that the resulting SQL query is returned by the
getQuery()
method. A common mistake is that only a
Query
instance is passed to the query()
method. See
the example:
//This code won't work return query(query, param);
Adding entries - The insert() method
public class Database { //class attribute //class methods /** * Inserts one row to a database table * @param table * @param params * @return * @throws java.sql.SQLException */ public int insert(String table, Object[] params) throws SQLException{ query = new Query(); query.insert(table) .values(params); return query(query.getQuery(), params); } }
The insert()
method of the Database
class has 2
parameters. The table name and the data to be written to the table. The method
is simple and there's nothing more to say.
Updating data - The update() method
public class Database { //class attribute //class methods /** * Updates data stored in a database table * @param table * @param columns * @param requirement * @param params * @return * @throws SQLException */ public int update(String table, String[] columns, String requirement, Object[] params) throws SQLException{ query = new Query(); query.update(table) .set(columns) .where(requirement); return query(query.getQuery(), params); } }
The first three parameters of this method are: the database table name, a
column names array, and the condition. In addition, the method may have other
parameters of the Object
type specifying data to be written to the
table instead of the old ones.
You can see the benefits of the fluent interface in all methods of the
Database
class. The code is very simple, readable, and easy to
understand.
So we still have the select()
method to finish. We'll create
this method in the next lesson, Finishing a Database Wrapper in Java, and there we'll work with our wrapper
and test it.