MySQL and Java - Simple JDBC data access

This is a very lightweight Java api for reading and writing data to an access database. This api is meant for use in development and is not meant for production use. This api offers the following Java methods for reading and writing to a MySQL database.

Java Methods included in the simple MySQL JDBC API

• Run SQL Select and return data in comma delimited format
• Run SQL Select and return data in XML format
• Run SQL Query and return data as Java Vector (pipe delimited, first row contains column names)
• Write to any MySQL table (SQL Update, Insert, Delete)
• Return column names

A few uses of the simple MySQL JDBC API

• Quick application prototyping.
• To build screen generators
• Quickly extract data

The zip file includes the java code and class, a javadoc page for the lightweight jdbc api, a test class. All the SQL schema, and SQL insert scripts to load the data for the example. 

Contents simple MySQL JDBC API zip package

Download Simple MySQL JDBC API

How to use the simple MySQL JDBC API

This is a Java program so it is expected that you already have a decent understanding of classpath. If you are a novice or brand new to Java you will be disappointed if you just expect to drop the code and start using it. I am using Eclipse as my Java IDE. You can implement this in any IDE you want. You can also modify the package and path references to better meet your needs. This api has even been used at the command line.

Simple MySQL JDBC API FAQ's

Can I modify the source and enhance the program?

Yes. We just ask that you respect the original copyright and authorship and url's of the program.

Can I redistribute this program or a modified version of this program/package?

Yes. As long as you leave the original copyright and authorship and url's of the program.

Can I post this program or a modified version of this program/package on another website?

Yes. As long as you leave the original copyright and authorship and url's of the program.

Are you planning on enhancing this program?

No promises here.  If we have a development need to do so yes. If you have a need for an enhancement you should just go ahead and make the enhancement. This is why we put it in the open source.

Any plans for an Oracle or SQL Server version of this program?

Yes. We have versions for these DBMS's. They are not in a state to publish them right now. We are planning to do so at a later date.

Using the simple MySQL JDBC API

Making the JDBC connection

Syntax 

public java.lang.String setConnectionInfo(java.lang.String url,
java.lang.String db,
java.lang.String usr,
java.lang.String pw,
java.lang.String jdbcDrv)

Example

jdbc_mysql jdbc = new jdbc_mysql();
      String ret = "";
      ret = jdbc.setConnectionInfo("jdbc:mysql://127.0.0.1:3305/",
                                   "person", 
                                   "root", 
                                   "canal", 
                                   jdbc.MYSQL_DRIVER);

Run SQL Select and return data in XML format

The method accepts a dml select statement and returns an XML document. This is a good example of encapsulation and keeping you from all of the evil details that one must deal with in order to return an xml document for either all fields or a partial field list.

Syntax

sqlRead(String qry) 

Example

sqlRead("Select * from Questions")

sqlRead("Select lastname, firstname from Person")

Potential Enhancements

• Only returns a simple XML stub. Could be enhanced to return more complex tagging, or even a complete, well formed xml document.

Run SQL Select and return data in comma delimited format

The method accepts a dml select statement and returns a comma delimited string. This method only returns the data.

Syntax

public java.lang.String sqlReadReturnCommaDelimited(java.lang.String qry)

Example

sqlStmt = "select num, lastname, firstname FROM person where num > 0 and num < 4";

str = jdbc.sqlReadReturnCommaDelimited(sqlStmt);

Potential Enhancements

• Optionally return the column names as the first row
• Allow for a delimiter other than a comma

Run SQL Query and return data as Java Vector

The method accepts a DML SELECT statement and returns column names and data in a Vector. The 1st element of the vector is '|' seperated column names in a string and rest of the elements are '|" seperated data in String. 

Syntax

public java.util.Vector sqlReadReturnVector(java.lang.String sqlQuery)

Example

String sqlStmt = "select * FROM person where num < 12";

Vector v = jdbc.sqlReadReturnVector(sqlStmt);
System.out.println("\nSQL Statement: " + sqlStmt);
for (Enumeration e = v.elements() ; e.hasMoreElements() ;)
System.out.println(i++ + "--> " + e.nextElement());

// remove all elements from vector
v.clear();

Write to any MySQL table

The method accepts a valid dml a dml update,insert,delete statement and returns the status of the IO operation (example: number of rows updated, etc...) Note: We have no need to get more granular (update, delete, insert) as each of these SQL IO operations uses the method executeUpdate(). 

Syntax

public java.lang.String sqlWrite(java.lang.String qry)

Examples

sqlWrite("delete from Questions WHERE (Question_num = 16) ;")

sqlWrite("INSERT INTO Questions (Stem, Question_type) 
VALUES ('What color is a banana?', 'TF');")

sqlWrite("UPDATE" + " Questions SET Question_type = 'MC' WHERE Question_type = 'TF';")

If you desire to see the status of the method call... 

result = sqlWrite("delete from Questions WHERE (Question_num = 16) ;")

In this context the status of the IO operation is returned to the caller. 0 means the operation was not performed. A number >= 1 indicates the number of writes performed. We also return the query string submitted, as well as any exceptions that are thrown. 

Return column names

The method accepts a dml select statement and returns a field list. It can also return other field related attributes like precision and data type though this is not presently implemented 

Syntax

public java.lang.String sqlSchema(java.lang.String qry)sqlSchema(String qry)

Example

sqlSchema("Select * from Questions")

Potential Enhancements

• Return the data type and other ddl information

Simple MySQL JDBC API Code

The java code is pretty well commented so you can just look at the code listing itself.

Closing thoughts

I have discussed and debated with my friends and colleagues for years the idea of encapsulation with respect to SQL. If you really boil down database access there are two functions one can perform.

1. Read from a table(s)
2. Write to a table

Further one can delineate the write functions into three sub functions.

1. Insert
2. Update
3. Delete

This is pretty simple. One would think that there would be ample opportunity to tie this into an api that encapsulated these functions. This simple api was a prototype to do this. I do not claim for a moment that it is robust or complete. But, it does take thousands of lines of code and reduces them to five methods.

I realize that there are many other issues one must contend with to build a robust but simple to use api. My point is that such an api is certainly doable and would greatly simplify development activities. I put this together on a very small scale to reduce my own work. We are just not doing enough with the design principle of encapsulation.