From 8c3216d7c51499f141d52933e8b518fa5acc15d1 Mon Sep 17 00:00:00 2001 From: spark_xp Date: Sun, 26 Jun 2011 16:35:51 +0000 Subject: [PATCH] =?utf8?q?SQLite=E3=81=B8=E3=81=AE=E3=83=AD=E3=82=B0?= =?utf8?q?=E6=9B=B8=E3=81=8D=E5=87=BA=E3=81=97=E5=87=A6=E7=90=86=E3=81=AE?= =?utf8?q?=E3=83=81=E3=83=A5=E3=83=BC=E3=83=8B=E3=83=B3=E3=82=B0=E3=82=92?= =?utf8?q?=E8=A1=8C=E3=81=AA=E3=81=A3=E3=81=9F=E3=80=82=E3=83=87=E3=83=BC?= =?utf8?q?=E3=82=BF=E6=8C=BF=E5=85=A5=E9=80=9F=E5=BA=A6=E3=81=8C=E5=8A=87?= =?utf8?q?=E7=9A=84=E3=81=AB=E6=97=A9=E3=81=8F=E3=81=AA=E3=81=A3=E3=81=9F?= MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit git-svn-id: http://svn.sourceforge.jp/svnroot/nt-manager/NishioTweetManager/trunk@151 d8c9ecd3-d47d-4367-8645-de82c00e513f --- src/twitter/log/TwitterLogDao.java | 185 +++++++++++++++++++++++++-------- src/twitter/log/TwitterLogManager.java | 15 ++- 2 files changed, 153 insertions(+), 47 deletions(-) diff --git a/src/twitter/log/TwitterLogDao.java b/src/twitter/log/TwitterLogDao.java index 6f2daa3..66be6cd 100644 --- a/src/twitter/log/TwitterLogDao.java +++ b/src/twitter/log/TwitterLogDao.java @@ -4,8 +4,10 @@ import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; +import java.util.ArrayList; import java.util.List; +import org.apache.commons.dbutils.DbUtils; import org.apache.commons.dbutils.QueryRunner; import org.apache.commons.dbutils.ResultSetHandler; import org.apache.commons.dbutils.handlers.BeanListHandler; @@ -67,6 +69,44 @@ public class TwitterLogDao { private static final String insertTweetDataSql = "INSERT INTO TWEET VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);"; + //UPDATE + private static final String updateTweetDataSql = + "UPDATE TWEET SET " + + " date = ?," + + " replyStatusID = ?," + + " replyUserID = ?," + + " text = ?," + + " created = ?," + + " description = ?," + + " userFavorite = ?," + + " followers = ?," + + " friend = ?," + + " userId = ?," + + " lang = ?," + + " location = ?," + + " name = ?," + + " profileBackgroundColor = ?," + + " profileBackgroundImageURL = ?," + + " profileImageURL = ?," + + " profileSidebarBorderColor = ?," + + " profileSidebarFillColor = ?," + + " profileTextColor = ?," + + " screenName = ?," + + " statusesCount = ?," + + " timeZone = ?," + + " url = ?," + + " utc = ?," + + " contributorsEnable = ?," + + " geoEnable = ?," + + " profileBackgroundTiled = ?," + + " isProtected = ?," + + " verified = ?," + + " source = ?," + + " favorite = ?," + + " retweet = ?," + + " truncated = ? " + + "WHERE id = ?;"; + //データ取得SQL private static final String selectTweetDataSql = "SELECT * FROM TWEET;"; @@ -112,54 +152,113 @@ public class TwitterLogDao { } /** - * 新規データの挿入 - * @param o - * @throws SQLException + * ツイートデータをDBに格納する + * @param dbobject + * @throws SQLException */ - public void insert( TweetDBObject o ) throws SQLException { + public void insert( List dbobject ) throws SQLException { Connection con = DriverManager.getConnection(DATABASE_CONNECTION); QueryRunner qr = new QueryRunner(); - //データ挿入 + //トランザクション + con.setAutoCommit(false); + for( TweetDBObject o : dbobject ) { + //データ挿入 + try { + int count = 0; + //updateする + count = qr.update(con, updateTweetDataSql, + o.getDate(), + o.getReplyStatusID(), + o.getReplyUserID(), + o.getText(), + o.getCreated(), + o.getDescription(), + o.getUserFavorite(), + o.getFollowers(), + o.getFriend(), + o.getUserId(), + o.getLang(), + o.getLocation(), + o.getName(), + o.getProfileBackgroundColor(), + o.getProfileBackgroundImageURL(), + o.getProfileImageURL(), + o.getProfileSidebarBorderColor(), + o.getProfileSidebarFillColor(), + o.getProfileTextColor(), + o.getScreenName(), + o.getStatusesCount(), + o.getTimeZone(), + o.getUrl(), + o.getUtc(), + o.getContributorsEnable(), + o.getGeoEnable(), + o.getProfileBackgroundTiled(), + o.getIsProtected(), + o.getVerified(), + o.getSource(), + o.getFavorite(), + o.getRetweet(), + o.getTruncated(), + o.getId() ); + //updateできない場合insertする + if( count == 0 ) { + qr.update(con, insertTweetDataSql, + o.getId(), + o.getDate(), + o.getReplyStatusID(), + o.getReplyUserID(), + o.getText(), + o.getCreated(), + o.getDescription(), + o.getUserFavorite(), + o.getFollowers(), + o.getFriend(), + o.getUserId(), + o.getLang(), + o.getLocation(), + o.getName(), + o.getProfileBackgroundColor(), + o.getProfileBackgroundImageURL(), + o.getProfileImageURL(), + o.getProfileSidebarBorderColor(), + o.getProfileSidebarFillColor(), + o.getProfileTextColor(), + o.getScreenName(), + o.getStatusesCount(), + o.getTimeZone(), + o.getUrl(), + o.getUtc(), + o.getContributorsEnable(), + o.getGeoEnable(), + o.getProfileBackgroundTiled(), + o.getIsProtected(), + o.getVerified(), + o.getSource(), + o.getFavorite(), + o.getRetweet(), + o.getTruncated() ); + } + }catch(SQLException e) { + e.printStackTrace(); + } + } try { - qr.update(con, insertTweetDataSql, - o.getId(), - o.getDate(), - o.getReplyStatusID(), - o.getReplyUserID(), - o.getText(), - o.getCreated(), - o.getDescription(), - o.getUserFavorite(), - o.getFollowers(), - o.getFriend(), - o.getUserId(), - o.getLang(), - o.getLocation(), - o.getName(), - o.getProfileBackgroundColor(), - o.getProfileBackgroundImageURL(), - o.getProfileImageURL(), - o.getProfileSidebarBorderColor(), - o.getProfileSidebarFillColor(), - o.getProfileTextColor(), - o.getScreenName(), - o.getStatusesCount(), - o.getTimeZone(), - o.getUrl(), - o.getUtc(), - o.getContributorsEnable(), - o.getGeoEnable(), - o.getProfileBackgroundTiled(), - o.getIsProtected(), - o.getVerified(), - o.getSource(), - o.getFavorite(), - o.getRetweet(), - o.getTruncated() ); - }catch(SQLException e) { - //挿入ではなく更新にする + DbUtils.commitAndCloseQuietly( con ); + }catch(Exception e) { + DbUtils.commitAndClose( con ); e.printStackTrace(); } - con.close(); + } + + /** + * 新規データの挿入 + * @param o + * @throws SQLException + */ + public void insert( TweetDBObject o ) throws SQLException { + List objects = new ArrayList(); + objects.add(o); + insert( objects ); } } diff --git a/src/twitter/log/TwitterLogManager.java b/src/twitter/log/TwitterLogManager.java index 6d3145e..4c8b20a 100644 --- a/src/twitter/log/TwitterLogManager.java +++ b/src/twitter/log/TwitterLogManager.java @@ -9,6 +9,7 @@ import java.io.FileWriter; import java.io.IOException; import java.io.OutputStreamWriter; import java.text.DateFormat; +import java.util.ArrayList; import java.util.List; import org.xml.sax.SAXParseException; @@ -148,15 +149,21 @@ public class TwitterLogManager { //sql関係 TwitterLogDao dao = new TwitterLogDao(); try { + //DB作成 dao.connectDB(); dao.createTable(); - long time = System.currentTimeMillis(); + //long time = System.currentTimeMillis(); + //DBにツイート保存 + List objects = new ArrayList(); for(Status s : statuses ) { TweetDBObject o = StatusDBObjectConverter.convertStatusToDBObject(s, true); - dao.insert( o ); + objects.add(o); } - time = System.currentTimeMillis() - time; - System.out.println("DB INSERT TIME:" + time); + if( objects != null && objects.size() > 0 ) { + dao.insert( objects ); + } + /*time = System.currentTimeMillis() - time; + System.out.println("DB INSERT TIME:" + time);*/ dao.closeDB(); }catch(Exception e) { e.printStackTrace(); -- 2.11.0