Implementation of "Post count" of HashTag with Mysql stored proc

Recently, I am making a feature similar to Instagram HashTag search in my app, which is searching the hashtag with post count with the given keyword.

To do this, I try to keep the post count in the Tag record and get it when do the search. However, to complete this, I need to handle the way to increase the count when a new post is created.

To do so, I had chosen using stored proc to do this task.

What’s and Why stored procedure ? 

Combine several SQL in a named procedure. It can make the application code more simple.

Before using procedure:
We need to use two SQLs to handle

UPDATE table SET count = count + 1 WHERE tag = ‘myTag’
SELECT count FROM table WHERE tag = ‘myTag’

After using procedure:
Just simply use the following SQL

CALL increase_post_count(‘myTag’)

Stored procedure of “Increase Post count” 

Define the procedure 

DELIMITER //
CREATE PROCEDURE inc_post_count
(
   IN inputTag VARCHAR(100)
)
BEGIN
    UPDATE tbl_hashtag SET postCount = postCount + 1 WHERE tag = inputTag ;
    SELECT postCount FROM tbl_hashtag WHERE tag = inputTag ;
END //
DELIMITER ;

Note: The above statement is working mysql client but not in phpMyAdmin

Using the procedure

CALL inc_post_count(‘MyTag’) 

Make java connection pool auto reconnect

Recently we encountered a problem when using java db connection pool. The problem is that the connection is being closed by the DB server since it is already timeout. To due with this, need to configure two more parameters.

They are:
- testOnBorrow  ;  define it as "true"
- validationQuery ; define it as "SELECT 1 FROM dual" if you are also using MySQL

Yes, It is easy to config it. But how to test it? (Many sites may not give the answer). 

To verify the configuration is work, we can make a simple Unit Test;

Step 1: Make a failure case

Make an unit test base on the following code;


Properties p = new Properties(); 
prop.put("driver", "org.gjt.mm.mysql.Driver");
prop.put("url", "jdbc://localhost:3306/db");
prop.put("username", "user");
prop.put("password", "123456");
p.setProperties("testOnBorrow", "false");
p.setProperties("validationQuery", "");

BasicDataSource ds = BasicDataSourceFactory.createDataSource(prop);
ds.setMaxActive(1);
ds.setMaxIdle(1);
ds.setMinIdle(0);

Connection conn = ds.getConnection();
System.out.println("First connection");


System.out.println("Sleep start");
Thread.sleep(10000);
System.out.println("Sleep end");

Connection conn = ds.getConnection();
System.out.println("Second connection");


To make the connection fail at the second time, try to kill the connection in DB side while "Thread.sleep";


Step 2: Fix the failure 

Simply change "testOnBorrow=false" and "validationQuery=" to 
 "testOnBorrow=true" and "validationQuery=SELECT 1 FROM dual"

Then, run the test again. we will see "Second connection" show up instead of Exception message.