月份: 一月 2016

spring + hibernate + mysql使用c3p0 connection pool會出現

Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 52,924,331 milliseconds ago. The last packet sent successfully to the server was 52,924,331 milliseconds ago. is longer than the server configured value of ‘wait_timeout’. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property ‘autoReconnect=true’ to avoid this problem.

在網路上搜尋了近10天的文章，也試了不少方式，總結大約有三種說法

1.若是用JDBC沒有用connection pool
設定如下：
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName">
<value>com.mysql.jdbc.Driver</value>
</property>
<property name="jdbcUrl" value="jdbc:mysql://<ip>:<port>/<dbname>?characterEncoding=utf-8&amp;autoReconnect=true" />
…
</bean>

2.若是用hibernate connection pool
<bean id="sessionFactory" class="org.springframework.orm.hibernate5.LocalSessionFactoryBean">
<property name="dataSource" ref="dataSource" />
<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">org.hibernate.dialect.MySQLDialect</prop>
<prop key="hibernate.show_sql">true</prop>
…
<prop key="hibernate.connection.autoReconnect">true</prop>
<prop key="hibernate.connection.autoReconnectForPools">true</prop>
<prop key="hibernate.connection.is-connection-validation-required">true</prop>
…
</props>
</property>
</bean>

3.若是用c3p0 connection pool
<bean id="sessionFactory" class="org.springframework.orm.hibernate5.LocalSessionFactoryBean">
<property name="dataSource" ref="dataSource" />
<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">org.hibernate.dialect.MySQLDialect</prop>
<prop key="hibernate.show_sql">true</prop>
…
<prop key="hibernate.c3p0.max_size">10</prop>
<prop key="hibernate.c3p0.min_size">1</prop>
<prop key="hibernate.c3p0.idle_test_period">1</prop>
<prop key="hibernate.c3p0.acquire_increment">1</prop>
<prop key="hibernate.c3p0.timeout">30</prop>
<prop key="hibernate.c3p0.validate">true</prop>
<prop key="hibernate.c3p0.initial_pool_size">3</prop>
<prop key="hibernate.c3p0.idleConnectionTestPeriod">100</prop>
<prop key="hibernate.c3p0.preferredTestQuery">SELECT 1 from dual</prop>
<prop key="hibernate.c3p0.testConnectionOnCheckout">true</prop>
<prop key="hibernate.c3p0.testConnectionOnCheckin">true</prop>
<prop key="hibernate.c3p0.maxConnectionAge">180</prop>
<prop key="hibernate.c3p0.acquireRetryDelay">30</prop>
<prop key="hibernate.c3p0.acquireRetryAttempts">20</prop>
<prop key="hibernate.c3p0.breakAfterAcquireFailure">false</prop>
…
</props>
</property>
</bean>

一直以為用的是c3p0，昨天看log竟然沒有c3p0的內容
後來試出來是一直用到的是hibernate的pool，而非c3p0

<prop key="hibernate.provider_class">org.hibernate.connection.C3P0ConnectionProvider</prop>

應改成

<prop key="hibernate.connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</prop>

後雖有讀到卻有找不到其它class的錯誤。
只好試另一種方式dataSource設定使用class com.mchange.v2.c3p0.ComboPooledDataSource，需下載c3p0 jar檔(我使用c3p0-0.9.5.2.jar)

<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
<property name="driverClass">
<value>com.mysql.jdbc.Driver</value>
</property>
<property name="jdbcUrl" value="jdbc:mysql://127.0.0.1:3306/dbname?characterEncoding=utf-8&amp;autoReconnect=true" />
…
</bean>

一直不想調這個地方，是因為這個設定為了將DB密碼加密做設定，寫了一個加密的class繼承org.springframework.jdbc.datasource.DriverManagerDataSource，設定如下

<bean id="dataSource" class="com.xxx.util.EncryptedDataSource">
…

後來有找到com.mchange.v2.c3p0.ComboPooledDataSource將DB密碼用成設定的方式

1.applicationContext.xml設定如下
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
<property name="driverClass">
<value>com.mysql.jdbc.Driver</value>
</property>
<property name="jdbcUrl" value="jdbc:mysql://127.0.0.1:3306/dbname?characterEncoding=utf-8&amp;autoReconnect=true" />
<property name="acquireIncrement" value="1″/>
<property name="idleConnectionTestPeriod" value="300″/>
<property name="maxPoolSize" value="10″/>
<property name="minPoolSize" value="1″/>
<property name="initialPoolSize" value="1″ />
<property name="numHelperThreads" value="3″/>
<property name="maxIdleTime" value="1200″ />
<property name="acquireRetryAttempts" value="2″/>
<property name="preferredTestQuery" value=" select 1 from dual “/>
<property name="testConnectionOnCheckout" value="true"/>
<property name="testConnectionOnCheckin" value="true"/>
<property name="maxConnectionAge" value="1800″ />
<property name="properties" ref="dataSourceProperties"/>
</bean>

