將Java的ArrayList(串列)寫到Mysql資料庫的blob欄位

案例: 收到的request資料中一個JSON格式的資料要保存，但為何不存JSON的字串就好?因為這個資料會不斷變動的，所以收到變動只要取出這個已將JSON轉成list的添加或刪除東西就好，不用再將JSON轉成list再轉回JSON存到DB去。 在Hibernate中是設定該欄位為type="java.sql.Blob"，會自動在Mysql中建成DataType為LONGBLOB
下面是把JSON轉成list後，轉成blob型態存入DB

 
ObjectMapper mapper = new ObjectMapper();//用Jackson處理JSON
  Blob blob = null;
  try {
   SongResp dto = mapper.readValue(json, SongResp.class);
   List list = dto.getSongs();
   ByteArrayOutputStream bout = new ByteArrayOutputStream();
   ObjectOutputStream oos = new ObjectOutputStream(bout);
   oos.writeObject(list);
   bout.close();
   oos.close();
   byte[] asBytes = bout.toByteArray();
   blob = Hibernate.createBlob(asBytes);
   
  } catch (JsonParseException e) {
   e.printStackTrace();
  } catch (JsonMappingException e) {
   e.printStackTrace();
  } catch (IOException e) {
   e.printStackTrace();
  }

以下是把DB中的該欄位取出成當時寫入的list

          

List list;
  Blob b = userDao.getUserData(id, dataType);
  try {
   if (b != null) {
    InputStream in = b.getBinaryStream();

    ObjectInputStream ooi = new ObjectInputStream(in);

    in.close();
    ooi.close();

    list = (List)ooi.readObject();
    return list;
   }
    
  } catch (SQLException e) {
   e.printStackTrace();
  } catch (IOException e) {
   e.printStackTrace();
  } catch (ClassNotFoundException e) {
   e.printStackTrace();
  }

Default value between Hibernate and Mysql

Data type of Mysql is Integer

Hibernate :

Data type of Mysql is Varchar(1)

Hibernate :

Please focus on default=" 'a' " ! If you use default="a" , it will create failure on Hibernate and also Mysql !

CentOS 5.5 install mysql phpmyadmin tomcat

安裝Mysql
# yum -y install mysql-server
# mysqladmin -u root password 'your password'

安裝 php
# yum -y install php php-mysql php-gd php-mbstring
# vi /etc/php.ini <= 自行修改您要的值，內容參考如下
max_execution_time = 300
max_input_time = 600
memory_limit = 160M
post_max_size = 80M
upload_max_filesize = 20M

安裝 phpMyAdmin
# cd /var/www/html
# wget http://sourceforge.net/projects/phpmyadmin/files/phpMyAdmin/2.11.11/phpMyAdmin-2.11.11-all-languages.tar.gz/download
# tar -zxvf phpMyAdmin-XXX.tar.gz
# mv phpMyAdmin-XXX phpMyAdmin
# cp /var/www/html/phpMyAdmin/libraries/config.default.php /var/www/html/phpMyAdmin/config.inc.php


# vi /var/www/html/phpMyAdmin/config.inc.php
$cfg['PmaAbsoluteUri'] = ''
改成 $cfg['PmaAbsoluteUri'] = 'http://ip/phpMyAdmin/';

$cfg['Servers'][$i]['auth_type'] = 'config'
改成 $cfg['Servers'][$i]['auth_type'] = 'http';

PS .開啟 phpMyAdmin 的首頁時, 出現"php 5.2+ is required"表示你裝phpMyAdmin版本要用到php5.2以上的版本

# chkconfig --level 2345 httpd on

讓tomcat開機時啟動
# touch /etc/init.d/tomcat
# chmod 755 /etc/init.d/tomcat
# vi /etc/init.d/tomcat
內容如下
#!/bin/bash
# process init file for tomcat
#
# chkconfig: 2345 90 80
# description: Apache Tomcat Daemon
#
# processname: /usr/local/tomcat/bin/startup.sh and /usr/local/tomcatbin/shutdown.sh

start() {
echo -n "Starting up Tomcat: ";echo "";
/usr/local/tomcat/bin/startup.sh
}

stop() {
echo -n "Shutting down Tomcat: ";echo "";
/usr/local/bin/shutdown.sh
}

restart() {
echo "Restarting Tomcat:"
stop
start
}

case "$1" in
start)
start
;;
stop)
stop
;;
restart)
restart
;;
*)
echo "Usage: Tomcat {start|stop|restart}"
exit 1
esac

測試一下啟動，停止 tomcat
# service tomcat start
# service tomcat stop

確認process有跑嗎
# ps -ef|grep tomcat

確認目前有tomcat這個服務在跑嗎?應該沒有才對喔!
# chkconfig --list|grep tomcat

加進來
# chkconfig --add tomcat

確認
# chkconfig --list|grep tomcat
tomcat 0:off 1:off 2:on 3:on 4:on 5:on 6:off

列出 /etc/rc.d/rcx.d/ 底下關於 tomcat 的部分
# find /etc/rc.d/ | grep 'tomcat'|sort

改httpd的port
# vi /etc/httpd/conf
Listen 8000

用Hibernate取前幾筆資料

Using Hibernate to fetch top 10 high score

public List getSortedScoreList(){
return (List)this.getHibernateTemplate().execute(new HibernateCallback(){
public Object doInHibernate(org.hibernate.Session session) throws HibernateException, SQLException {
Criteria cri=session.createCriteria(ScoreVO.class);
cri.addOrder(Order.desc("score")).addOrder(Order.desc("createTime"));
cri.setMaxResults(10);
return cri.list();
}
});
}

TransactionException in Hibernate and MySQL

在過一段時間後會出現如上錯誤訊息，這是因為MySQL已經切斷閒置的連線，所以可以利用connection pool的配置來處理這個問題。
Mysql服務器預設的「wait_timeout」是8小時(待查證)，所以mysql配置中my.ini 的wait_timeout值一定要大於等於connection pool的idle_timeout 值，否則mysql會在wait_timeout的時間後關閉連接，然而連接池還認為該連接可用，這樣就會產生以上圖內的錯誤。
MySQL :
interactive_timeout=XX
The number of seconds the server waits for activity on an interactive connection before closing it.

wait_timeout=XX
The number of seconds the server waits for activity on a noninteractive connection before closing it.

DBCP
timeBetweenEvictionRunsMillis 每毫秒檢查一次連接池中閒置的連接(spec :The number of milliseconds to sleep between runs of the idle object evictor thread. When non-positive, no idle object evictor thread will be run.)預設是-1

minEvictableIdleTimeMillis 連接池中連接可閒置的時間(spec:The minimum amount of time an object may sit idle in the pool before it is eligable for eviction by the idle object evictor (if any). )

上面兩個設定要一起使用，意思是說每timeBetweenEvictionRunsMillis毫秒檢查一次連接池中空閒的連接,把空閒時間超過 minEvictableIdleTimeMillis毫秒的連接斷開,直到連接池中的連接數到minIdle為止