Dec 042012
 
Article Java

A sample java code to connect to a mysql database “db_test” as user “root”, pass “rootpass”, is shown below:

In this code, the values of the variables url, dbName, userName and password must be edited to match the details of the mysql installation where it is going to be executed.

Besides, for this example to work the mysql driver for java must be downloaded and installed, as explained below.

Installing the MySQL driver for Java

The driver can be downloaded from http://dev.mysql.com/downloads/connector/j/. The file downloaded is named mysql-connector-java-5.1.22.tar.gz and is 4.028.047 bytes in size.

Uncompress the file, and extract the files inside the tarball with the command:

Next, copy the file “mysql-connector-java-5.1.22/mysql-connector-java-5.1.22-bin.jar” to the desired installation directory (for instance, copy it to “/usr/lib/mysql/mysql-connector-java-5.1.22-bin.jar”).

Then, included in the CLASSPATH variable the full path of the file:

  • In Linux, this can be done by adding to the shell init script (“.profile”, “.login”, “.bashrc”,… ) the following line:
  • In Windows, the system variables are edited using the Control Panel. For instance, in Windows 7  go to “Control Panel -> System -> Advanced -> Environment Variables”:

 

How to insert records and query the database

First, we use the createStatement method to retrieve an object of class Statement.

The Statement object provides a method ‘executeQuery’ to run SQL SELECT sentences. This class provides also a method ‘executeUpdate’ to execute other SQL sentences (INSERT, UPDATE, DELETE) that do not return records, as well as database management sentences (CREATE TABLE, DROP TABLE,…)

SQL to Java data type conversion

The sample code above uses the catch-all method “getObject()” to retrieve the values from the table. In the context of the “System.out.println()” call where they are referenced, the returned values are automatically converted to String type object, to be concatenated with the text literals in that sentence.

In most other cases, it is better to use more specific methods to convert the SQL data to more suitable Java data types. For instance:

For booleand and numeric values:

  • getBoolean()
  • getByte(), getShort(), getInt(), getLong()
  • getFloat(), getDouble()

For text data:

  • getString()
  • getAsciiStream() – returns a java.io.Inputstream object
  • getCharacterStream() – – returns a java.io.Reader object

For dates and times:

  • getTime(), getTimestamp(), getDate() – return java.sql.(Time,Timestamp,Date) types respectively

For binary data types:

  • getBinaryStream() – returns a java.io.Inputstream object
  • getBlob() – To retrieve data of SQL BLOB type. returns a java.sql.Blob object
  • getClob() – To retrieve data of Oracle SQL CLOB type or MySQL TEXT type

Other methods implemented by the ResultSet interface:

  • getArray() – to retrieve a column of SQL ARRAY data type (not available in MySQL)
  • getBytes() – returns a byte array (can be used, for instance, when reading a BLOB)

References

 Posted by at 11:45 am

 Leave a Reply

(required)

(required)