Simple MySQL Library for Java

PHP like MySQL Programming for Java

About

A few years ago I released a Java library allowing programers to utilize the MySQL database in simple programs without the need of any persistence frameworks, or without the need of having in depth knowledge of the JDBC API. It was called the SimpleMySQL library. The library allows you to use functions and methods similar to PHP.

I wrote this when I was just starting out on the Java language (coming from PHP), daunted by the complexity of JDBC and persistence frameworks. Over the years I found myself utilizing the SimpleMySQL library in several of my Java projects. Even though my experience with Java has increased, and things such as JDBC or persistence no longer scare me, I still prefer to use this simple yet very functional code.

I am pleased to offer an updated version of the SimpleMySQL Library for Java. It includes new features, improved simplicity, a major code clean up, and of course several improvements applied from my longer experience with Java. Additionally Javadoc has been added.

Requirements

• You will need the MySQL JDBC Driver in order to use this. This is included with Netbeans
• A MySQL Database Server
• Basic Knowledge of Java Programming

How to use (Example)

Add the SimpleMySQL Jar and the MySQL JDBC jar to your projects library files. Then add the following import statements:

import simplemysql.SimpleMySQL;
import simplemysql.SimpleMySQLResult;

If your familiar with the old version of the SimpleMySQL library, you'll notice that we no longer need the java.sql.*; import. This is becuase of the new SimpleMySQLResult object. However, if you do want to continue using the java.sql.ResultSet, you'll need to add that import.

The SimpleMySQL object can be created in the same way you can create other objects, or you can utilize one of the new features of this library. The SimpleMySQL library supports the Singleton property, meaning that you only keep 1 instance of the object at any time throughout your program. This is useful for when you have multiple different types objects that require database access.

To create a new instance of SimpleMySQL:

SimpleMySQL mysql;
mysql = new SimpleMySQL();

SimpleMySQL mysql;
mysql = SimpleMySQL.getInstance();

WARNING: If your program requires connections to multiple databases, then you are probably better off extending the SimpleMySQL as your own singleton to retain the singleton functionality without running into issues. Otherwise you can create as many SimpleMySQL objects as you wish. Just remember, getInstance() will always return the last one that was created.

Connect to the database

Connecting to the database is as simple as one line, just like in PHP!

mysql.connect("mysql.example.com", "db_user", "db_password", "db_name");

If you do not want to specify the database name in the connect statement, you can omit it and later use the SelectDB function.

mysql.SelectDB("db_name");

The SELECT Query

One of the changes in this new version is the inclusion of two query methods. One is the original query function witch returns the standard ResultSet object. The second is the new simplified Query function that will return a SimpleMySQLResult. Notice that the difference between the two is the lowercase and capital 'Q'. The old (lowercase) query method is depreciated in the new SimpleMySQL library.

To perform a select query:

SimpleMySQLResult result;
result = mysql.Query ("SELECT * FROM myTable");

Print all the results:

while (result.next()){
	System.out.println((result.getString("name"));
}
result.close();

Or to fetch a whole row (similar to PHP's mysqli->fetch_assoc() function) is a little different, yet just as easy

Map<String, String> myRow;
while (!(myRow = result.FetchAssoc()).isEmpty()){
	System.out.println((myRow.get("name"));
	System.out.println((myRow.get("name1"));
	System.out.println((myRow.get("name2"));
}
result.close();

Users of the original SimpleMySQL may notice that you no longer have to enclose your code in a try-catch block. For those looking to use the standard java.sql.ResultSet object, you can call the SimpleMySQLResult.getResultSet() function.

Queries That do not Return a Result

Insert Query

mysql.query ("INSERT INTO myTable (name) VALUES("MyName")");
mysql.close();

Update Query

mysql.query ("UPDATE myTable SET(name="MyName")");
mysql.close();

The idea is the same for every other type of query.

Additional Features

Yes (now in Java) it's possible to get the number of rows in the result by calling one method: getNumRows(). It does what it says.

The SimpleMySQL library includes an automatic reconnect feature that is enabled by default. Should your database connection get lost or closed during the execution of your program, the SimpleMySQL object will automatically attempt to reconnect 15 times and wait 5 seconds between each connection attempt. This feature is fully configurable and can also be disabled.

mysql.setReconnectNumRetry(4); //4 attempts
mysql.setReconnectTime(10000); // 10 seconds
mysql.DisableReconnect(); //disable if desired

Where to get it

Use the download links below to get a copy of the complied JAR or the source code. You can also download an archive of the entire Netbeans project. If for some reason you want the old version, it can be found at the Origional SimpleMySQL Java Library web page.

ENJOY!