Unicode in SQL statements, the PreparedStatement class

1. Open the database.

   The following code shows how to open the database. There are three steps to establish a connection to the database. The first step is to load the driver. Then, connect to the server using DriverManager class. The last step is to create a Statement class from the connection.

   
           Connection connection;
           Statement statement;
   	String driver = "com.mysql.jdbc.Driver";
   	String openDBString = "jdbc:mysql://localhost/";
   	String user = "easai";
   	String password = "password";
   	String dbName = "dictionaries";
   	try
   	    {
   		Class.forName(driver).newInstance(); 		
   		connection = DriverManager.getConnection(openDBString+dbName,user,password);
   		statement = connection.createStatement();
   	    }
   	catch(Exception e){e.printStackTrace();}
   
   

   The driver in the first statement should indicate the name of the JDBC driver, such as com.mysql.jdbc.Driver for mysql. The strings openDBString, user, password should differ for each database server. The above code shows sample values for each parameters.

2. Store the information.

   Now that the connection is established, issue SQL commands to store the data. By all means, use PreparedStatement to save Unicode strings to the database.

   
   	try
   	    {
   		String tableName = "french";
   		String usage = "les raz-de-marée du 26 décembre";
   		String word = "raz-de-marée";
   		String description = "tsunami";
   
   		String sql = "INSERT INTO "+tableName+" VALUES(?,?,?);";
   		PreparedStatement preparedStatement = connection.prepareStatement(sql);
   		preparedStatement.setBytes(1,usage.getBytes("UTF8"));
   		preparedStatement.setBytes(2,word.getBytes("UTF8"));
   		preparedStatement.setBytes(3,description.getBytes("UTF8"));
   		if(preparedStatement.executeUpdate() == -1)
   		    JOptionPane.showMessageDialog(this,"The database can not execute the following SQL command: \n"+sql);
   	    }
   	catch(Exception e){e.printStackTrace();}
   
   

   The above code is assuming there is a database table called "french" in the "dictionaries" database. Notice the SQL command to store the information has "?" marks. The "?" marks will be set by following setBytes() commands. The first argument will indicate which of the "?" mark will be set to the second argument, which is a byte array encoded in utf-8. To issue the SQL command, call executeUpdate(). executeUpdate command will return -1 when something goes wrong.

3. Close the database.

   The last step is to close the database. The following code will close the Statement and the Connection class instances. This completes all the necessary steps to access to a database from Java programs. Do not forget to add the path to the JDBC driver to classpath when you run the program.

   
   	try 
   	    { 
   		if(statement != null) 
   		    statement.close(); 
   		if(connection != null) 
   		    connection.close(); 
   	    } 
   	catch (Exception e){e.printStackTrace();}
   
   

DicEntry.java is a sample Java application that implements the procedure. 1. openDB() opens the database, 2. addEntry() stores the information, 3. closeDB() closes the connection. To compile the program, download EditProperties.java also and run javac. Requires a JDBC driver, which must be specified by the -classpath option.