<bean id="dataSourceProperties" class="com.xxx.util.PropertiesEncryptFactoryBean">
<property name="properties">
<props>
<prop key="user">dbuser</prop>
<prop key="password">99d48e6365c86d26d21ea0f0ac1458ef</prop>
</props>
</property>
</bean>

2.PropertiesEncryptFactoryBean的寫法

這個slideshow需要JavaScript。

1.為了選取自已上傳的圖檔

增加如下紅字

tinymce.init({selector: elem,
theme: “modern",
…
file_browser_callback : myFileBrowser,
});

function myFileBrowser (field_name, url, type, win) {
var cmsURL = window.location.toString();
if (cmsURL.indexOf(“?") < 0) {
cmsURL = cmsURL + “?type=" + type;
} else {
cmsURL = cmsURL + “&type=" + type;
}
cmsURL = “WAImage.action?page=9″;

tinyMCE.activeEditor.windowManager.open({
file : cmsURL,
title : ‘請選取上傳圖檔’,
width : $(window).width()-50,
height : $(window).height()-50,
resizable : “yes",
inline : “yes",
close_previous : “no"
}, {
window : win,
input : field_name
});
return false;
}

新開的圖檔選擇頁需新增如下

$(document).ready(function() {

tinyMCEPopup.onInit.add(FileBrowserDialogue.init,FileBrowserDialogue);

});

var FileBrowserDialogue = {
init : function () {
// Here goes your code for setting your custom things onLoad.
},
mySubmit : function () {
// Here goes your code to insert the retrieved URL value into the original dialogue window.
// For example code see below.
}
}

選取完圖檔的回傳方式如下，即可將圖檔連結回傳至Insert/Edit Image對話框內的Source欄位：

var win = tinyMCEPopup.getWindowArg(“window");
win.document.getElementById(tinyMCEPopup.getWindowArg(“input")).value = imgUrl;
if (win.getImageData) win.getImageData();
tinyMCEPopup.close();

2.畫面無法出現編輯頁面(如下圖)，而只出現<frameset>原始的樣子

後來發現是因為我用動態的呼叫方式，沒有每次重load網頁，後改成window.location.reload();就OK了

3.取出字串其format不管是raw或html均會出現<html>.<br/>..<head>…<body>，且每次都會在<body>的最前頭加上<p><br/></p>，造成最後顯示會前面愈空愈大

var blogContent = tinyMCE.get(‘blog_content’).getContent({format : ‘raw’});

加上以下紅字

tinymce.init({selector: elem,
…
forced_root_block : false,
   force_br_newlines : false,
   force_p_newlines : false
});

仍有<html>.<br/>..<head>…<body>，但至少每次<body>最前頭不會加上<p><br/></p>，而<br/>變成\n

故替換掉\n字元

blogContent = blogContent.replace(/(?:\r\n|\r|\n)/g, “);

存入資料庫則只取body內的html
blogContent = parseHTML(blogContent).getElementsByTagName(‘body’)[0].innerHTML;

因為hibernate每次存取DB的速度過慢，一次查詢竟然需要5秒鐘，

經查發現我寫的程式碼在初始化spring bean Dao以下4行的前3行就是問題的所在：

GenericXmlApplicationContext ctx = new GenericXmlApplicationContext();
ctx.load(new ClassPathResource(“../applicationContext.xml"));
ctx.refresh();
waServiceDao = ctx.getBean(“WAServiceDao",WAServiceDao.class);

故將前3行改為static的方式，不要每次存取DB都做初始化

private static GenericXmlApplicationContext ctx = null;
static {
ctx = new GenericXmlApplicationContext();
ctx.load(new ClassPathResource(“../applicationContext.xml"));
ctx.refresh();
}

但竟變成每次查詢都有db cache，明明資料庫已異動，但怎麼查都是舊資料，上網google發現hibernate cache有level 1及2，level 1是session level，level 2是application level，原本一直往level 2的方向查，但怎麼調整還是有cache (applicationContext.xml加上如下指令也不work)

<prop key="current_session_context_class">org.hibernate.context.ManagedSessionContext</prop>
<prop key="hibernate.cache.use_second_level_cache">false</prop>
<prop key="hibernate.cache.use_query_cache">false</prop>
<prop key="cache.provider_class">org.hibernate.cache.NoCacheProvider</prop>

後來想到有可能是卡在level 1 – session cache，全部共用hibernate同一個session，故調整如下紅字，就沒cache了~

protected Session getSession() {
if (session == null || session.isOpen() == false)
session = sessionFactory.openSession();
else
        session.clear();
return session;
}