410 Star 4.1K Fork 1.4K

GVP野火IM/im-server

加入 Gitee
与超过 1200万 开发者一起发现、参与优秀开源项目,私有仓库也完全免费 :)
免费加入
文件
克隆/下载
DatabaseStore.java 159.94 KB
一键复制 编辑 原始数据 按行查看 历史
12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697989910010110210310410510610710810911011111211311411511611711811912012112212312412512612712812913013113213313413513613713813914014114214314414514614714814915015115215315415515615715815916016116216316416516616716816917017117217317417517617717817918018118218318418518618718818919019119219319419519619719819920020120220320420520620720820921021121221321421521621721821922022122222322422522622722822923023123223323423523623723823924024124224324424524624724824925025125225325425525625725825926026126226326426526626726826927027127227327427527627727827928028128228328428528628728828929029129229329429529629729829930030130230330430530630730830931031131231331431531631731831932032132232332432532632732832933033133233333433533633733833934034134234334434534634734834935035135235335435535635735835936036136236336436536636736836937037137237337437537637737837938038138238338438538638738838939039139239339439539639739839940040140240340440540640740840941041141241341441541641741841942042142242342442542642742842943043143243343443543643743843944044144244344444544644744844945045145245345445545645745845946046146246346446546646746846947047147247347447547647747847948048148248348448548648748848949049149249349449549649749849950050150250350450550650750850951051151251351451551651751851952052152252352452552652752852953053153253353453553653753853954054154254354454554654754854955055155255355455555655755855956056156256356456556656756856957057157257357457557657757857958058158258358458558658758858959059159259359459559659759859960060160260360460560660760860961061161261361461561661761861962062162262362462562662762862963063163263363463563663763863964064164264364464564664764864965065165265365465565665765865966066166266366466566666766866967067167267367467567667767867968068168268368468568668768868969069169269369469569669769869970070170270370470570670770870971071171271371471571671771871972072172272372472572672772872973073173273373473573673773873974074174274374474574674774874975075175275375475575675775875976076176276376476576676776876977077177277377477577677777877978078178278378478578678778878979079179279379479579679779879980080180280380480580680780880981081181281381481581681781881982082182282382482582682782882983083183283383483583683783883984084184284384484584684784884985085185285385485585685785885986086186286386486586686786886987087187287387487587687787887988088188288388488588688788888989089189289389489589689789889990090190290390490590690790890991091191291391491591691791891992092192292392492592692792892993093193293393493593693793893994094194294394494594694794894995095195295395495595695795895996096196296396496596696796896997097197297397497597697797897998098198298398498598698798898999099199299399499599699799899910001001100210031004100510061007100810091010101110121013101410151016101710181019102010211022102310241025102610271028102910301031103210331034103510361037103810391040104110421043104410451046104710481049105010511052105310541055105610571058105910601061106210631064106510661067106810691070107110721073107410751076107710781079108010811082108310841085108610871088108910901091109210931094109510961097109810991100110111021103110411051106110711081109111011111112111311141115111611171118111911201121112211231124112511261127112811291130113111321133113411351136113711381139114011411142114311441145114611471148114911501151115211531154115511561157115811591160116111621163116411651166116711681169117011711172117311741175117611771178117911801181118211831184118511861187118811891190119111921193119411951196119711981199120012011202120312041205120612071208120912101211121212131214121512161217121812191220122112221223122412251226122712281229123012311232123312341235123612371238123912401241124212431244124512461247124812491250125112521253125412551256125712581259126012611262126312641265126612671268126912701271127212731274127512761277127812791280128112821283128412851286128712881289129012911292129312941295129612971298129913001301130213031304130513061307130813091310131113121313131413151316131713181319132013211322132313241325132613271328132913301331133213331334133513361337133813391340134113421343134413451346134713481349135013511352135313541355135613571358135913601361136213631364136513661367136813691370137113721373137413751376137713781379138013811382138313841385138613871388138913901391139213931394139513961397139813991400140114021403140414051406140714081409141014111412141314141415141614171418141914201421142214231424142514261427142814291430143114321433143414351436143714381439144014411442144314441445144614471448144914501451145214531454145514561457145814591460146114621463146414651466146714681469147014711472147314741475147614771478147914801481148214831484148514861487148814891490149114921493149414951496149714981499150015011502150315041505150615071508150915101511151215131514151515161517151815191520152115221523152415251526152715281529153015311532153315341535153615371538153915401541154215431544154515461547154815491550155115521553155415551556155715581559156015611562156315641565156615671568156915701571157215731574157515761577157815791580158115821583158415851586158715881589159015911592159315941595159615971598159916001601160216031604160516061607160816091610161116121613161416151616161716181619162016211622162316241625162616271628162916301631163216331634163516361637163816391640164116421643164416451646164716481649165016511652165316541655165616571658165916601661166216631664166516661667166816691670167116721673167416751676167716781679168016811682168316841685168616871688168916901691169216931694169516961697169816991700170117021703170417051706170717081709171017111712171317141715171617171718171917201721172217231724172517261727172817291730173117321733173417351736173717381739174017411742174317441745174617471748174917501751175217531754175517561757175817591760176117621763176417651766176717681769177017711772177317741775177617771778177917801781178217831784178517861787178817891790179117921793179417951796179717981799180018011802180318041805180618071808180918101811181218131814181518161817181818191820182118221823182418251826182718281829183018311832183318341835183618371838183918401841184218431844184518461847184818491850185118521853185418551856185718581859186018611862186318641865186618671868186918701871187218731874187518761877187818791880188118821883188418851886188718881889189018911892189318941895189618971898189919001901190219031904190519061907190819091910191119121913191419151916191719181919192019211922192319241925192619271928192919301931193219331934193519361937193819391940194119421943194419451946194719481949195019511952195319541955195619571958195919601961196219631964196519661967196819691970197119721973197419751976197719781979198019811982198319841985198619871988198919901991199219931994199519961997199819992000200120022003200420052006200720082009201020112012201320142015201620172018201920202021202220232024202520262027202820292030203120322033203420352036203720382039204020412042204320442045204620472048204920502051205220532054205520562057205820592060206120622063206420652066206720682069207020712072207320742075207620772078207920802081208220832084208520862087208820892090209120922093209420952096209720982099210021012102210321042105210621072108210921102111211221132114211521162117211821192120212121222123212421252126212721282129213021312132213321342135213621372138213921402141214221432144214521462147214821492150215121522153215421552156215721582159216021612162216321642165216621672168216921702171217221732174217521762177217821792180218121822183218421852186218721882189219021912192219321942195219621972198219922002201220222032204220522062207220822092210221122122213221422152216221722182219222022212222222322242225222622272228222922302231223222332234223522362237223822392240224122422243224422452246224722482249225022512252225322542255225622572258225922602261226222632264226522662267226822692270227122722273227422752276227722782279228022812282228322842285228622872288228922902291229222932294229522962297229822992300230123022303230423052306230723082309231023112312231323142315231623172318231923202321232223232324232523262327232823292330233123322333233423352336233723382339234023412342234323442345234623472348234923502351235223532354235523562357235823592360236123622363236423652366236723682369237023712372237323742375237623772378237923802381238223832384238523862387238823892390239123922393239423952396239723982399240024012402240324042405240624072408240924102411241224132414241524162417241824192420242124222423242424252426242724282429243024312432243324342435243624372438243924402441244224432444244524462447244824492450245124522453245424552456245724582459246024612462246324642465246624672468246924702471247224732474247524762477247824792480248124822483248424852486248724882489249024912492249324942495249624972498249925002501250225032504250525062507250825092510251125122513251425152516251725182519252025212522252325242525252625272528252925302531253225332534253525362537253825392540254125422543254425452546254725482549255025512552255325542555255625572558255925602561256225632564256525662567256825692570257125722573257425752576257725782579258025812582258325842585258625872588258925902591259225932594259525962597259825992600260126022603260426052606260726082609261026112612261326142615261626172618261926202621262226232624262526262627262826292630263126322633263426352636263726382639264026412642264326442645264626472648264926502651265226532654265526562657265826592660266126622663266426652666266726682669267026712672267326742675267626772678267926802681268226832684268526862687268826892690269126922693269426952696269726982699270027012702270327042705270627072708270927102711271227132714271527162717271827192720272127222723272427252726272727282729273027312732273327342735273627372738273927402741274227432744274527462747274827492750275127522753275427552756275727582759276027612762276327642765276627672768276927702771277227732774277527762777277827792780278127822783278427852786278727882789279027912792279327942795279627972798279928002801280228032804280528062807280828092810281128122813281428152816281728182819282028212822282328242825282628272828282928302831283228332834283528362837283828392840284128422843284428452846284728482849285028512852285328542855285628572858285928602861286228632864286528662867286828692870287128722873287428752876287728782879288028812882288328842885288628872888288928902891289228932894289528962897289828992900290129022903290429052906290729082909291029112912291329142915291629172918291929202921292229232924292529262927292829292930293129322933293429352936293729382939294029412942294329442945294629472948294929502951295229532954295529562957295829592960296129622963296429652966296729682969297029712972297329742975297629772978297929802981298229832984298529862987298829892990299129922993299429952996299729982999300030013002300330043005300630073008300930103011301230133014301530163017301830193020302130223023302430253026302730283029303030313032303330343035303630373038303930403041304230433044304530463047304830493050305130523053305430553056305730583059306030613062306330643065306630673068306930703071307230733074307530763077307830793080308130823083308430853086308730883089309030913092309330943095309630973098309931003101310231033104310531063107310831093110311131123113311431153116311731183119312031213122312331243125312631273128312931303131313231333134313531363137313831393140314131423143314431453146314731483149315031513152315331543155315631573158315931603161316231633164316531663167316831693170317131723173317431753176317731783179318031813182318331843185318631873188318931903191319231933194319531963197319831993200320132023203320432053206320732083209321032113212321332143215321632173218321932203221322232233224322532263227322832293230323132323233323432353236323732383239324032413242324332443245324632473248324932503251325232533254325532563257325832593260326132623263326432653266326732683269327032713272327332743275327632773278327932803281328232833284328532863287328832893290329132923293329432953296329732983299330033013302330333043305330633073308330933103311331233133314331533163317331833193320332133223323332433253326332733283329333033313332333333343335333633373338333933403341334233433344334533463347334833493350335133523353335433553356335733583359336033613362336333643365336633673368336933703371337233733374337533763377337833793380338133823383338433853386338733883389339033913392339333943395339633973398339934003401340234033404340534063407340834093410341134123413341434153416341734183419342034213422342334243425342634273428342934303431343234333434343534363437343834393440344134423443344434453446344734483449345034513452345334543455345634573458345934603461346234633464346534663467346834693470347134723473347434753476347734783479348034813482348334843485348634873488348934903491349234933494349534963497349834993500350135023503350435053506350735083509351035113512351335143515351635173518351935203521352235233524352535263527352835293530353135323533353435353536353735383539354035413542354335443545354635473548354935503551355235533554355535563557355835593560356135623563356435653566356735683569357035713572357335743575357635773578357935803581358235833584358535863587358835893590359135923593359435953596359735983599360036013602360336043605360636073608360936103611361236133614361536163617361836193620362136223623362436253626362736283629363036313632363336343635363636373638363936403641364236433644364536463647364836493650365136523653365436553656365736583659366036613662366336643665366636673668366936703671367236733674367536763677367836793680368136823683368436853686368736883689369036913692369336943695369636973698369937003701370237033704370537063707370837093710371137123713371437153716371737183719372037213722372337243725372637273728372937303731373237333734373537363737373837393740374137423743374437453746374737483749375037513752375337543755375637573758375937603761376237633764376537663767376837693770377137723773377437753776377737783779378037813782378337843785378637873788378937903791379237933794379537963797379837993800380138023803380438053806380738083809381038113812381338143815381638173818381938203821382238233824382538263827382838293830383138323833383438353836383738383839384038413842384338443845384638473848384938503851385238533854385538563857385838593860386138623863386438653866386738683869387038713872387338743875387638773878387938803881388238833884388538863887388838893890389138923893389438953896389738983899390039013902390339043905390639073908390939103911391239133914391539163917391839193920392139223923392439253926392739283929393039313932393339343935393639373938393939403941394239433944394539463947394839493950395139523953395439553956395739583959396039613962396339643965396639673968396939703971397239733974397539763977397839793980398139823983398439853986398739883989399039913992399339943995399639973998399940004001400240034004400540064007400840094010401140124013401440154016401740184019402040214022402340244025402640274028402940304031403240334034403540364037403840394040404140424043
/*
* This file is part of the Wildfire Chat package.
* (c) Heavyrain2012 <heavyrain.lee@gmail.com>
*
* For the full copyright and license information, please view the LICENSE
* file that was distributed with this source code.
*/
package io.moquette.persistence;
import cn.wildfirechat.pojos.SystemSettingPojo;
import cn.wildfirechat.proto.ProtoConstants;
import cn.wildfirechat.proto.WFCMessage;
import cn.wildfirechat.server.ThreadPoolExecutorWrapper;
import com.hazelcast.core.HazelcastInstance;
import com.hazelcast.core.MultiMap;
import com.hazelcast.util.StringUtil;
import com.xiaoleilu.loServer.model.FriendData;
import io.moquette.server.Server;
import io.moquette.spi.ClientSession;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import win.liyufan.im.DBUtil;
import win.liyufan.im.MessageBundle;
import win.liyufan.im.MessageShardingUtil;
import win.liyufan.im.Utility;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.security.MessageDigest;
import java.sql.*;
import java.util.*;
import java.util.function.Function;
import static cn.wildfirechat.common.IMExceptionEvent.EventType.RDBS_Exception;
import static cn.wildfirechat.proto.ProtoConstants.PersistFlag.Transparent;
import static cn.wildfirechat.proto.ProtoConstants.UserSearchUserType.UserSearchUserType_ONLY_ROBOT;
import static cn.wildfirechat.proto.ProtoConstants.UserSearchUserType.UserSearchUserType_ONLY_USER;
import static io.moquette.BrokerConstants.GROUP_INFO_MARK_DELETION;
import static io.moquette.server.Constants.MAX_MESSAGE_QUEUE;
import static cn.wildfirechat.proto.ProtoConstants.SearchUserType.*;
import static win.liyufan.im.UserSettingScope.kUserSettingPrivacySearchable;
public class DatabaseStore {
private static final Logger LOG = LoggerFactory.getLogger(DatabaseStore.class);
private final ThreadPoolExecutorWrapper mScheduler;
private boolean disableRemoteMessageSearch = false;
private boolean encryptMessage = false;
private boolean keepGroupInfo = false;
public void setDisableRemoteMessageSearch(boolean disableRemoteMessageSearch) {
this.disableRemoteMessageSearch = disableRemoteMessageSearch;
}
public void setEncryptMessage(boolean encryptMessage) {
this.encryptMessage = encryptMessage;
}
public DatabaseStore(ThreadPoolExecutorWrapper scheduler) {
this.mScheduler = scheduler;
try {
keepGroupInfo = Boolean.parseBoolean(Server.getServer().getConfig().getProperty(GROUP_INFO_MARK_DELETION, "false"));
} catch (Exception e) {
}
}
TreeMap<Long, Long> reloadUserMessageMaps(String userId) {
Connection connection = null;
PreparedStatement statement = null;
ResultSet rs = null;
TreeMap<Long, Long> out = new TreeMap<>();
try {
connection = DBUtil.getConnection();
String sql = "select `_seq`, `_mid` from " + getUserMessageTable(userId) + " where `_uid` = ? order by `_seq` DESC limit " + MAX_MESSAGE_QUEUE;
statement = connection.prepareStatement(sql);
statement.setString(1, userId);
rs = statement.executeQuery();
while (rs.next()) {
int index = 1;
long msgSeq = rs.getLong(index++);
long msgId = rs.getLong(index);
out.put(msgSeq, msgId);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} finally {
DBUtil.closeDB(connection, statement, rs);
}
return out;
}
boolean updateSystemSetting(int id, String value, String desc) {
Connection connection = null;
PreparedStatement statement = null;
try {
connection = DBUtil.getConnection();
String sql = "insert into t_settings " +
" (`id`, `_value`, `_desc`) values(?, ?, ?)" +
" ON DUPLICATE KEY UPDATE " +
"`_value` = ?," +
"`_desc` = ?";
statement = connection.prepareStatement(sql);
int index = 1;
statement.setLong(index++, id);
statement.setString(index++, value);
statement.setString(index++, desc);
statement.setString(index++, value);
statement.setString(index++, desc);
int count = statement.executeUpdate();
LOG.info("Update rows {}", count);
return true;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} finally {
DBUtil.closeDB(connection, statement);
}
return false;
}
SystemSettingPojo getSystemSetting(int id) {
Connection connection = null;
PreparedStatement statement = null;
ResultSet rs = null;
try {
connection = DBUtil.getConnection();
String sql = "select `_value`, `_desc` from t_settings where `id` = ?";
statement = connection.prepareStatement(sql);
int index = 1;
statement.setLong(index++, id);
rs = statement.executeQuery();
while (rs.next()) {
SystemSettingPojo out = new SystemSettingPojo();
index = 1;
out.id = id;
String value = rs.getString(index++);
value = (value == null ? "" : value);
out.value = value;
value = rs.getString(index++);
value = (value == null ? "" : value);
out.desc = value;
return out;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} finally {
DBUtil.closeDB(connection, statement, rs);
}
return null;
}
List<WFCMessage.User> searchUserByNameMobileUserId(String keyword, int searchType, int userType) {
ArrayList<WFCMessage.User> out = new ArrayList<>();
Connection connection = null;
PreparedStatement statement = null;
ResultSet rs = null;
try {
connection = DBUtil.getConnection();
String sql = "select `_uid`, `_name`" +
", `_display_name`" +
", `_portrait`" +
", `_mobile`" +
", `_gender`" +
", `_email`" +
", `_address`" +
", `_company`" +
", `_social`" +
", `_extra`" +
", `_dt`, `_type` from t_user";
int sqlParaCount = 1;
if(searchType == SearchUserType_Name) {
sql += " where `_name` = ? ";
} else if(searchType == SearchUserType_Mobile) {
sql += " where `_mobile` = ? ";
} else if(searchType == SearchUserType_UserId) {
sql += " where `_uid` = ? ";
} else if(searchType == SearchUserType_Name_Mobile || searchType == SearchUserType_Name_Mobile_DisplayName) {
sqlParaCount = 2;
sql += " where (`_name` = ? or `_mobile` = ?) ";
} else {
sqlParaCount = 3;
sql += " where (`_name` = ? or `_mobile` = ? or `_uid` = ?) ";
}
if(userType == UserSearchUserType_ONLY_USER) {
sql += " and _type <> 2 "; //can not search device
sql += " and _type <> 1 ";
} else if(userType == UserSearchUserType_ONLY_ROBOT) {
sql += " and _type = 1 ";
} else {
sql += " and _type <> 2 "; //can not search device
}
sql += " and _deleted = 0 ";
if(searchType == SearchUserType_Name || searchType == SearchUserType_Mobile || searchType == SearchUserType_UserId) {
sql += " limit 1";
} else if(searchType == SearchUserType_Name_Mobile || searchType == SearchUserType_Name_Mobile_DisplayName) {
sql += " limit 2";
} else {
sql += " limit 3";
}
statement = connection.prepareStatement(sql);
for (int i = 0; i < sqlParaCount; i++) {
statement.setString(i+1, keyword);
}
rs = statement.executeQuery();
while (rs.next()) {
WFCMessage.User.Builder builder = WFCMessage.User.newBuilder();
int index = 1;
String value = rs.getString(index++);
value = (value == null ? "" : value);
builder.setUid(value);
value = rs.getString(index++);
value = (value == null ? "" : value);
builder.setName(value);
value = rs.getString(index++);
value = (value == null ? "" : value);
builder.setDisplayName(value);
value = rs.getString(index++);
value = (value == null ? "" : value);
builder.setPortrait(value);
value = rs.getString(index++);
value = (value == null ? "" : value);
builder.setMobile(value);
int gender = rs.getInt(index++);
builder.setGender(gender);
value = rs.getString(index++);
value = (value == null ? "" : value);
builder.setEmail(value);
value = rs.getString(index++);
value = (value == null ? "" : value);
builder.setAddress(value);
value = rs.getString(index++);
value = (value == null ? "" : value);
builder.setCompany(value);
value = rs.getString(index++);
value = (value == null ? "" : value);
builder.setSocial(value);
value = rs.getString(index++);
value = (value == null ? "" : value);
builder.setExtra(value);
long longValue = rs.getLong(index++);
builder.setUpdateDt(longValue);
int type = rs.getInt(index++);
builder.setType( type);
WFCMessage.User user = builder.build();
out.add(user);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} finally {
DBUtil.closeDB(connection, statement, rs);
}
List<WFCMessage.User> filter = new ArrayList<>();
for (WFCMessage.User user : out) {
WFCMessage.UserSettingEntry userSettingEntry = Server.getServer().getStore().messagesStore().getUserSetting(user.getUid(), kUserSettingPrivacySearchable, null);
if(userSettingEntry != null) {
int value = 0;
try {
value = StringUtil.isNullOrEmpty(userSettingEntry.getValue())?0:Integer.parseInt(userSettingEntry.getValue());
} catch (NumberFormatException e) {
e.printStackTrace();
}
if((value & ProtoConstants.DisableSearchMask.DisableSearchNameMask) > 0) {
if (searchType == SearchUserType_Name) {
filter.add(user);
continue;
} else if(searchType == SearchUserType_Name_Mobile || searchType == SearchUserType_Name_Mobile_DisplayName) {
if(keyword.equals(user.getName())) {
if(!keyword.equals(user.getMobile())) {
filter.add(user);
continue;
} else if((value & ProtoConstants.DisableSearchMask.DisableSearchMobileMask) > 0) {
filter.add(user);
continue;
}
}
} else if(searchType == SearchUserType_Name_Mobile_UserId) {
if(keyword.equals(user.getName())) {
if(keyword.equals(user.getMobile()) && keyword.equals(user.getUid())) {
if((value & ProtoConstants.DisableSearchMask.DisableSearchMobileMask) > 0 && (value & ProtoConstants.DisableSearchMask.DisableSearchUserIdMask) > 0) {
filter.add(user);
continue;
}
} else if(keyword.equals(user.getMobile())) {
if((value & ProtoConstants.DisableSearchMask.DisableSearchMobileMask) > 0) {
filter.add(user);
continue;
}
} else if(keyword.equals(user.getUid())) {
if((value & ProtoConstants.DisableSearchMask.DisableSearchUserIdMask) > 0) {
filter.add(user);
continue;
}
} else {
filter.add(user);
continue;
}
}
}
}
if((value & ProtoConstants.DisableSearchMask.DisableSearchMobileMask) > 0) {
if (searchType == SearchUserType_Mobile) {
filter.add(user);
continue;
} else if(searchType == SearchUserType_Name_Mobile || searchType == SearchUserType_Name_Mobile_DisplayName) {
if(keyword.equals(user.getMobile())) {
if(!keyword.equals(user.getName())) {
filter.add(user);
continue;
} else if((value & ProtoConstants.DisableSearchMask.DisableSearchNameMask) > 0) {
filter.add(user);
continue;
}
}
} else if(searchType == SearchUserType_Name_Mobile_UserId) {
if(keyword.equals(user.getMobile())) {
if(keyword.equals(user.getName()) && keyword.equals(user.getUid())) {
if((value & ProtoConstants.DisableSearchMask.DisableSearchNameMask) > 0 && (value & ProtoConstants.DisableSearchMask.DisableSearchUserIdMask) > 0) {
filter.add(user);
continue;
}
} else if(keyword.equals(user.getName())) {
if((value & ProtoConstants.DisableSearchMask.DisableSearchNameMask) > 0) {
filter.add(user);
continue;
}
} else if(keyword.equals(user.getUid())) {
if((value & ProtoConstants.DisableSearchMask.DisableSearchUserIdMask) > 0) {
filter.add(user);
continue;
}
} else {
filter.add(user);
continue;
}
}
}
}
if((value & ProtoConstants.DisableSearchMask.DisableSearchUserIdMask) > 0) {
if (searchType == SearchUserType_UserId) {
filter.add(user);
continue;
} else if(searchType == SearchUserType_Name_Mobile_UserId) {
if(keyword.equals(user.getUid())) {
if(keyword.equals(user.getName()) && keyword.equals(user.getMobile())) {
if((value & ProtoConstants.DisableSearchMask.DisableSearchNameMask) > 0 && (value & ProtoConstants.DisableSearchMask.DisableSearchMobileMask) > 0) {
filter.add(user);
continue;
}
} else if(keyword.equals(user.getName())) {
if((value & ProtoConstants.DisableSearchMask.DisableSearchNameMask) > 0) {
filter.add(user);
continue;
}
} else if(keyword.equals(user.getMobile())) {
if((value & ProtoConstants.DisableSearchMask.DisableSearchMobileMask) > 0) {
filter.add(user);
continue;
}
} else {
filter.add(user);
continue;
}
}
}
}
}
}
out.removeAll(filter);
return out;
}
List<WFCMessage.User> searchUserByDisplayName(String keyword, int userType, int page, List<WFCMessage.User> nameOrIdMatched) {
Connection connection = null;
PreparedStatement statement = null;
ResultSet rs = null;
ArrayList<WFCMessage.User> out = new ArrayList<>();
try {
connection = DBUtil.getConnection();
String sql = "select u._uid, u._name, u._display_name, u._portrait, u._mobile, u._gender, u._email, u._address, u._company, u._social, u._extra, u._dt, u._type " +
" from t_user u left join (select _uid, _value from t_user_setting where _scope = 27) s on u._uid = s._uid " +
" where u._display_name like ? ESCAPE '!' ";
if(userType == UserSearchUserType_ONLY_USER) {
sql += " and u._type <> 2 ";
sql += " and u._type <> 1 ";
} else if(userType == UserSearchUserType_ONLY_ROBOT) {
sql += " and u._type = 1 ";
} else {
sql += " and u._type <> 2 ";
}
sql += " and u._deleted = 0 ";
if(nameOrIdMatched.size() == 1) {
sql += " and u._uid <> ? and u._name <> ?";
} else if(nameOrIdMatched.size() == 2) {
sql += " and u._uid <> ? and u._uid <>? and u._name <> ? and u._name <>? ";
}
sql += " and (s._value is null or (s._value <> 1 and s._value <> 3 and s._value <> 5 and s._value <> 7)) limit ";
if(page == 0) {
sql += (20 - nameOrIdMatched.size());
} else {
sql += 20;
}
if (page > 0) {
sql += " offset " + (page * 20 - nameOrIdMatched.size());
}
keyword = keyword
.replace("!", "!!")
.replace("%", "!%")
.replace("_", "!_")
.replace("[", "![");
statement = connection.prepareStatement(sql);
statement.setString(1, "%" + keyword + "%");
if(nameOrIdMatched.size() == 1) {
statement.setString(2, nameOrIdMatched.toArray(new WFCMessage.User[0])[0].getUid());
statement.setString(3, nameOrIdMatched.toArray(new WFCMessage.User[0])[0].getUid());
} else if(nameOrIdMatched.size() == 2) {
statement.setString(2, nameOrIdMatched.toArray(new WFCMessage.User[0])[0].getUid());
statement.setString(3, nameOrIdMatched.toArray(new WFCMessage.User[0])[1].getUid());
statement.setString(4, nameOrIdMatched.toArray(new WFCMessage.User[0])[0].getUid());
statement.setString(5, nameOrIdMatched.toArray(new WFCMessage.User[0])[1].getUid());
}
rs = statement.executeQuery();
while (rs.next()) {
WFCMessage.User.Builder builder = WFCMessage.User.newBuilder();
int index = 1;
String value = rs.getString(index++);
value = (value == null ? "" : value);
builder.setUid(value);
value = rs.getString(index++);
value = (value == null ? "" : value);
builder.setName(value);
value = rs.getString(index++);
value = (value == null ? "" : value);
builder.setDisplayName(value);
value = rs.getString(index++);
value = (value == null ? "" : value);
builder.setPortrait(value);
value = rs.getString(index++);
value = (value == null ? "" : value);
builder.setMobile(value);
int gender = rs.getInt(index++);
builder.setGender(gender);
value = rs.getString(index++);
value = (value == null ? "" : value);
builder.setEmail(value);
value = rs.getString(index++);
value = (value == null ? "" : value);
builder.setAddress(value);
value = rs.getString(index++);
value = (value == null ? "" : value);
builder.setCompany(value);
value = rs.getString(index++);
value = (value == null ? "" : value);
builder.setSocial(value);
value = rs.getString(index++);
value = (value == null ? "" : value);
builder.setExtra(value);
long longValue = rs.getLong(index++);
builder.setUpdateDt(longValue);
int type = rs.getInt(index++);
builder.setType(type);
WFCMessage.User user = builder.build();
out.add(user);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} finally {
DBUtil.closeDB(connection, statement, rs);
}
return out;
}
List<WFCMessage.User> searchUserFromDB(String keyword, int searchType, int userType, int page) {
List<WFCMessage.User> nameOrIdMatched = searchUserByNameMobileUserId(keyword, searchType, userType);
List<WFCMessage.User> out = new ArrayList<>();
if(page == 0) {
out.addAll(nameOrIdMatched);
}
if(searchType == SearchUserType_General || searchType == SearchUserType_Name_Mobile_DisplayName) {
List<WFCMessage.User> general = searchUserByDisplayName(keyword, userType, page, nameOrIdMatched);
general.removeAll(nameOrIdMatched);
out.addAll(general);
}
return out;
}
Integer getUserStatus(String userId) {
Connection connection = null;
PreparedStatement statement = null;
ResultSet rs = null;
try {
connection = DBUtil.getConnection();
String sql = "select `_status` from t_user_status where `_uid` = ?";
statement = connection.prepareStatement(sql);
statement.setString(1, userId);
rs = statement.executeQuery();
if (rs.next()) {
return rs.getInt(1);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} finally {
DBUtil.closeDB(connection, statement, rs);
}
return 0;
}
synchronized Collection<WFCMessage.GroupMember> reloadGroupMemberFromDB(HazelcastInstance hzInstance, String groupId) {
MultiMap<String, WFCMessage.GroupMember> groupMembers = hzInstance.getMultiMap(MemoryMessagesStore.GROUP_MEMBERS);
if (groupMembers.get(groupId).size() > 0) {
return groupMembers.get(groupId);
}
Connection connection = null;
PreparedStatement statement = null;
ResultSet rs = null;
try {
connection = DBUtil.getConnection();
String sql = "select `_mid`" +
", `_alias`" +
", `_type`" +
", `_dt`, `_create_dt`, `_extra` from t_group_member where _gid = ?";
statement = connection.prepareStatement(sql);
statement.setString(1, groupId);
int index;
rs = statement.executeQuery();
while (rs.next()) {
WFCMessage.GroupMember.Builder builder = WFCMessage.GroupMember.newBuilder();
index = 1;
String value = rs.getString(index++);
value = (value == null ? "" : value);
builder.setMemberId(value);
value = rs.getString(index++);
value = (value == null ? "" : value);
builder.setAlias(value);
int intvalue = rs.getInt(index++);
builder.setType(intvalue);
long longValue = rs.getLong(index++);
builder.setUpdateDt(longValue);
longValue = rs.getLong(index++);
builder.setCreateDt(longValue);
value = rs.getString(index++);
value = (value == null ? "" : value);
builder.setExtra(value);
WFCMessage.GroupMember member = builder.build();
groupMembers.put(groupId, member);
}
return groupMembers.get(groupId);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} finally {
DBUtil.closeDB(connection, statement, rs);
}
return new ArrayList<>();
}
void reloadFriendsFromDB(HazelcastInstance hzInstance) {
Connection connection = null;
PreparedStatement statement = null;
ResultSet rs = null;
MultiMap<String, FriendData> friendsMap = hzInstance.getMultiMap(MemoryMessagesStore.USER_FRIENDS);
if (friendsMap.size() > 0) {
return;
}
try {
connection = DBUtil.getConnection();
String sql = "select `_uid`, `_friend_uid`, `_alias`, `_state`, `_blacked`, `_dt`, `_extra` from t_friend";
statement = connection.prepareStatement(sql);
int index;
rs = statement.executeQuery();
while (rs.next()) {
FriendData builder = new FriendData();
index = 1;
String value = rs.getString(index++);
value = (value == null ? "" : value);
builder.setUserId(value);
value = rs.getString(index++);
value = (value == null ? "" : value);
builder.setFriendUid(value);
value = rs.getString(index++);
value = (value == null ? "" : value);
builder.setAlias(value);
int intvalue = rs.getInt(index++);
builder.setState(intvalue);
intvalue = rs.getInt(index++);
builder.setBlacked(intvalue);
long longvalue = rs.getLong(index++);
builder.setTimestamp(longvalue);
builder.setExtra(rs.getString(index++));
friendsMap.put(builder.getUserId(), builder);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} finally {
DBUtil.closeDB(connection, statement, rs);
}
}
void reloadFriendRequestsFromDB(HazelcastInstance hzInstance) {
Connection connection = null;
PreparedStatement statement = null;
ResultSet rs = null;
MultiMap<String, WFCMessage.FriendRequest> requestMap = hzInstance.getMultiMap(MemoryMessagesStore.USER_FRIENDS_REQUEST);
if (requestMap.size() > 0) {
return;
}
try {
connection = DBUtil.getConnection();
String sql = "select `_uid`, `_friend_uid`, `_reason`, `_status`, `_dt`, `_from_read_status`, `_to_read_status`, `_extra` from t_friend_request";
statement = connection.prepareStatement(sql);
int index;
rs = statement.executeQuery();
while (rs.next()) {
WFCMessage.FriendRequest.Builder builder = WFCMessage.FriendRequest.newBuilder();
index = 1;
String value = rs.getString(index++);
value = (value == null ? "" : value);
builder.setFromUid(value);
value = rs.getString(index++);
value = (value == null ? "" : value);
builder.setToUid(value);
value = rs.getString(index++);
value = (value == null ? "" : value);
builder.setReason(value);
int intvalue = rs.getInt(index++);
builder.setStatus(intvalue);
long longvalue = rs.getLong(index++);
builder.setUpdateDt(longvalue);
intvalue = rs.getInt(index++);
builder.setFromReadStatus(intvalue > 0);
intvalue = rs.getInt(index++);
builder.setToReadStatus(intvalue > 0);
value = rs.getString(index++);
value = (value == null ? "" : value);
builder.setExtra(value);
WFCMessage.FriendRequest request = builder.build();
requestMap.put(request.getFromUid(), request);
requestMap.put(request.getToUid(), request);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} finally {
DBUtil.closeDB(connection, statement, rs);
}
}
void persistMessage(final WFCMessage.Message message, boolean update) {
if(message.getContent().getPersistFlag() == Transparent) {
return;
}
mScheduler.execute(()-> {
Connection connection = null;
PreparedStatement statement = null;
try {
connection = DBUtil.getConnection();
String table = MessageShardingUtil.getMessageTable(message.getMessageId());
String sql;
if (disableRemoteMessageSearch) {
sql = "insert into " + table +
" (`_mid`, `_from`, `_type`, `_target`, `_line`, `_data`, `_dt`, `_content_type`, `_to`) values(?, ?, ?, ?, ?, ?, ?, ?, ?)" +
" ON DUPLICATE KEY UPDATE " +
"`_data` = ?," +
"`_dt` = ?," +
"`_content_type` = ?";
} else {
sql = "insert into " + table +
" (`_mid`, `_from`, `_type`, `_target`, `_line`, `_data`, `_searchable_key`, `_dt`, `_content_type`, `_to`) values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)" +
" ON DUPLICATE KEY UPDATE " +
"`_data` = ?," +
"`_searchable_key` = ?," +
"`_dt` = ?," +
"`_content_type` = ?";
}
String searchableContent = message.getContent().getSearchableContent() == null ? "" : message.getContent().getSearchableContent();
statement = connection.prepareStatement(sql);
int index = 1;
statement.setLong(index++, message.getMessageId());
statement.setString(index++, message.getFromUser());
statement.setInt(index++, message.getConversation().getType());
statement.setString(index++, message.getConversation().getTarget());
statement.setInt(index++, message.getConversation().getLine());
Blob blob = connection.createBlob();
blob.setBytes(1, encryptMessageContent(message.getContent().toByteArray(), false));
statement.setBlob(index++, blob);
if (!disableRemoteMessageSearch) {
statement.setString(index++, searchableContent);
}
statement.setTimestamp(index++, new Timestamp(message.getServerTimestamp()));
statement.setInt(index++, message.getContent().getType());
String to = message.getToUser();
if (StringUtil.isNullOrEmpty(message.getToUser())) {
if (message.getToList().size() > 0) {
to = message.getToList().get(0);
}
}
statement.setString(index++, to);
statement.setBlob(index++, blob);
if (!disableRemoteMessageSearch) {
statement.setString(index++, searchableContent);
}
statement.setTimestamp(index++, new Timestamp(message.getServerTimestamp()));
statement.setInt(index++, message.getContent().getType());
int count = statement.executeUpdate();
LOG.info("Update rows {}", count);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} finally {
DBUtil.closeDB(connection, statement);
}
});
}
byte[] encryptMessageContent(byte[] in, boolean force) {
if(in != null && (encryptMessage || force)) {
for (int i = 0; i < in.length; i++) {
in[i] ^= 0xBD;
}
}
return in;
}
void persistSensitiveMessage(final WFCMessage.Message message) {
if(message.getContent().getPersistFlag() == Transparent) {
return;
}
mScheduler.execute(()-> {
Connection connection = null;
PreparedStatement statement = null;
try {
connection = DBUtil.getConnection();
String sql = "insert into " + "t_sensitive_messages" +
" (`_mid`, `_from`, `_type`, `_target`, `_line`, `_data`, `_searchable_key`, `_dt`, `_content_type`) values(?, ?, ?, ?, ?, ?, ?, ?, ?)" +
" ON DUPLICATE KEY UPDATE " +
"`_data` = ?," +
"`_searchable_key` = ?," +
"`_dt` = ?," +
"`_content_type` = ?";
String searchableContent = message.getContent().getSearchableContent() == null ? "" : message.getContent().getSearchableContent();
statement = connection.prepareStatement(sql);
int index = 1;
statement.setLong(index++, message.getMessageId());
statement.setString(index++, message.getFromUser());
statement.setInt(index++, message.getConversation().getType());
statement.setString(index++, message.getConversation().getTarget());
statement.setInt(index++, message.getConversation().getLine());
Blob blob = connection.createBlob();
blob.setBytes(1, encryptMessageContent(message.getContent().toByteArray(), false));
statement.setBlob(index++, blob);
statement.setString(index++, searchableContent);
statement.setTimestamp(index++, new Timestamp(message.getServerTimestamp()));
statement.setInt(index++, message.getContent().getType());
statement.setBlob(index++, blob);
statement.setString(index++, searchableContent);
statement.setTimestamp(index++, new Timestamp(message.getServerTimestamp()));
statement.setInt(index++, message.getContent().getType());
int count = statement.executeUpdate();
LOG.info("Update rows {}", count);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} finally {
DBUtil.closeDB(connection, statement);
}
});
}
Map<Long, MessageBundle> getMessages(Collection<Long> keys) {
Map<String, List<Long>> messageTableMap = new HashMap<>();
for (Long key : keys) {
String messageTableId = MessageShardingUtil.getMessageTable(key);
messageTableMap.computeIfAbsent(messageTableId, new Function<String, List<Long>>() {
@Override
public List<Long> apply(String s) {
return new ArrayList<>();
}
});
messageTableMap.get(messageTableId).add(key);
}
Map<Long, MessageBundle> out = null;
Connection connection = null;
try {
connection = DBUtil.getConnection();
out = new HashMap<>();
for (Map.Entry<String, List<Long>> entry : messageTableMap.entrySet()) {
String sql = "select `_mid`, `_from`, `_type`, `_target`, `_line`, `_data`, `_dt` from " + entry.getKey() +" where _mid in (";
for (int i = 0; i < entry.getValue().size(); i++) {
sql += entry.getValue().get(i);
if (i != entry.getValue().size() - 1) {
sql += ",";
}
}
sql += ")";
ResultSet resultSet = null;
try {
PreparedStatement statement = connection.prepareStatement(sql);
resultSet = statement.executeQuery();
while (resultSet.next()) {
WFCMessage.Message.Builder builder = WFCMessage.Message.newBuilder();
int index = 1;
builder.setMessageId(resultSet.getLong(index++));
builder.setFromUser(resultSet.getString(index++));
WFCMessage.Conversation.Builder cb = WFCMessage.Conversation.newBuilder();
cb.setType(resultSet.getInt(index++));
cb.setTarget(resultSet.getString(index++));
cb.setLine(resultSet.getInt(index++));
builder.setConversation(cb.build());
Blob blob = resultSet.getBlob(index++);
WFCMessage.MessageContent messageContent = WFCMessage.MessageContent.parseFrom(encryptMessageContent(toByteArray(blob.getBinaryStream()), false));
builder.setContent(messageContent);
builder.setServerTimestamp(resultSet.getTimestamp(index++).getTime());
WFCMessage.Message message = builder.build();
out.put(message.getMessageId(),new MessageBundle(message.getMessageId(), message.getFromUser(), null, message));
}
} catch (SQLException e) {
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} catch (IOException e) {
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} finally {
try {
if (resultSet!=null) {
resultSet.close();
}
} catch (SQLException e) {
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
}
}
}
} catch (SQLException e) {
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} finally {
DBUtil.closeDB(connection, null);
}
return out;
}
public byte[] toByteArray(InputStream input) throws IOException {
ByteArrayOutputStream output = new ByteArrayOutputStream();
byte[] buffer = new byte[4096];
int n = 0;
while (-1 != (n = input.read(buffer))) {
output.write(buffer, 0, n);
}
return output.toByteArray();
}
MessageBundle getMessage(long messageId) {
String sql = "select `_from`, `_type`, `_target`, `_line`, `_data`, `_dt` from " + MessageShardingUtil.getMessageTable(messageId) +" where _mid = ? order by `_dt` DESC limit 1";
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
try {
connection = DBUtil.getConnection();
statement = connection.prepareStatement(sql);
statement.setLong(1, messageId);
resultSet = statement.executeQuery();
if (resultSet.next()) {
WFCMessage.Message.Builder builder = WFCMessage.Message.newBuilder();
builder.setMessageId(messageId);
int index = 1;
builder.setFromUser(resultSet.getString(index++));
WFCMessage.Conversation.Builder cb = WFCMessage.Conversation.newBuilder();
cb.setType(resultSet.getInt(index++));
cb.setTarget(resultSet.getString(index++));
cb.setLine(resultSet.getInt(index++));
builder.setConversation(cb.build());
Blob blob = resultSet.getBlob(index++);
WFCMessage.MessageContent messageContent = WFCMessage.MessageContent.parseFrom(encryptMessageContent(toByteArray(blob.getBinaryStream()), false));
builder.setContent(messageContent);
builder.setServerTimestamp(resultSet.getTimestamp(index++).getTime());
WFCMessage.Message message = builder.build();
return new MessageBundle(messageId, message.getFromUser(), null, message);
}
} catch (SQLException | IOException e) {
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} finally {
DBUtil.closeDB(connection, statement, resultSet);
}
return null;
}
void deleteMessage(long messageId) {
String sql = "delete from " + MessageShardingUtil.getMessageTable(messageId) + " where _mid = ?";
Connection connection = null;
PreparedStatement statement = null;
try {
connection = DBUtil.getConnection();
statement = connection.prepareStatement(sql);
statement.setLong(1, messageId);
int count = statement.executeUpdate();
LOG.info("Update rows {}", count);
} catch (SQLException e) {
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} finally {
DBUtil.closeDB(connection, statement);
}
}
List<WFCMessage.Message> loadRemoteMessages(String user, WFCMessage.Conversation conversation, long beforeUid, int count, Collection<Integer> contentTypes, String channelOwner) {
List<WFCMessage.Message> messages = new ArrayList<>();
long[] before = new long[1];
before[0] = beforeUid;
boolean hasMore = loadRemoteMessagesFromTable(user, conversation, before, count, MessageShardingUtil.getMessageTable(beforeUid), messages, contentTypes, channelOwner);
while (messages.size() < count && hasMore) {
hasMore = loadRemoteMessagesFromTable(user, conversation, before, count - messages.size(), MessageShardingUtil.getMessageTable(beforeUid), messages, contentTypes, channelOwner);
}
int month = 0;
while (messages.size() < count && !DBUtil.IsEmbedDB && month++ < 24) {
String nexTable = MessageShardingUtil.getMessageTable(beforeUid, -month);
int size = messages.size();
hasMore = true;
while (size == messages.size() && hasMore) {
hasMore = loadRemoteMessagesFromTable(user, conversation, before, count - messages.size(), nexTable, messages, contentTypes, channelOwner);
}
if (size < messages.size()) {
break;
}
}
return messages;
}
boolean loadRemoteMessagesFromTable(String user, WFCMessage.Conversation conversation, long[] before, int count, String table, List<WFCMessage.Message> messages, Collection<Integer> contentTypes, String channelOwner) {
long beforeUid = before[0];
String sql = "select `_mid`, `_from`, `_type`, `_target`, `_line`, `_data`, `_dt`, `_to` from " + table +" where";
if (conversation.getType() == ProtoConstants.ConversationType.ConversationType_Private) {
sql += " _type = ? and _line = ? and _mid < ? and ((_target = ? and _from = ?) or (_target = ? and _from = ?)) and (_to = '' or _to = ?)";
} else if (conversation.getType() == ProtoConstants.ConversationType.ConversationType_Channel && !user.equals(channelOwner)) {
sql += " _type = ? and _line = ? and _mid < ? and _target = ? and ((_from = ? and (_to = '' or _to = ?)) or (_from = ?))";
} else {
sql += " _type = ? and _line = ? and _mid < ? and _target = ?";
}
if(contentTypes != null && !contentTypes.isEmpty()) {
sql += " and _content_type in (";
boolean first = true;
for (int i:contentTypes) {
if(first) {
first = false;
} else {
sql += ",";
}
sql += i;
}
sql += ")";
}
sql += " order by `_mid` DESC limit ?";
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
try {
connection = DBUtil.getConnection();
statement = connection.prepareStatement(sql);
int index = 1;
statement.setInt(index++, conversation.getType());
statement.setInt(index++, conversation.getLine());
statement.setLong(index++, beforeUid);
statement.setString(index++, conversation.getTarget());
if (conversation.getType() == ProtoConstants.ConversationType.ConversationType_Private) {
statement.setString(index++, user);
statement.setString(index++, user);
statement.setString(index++, conversation.getTarget());
statement.setString(index++, user);
} else if (conversation.getType() == ProtoConstants.ConversationType.ConversationType_Channel && !user.equals(channelOwner)) {
statement.setString(index++, channelOwner);
statement.setString(index++, user);
statement.setString(index++, user);
}
statement.setInt(index++, count);
resultSet = statement.executeQuery();
while (resultSet.next()) {
count--;
WFCMessage.Message.Builder builder = WFCMessage.Message.newBuilder();
index = 1;
builder.setMessageId(resultSet.getLong(index++));
before[0] = builder.getMessageId();
builder.setFromUser(resultSet.getString(index++));
WFCMessage.Conversation.Builder cb = WFCMessage.Conversation.newBuilder();
cb.setType(resultSet.getInt(index++));
cb.setTarget(resultSet.getString(index++));
cb.setLine(resultSet.getInt(index++));
builder.setConversation(cb.build());
Blob blob = resultSet.getBlob(index++);
WFCMessage.MessageContent messageContent = WFCMessage.MessageContent.parseFrom(encryptMessageContent(toByteArray(blob.getBinaryStream()), false));
builder.setContent(messageContent);
builder.setServerTimestamp(resultSet.getTimestamp(index++).getTime());
String to = resultSet.getString(index++);
if (!StringUtil.isNullOrEmpty(to)) {
if (to.equals(user) || builder.getFromUser().equals(user)) {
builder.setToUser(to);
} else {
continue;
}
}
WFCMessage.Message message = builder.build();
boolean expired = false;
if (message.getContent().getExpireDuration() > 0) {
if (System.currentTimeMillis() > message.getServerTimestamp() + message.getContent().getExpireDuration()*1000) {
expired = true;
}
}
if (!expired) {
boolean duplicated = false;
for (WFCMessage.Message msg : messages) {
if(message.getMessageId() == msg.getMessageId()) {
duplicated = true;
break;
}
}
if(!duplicated) {
messages.add(message);
}
}
}
if (count == 0) {
return true;
}
} catch (SQLException | IOException e) {
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} finally {
DBUtil.closeDB(connection, statement, resultSet);
}
return false;
}
void persistUserMessage(final String userId, final String sender, final long messageId, final long messageSeq, int type, String target, int line, boolean directing, final int messageContentType) {
mScheduler.execute(()->{
Connection connection = null;
PreparedStatement statement = null;
try {
connection = DBUtil.getConnection();
String sql = "insert into " + getUserMessageTable(userId) + " (`_mid`, `_uid`, `_seq`, `_type`, `_target`, `_line`, `_directing`, `_cont_type`) values(?, ?, ?, ?, ?, ?, ?, ?)";
statement = connection.prepareStatement(sql);
int index = 1;
statement.setLong(index++, messageId);
statement.setString(index++, userId);
statement.setLong(index++, messageSeq);
statement.setInt(index++, type);
statement.setString(index++, getPrivateChatUserMessageTarget(type, target, sender));
statement.setInt(index++, line);
statement.setInt(index++, directing ? 1 :0);
statement.setInt(index++, messageContentType);
int count = statement.executeUpdate();
LOG.info("Update rows {}", count);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} finally {
DBUtil.closeDB(connection, statement);
}
});
}
String getPrivateChatUserMessageTarget(int conversationType, String target, String userId) {
if(conversationType == ProtoConstants.ConversationType.ConversationType_Private) {
return target.compareTo(userId) > 0 ? (userId + "|" + target) : (target + "|" + userId);
} else {
return target;
}
}
void clearUserMessage(final String userId) {
mScheduler.execute(()->{
String tableName = getUserMessageTable(userId);
Connection connection = null;
PreparedStatement statement = null;
try {
connection = DBUtil.getConnection();
String sql = "delete from " + tableName + " where _uid = ?";
statement = connection.prepareStatement(sql);
statement.setString(1, userId);
int count = statement.executeUpdate();
LOG.info("Update rows {}", count);
try {
if (statement!=null) {
statement.close();
}
statement = null;
} catch (SQLException e) {
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} finally {
DBUtil.closeDB(connection, statement);
}
});
}
void removeFavGroup(final String groupId, final List<String> memberIds) {
Connection connection = null;
PreparedStatement statement = null;
try {
connection = DBUtil.getConnection();
StringBuilder sb = new StringBuilder("update t_user_setting set _value = ?, _dt = ? where _uid in (");
for (int i = 0; i < memberIds.size(); i++) {
sb.append("?");
if (i != memberIds.size() - 1) {
sb.append(",");
}
}
sb.append(")");
sb.append(" and _scope in (5,6,26) and _key = ?");
statement = connection.prepareStatement(sb.toString());
int index = 1;
statement.setString(index++, "0");
statement.setLong(index++, System.currentTimeMillis());
for (int i = 0; i < memberIds.size(); i++) {
statement.setString(index++, memberIds.get(i));
}
statement.setString(index++, groupId);
int count = statement.executeUpdate();
LOG.info("Update rows {}", count);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} finally {
DBUtil.closeDB(connection, statement);
}
}
void persistUserSetting(final String userId, WFCMessage.UserSettingEntry entry) {
mScheduler.execute(()->{
Connection connection = null;
PreparedStatement statement = null;
try {
connection = DBUtil.getConnection();
String sql = "insert into t_user_setting (`_uid`" +
", `_scope`" +
", `_key`" +
", `_value`" +
", `_dt`) values(?, ?, ?, ?, ?)" +
" ON DUPLICATE KEY UPDATE " +
"`_value` = ?," +
"`_dt` = ?";
statement = connection.prepareStatement(sql);
int index = 1;
statement.setString(index++, userId);
statement.setInt(index++, entry.getScope());
statement.setString(index++, entry.getKey());
statement.setString(index++, entry.getValue());
statement.setLong(index++, entry.getUpdateDt());
statement.setString(index++, entry.getValue());
statement.setLong(index++, entry.getUpdateDt());
int count = statement.executeUpdate();
LOG.info("Update rows {}", count);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} finally {
DBUtil.closeDB(connection, statement);
}
});
}
void clearUserSetting(final String userId) {
mScheduler.execute(()->{
Connection connection = null;
PreparedStatement statement = null;
try {
connection = DBUtil.getConnection();
String sql = "delete from t_user_setting where `_uid` = ?";
statement = connection.prepareStatement(sql);
int index = 1;
statement.setString(index++, userId);
int count = statement.executeUpdate();
LOG.info("Update rows {}", count);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} finally {
DBUtil.closeDB(connection, statement);
}
});
}
List<WFCMessage.UserSettingEntry> getPersistUserSetting(final String userId) {
Connection connection = null;
PreparedStatement statement = null;
ResultSet rs = null;
try {
connection = DBUtil.getConnection();
String sql = "select " +
"`_scope`" +
", `_key`" +
", `_value`" +
", `_dt`" +
" from t_user_setting where `_uid` = ?";
statement = connection.prepareStatement(sql);
int index = 1;
statement.setString(index++, userId);
rs = statement.executeQuery();
List<WFCMessage.UserSettingEntry> out = new ArrayList<>();
while (rs.next()) {
WFCMessage.UserSettingEntry.Builder builder = WFCMessage.UserSettingEntry.newBuilder();
index = 1;
int intvalue = rs.getInt(index++);
builder.setScope(intvalue);
String value = rs.getString(index++);
value = (value == null ? "" : value);
builder.setKey(value);
value = rs.getString(index++);
value = (value == null ? "" : value);
builder.setValue(value);
long longValue = rs.getLong(index++);
builder.setUpdateDt(longValue);
out.add(builder.build());
}
if (out.isEmpty()) {
WFCMessage.UserSettingEntry.Builder builder = WFCMessage.UserSettingEntry.newBuilder().setScope(999).setKey("").setValue("").setUpdateDt(0);
out.add(builder.build());
}
return out;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} finally {
DBUtil.closeDB(connection, statement, rs);
}
return null;
}
void removeGroupUserSettings(String groupId, List<String> users) {
Connection connection = null;
PreparedStatement statement = null;
try {
connection = DBUtil.getConnection();
StringBuilder sb = new StringBuilder("delete from t_user_setting where _scope in (1,3,5,6,7,19) and _uid in (");
for (int i = 0; i < users.size(); i++) {
sb.append("?");
if (i != users.size() - 1) {
sb.append(",");
}
}
sb.append(") and _key like ?");
statement = connection.prepareStatement(sb.toString());
int index = 1;
for (String userId:users) {
statement.setString(index++, userId);
}
statement.setString(index++, "1-_-" + groupId);
int count = statement.executeUpdate();
LOG.info("Update rows {}", count);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} finally {
DBUtil.closeDB(connection, statement);
}
}
void persistGroupInfo(final WFCMessage.GroupInfo groupInfo) {
Connection connection = null;
PreparedStatement statement = null;
try {
connection = DBUtil.getConnection();
String sql = "insert into t_group (`_gid`" +
", `_name`" +
", `_portrait`" +
", `_owner`" +
", `_type`" +
", `_extra`" +
", `_dt`" +
", `_member_count`" +
", `_mute`" +
", `_join_type`" +
", `_private_chat`" +
", `_searchable`" +
", `_deleted`" +
", `_member_dt`) values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)" +
" ON DUPLICATE KEY UPDATE " +
"`_name` = ?," +
"`_portrait` = ?," +
"`_owner` = ?," +
"`_type` = ?," +
"`_extra` = ?," +
"`_dt` = ?," +
"`_mute` = ?" +
", `_join_type` = ?" +
", `_private_chat` = ?" +
", `_searchable` = ?" +
", `_deleted` = ?" +
", `_member_dt` = ?";
statement = connection.prepareStatement(sql);
int index = 1;
statement.setString(index++, groupInfo.getTargetId());
statement.setString(index++, groupInfo.getName());
statement.setString(index++, groupInfo.getPortrait());
statement.setString(index++, groupInfo.getOwner());
statement.setInt(index++, groupInfo.getType());
statement.setString(index++, groupInfo.getExtra());
statement.setLong(index++, groupInfo.getUpdateDt() == 0 ? System.currentTimeMillis() : groupInfo.getUpdateDt());
statement.setInt(index++, groupInfo.getMemberCount());
statement.setInt(index++, groupInfo.getMute());
statement.setInt(index++, groupInfo.getJoinType());
statement.setInt(index++, groupInfo.getPrivateChat());
statement.setInt(index++, groupInfo.getSearchable());
statement.setInt(index++, groupInfo.getDeleted());
statement.setLong(index++, groupInfo.getMemberUpdateDt() == 0 ? System.currentTimeMillis() : groupInfo.getUpdateDt());
statement.setString(index++, groupInfo.getName());
statement.setString(index++, groupInfo.getPortrait());
statement.setString(index++, groupInfo.getOwner());
statement.setInt(index++, groupInfo.getType());
statement.setString(index++, groupInfo.getExtra());
statement.setLong(index++, groupInfo.getUpdateDt() == 0 ? System.currentTimeMillis() : groupInfo.getUpdateDt());
statement.setInt(index++, groupInfo.getMute());
statement.setInt(index++, groupInfo.getJoinType());
statement.setInt(index++, groupInfo.getPrivateChat());
statement.setInt(index++, groupInfo.getSearchable());
statement.setInt(index++, groupInfo.getDeleted());
statement.setLong(index++, groupInfo.getMemberUpdateDt() == 0 ? System.currentTimeMillis() : groupInfo.getUpdateDt());
int count = statement.executeUpdate();
LOG.info("Update rows {}", count);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} finally {
DBUtil.closeDB(connection, statement);
}
}
List<MemorySessionStore.Session> getUserActivedSessions(String uid) {
String sql = "select `_cid`, `_package_name`,`_token`,`_voip_token`,`_secret`,`_db_secret`,`_platform`,`_push_type`,`_device_name`,`_device_version`,`_phone_name`,`_language`,`_carrier_name`, `_dt` from t_user_session where `_uid` = ? and `_deleted` = 0";
Connection connection = null;
PreparedStatement statement = null;
List<MemorySessionStore.Session> result = new ArrayList<>();
ResultSet resultSet = null;
try {
connection = DBUtil.getConnection();
statement = connection.prepareStatement(sql);
statement.setString(1, uid);
resultSet = statement.executeQuery();
while (resultSet.next()) {
int index = 1;
String cid = resultSet.getString(index++);
ClientSession clientSession = new ClientSession(cid, Server.getServer().getStore().sessionsStore());
MemorySessionStore.Session session = new MemorySessionStore.Session(uid, cid, clientSession);
session.setAppName(resultSet.getString(index++));
session.setDeviceToken(resultSet.getString(index++));
session.setVoipDeviceToken(resultSet.getString(index++));
session.setSecret(resultSet.getString(index++));
session.setDbSecret(resultSet.getString(index++));
session.setPlatform(resultSet.getInt(index++));
session.setPushType(resultSet.getInt(index++));
session.setDeviceName(resultSet.getString(index++));
session.setDeviceVersion(resultSet.getString(index++));
session.setPhoneName(resultSet.getString(index++));
session.setLanguage(resultSet.getString(index++));
session.setCarrierName(resultSet.getString(index++));
session.setUpdateDt(resultSet.getLong(index++));
result.add(session);
}
} catch (SQLException e) {
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} finally {
DBUtil.closeDB(connection, statement, resultSet);
}
return result;
}
void clearUserSessions(String uid) {
Connection connection = null;
PreparedStatement statement = null;
try {
connection = DBUtil.getConnection();
String sql = "delete from t_user_session where `_uid`=?";
statement = connection.prepareStatement(sql);
int index = 1;
statement.setString(index++, uid);
int count = statement.executeUpdate();
LOG.info("Update rows {}", count);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} finally {
DBUtil.closeDB(connection, statement);
}
}
MemorySessionStore.Session getSession(String uid, String clientId, ClientSession clientSession) {
String sql = "select `_package_name`,`_token`,`_voip_token`,`_secret`,`_db_secret`,`_platform`,`_push_type`,`_device_name`,`_device_version`,`_phone_name`,`_language`,`_carrier_name`, `_dt`, `_deleted` from t_user_session where `_uid` = ? and `_cid` = ? limit 1";
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
try {
connection = DBUtil.getConnection();
statement = connection.prepareStatement(sql);
statement.setString(1, uid);
statement.setString(2, clientId);
resultSet = statement.executeQuery();
if (resultSet.next()) {
MemorySessionStore.Session session = new MemorySessionStore.Session(uid, clientId, clientSession);
int index = 1;
session.setAppName(resultSet.getString(index++));
session.setDeviceToken(resultSet.getString(index++));
session.setVoipDeviceToken(resultSet.getString(index++));
session.setSecret(resultSet.getString(index++));
session.setDbSecret(resultSet.getString(index++));
session.setPlatform(resultSet.getInt(index++));
session.setPushType(resultSet.getInt(index++));
session.setDeviceName(resultSet.getString(index++));
session.setDeviceVersion(resultSet.getString(index++));
session.setPhoneName(resultSet.getString(index++));
session.setLanguage(resultSet.getString(index++));
session.setCarrierName(resultSet.getString(index++));
session.setUpdateDt(resultSet.getLong(index++));
session.setDeleted(resultSet.getInt(index));
return session;
}
} catch (SQLException e) {
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} finally {
DBUtil.closeDB(connection, statement, resultSet);
}
return null;
}
void clearOtherSessionToken(String cid, String token, int pushType, boolean voipToken) {
if (voipToken) {
return;
}
Connection connection = null;
PreparedStatement statement = null;
try {
connection = DBUtil.getConnection();
String sql = "update t_user_session set `_token` = ?, `_dt` = ? where `_token` = ? and `_push_type` = ? and `_cid` <> ?";
statement = connection.prepareStatement(sql);
int index = 1;
statement.setString(index++, "");
statement.setLong(index++, System.currentTimeMillis());
statement.setString(index++, token);
statement.setInt(index++, pushType);
statement.setString(index++, cid);
int c = statement.executeUpdate();
LOG.info("Update rows {}", c);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} finally {
DBUtil.closeDB(connection, statement);
}
}
void updateSessionToken(String uid, String cid, String token, int pushType, boolean voipToken) {
mScheduler.execute(()->{
Connection connection = null;
PreparedStatement statement = null;
clearOtherSessionToken(cid, token, pushType, voipToken);
try {
connection = DBUtil.getConnection();
String sql ;
if (voipToken) {
sql = "update t_user_session set `_voip_token` = ?, `_dt` = ? where `_uid` = ? and `_cid` = ?";
} else {
sql = "update t_user_session set `_token` = ?, `_push_type` = ?, `_dt` = ? where `_uid` = ? and `_cid` = ?";
}
statement = connection.prepareStatement(sql);
int index = 1;
statement.setString(index++, token);
if (!voipToken) {
statement.setInt(index++, pushType);
}
statement.setLong(index++, System.currentTimeMillis());
statement.setString(index++, uid);
statement.setString(index++, cid);
int c = statement.executeUpdate();
LOG.info("Update rows {}", c);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} finally {
DBUtil.closeDB(connection, statement);
}
});
}
void updateSessionDeleted(String uid, String cid, int deleted) {
Connection connection = null;
PreparedStatement statement = null;
try {
connection = DBUtil.getConnection();
String sql = "update t_user_session set `_deleted` = ? where `_uid` = ? and `_cid` = ?";
statement = connection.prepareStatement(sql);
int index = 1;
statement.setInt(index++, deleted);
statement.setString(index++, uid);
statement.setString(index++, cid);
int c = statement.executeUpdate();
LOG.info("Update rows {}", c);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} finally {
DBUtil.closeDB(connection, statement);
}
}
void updateSessionPlatform(String uid, String cid, int platform) {
Connection connection = null;
PreparedStatement statement = null;
try {
connection = DBUtil.getConnection();
String sql = "update t_user_session set `_platform` = ? where `_uid` = ? and `_cid` = ?";
statement = connection.prepareStatement(sql);
int index = 1;
statement.setInt(index++, platform);
statement.setString(index++, uid);
statement.setString(index++, cid);
int c = statement.executeUpdate();
LOG.info("Update rows {}", c);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} finally {
DBUtil.closeDB(connection, statement);
}
}
private boolean strEqual(String left, String right) {
if (left == right)
return true;
if (left == null)
return false;
return left.equals(right);
}
void updateSession(String uid, String cid, MemorySessionStore.Session session, WFCMessage.RouteRequest request) {
mScheduler.execute(()->{
Connection connection = null;
PreparedStatement statement = null;
try {
connection = DBUtil.getConnection();
String sql = "update t_user_session set";
if (!strEqual(session.getAppName(), request.getApp())) {
sql += " `_package_name` = ?,";
}
if (!strEqual(session.getDeviceName(), request.getDeviceName())) {
sql += " `_device_name` = ?,";
}
if (!strEqual(session.getDeviceVersion(), request.getDeviceVersion())) {
sql += " `_device_version` = ?,";
}
if (!strEqual(session.getPhoneName(), request.getPhoneName())) {
sql += " `_phone_name` = ?,";
}
if (!strEqual(session.getLanguage(), request.getLanguage())) {
sql += " `_language` = ?,";
}
if (!strEqual(session.getCarrierName(), request.getCarrierName())) {
sql += " `_carrier_name` = ?,";
}
sql += " `_dt` = ?";
sql += " where `_uid` = ? and `_cid` = ?";
statement = connection.prepareStatement(sql);
int index = 1;
if (!strEqual(session.getAppName(), request.getApp())) {
statement.setString(index++, request.getApp());
}
if (!strEqual(session.getDeviceName(), request.getDeviceName())) {
statement.setString(index++, request.getDeviceName());
}
if (!strEqual(session.getDeviceVersion(), request.getDeviceVersion())) {
statement.setString(index++, request.getDeviceVersion());
}
if (!strEqual(session.getPhoneName(), request.getPhoneName())) {
statement.setString(index++, request.getPhoneName());
}
if (!strEqual(session.getLanguage(), request.getLanguage())) {
statement.setString(index++, request.getLanguage());
}
if (!strEqual(session.getCarrierName(), request.getCarrierName())) {
statement.setString(index++, request.getCarrierName());
}
statement.setLong(index++, System.currentTimeMillis());
statement.setString(index++, uid);
statement.setString(index++, cid);
int c = statement.executeUpdate();
LOG.info("Update rows {}", c);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} finally {
DBUtil.closeDB(connection, statement);
}
});
}
void clearMultiEndpoint(String uid, String clientId, int platform) {
LOG.info("clearMultiEndpoint {}, {}", uid, clientId);
Connection connection = null;
PreparedStatement statement = null;
try {
connection = DBUtil.getConnection();
String sql;
if (platform == ProtoConstants.Platform.Platform_Windows || platform == ProtoConstants.Platform.Platform_OSX || platform == ProtoConstants.Platform.Platform_LINUX || platform == ProtoConstants.Platform.Platform_HarmonyPC) {
sql = "update t_user_session set `_deleted` = ?, `_token` = ?, `_voip_token` = ?, `_dt` = ? where `_uid`=? and (`_platform` = ? or `_platform` = ? or `_platform` = ? or `_platform` = ?) and `_cid` <> ? and `_deleted` = 0";
} else if(platform == ProtoConstants.Platform.Platform_iOS || platform == ProtoConstants.Platform.Platform_Android || platform == ProtoConstants.Platform.Platform_Harmony) {
sql = "update t_user_session set `_deleted` = ?, `_token` = ?, `_voip_token` = ?, `_dt` = ? where `_uid`=? and (`_platform` = ? or `_platform` = ? or `_platform` = ?) and `_cid` <> ? and `_deleted` = 0";
} else if(platform == ProtoConstants.Platform.Platform_iPad || platform == ProtoConstants.Platform.Platform_APad || platform == ProtoConstants.Platform.Platform_HarmonyPad) {
sql = "update t_user_session set `_deleted` = ?, `_token` = ?, `_voip_token` = ?, `_dt` = ? where `_uid`=? and (`_platform` = ? or `_platform` = ? or `_platform` = ?) and `_cid` <> ? and `_deleted` = 0";
} else {
sql = "update t_user_session set `_deleted` = ?, `_token` = ?, `_voip_token` = ?, `_dt` = ? where `_uid`=? and `_platform` = ? and `_cid` <> ? and `_deleted` = 0";
}
statement = connection.prepareStatement(sql);
int index = 1;
statement.setInt(index++, 1);
statement.setString(index++, "");
statement.setString(index++, "");
statement.setLong(index++, System.currentTimeMillis());
statement.setString(index++, uid);
if (platform == ProtoConstants.Platform.Platform_Windows || platform == ProtoConstants.Platform.Platform_OSX || platform == ProtoConstants.Platform.Platform_LINUX || platform == ProtoConstants.Platform.Platform_HarmonyPC) {
statement.setInt(index++, ProtoConstants.Platform.Platform_Windows);
statement.setInt(index++, ProtoConstants.Platform.Platform_OSX);
statement.setInt(index++, ProtoConstants.Platform.Platform_LINUX);
statement.setInt(index++, ProtoConstants.Platform.Platform_HarmonyPC);
} else if(platform == ProtoConstants.Platform.Platform_iOS || platform == ProtoConstants.Platform.Platform_Android || platform == ProtoConstants.Platform.Platform_Harmony) {
statement.setInt(index++, ProtoConstants.Platform.Platform_iOS);
statement.setInt(index++, ProtoConstants.Platform.Platform_Android);
statement.setInt(index++, ProtoConstants.Platform.Platform_Harmony);
} else if(platform == ProtoConstants.Platform.Platform_iPad || platform == ProtoConstants.Platform.Platform_APad || platform == ProtoConstants.Platform.Platform_HarmonyPad) {
statement.setInt(index++, ProtoConstants.Platform.Platform_iPad);
statement.setInt(index++, ProtoConstants.Platform.Platform_APad);
statement.setInt(index++, ProtoConstants.Platform.Platform_HarmonyPad);
} else {
statement.setInt(index++, platform);
}
statement.setString(index++, clientId);
int count = statement.executeUpdate();
LOG.info("Update rows {}", count);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} finally {
DBUtil.closeDB(connection, statement);
}
}
void clearMultiUser(String uid, String clientId) {
long start = System.currentTimeMillis();
LOG.info("clearMultiUser {}, {}", uid, clientId);
Connection connection = null;
PreparedStatement statement = null;
try {
connection = DBUtil.getConnection();
String sql = "update t_user_session set _deleted = ?, _token = ?, _voip_token = ?, _dt = ? where _cid = ? and _uid <> ? and _deleted = 0";
statement = connection.prepareStatement(sql);
int index = 1;
statement.setInt(index++, 1);
statement.setString(index++, "");
statement.setString(index++, "");
statement.setLong(index++, System.currentTimeMillis());
statement.setString(index++, clientId);
statement.setString(index++, uid);
int count = statement.executeUpdate();
LOG.info("Update rows {}", count);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} finally {
DBUtil.closeDB(connection, statement);
}
}
MemorySessionStore.Session createSession(String uid, String clientId, ClientSession clientSession, int platform) {
Connection connection = null;
PreparedStatement statement = null;
LOG.info("Database create session {},{}", uid, clientId);
try {
connection = DBUtil.getConnection();
String sql = "insert into t_user_session (`_uid`,`_cid`,`_platform`,`_secret`,`_db_secret`, `_dt`) values (?,?,?,?,?,?)";
statement = connection.prepareStatement(sql);
int index = 1;
MemorySessionStore.Session session = new MemorySessionStore.Session(uid, clientId, clientSession);
session.setPlatform(platform);
statement.setString(index++, uid);
statement.setString(index++, clientId);
statement.setInt(index++, platform);
session.setSecret(UUID.randomUUID().toString());
statement.setString(index++, session.getSecret());
session.setDbSecret(UUID.randomUUID().toString());
statement.setString(index++, session.getDbSecret());
statement.setLong(index++, System.currentTimeMillis());
int count = statement.executeUpdate();
LOG.info("Update rows {}", count);
return session;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} finally {
DBUtil.closeDB(connection, statement);
}
return null;
}
Set<String> getUserGroupIds(String userId) {
Connection connection = null;
PreparedStatement statement = null;
ResultSet rs = null;
try {
connection = DBUtil.getConnection();
String sql = "select `_gid` from t_group_member where `_mid` = ? and `_type` <> 4";
statement = connection.prepareStatement(sql);
statement.setString(1, userId);
rs = statement.executeQuery();
Set<String> out = new HashSet<>();
while (rs.next()) {
String uid = rs.getString(1);
out.add(uid);
}
return out;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} finally {
DBUtil.closeDB(connection, statement, rs);
}
return null;
}
Set<String> getUserGroupIds(String userId, List<Integer> memberTypes) {
Connection connection = null;
PreparedStatement statement = null;
ResultSet rs = null;
try {
connection = DBUtil.getConnection();
String sql = "select _gid from t_group_member where _mid = ? ";
if(!memberTypes.isEmpty()) {
sql += " and _type in (";
for (int i = 0; i < memberTypes.size(); i++) {
if(i > 0) {
sql += ",";
}
sql += memberTypes.get(i);
}
sql += ")";
}
statement = connection.prepareStatement(sql);
statement.setString(1, userId);
rs = statement.executeQuery();
Set<String> out = new HashSet<>();
while (rs.next()) {
String uid = rs.getString(1);
out.add(uid);
}
return out;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} finally {
DBUtil.closeDB(connection, statement, rs);
}
return null;
}
Set<String> getCommonGroupIds(String userId1, String userId2) {
Connection connection = null;
PreparedStatement statement = null;
ResultSet rs = null;
try {
connection = DBUtil.getConnection();
String sql = "select t1._gid from t_group_member t1, t_group_member t2 where t1._mid = ? and t2._mid = ? and t1._type <> 4 and t2._type <> 4 and t1._gid = t2._gid";
statement = connection.prepareStatement(sql);
statement.setString(1, userId1);
statement.setString(2, userId2);
rs = statement.executeQuery();
Set<String> out = new HashSet<>();
while (rs.next()) {
String uid = rs.getString(1);
out.add(uid);
}
return out;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} finally {
DBUtil.closeDB(connection, statement, rs);
}
return null;
}
void updateGroupMemberDt(final String groupId, final long dt) {
Connection connection = null;
PreparedStatement statement = null;
try {
connection = DBUtil.getConnection();
String sql = "update t_group set `_member_dt` = ?, `_dt` = ? where `_gid` = ?";
statement = connection.prepareStatement(sql);
int index = 1;
statement.setLong(index++, dt);
statement.setLong(index++, dt);
statement.setString(index++, groupId);
int c = statement.executeUpdate();
LOG.info("Update rows {}", c);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} finally {
DBUtil.closeDB(connection, statement);
}
}
void updateGroupMemberCountDt(final String groupId) {
Connection connection = null;
PreparedStatement statement = null;
try {
connection = DBUtil.getConnection();
String sql = "update t_group set `_member_count` = (select count(*) from t_group_member where `_gid` = ? and `_type` <> 4 limit 1), `_dt` = ?, `_member_dt` = `_member_dt` + 1 where `_gid` = ?";
statement = connection.prepareStatement(sql);
statement.setString(1, groupId);
statement.setLong(2, System.currentTimeMillis());
statement.setString(3, groupId);
int c = statement.executeUpdate();
LOG.info("Update rows {}", c);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} finally {
DBUtil.closeDB(connection, statement);
}
}
void persistGroupMember(final String groupId, final List<WFCMessage.GroupMember> memberList, boolean updateCreateTime) {
Connection connection = null;
PreparedStatement statement = null;
try {
connection = DBUtil.getConnection();
connection.setAutoCommit(false);
String sql = "insert into t_group_member (`_gid`" +
", `_mid`" +
", `_alias`" +
", `_type`" +
", `_dt`, `_create_dt`, `_extra`) values(?, ?, ?, ?, ?, ?,?)" +
" ON DUPLICATE KEY UPDATE " +
"`_alias` = ?," +
"`_type` = ?," +
"`_dt` = ?," +
"`_extra` = ?";
if(updateCreateTime) {
sql += ", `_create_dt` = ?";
}
statement = connection.prepareStatement(sql);
for (WFCMessage.GroupMember member : memberList
) {
int index = 1;
long dt = System.currentTimeMillis();
if (member.getUpdateDt() > 0) {
dt = member.getUpdateDt();
}
statement.setString(index++, groupId);
statement.setString(index++, member.getMemberId());
statement.setString(index++, member.getAlias());
statement.setInt(index++, member.getType());
statement.setLong(index++, dt);
statement.setLong(index++, member.getCreateDt());
statement.setString(index++, member.getExtra());
statement.setString(index++, member.getAlias());
statement.setInt(index++, member.getType());
statement.setLong(index++, dt);
statement.setString(index++, member.getExtra());
if(updateCreateTime) {
statement.setLong(index++, dt);
}
int count = statement.executeUpdate();
LOG.info("Update rows {}", count);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} finally {
if (connection != null) {
try {
connection.commit();
connection.setAutoCommit(true);
} catch (SQLException e1) {
e1.printStackTrace();
}
}
DBUtil.closeDB(connection, statement);
}
}
int removeGroupMember(String groupId, List<String> groupMembers) {
Connection connection = null;
PreparedStatement statement = null;
try {
connection = DBUtil.getConnection();
StringBuilder sqlBuilder = new StringBuilder("update t_group_member set `_type` = ?, `_dt` = ? where `_mid` in (");
for (int i = 0; i < groupMembers.size(); i++) {
sqlBuilder.append("?");
if (i != groupMembers.size()-1) {
sqlBuilder.append(",");
}
}
sqlBuilder.append(")");
sqlBuilder.append(" and _gid = ?");
statement = connection.prepareStatement(sqlBuilder.toString());
int index = 1;
long current = System.currentTimeMillis();
statement.setInt(index++, ProtoConstants.GroupMemberType.GroupMemberType_Removed);
statement.setLong(index++, current);
for (String memberId:groupMembers) {
statement.setString(index++, memberId);
}
statement.setString(index++, groupId);
int count = statement.executeUpdate();
LOG.info("Update rows {}", count);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} finally {
DBUtil.closeDB(connection, statement);
}
return 0;
}
WFCMessage.GroupInfo getPersistGroupInfo(String groupId) {
Connection connection = null;
PreparedStatement statement = null;
ResultSet rs = null;
try {
connection = DBUtil.getConnection();
String sql = "select `_name`" +
", `_portrait`" +
", `_owner`" +
", `_type`" +
", `_extra`" +
", `_dt`" +
", `_member_count`" +
", `_member_dt`" +
", `_mute`" +
", `_join_type`" +
", `_private_chat`" +
", `_searchable`" +
", _deleted" +
" from t_group where `_gid` = ?";
statement = connection.prepareStatement(sql);
int index = 1;
statement.setString(index++, groupId);
rs = statement.executeQuery();
if (rs.next()) {
String strValue;
int intValue;
WFCMessage.GroupInfo.Builder builder = WFCMessage.GroupInfo.newBuilder();
index = 1;
builder.setTargetId(groupId);
strValue = rs.getString(index++);
strValue = (strValue == null ? "" : strValue);
builder.setName(strValue);
strValue = rs.getString(index++);
strValue = (strValue == null ? "" : strValue);
builder.setPortrait(strValue);
strValue = rs.getString(index++);
strValue = (strValue == null ? "" : strValue);
builder.setOwner(strValue);
intValue = rs.getInt(index++);
builder.setType(intValue);
strValue = rs.getString(index++);
strValue = (strValue == null ? "" : strValue);
builder.setExtra(strValue);
long longValue = rs.getLong(index++);
builder.setUpdateDt(longValue);
intValue = rs.getInt(index++);
builder.setMemberCount(intValue);
longValue = rs.getLong(index++);
builder.setMemberUpdateDt(longValue);
intValue = rs.getInt(index++);
builder.setMute(intValue);
intValue = rs.getInt(index++);
builder.setJoinType(intValue);
intValue = rs.getInt(index++);
builder.setPrivateChat(intValue);
intValue = rs.getInt(index++);
builder.setSearchable(intValue);
intValue = rs.getInt(index++);
builder.setDeleted(intValue);
return builder.build();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} finally {
DBUtil.closeDB(connection, statement, rs);
}
return null;
}
void updateChatroomInfo(String chatroomId, WFCMessage.ChatroomInfo chatroomInfo) {
LOG.info("Database update chatroom info {}", chatroomId);
mScheduler.execute(()->{
Connection connection = null;
PreparedStatement statement = null;
try {
connection = DBUtil.getConnection();
String sql = "insert into t_chatroom (`_cid`" +
", `_title`" +
", `_portrait`" +
", `_state`" +
", `_desc`" +
", `_extra`" +
", `_dt`) values(?, ?, ?, ?, ?, ?, ?)" +
" ON DUPLICATE KEY UPDATE `_title`=?" +
", `_portrait`=?" +
", `_state`=?" +
", `_desc`=?" +
", `_extra`=?" +
", `_dt`=?";
statement = connection.prepareStatement(sql);
int index = 1;
statement.setString(index++, chatroomId);
statement.setString(index++, chatroomInfo.getTitle());
statement.setString(index++, chatroomInfo.getPortrait());
statement.setInt(index++, chatroomInfo.getState());
statement.setString(index++, chatroomInfo.getDesc());
statement.setString(index++, chatroomInfo.getExtra());
statement.setLong(index++, chatroomInfo.getUpdateDt() == 0 ? System.currentTimeMillis() : chatroomInfo.getUpdateDt());
statement.setString(index++, chatroomInfo.getTitle());
statement.setString(index++, chatroomInfo.getPortrait());
statement.setInt(index++, chatroomInfo.getState());
statement.setString(index++, chatroomInfo.getDesc());
statement.setString(index++, chatroomInfo.getExtra());
statement.setLong(index++, chatroomInfo.getUpdateDt() == 0 ? System.currentTimeMillis() : chatroomInfo.getUpdateDt());
int count = statement.executeUpdate();
LOG.info("Update rows {}", count);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} finally {
DBUtil.closeDB(connection, statement);
}
});
}
void removeChatroomInfo(String chatroomId) {
LOG.info("Database remove chatroom {}", chatroomId);
mScheduler.execute(()->{
Connection connection = null;
PreparedStatement statement = null;
try {
connection = DBUtil.getConnection();
String sql = "delete from t_chatroom where `_cid`=?";
statement = connection.prepareStatement(sql);
int index = 1;
statement.setString(index++, chatroomId);
int count = statement.executeUpdate();
LOG.info("Update rows {}", count);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} finally {
DBUtil.closeDB(connection, statement);
}
});
}
WFCMessage.ChatroomInfo getPersistChatroomInfo(String chatroomId) {
Connection connection = null;
PreparedStatement statement = null;
ResultSet rs = null;
try {
connection = DBUtil.getConnection();
String sql = "select `_title`" +
", `_portrait`" +
", `_state`" +
", `_desc`" +
", `_extra`" +
", `_dt`" +
" from t_chatroom where `_cid` = ?";
statement = connection.prepareStatement(sql);
int index = 1;
statement.setString(index++, chatroomId);
rs = statement.executeQuery();
if (rs.next()) {
String strValue;
int intValue;
WFCMessage.ChatroomInfo.Builder builder = WFCMessage.ChatroomInfo.newBuilder();
index = 1;
strValue = rs.getString(index++);
strValue = (strValue == null ? "" : strValue);
builder.setTitle(strValue);
strValue = rs.getString(index++);
strValue = (strValue == null ? "" : strValue);
builder.setPortrait(strValue);
intValue = rs.getInt(index++);
builder.setState(intValue);
strValue = rs.getString(index++);
strValue = (strValue == null ? "" : strValue);
builder.setDesc(strValue);
strValue = rs.getString(index++);
strValue = (strValue == null ? "" : strValue);
builder.setExtra(strValue);
long longValue = rs.getLong(index++);
builder.setUpdateDt(longValue);
builder.setCreateDt(longValue);
return builder.build();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} finally {
DBUtil.closeDB(connection, statement, rs);
}
return null;
}
void updateUserPassword(final String userId, final String password) {
mScheduler.execute(()->{
Connection connection = null;
PreparedStatement statement = null;
try {
connection = DBUtil.getConnection();
String sql = "update t_user set `_passwd_md5` = ? where `_uid` = ?";
statement = connection.prepareStatement(sql);
int index = 1;
try {
MessageDigest md5 = MessageDigest.getInstance("MD5");
String passwdMd5 = Base64.getEncoder().encodeToString(md5.digest(password.getBytes("utf-8")));
statement.setString(index, passwdMd5);
} catch (Exception e) {
statement.setString(index, "");
}
index++;
statement.setString(index++, userId);
int count = statement.executeUpdate();
LOG.info("Update rows {}", count);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} finally {
DBUtil.closeDB(connection, statement);
}
});
}
void deleteUser(final String userId) {
mScheduler.execute(()->{
Connection connection = null;
PreparedStatement statement = null;
try {
connection = DBUtil.getConnection();
String sql = "delete from t_user where `_uid`=?";
statement = connection.prepareStatement(sql);
int index = 1;
statement.setString(index++, userId);
int count = statement.executeUpdate();
LOG.info("Update rows {}", count);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} finally {
DBUtil.closeDB(connection, statement);
}
});
}
void deleteRobot(String robotId) {
mScheduler.execute(()->{
Connection connection = null;
PreparedStatement statement = null;
try {
connection = DBUtil.getConnection();
String sql = "delete from t_robot where `_uid`=?";
statement = connection.prepareStatement(sql);
statement.setString(1, robotId);
int count = statement.executeUpdate();
LOG.info("Update rows {}", count);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} finally {
DBUtil.closeDB(connection, statement);
}
});
}
WFCMessage.Robot getRobot(String robotId) {
Connection connection = null;
PreparedStatement statement = null;
ResultSet rs = null;
try {
connection = DBUtil.getConnection();
String sql = "select `_owner`" +
", `_secret`" +
", `_callback`" +
", `_state`" +
", `_extra`" +
", `_dt` from t_robot where `_uid` = ?";
statement = connection.prepareStatement(sql);
int index = 1;
statement.setString(1, robotId);
rs = statement.executeQuery();
if (rs.next()) {
WFCMessage.Robot.Builder builder = WFCMessage.Robot.newBuilder();
index = 1;
String value = rs.getString(index++);
value = (value == null ? "" : value);
builder.setUid(robotId);
builder.setOwner(value);
value = rs.getString(index++);
value = (value == null ? "" : value);
builder.setSecret(value);
value = rs.getString(index++);
value = (value == null ? "" : value);
builder.setCallback(value);
int state = rs.getInt(index++);
builder.setState(state);
value = rs.getString(index++);
value = (value == null ? "" : value);
builder.setExtra(value);
long longValue = rs.getLong(index++);
return builder.build();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} finally {
DBUtil.closeDB(connection, statement, rs);
}
return null;
}
void updateRobot(final WFCMessage.Robot robot) {
LOG.info("Database update user info {}", robot.getUid());
mScheduler.execute(()->{
Connection connection = null;
PreparedStatement statement = null;
try {
connection = DBUtil.getConnection();
String sql = "insert into t_robot (`_uid`" +
", `_owner`" +
", `_secret`" +
", `_callback`" +
", `_state`" +
", `_extra`" +
", `_dt`) values(?, ?, ?, ?, ?, ?, ?)" +
" ON DUPLICATE KEY UPDATE `_owner`=?" +
", `_secret`=?" +
", `_callback`=?" +
", `_state`=?" +
", `_extra`=?" +
", `_dt`=?";
statement = connection.prepareStatement(sql);
int index = 1;
statement.setString(index++, robot.getUid());
statement.setString(index++, robot.getOwner());
statement.setString(index++, robot.getSecret());
statement.setString(index++, robot.getCallback());
statement.setInt(index++, robot.getState());
statement.setString(index++, robot.getExtra());
statement.setLong(index++, System.currentTimeMillis());
statement.setString(index++, robot.getOwner());
statement.setString(index++, robot.getSecret());
statement.setString(index++, robot.getCallback());
statement.setInt(index++, robot.getState());
statement.setString(index++, robot.getExtra());
statement.setLong(index++, System.currentTimeMillis());
int count = statement.executeUpdate();
LOG.info("Update rows {}", count);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} finally {
DBUtil.closeDB(connection, statement);
}
});
}
boolean isUidAndNameConflict(String uid, String name) {
Connection connection = null;
PreparedStatement statement = null;
ResultSet rs = null;
long start = System.currentTimeMillis();
try {
connection = DBUtil.getConnection();
String sql = "select _uid from t_user where _name = ? and _uid <> ? limit 1";
statement = connection.prepareStatement(sql);
statement.setString(1, name);
statement.setString(2, uid);
rs = statement.executeQuery();
if (rs.next()) {
String conflictId = rs.getString(1);
LOG.error("user {} already have name {} !!!", conflictId, name);
return true;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} finally {
DBUtil.closeDB(connection, statement, rs);
}
return false;
}
void updateUser(final WFCMessage.User user) throws Exception {
LOG.info("Database update user info {} {}", user.getUid(), user.getUpdateDt());
Connection connection = null;
PreparedStatement statement = null;
LOG.info("Database update user info {}", user.getDisplayName());
try {
connection = DBUtil.getConnection();
String sql = "insert into t_user (`_uid`" +
", `_name`" +
", `_display_name`" +
", `_portrait`" +
", `_mobile`" +
", `_gender`" +
", `_email`" +
", `_address`" +
", `_company`" +
", `_social`" +
", `_extra`" +
", `_type`" +
", `_dt`) values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)" +
" ON DUPLICATE KEY UPDATE `_name`=?" +
", `_display_name`=?" +
", `_portrait`=?" +
", `_mobile`=?" +
", `_gender`=?" +
", `_email`=?" +
", `_address`=?" +
", `_company`=?" +
", `_social`=?" +
", `_extra`=?" +
", `_type`=?" +
", `_deleted`=?" +
", `_dt`=?";
statement = connection.prepareStatement(sql);
int index = 1;
statement.setString(index++, user.getUid());
statement.setString(index++, user.getName());
statement.setString(index++, user.getDisplayName());
statement.setString(index++, user.getPortrait());
statement.setString(index++, user.getMobile());
statement.setInt(index++, user.getGender());
statement.setString(index++, user.getEmail());
statement.setString(index++, user.getAddress());
statement.setString(index++, user.getCompany());
statement.setString(index++, user.getSocial());
statement.setString(index++, user.getExtra());
statement.setInt(index++, user.getType());
statement.setLong(index++, user.getUpdateDt() == 0 ? System.currentTimeMillis() : user.getUpdateDt());
statement.setString(index++, user.getName());
statement.setString(index++, user.getDisplayName());
statement.setString(index++, user.getPortrait());
statement.setString(index++, user.getMobile());
statement.setInt(index++, user.getGender());
statement.setString(index++, user.getEmail());
statement.setString(index++, user.getAddress());
statement.setString(index++, user.getCompany());
statement.setString(index++, user.getSocial());
statement.setString(index++, user.getExtra());
statement.setInt(index++, user.getType());
statement.setInt(index++, user.getDeleted());
statement.setLong(index++, user.getUpdateDt() == 0 ? System.currentTimeMillis() : user.getUpdateDt());
int count = statement.executeUpdate();
LOG.info("Update rows {}", count);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
throw new Exception(e.getMessage());
} finally {
DBUtil.closeDB(connection, statement);
}
}
void deleteUserStatus(String userId) {
Connection connection = null;
PreparedStatement statement = null;
try {
connection = DBUtil.getConnection();
String sql = "delete from t_user_status where _uid = ?";
statement = connection.prepareStatement(sql);
statement.setString(1, userId);
int count = statement.executeUpdate();
LOG.info("Update rows {}", count);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} finally {
DBUtil.closeDB(connection, statement);
}
}
void updateUserStatus(String userId, int status) {
Connection connection = null;
PreparedStatement statement = null;
try {
connection = DBUtil.getConnection();
if (status == 0) {
String sql = "delete from t_user_status where _uid = ?";
statement = connection.prepareStatement(sql);
statement.setString(1, userId);
int count = statement.executeUpdate();
LOG.info("Update rows {}", count);
} else {
String sql = "insert into t_user_status (`_uid`, `_status`, `_dt`) values(?,?,?) ON DUPLICATE KEY UPDATE `_status` = ?";
statement = connection.prepareStatement(sql);
int index = 1;
statement.setString(index++, userId);
statement.setInt(index++, status);
statement.setLong(index++, System.currentTimeMillis());
statement.setInt(index++, status);
int count = statement.executeUpdate();
LOG.info("Update rows {}", count);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} finally {
DBUtil.closeDB(connection, statement);
}
}
int getGeneratedId() {
Connection connection = null;
PreparedStatement statement = null;
ResultSet rs = null;
try {
connection = DBUtil.getConnection();
String sql = "INSERT INTO `t_id_generator` (`id`) VALUES (NULL);";
statement = connection.prepareStatement(sql);
if(statement.executeUpdate()> 0) {
sql = "SELECT LAST_INSERT_ID()";
try {
if (statement!=null) {
statement.close();
}
} catch (Exception e) {
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
}
statement = connection.prepareStatement(sql);
rs = statement.executeQuery();
if (rs.next()) {
return rs.getInt(1);
}
}
return -1;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
return -1;
} finally {
DBUtil.closeDB(connection, statement, rs);
}
}
WFCMessage.User getPersistUser(String userId) {
Connection connection = null;
PreparedStatement statement = null;
ResultSet rs = null;
try {
connection = DBUtil.getConnection();
String sql = "select `_name`" +
", `_display_name`" +
", `_portrait`" +
", `_mobile`" +
", `_gender`" +
", `_email`" +
", `_address`" +
", `_company`" +
", `_social`" +
", `_extra`" +
", `_type`" +
", `_deleted`" +
", `_dt` from t_user where `_uid` = ?";
statement = connection.prepareStatement(sql);
int index = 1;
statement.setString(1, userId);
rs = statement.executeQuery();
if (rs.next()) {
WFCMessage.User.Builder builder = WFCMessage.User.newBuilder();
index = 1;
String value = rs.getString(index++);
value = (value == null ? "" : value);
builder.setUid(userId);
builder.setName(value);
value = rs.getString(index++);
value = (value == null ? "" : value);
builder.setDisplayName(value);
value = rs.getString(index++);
value = (value == null ? "" : value);
builder.setPortrait(value);
value = rs.getString(index++);
value = (value == null ? "" : value);
builder.setMobile(value);
int gender = rs.getInt(index++);
builder.setGender(gender);
value = rs.getString(index++);
value = (value == null ? "" : value);
builder.setEmail(value);
value = rs.getString(index++);
value = (value == null ? "" : value);
builder.setAddress(value);
value = rs.getString(index++);
value = (value == null ? "" : value);
builder.setCompany(value);
value = rs.getString(index++);
value = (value == null ? "" : value);
builder.setSocial(value);
value = rs.getString(index++);
value = (value == null ? "" : value);
builder.setExtra(value);
int type = rs.getInt(index++);
builder.setType(type);
int deleted = rs.getInt(index++);
builder.setDeleted(deleted);
long longValue = rs.getLong(index++);
if(longValue <= 0)
longValue = 1;
builder.setUpdateDt(longValue);
return builder.build();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} finally {
DBUtil.closeDB(connection, statement, rs);
}
return null;
}
String getUserIdByName(String name) {
Connection connection = null;
PreparedStatement statement = null;
ResultSet rs = null;
try {
connection = DBUtil.getConnection();
String sql = "select `_uid` from t_user where `_name` = ? limit 1";
statement = connection.prepareStatement(sql);
statement.setString(1, name);
rs = statement.executeQuery();
if (rs.next()) {
return rs.getString(1);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} finally {
DBUtil.closeDB(connection, statement, rs);
}
return null;
}
String getUserIdByMobile(String mobile) {
Connection connection = null;
PreparedStatement statement = null;
ResultSet rs = null;
try {
connection = DBUtil.getConnection();
String sql = "select `_uid` from t_user where `_mobile` = ? limit 1";
statement = connection.prepareStatement(sql);
statement.setString(1, mobile);
rs = statement.executeQuery();
if (rs.next()) {
return rs.getString(1);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} finally {
DBUtil.closeDB(connection, statement, rs);
}
return null;
}
List<WFCMessage.User> getUserInfosByEmail(String email) {
Connection connection = null;
PreparedStatement statement = null;
ResultSet rs = null;
List<WFCMessage.User> outList = new ArrayList<>();
try {
connection = DBUtil.getConnection();
String sql = "select `_uid`, `_name`" +
", `_display_name`" +
", `_portrait`" +
", `_mobile`" +
", `_gender`" +
", `_email`" +
", `_address`" +
", `_company`" +
", `_social`" +
", `_extra`" +
", `_type`" +
", `_deleted`" +
", `_dt` from t_user where `_deleted` = 0 and `_email` = ?";
statement = connection.prepareStatement(sql);
statement.setString(1, email);
rs = statement.executeQuery();
while (rs.next()) {
WFCMessage.User.Builder builder = WFCMessage.User.newBuilder();
int index = 1;
String value = rs.getString(index++);
value = (value == null ? "" : value);
builder.setUid(value);
value = rs.getString(index++);
value = (value == null ? "" : value);
builder.setName(value);
value = rs.getString(index++);
value = (value == null ? "" : value);
builder.setDisplayName(value);
value = rs.getString(index++);
value = (value == null ? "" : value);
builder.setPortrait(value);
value = rs.getString(index++);
value = (value == null ? "" : value);
builder.setMobile(value);
int gender = rs.getInt(index++);
builder.setGender(gender);
value = rs.getString(index++);
value = (value == null ? "" : value);
builder.setEmail(value);
value = rs.getString(index++);
value = (value == null ? "" : value);
builder.setAddress(value);
value = rs.getString(index++);
value = (value == null ? "" : value);
builder.setCompany(value);
value = rs.getString(index++);
value = (value == null ? "" : value);
builder.setSocial(value);
value = rs.getString(index++);
value = (value == null ? "" : value);
builder.setExtra(value);
int type = rs.getInt(index++);
builder.setType(type);
int deleted = rs.getInt(index++);
builder.setDeleted(deleted);
long longValue = rs.getLong(index++);
if(longValue <= 0)
longValue = 1;
builder.setUpdateDt(longValue);
outList.add(builder.build());
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} finally {
DBUtil.closeDB(connection, statement, rs);
}
return outList;
}
List<WFCMessage.User> getAllUsers(int count, int offset) {
Connection connection = null;
PreparedStatement statement = null;
ResultSet rs = null;
List<WFCMessage.User> outList = new ArrayList<>();
try {
connection = DBUtil.getConnection();
String sql = "select `_uid`, `_name`" +
", `_display_name`" +
", `_portrait`" +
", `_mobile`" +
", `_gender`" +
", `_email`" +
", `_address`" +
", `_company`" +
", `_social`" +
", `_extra`" +
", `_type`" +
", `_deleted`" +
", `_dt` from t_user where `_deleted` = 0 order by _createTime limit ? offset ?";
statement = connection.prepareStatement(sql);
int index = 1;
statement.setInt(1, count);
statement.setInt(2, offset);
rs = statement.executeQuery();
while (rs.next()) {
WFCMessage.User.Builder builder = WFCMessage.User.newBuilder();
index = 1;
String value = rs.getString(index++);
value = (value == null ? "" : value);
builder.setUid(value);
value = rs.getString(index++);
value = (value == null ? "" : value);
builder.setName(value);
value = rs.getString(index++);
value = (value == null ? "" : value);
builder.setDisplayName(value);
value = rs.getString(index++);
value = (value == null ? "" : value);
builder.setPortrait(value);
value = rs.getString(index++);
value = (value == null ? "" : value);
builder.setMobile(value);
int gender = rs.getInt(index++);
builder.setGender(gender);
value = rs.getString(index++);
value = (value == null ? "" : value);
builder.setEmail(value);
value = rs.getString(index++);
value = (value == null ? "" : value);
builder.setAddress(value);
value = rs.getString(index++);
value = (value == null ? "" : value);
builder.setCompany(value);
value = rs.getString(index++);
value = (value == null ? "" : value);
builder.setSocial(value);
value = rs.getString(index++);
value = (value == null ? "" : value);
builder.setExtra(value);
int type = rs.getInt(index++);
builder.setType(type);
int deleted = rs.getInt(index++);
builder.setDeleted(deleted);
long longValue = rs.getLong(index++);
if(longValue <= 0)
longValue = 1;
builder.setUpdateDt(longValue);
outList.add(builder.build());
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} finally {
DBUtil.closeDB(connection, statement, rs);
}
return outList;
}
Set<String> getAllEnds(boolean fromUserTable) {
Connection connection = null;
PreparedStatement statement = null;
ResultSet rs = null;
try {
connection = DBUtil.getConnection();
String sql;
if(fromUserTable) {
sql = "select distinct(`_uid`) from t_user_session";
} else {
sql = "select distinct(`_uid`) from t_user where `_deleted` = 0 and `_type` <> 1 and `_type` <> 2";
}
statement = connection.prepareStatement(sql);
rs = statement.executeQuery();
Set<String> out = new HashSet<>();
while (rs.next()) {
String uid = rs.getString(1);
out.add(uid);
}
return out;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} finally {
DBUtil.closeDB(connection, statement, rs);
}
return null;
}
List<FriendData> getPersistFriends(String userId) {
Connection connection = null;
PreparedStatement statement = null;
ResultSet rs = null;
try {
connection = DBUtil.getConnection();
String sql = "select `_friend_uid`, `_alias`, `_state`, `_blacked`, `_dt`, `_extra` from t_friend where `_uid` = ?";
statement = connection.prepareStatement(sql);
int index = 1;
statement.setString(index++, userId);
rs = statement.executeQuery();
List<FriendData> out = new ArrayList<>();
while (rs.next()) {
String uid = rs.getString(1);
String alias = rs.getString(2);
int state = rs.getInt(3);
int blacked = rs.getInt(4);
long timestamp = rs.getLong(5);
String extra = rs.getString(6);
FriendData data = new FriendData(userId, uid, alias, extra, state, blacked, timestamp);
out.add(data);
}
return out;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} finally {
DBUtil.closeDB(connection, statement, rs);
}
return null;
}
void removeUserFriend(String userId) {
Connection connection = null;
PreparedStatement statement = null;
try {
connection = DBUtil.getConnection();
String sql = "update t_friend set `_alias` = '', `_state` = 1, `_blacked` = 0, `_dt` = ?, `_extra` = 0 where `_uid` = ? or `_friend_uid` = ?";
statement = connection.prepareStatement(sql);
int index = 1;
statement.setLong(index++, System.currentTimeMillis());
statement.setString(index++, userId);
statement.setString(index++, userId);
int count = statement.executeUpdate();
LOG.info("Update rows {}", count);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} finally {
DBUtil.closeDB(connection, statement);
}
}
void removeUserFriendRequest(String userId) {
Connection connection = null;
PreparedStatement statement = null;
try {
connection = DBUtil.getConnection();
String sql = "delete from t_friend_request where _uid = ? or _friend_uid = ?";
statement = connection.prepareStatement(sql);
int index = 1;
statement.setString(index++, userId);
statement.setString(index++, userId);
int count = statement.executeUpdate();
LOG.info("Update rows {}", count);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} finally {
DBUtil.closeDB(connection, statement);
}
}
List<WFCMessage.FriendRequest> getPersistFriendRequests(String userId) {
Connection connection = null;
PreparedStatement statement = null;
ResultSet rs = null;
try {
connection = DBUtil.getConnection();
String sql = "select `_uid`" +
", `_friend_uid`" +
", `_reason`" +
", `_status`" +
", `_dt`" +
", `_from_read_status`" +
", `_to_read_status`, `_extra` from t_friend_request where `_uid` = ? UNION ALL " +
"select `_uid`" +
", `_friend_uid`" +
", `_reason`" +
", `_status`" +
", `_dt`" +
", `_from_read_status`" +
", `_to_read_status`, `_extra` from t_friend_request where `_friend_uid` = ?";
statement = connection.prepareStatement(sql);
int index = 1;
statement.setString(index++, userId);
statement.setString(index++, userId);
rs = statement.executeQuery();
List<WFCMessage.FriendRequest> out = new ArrayList<>();
while (rs.next()) {
WFCMessage.FriendRequest.Builder builder = WFCMessage.FriendRequest.newBuilder();
index = 1;
String value = rs.getString(index++);
value = (value == null ? "" : value);
builder.setFromUid(value);
value = rs.getString(index++);
value = (value == null ? "" : value);
builder.setToUid(value);
value = rs.getString(index++);
value = (value == null ? "" : value);
builder.setReason(value);
int intValue = rs.getInt(index++);
builder.setStatus(intValue);
long longValue = rs.getLong(index++);
builder.setUpdateDt(longValue);
boolean b = rs.getBoolean(index++);
builder.setFromReadStatus(b);
b = rs.getBoolean(index++);
builder.setToReadStatus(b);
value = rs.getString(index++);
value = (value == null ? "" : value);
builder.setExtra(value);
out.add(builder.build());
}
return out;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} finally {
DBUtil.closeDB(connection, statement, rs);
}
return null;
}
void persistFriendRequestUnreadStatus(String userId, long readDt, long updateDt) {
Connection connection = null;
PreparedStatement statement = null;
try {
connection = DBUtil.getConnection();
String sql = "update t_friend_request set `_dt`=? , `_to_read_status`=?" +
" where " +
"`_friend_uid` = ? and" +
"`_dt` <= ? and" +
"`_to_read_status` = ?";
statement = connection.prepareStatement(sql);
int index = 1;
statement.setLong(index++, updateDt);
statement.setBoolean(index++, true);
statement.setString(index++, userId);
statement.setLong(index++, readDt);
statement.setBoolean(index++, false);
int count = statement.executeUpdate();
LOG.info("Update rows {}", count);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} finally {
DBUtil.closeDB(connection, statement);
}
}
//
void persistOrUpdateFriendRequest(final WFCMessage.FriendRequest request) {
Connection connection = null;
PreparedStatement statement = null;
try {
connection = DBUtil.getConnection();
String sql = "insert into t_friend_request (`_uid`, `_friend_uid`, `_reason`, `_status`, `_dt`, `_from_read_status`, `_to_read_status`, `_extra`) values(?, ?, ?, ?, ?, ?, ?, ?)" +
" ON DUPLICATE KEY UPDATE " +
"`_reason` = ?," +
"`_status` = ?," +
"`_dt` = ?," +
"`_from_read_status` = ?," +
"`_to_read_status` = ?," +
"`_extra` = ?";
statement = connection.prepareStatement(sql);
int index = 1;
statement.setString(index++, request.getFromUid());
statement.setString(index++, request.getToUid());
statement.setString(index++, request.getReason());
statement.setInt(index++, request.getStatus());
statement.setLong(index++, request.getUpdateDt());
statement.setInt(index++, request.getFromReadStatus() ? 1 : 0);
statement.setInt(index++, request.getToReadStatus() ? 1 : 0);
statement.setString(index++, request.getExtra());
statement.setString(index++, request.getReason());
statement.setInt(index++, request.getStatus());
statement.setLong(index++, request.getUpdateDt());
statement.setInt(index++, request.getFromReadStatus() ? 1 : 0);
statement.setInt(index++, request.getToReadStatus() ? 1 : 0);
statement.setString(index++, request.getExtra());
int count = statement.executeUpdate();
LOG.info("Update rows {}", count);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} finally {
DBUtil.closeDB(connection, statement);
}
}
void persistOrUpdateFriendData(final FriendData request) {
Connection connection = null;
PreparedStatement statement = null;
try {
connection = DBUtil.getConnection();
String sql = "insert into t_friend (`_uid`, `_friend_uid`, `_alias`, `_state`, `_blacked`, `_dt`, `_extra`) values(?, ?, ?, ?, ?, ?, ?)" +
" ON DUPLICATE KEY UPDATE " +
"`_alias` = ?," +
"`_state` = ?," +
"`_blacked` = ?," +
"`_dt` = ?," +
"`_extra` = ?";
statement = connection.prepareStatement(sql);
int index = 1;
statement.setString(index++, request.getUserId());
statement.setString(index++, request.getFriendUid());
statement.setString(index++, request.getAlias());
statement.setInt(index++, request.getState());
statement.setInt(index++, request.getBlacked());
statement.setLong(index++, request.getTimestamp());
statement.setString(index++, request.getExtra());
statement.setString(index++, request.getAlias());
statement.setInt(index++, request.getState());
statement.setInt(index++, request.getBlacked());
statement.setLong(index++, request.getTimestamp());
statement.setString(index++, request.getExtra());
int count = statement.executeUpdate();
LOG.info("Update rows {}", count);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} finally {
DBUtil.closeDB(connection, statement);
}
}
boolean removeGroupInfoFromDB(String groupId) {
Connection connection = null;
PreparedStatement statement = null;
try {
connection = DBUtil.getConnection();
String sql;
if(keepGroupInfo) {
sql = "update t_group set _deleted = 1, _dt = _dt+1, _member_count = 0, _member_dt = _member_dt+1 where _gid = ?";
} else {
sql = "delete from t_group where _gid = ?";
}
statement = connection.prepareStatement(sql);
int index = 1;
statement.setString(index++, groupId);
return statement.executeUpdate() > 0;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} finally {
DBUtil.closeDB(connection, statement);
}
return false;
}
boolean removeGroupMemberFromDB(String groupId) {
Connection connection = null;
PreparedStatement statement = null;
try {
connection = DBUtil.getConnection();
String sql = "delete from t_group_member where _gid = ?";
statement = connection.prepareStatement(sql);
int index = 1;
statement.setString(index++, groupId);
return statement.executeUpdate() > 0;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} finally {
DBUtil.closeDB(connection, statement);
}
return false;
}
void updateChannelInfo(final WFCMessage.ChannelInfo channelInfo) {
LOG.info("Database update channel info {} {}", channelInfo.getTargetId(), channelInfo.getUpdateDt());
Connection connection = null;
PreparedStatement statement = null;
try {
connection = DBUtil.getConnection();
String sql = "insert into t_channel (`_cid`" +
", `_name`" +
", `_portrait`" +
", `_owner`" +
", `_status`" +
", `_desc`" +
", `_extra`" +
", `_secret`" +
", `_callback`" +
", `_automatic`" +
", `_menu`" +
", `_dt`) values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)" +
" ON DUPLICATE KEY UPDATE `_name`=?" +
", `_portrait`=?" +
", `_owner`=?" +
", `_status`=?" +
", `_desc`=?" +
", `_extra`=?" +
", `_secret`=?" +
", `_callback`=?" +
", `_automatic`=?" +
", `_menu`=?" +
", `_dt`=?";
statement = connection.prepareStatement(sql);
WFCMessage.ChannelMenuList.Builder builder = WFCMessage.ChannelMenuList.newBuilder();
if (!channelInfo.getMenuList().isEmpty()) {
for (WFCMessage.ChannelMenu menuBtn:channelInfo.getMenuList()) {
builder.addMenu(menuBtn);
}
}
byte[] menuBytes = builder.build().toByteArray();
int index = 1;
statement.setString(index++, channelInfo.getTargetId());
statement.setString(index++, channelInfo.getName());
statement.setString(index++, channelInfo.getPortrait());
statement.setString(index++, channelInfo.getOwner());
statement.setInt(index++, channelInfo.getStatus());
statement.setString(index++, channelInfo.getDesc());
statement.setString(index++, channelInfo.getExtra());
statement.setString(index++, channelInfo.getSecret());
statement.setString(index++, channelInfo.getCallback());
statement.setInt(index++, channelInfo.getAutomatic());
statement.setBytes(index++, menuBytes);
statement.setLong(index++, channelInfo.getUpdateDt() == 0 ? System.currentTimeMillis() : channelInfo.getUpdateDt());
statement.setString(index++, channelInfo.getName());
statement.setString(index++, channelInfo.getPortrait());
statement.setString(index++, channelInfo.getOwner());
statement.setInt(index++, channelInfo.getStatus());
statement.setString(index++, channelInfo.getDesc());
statement.setString(index++, channelInfo.getExtra());
statement.setString(index++, channelInfo.getSecret());
statement.setString(index++, channelInfo.getCallback());
statement.setInt(index++, channelInfo.getAutomatic());
statement.setBytes(index++, menuBytes);
statement.setLong(index++, channelInfo.getUpdateDt() == 0 ? System.currentTimeMillis() : channelInfo.getUpdateDt());
int count = statement.executeUpdate();
LOG.info("Update rows {}", count);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} finally {
DBUtil.closeDB(connection, statement);
}
}
void removeChannelInfo(final String channelId) {
LOG.info("Database remove channel {}", channelId);
mScheduler.execute(()->{
Connection connection = null;
PreparedStatement statement = null;
try {
connection = DBUtil.getConnection();
String sql = "delete from t_channel where `_cid`=?";
statement = connection.prepareStatement(sql);
int index = 1;
statement.setString(index++, channelId);
int count = statement.executeUpdate();
LOG.info("Update rows {}", count);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} finally {
DBUtil.closeDB(connection, statement);
}
});
}
WFCMessage.ChannelInfo getPersistChannelInfo(String channelId) {
Connection connection = null;
PreparedStatement statement = null;
ResultSet rs = null;
try {
connection = DBUtil.getConnection();
String sql = "select `_name`" +
", `_portrait`" +
", `_owner`" +
", `_status`" +
", `_desc`" +
", `_extra`" +
", `_secret`" +
", `_callback`" +
", `_automatic`" +
", `_menu`" +
", `_dt` from t_channel where `_cid` = ?";
statement = connection.prepareStatement(sql);
int index = 1;
statement.setString(index++, channelId);
rs = statement.executeQuery();
if (rs.next()) {
String strValue;
int intValue;
WFCMessage.ChannelInfo.Builder builder = WFCMessage.ChannelInfo.newBuilder();
index = 1;
builder.setTargetId(channelId);
strValue = rs.getString(index++);
strValue = (strValue == null ? "" : strValue);
builder.setName(strValue);
strValue = rs.getString(index++);
strValue = (strValue == null ? "" : strValue);
builder.setPortrait(strValue);
strValue = rs.getString(index++);
strValue = (strValue == null ? "" : strValue);
builder.setOwner(strValue);
intValue = rs.getInt(index++);
builder.setStatus(intValue);
strValue = rs.getString(index++);
strValue = (strValue == null ? "" : strValue);
builder.setDesc(strValue);
strValue = rs.getString(index++);
strValue = (strValue == null ? "" : strValue);
builder.setExtra(strValue);
strValue = rs.getString(index++);
strValue = (strValue == null ? "" : strValue);
builder.setSecret(strValue);
strValue = rs.getString(index++);
strValue = (strValue == null ? "" : strValue);
builder.setCallback(strValue);
intValue = rs.getInt(index++);
builder.setAutomatic(intValue);
try {
byte[] bytes = null;
Blob blob = rs.getBlob(index++);
if (blob != null) {
bytes = toByteArray(blob.getBinaryStream());
}
WFCMessage.ChannelMenuList menuButtonList = WFCMessage.ChannelMenuList.parseFrom(bytes);
if (menuButtonList.getMenuCount() > 0) {
builder.addAllMenu(menuButtonList.getMenuList());
}
} catch (IOException e) {
e.printStackTrace();
}
long longValue = rs.getLong(index++);
builder.setUpdateDt(longValue);
return builder.build();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} finally {
DBUtil.closeDB(connection, statement, rs);
}
return null;
}
void persistChannelListener(final String groupId, final List<String> memberList) {
mScheduler.execute(()->{
Connection connection = null;
PreparedStatement statement = null;
try {
connection = DBUtil.getConnection();
connection.setAutoCommit(false);
String sql = "insert into t_channel_listener (`_cid`" +
", `_mid`" +
", `_dt`) values(?, ?, ?)" +
" ON DUPLICATE KEY UPDATE " +
"`_dt` = ?";
statement = connection.prepareStatement(sql);
long dt = System.currentTimeMillis();
for (String member : memberList) {
int index = 1;
statement.setString(index++, groupId);
statement.setString(index++, member);
statement.setLong(index++, dt);
statement.setLong(index++, dt);
int count = statement.executeUpdate();
LOG.info("Update rows {}", count);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} finally {
if (connection != null) {
try {
connection.commit();
connection.setAutoCommit(true);
} catch (SQLException e1) {
e1.printStackTrace();
}
}
DBUtil.closeDB(connection, statement);
}
});
}
List<String> getChannelListener(String channelId) {
List<String> out = new ArrayList<>();
Connection connection = null;
PreparedStatement statement = null;
ResultSet rs = null;
try {
connection = DBUtil.getConnection();
String sql = "select `_mid` from t_channel_listener where _cid = ?";
statement = connection.prepareStatement(sql);
int index = 1;
statement.setString(index++, channelId);
rs = statement.executeQuery();
while (rs.next()) {
String value = rs.getString(1);
if (!StringUtil.isNullOrEmpty(value)) {
out.add(value);
}
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} finally {
DBUtil.closeDB(connection, statement, rs);
}
return out;
}
List<WFCMessage.ChannelInfo> searchChannelFromDB(String keyword, boolean buzzy, int page) {
Connection connection = null;
PreparedStatement statement = null;
ResultSet rs = null;
ArrayList<WFCMessage.ChannelInfo> out = new ArrayList<>();
try {
connection = DBUtil.getConnection();
String sql = "select `_cid`, `_name`" +
", `_portrait`" +
", `_owner`" +
", `_status`" +
", `_desc`" +
", `_extra`" +
", `_dt` from t_channel";
if (buzzy) {
sql += " where `_name` like ? ESCAPE '!' ";
} else {
sql += " where `_name` = ?";
}
sql += " and _status <> 64";
sql += " and _status <> 16";
sql += " and _status <> 17";
sql += " and _status <> 24";
sql += " and _status <> 25";
sql += " limit 20";
if (page > 0) {
sql += " offset " + page * 20;
}
keyword = keyword
.replace("!", "!!")
.replace("%", "!%")
.replace("_", "!_")
.replace("[", "![");
statement = connection.prepareStatement(sql);
int index = 1;
if (buzzy) {
statement.setString(index++, "%" + keyword + "%");
} else {
statement.setString(index++, keyword);
}
rs = statement.executeQuery();
while (rs.next()) {
WFCMessage.ChannelInfo.Builder builder = WFCMessage.ChannelInfo.newBuilder();
index = 1;
String value = rs.getString(index++);
value = (value == null ? "" : value);
builder.setTargetId(value);
value = rs.getString(index++);
value = (value == null ? "" : value);
builder.setName(value);
value = rs.getString(index++);
value = (value == null ? "" : value);
builder.setPortrait(value);
value = rs.getString(index++);
value = (value == null ? "" : value);
builder.setOwner(value);
int status = rs.getInt(index++);
builder.setStatus(status);
value = rs.getString(index++);
value = (value == null ? "" : value);
builder.setDesc(value);
value = rs.getString(index++);
value = (value == null ? "" : value);
builder.setExtra(value);
long longValue = rs.getLong(index++);
builder.setUpdateDt(longValue);
out.add(builder.build());
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} finally {
DBUtil.closeDB(connection, statement, rs);
}
return out;
}
void updateChannelListener(final String channelId, final String listener, final boolean listen) {
LOG.info("updateChannelListener channel {}, listener {}, listen {}", channelId, listener, listen);
Connection connection = null;
PreparedStatement statement = null;
try {
connection = DBUtil.getConnection();
String sql;
if (listen) {
sql = "insert into t_channel_listener (`_cid`" +
", `_mid`" +
", `_dt`) values(?, ?, ?)" +
" ON DUPLICATE KEY UPDATE " +
"`_dt` = ?";
statement = connection.prepareStatement(sql);
int index = 1;
statement.setString(index++, channelId);
statement.setString(index++, listener);
statement.setLong(index++, System.currentTimeMillis());
statement.setLong(index++, System.currentTimeMillis());
int count = statement.executeUpdate();
LOG.info("Update rows {}", count);
} else {
sql = "delete from t_channel_listener where `_cid`=? and `_mid`=?";
statement = connection.prepareStatement(sql);
int index = 1;
statement.setString(index++, channelId);
statement.setString(index++, listener);
int count = statement.executeUpdate();
LOG.info("Update rows {}", count);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} finally {
DBUtil.closeDB(connection, statement);
}
}
List<String> getUserChannels(String userId) {
List<String> out = new ArrayList<>();
Connection connection = null;
PreparedStatement statement = null;
ResultSet rs = null;
try {
connection = DBUtil.getConnection();
String sql = "select _cid from t_channel_listener where _mid = ?";
statement = connection.prepareStatement(sql);
int index = 1;
statement.setString(index++, userId);
rs = statement.executeQuery();
while (rs.next()) {
String value = rs.getString(1);
if (!StringUtil.isNullOrEmpty(value)) {
out.add(value);
}
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} finally {
DBUtil.closeDB(connection, statement, rs);
}
return out;
}
void clearChannelListener(final String channelId) {
LOG.info("Database remove channel {}", channelId);
Connection connection = null;
PreparedStatement statement = null;
try {
connection = DBUtil.getConnection();
String sql = "delete from t_channel_listener where _cid=?";
statement = connection.prepareStatement(sql);
statement.setString(1, channelId);
int count = statement.executeUpdate();
LOG.info("Update rows {}", count);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} finally {
DBUtil.closeDB(connection, statement);
}
}
public Set<String> getSensitiveWord() {
Connection connection = null;
PreparedStatement statement = null;
ResultSet rs = null;
Set<String> out = new HashSet<>();
try {
connection = DBUtil.getConnection();
String sql = "select `_word` from t_sensitiveword order by `id` desc";
statement = connection.prepareStatement(sql);
rs = statement.executeQuery();
while (rs.next()) {
String value = rs.getString(1);
out.add(value);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} finally {
DBUtil.closeDB(connection, statement, rs);
}
return out;
}
void deleteSensitiveWord(final String word) {
LOG.info("delete sensitive word {}", word);
mScheduler.execute(()->{
Connection connection = null;
PreparedStatement statement = null;
try {
connection = DBUtil.getConnection();
String sql = "delete from t_sensitiveword where `_word` = ?";
statement = connection.prepareStatement(sql);
statement.setString(1, word);
int c = statement.executeUpdate();
LOG.info("Update rows {}", c);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} finally {
DBUtil.closeDB(connection, statement);
}
});
}
void persistSensitiveWord(final String word) {
mScheduler.execute(()->{
Connection connection = null;
PreparedStatement statement = null;
try {
connection = DBUtil.getConnection();
String sql = "insert into t_sensitiveword (`_word`) values(?)";
statement = connection.prepareStatement(sql);
statement.setString(1, word);
int count = statement.executeUpdate();
LOG.info("Update rows {}", count);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
Utility.printExecption(LOG, e, RDBS_Exception);
} finally {
DBUtil.closeDB(connection, statement);
}
});
}
private String getUserMessageTable(String uid) {
if (DBUtil.IsEmbedDB) {
return "t_user_messages";
}
int hashId = Math.abs(uid.hashCode())%128;
return "t_user_messages_" + hashId;
}
}
Loading...
马建仓 AI 助手
尝试更多
代码解读
代码找茬
代码优化
Java
1
https://gitee.com/wfchat/im-server.git
git@gitee.com:wfchat/im-server.git
wfchat
im-server
im-server
wildfirechat

搜索帮助