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
128
129
130
131
132
133
134
135
136
137
138
139
|
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 {
/**
* Create an insert tweet.
*
* @return A valid database query.
*/
public static String insertProfile() {
return "INSERT INTO twitteruser (userid,displayname,timezone,tweetcount,"
+ "followercount,followedcount,location,tweetname,createdat,language) "
+ "SELECT ?, ?, ?, ?, ?, ?, ?, ?, ?, ? "
+ "WHERE NOT EXISTS "
+ "(SELECT * FROM twitteruser WHERE userid= ? )";
}
public static String insertTweet() {
return "INSERT INTO tweet (tweetid,createdat,favcount,retweetcount,text,"
+ "coordinates,language,retweetid,replyid,place) "
+ "SELECT ?, ?, ?, ?, ?, ?, ?, ?, ?, ? "
+ "WHERE NOT EXISTS "
+ "(SELECT * FROM tweet WHERE tweetid=? )";
}
public static String insertHash() {
return "INSERT INTO hashtag (tweetid, hashtag) "
+ "SELECT ?, ? "
+ "WHERE NOT EXISTS "
+ "(SELECT * FROM hashtag WHERE tweetid=? and hashtag =? )";
}
public static String insertUrl() {
return "INSERT INTO url (tweetid, url) "
+ "SELECT ?, ? "
+ "WHERE NOT EXISTS "
+ "(SELECT * FROM url WHERE tweetid=? and url =? )";
}
public static String insertMentions() {
return "INSERT INTO mentionsuser (tweetid, userid) "
+ "SELECT ?, ? "
+ "WHERE NOT EXISTS "
+ "(SELECT * FROM mentionsuser WHERE tweetid=? and userid =? )";
}
public static String insertPosted() {
return "INSERT INTO ispostedby (tweetid,userid) "
+ "SELECT ? , ? "
+ "WHERE NOT EXISTS "
+ "(SELECT * FROM ispostedby WHERE tweetid= ? )";
}
public static String insertBrand() {
return "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);
}
}
|
