1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
|
package database;
import data.Tweet;
import data.User;
import java.sql.PreparedStatement;
import java.sql.SQLException;
/**
* Utilities to create queries.
*
* @author Maurice Laveaux
*/
public class QueryUtils {
public final static String insertProfile
= "INSERT INTO twitteruser (userid,displayname,timezone,tweetcount,"
+ "followercount,followedcount,location,tweetname,createdat,language) "
+ "SELECT ?, ?, ?, ?, ?, ?, ?, ?, ?, ? "
+ "WHERE NOT EXISTS "
+ "(SELECT * FROM twitteruser WHERE userid= ? )";
public final static String insertTweet
= "INSERT INTO tweet (tweetid,createdat,favcount,retweetcount,text,"
+ "coordinates,language,retweetid,replyid,place) "
+ "SELECT ?, ?, ?, ?, ?, ?, ?, ?, ?, ? "
+ "WHERE NOT EXISTS "
+ "(SELECT * FROM tweet WHERE tweetid=? )";
public final static String insertHash
= "INSERT INTO hashtag (tweetid, hashtag) "
+ "SELECT ?, ? "
+ "WHERE NOT EXISTS "
+ "(SELECT * FROM hashtag WHERE tweetid=? and hashtag =? )";
public final static String insertUrl
= "INSERT INTO url (tweetid, url) "
+ "SELECT ?, ? "
+ "WHERE NOT EXISTS "
+ "(SELECT * FROM url WHERE tweetid=? and url =? )";
public final static String insertMentions
= "INSERT INTO mentionsuser (tweetid, userid) "
+ "SELECT ?, ? "
+ "WHERE NOT EXISTS "
+ "(SELECT * FROM mentionsuser WHERE tweetid=? and userid =? )";
public final static String insertPosted
= "INSERT INTO ispostedby (tweetid,userid) "
+ "SELECT ? , ? "
+ "WHERE NOT EXISTS "
+ "(SELECT * FROM ispostedby WHERE tweetid= ? )";
public final static String insertBrand
= "INSERT INTO mentionsbrand (tweetid,brand) "
+ "SELECT ? , ? "
+ "WHERE NOT EXISTS "
+ "(SELECT * FROM mentionsbrand WHERE tweetid = ? AND brand = ?)";
public static void setInsertParams(PreparedStatement tweetStatement,
PreparedStatement profileStatement,
PreparedStatement postedStatement,
Tweet tweet) throws SQLException {
tweetStatement.setLong(1, tweet.id);
tweetStatement.setString(2, tweet.created_at);
tweetStatement.setLong(3, tweet.favorite_count);
tweetStatement.setLong(4, tweet.retweet_count);
tweetStatement.setString(5, tweet.text);
tweetStatement.setString(6, tweet.coordinates);
tweetStatement.setString(7, tweet.lang);
if (tweet.retweeted_status != null) {
tweetStatement.setLong(8, tweet.retweeted_status.id);
} else {
tweetStatement.setLong(8, 0);
}
tweetStatement.setLong(9, tweet.in_reply_to_user_id);
tweetStatement.setString(10, tweet.place);
tweetStatement.setLong(11, tweet.id);
User twuser = tweet.user;
profileStatement.setLong(1, twuser.id);
profileStatement.setString(2, twuser.name);
profileStatement.setString(3, twuser.time_zone);
profileStatement.setLong(4, twuser.statuses_count);
profileStatement.setLong(5, twuser.followers_count);
profileStatement.setLong(6, twuser.friends_count);
profileStatement.setString(7, twuser.location);
profileStatement.setString(8, twuser.screen_name);
profileStatement.setString(9, twuser.created_at);
profileStatement.setString(10, twuser.lang);
profileStatement.setLong(11, twuser.id);
postedStatement.setLong(1, tweet.id);
postedStatement.setLong(2, twuser.id);
postedStatement.setLong(3, tweet.id);
}
public static void setInsertBrandParams(PreparedStatement brandStatement,
long id, String brand) throws SQLException {
brandStatement.setLong(1, id);
brandStatement.setString(2, brand);
brandStatement.setLong(3, id);
brandStatement.setString(4, brand);
}
public static void setInsertHashParams(PreparedStatement hashStatement,
long id, String text) throws SQLException {
hashStatement.setLong(1, id);
hashStatement.setString(2, text);
hashStatement.setLong(3, id);
hashStatement.setString(4, text);
}
public static void setInsertUrlParams(PreparedStatement UrlStatement,
long id, String text) throws SQLException {
UrlStatement.setLong(1, id);
UrlStatement.setString(2, text);
UrlStatement.setLong(3, id);
UrlStatement.setString(4, text);
}
public static void setInsertMentionsParams(PreparedStatement UrlStatement,
long id, long userid) throws SQLException {
UrlStatement.setLong(1, id);
UrlStatement.setLong(2, userid);
UrlStatement.setLong(3, id);
UrlStatement.setLong(4, userid);
}
}
|
