How to escape apostrophe (') in MySQL ?

Problem : The data with apostrophe  is not getting saved in MySQL database properly.
In my case I was trying to save <John's Car> in the database. But was getting error on executing the update statement.

Java MySQL JDBC

Solution : I read some solution like using methods to escape apostrophe. But in the end, as I was coding in Java, what I found the best approach is using PreparedStatement.

When you set data like following, escaping is handled implicitly by Java.

 PreparedStatement pstmt = con.prepareStatement("UPDATE CUSTOM_TABLE SET CUSTOM_COLUMN = ?");

 pstmt.setString(1, "John's Car");

Did it work for you?

Read More

Deal with JDBC Batch Update Results

Handle batch update results with following code snippet :

int[] numUpdates= stmt.executeBatch();


for (int i = 0; i < numUpdates.length; i++) {
if (numUpdates[i] >= 0) {
// Successfully executed; the number represents number of affected rows
System.out.println("OK: updateCount=" + numUpdates[i]);
} else if (numUpdates[i] == Statement.SUCCESS_NO_INFO) {
// Successfully executed; number of affected rows not available
System.out.println("OK: updateCount=Statement.SUCCESS_NO_INFO");
} else if (numUpdates[i] == Statement.EXECUTE_FAILED) {
System.out.println("updateCount=Statement.EXECUTE_FAILED");
}
}

Extra Info :

int java.sql.Statement.SUCCESS_NO_INFO = -2 [0xfffffffe]

The constant indicating that a batch statement executed successfully but that no count of the number of rows it affected is available.

int java.sql.Statement.EXECUTE_FAILED = -3 [0xfffffffd]

The constant indicating that an error occured while executing a batch statement.

Following is the good link for more info about Batch Updates :

http://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp?topic=%2Frzaha%2Fbatchstm.htm

Read More

Retrieve Automatically Generated Keys in JDBC

Two steps to implement:
1 Step : 

Convey your intentions that you want to retrieve Generated Keys with help of following syntax

 PreparedStatement pst = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS) ;

One thing to note that for some JDBC Drivers such as DB2, Oracle, above mentioned syntax may not work.

In those cases following syntax may help you

 PreparedStatement psmt = con.prepareStatement(sql, new String[]{"USER_ID"})

In String Array passed as an argument, you specify column names you want to retrieve or u can also specify indexes.

2 Step : Fetch Generated Keys

Fetch generated keys in a ResultSet as following

 rs = psmt.getGeneratedKeys();

Following is example code for this

 import java.sql.*;
import com.ibm.db2.jcc.*; 

Connection con; 
Statement stmt; 
ResultSet rs;

stmt = con.createStatement();
stmt.executeUpdate("INSERT INTO EMP_PHONE 
(EMPNO, PHONENO) VALUES ('123', '555')",
Statement.RETURN_GENERATED_KEYS); 
rs = stmt.getGeneratedKeys();
if (rs.next()) 
{ System.out.println("Automatically generated key value = " + rs.getString(1)); }
rs.close(); 
stmt.close(); 

by the way i have noticed that we can retrieve any column from last inserted row, for this i used the method mentioned for DB2, Oracle above.

Read More