public Vector<Integer> getAllUrlForTheSameVisit(
String ip, String configOs, String configBrowser) {
SqlRowSet s =
this.queryForRowSet(
"SELECT *"
+ " FROM log_visit "
+ "WHERE ip= '"
+ ip
+ "'"
+ " AND config_os= '"
+ configOs
+ "'"
+ " AND config_browser_name= '"
+ configBrowser
+ "';");
Vector<Integer> l = new Vector<Integer>();
while (s.next()) {
int id = s.getInt("idvisit");
SqlRowSet s1 =
this.queryForRowSet(
"SELECT DISTINCT id_url "
+ "FROM log_link_visit_url "
+ "WHERE idvisit ='"
+ id
+ "';");
while (s1.next()) {
int idurl = s1.getInt("id_url");
if (l.contains(idurl) == true) continue;
else l.add(idurl);
}
}
return l;
}
public void saveOverviewVisitInfo() {
SqlRowSet s =
this.queryForRowSet(
"SELECT COUNT(*) AS visit,COUNT(DISTINCT ip) AS unique_visit " + "FROM log_visit;");
VisitOverviewInfo visitOverviewInfo = new VisitOverviewInfo();
while (s.next()) {
visitOverviewInfo.setVisit(s.getInt("visit"));
visitOverviewInfo.setUniqueVisit(s.getInt("unique_visit"));
}
SqlRowSet s1 =
this.queryForRowSet(
"SELECT COUNT(A.visit) AS first_visit "
+ " FROM(SELECT COUNT(*) AS visit "
+ " FROM log_visit "
+ " GROUP BY ip "
+ " HAVING COUNT(*)=1)A ;");
while (s1.next()) {
visitOverviewInfo.setFirstVisit(s1.getInt("first_visit"));
}
SqlRowSet s2 =
this.queryForRowSet(
"SELECT COUNT(id_url) AS page,SUM(time_spent) AS total_time "
+ " FROM log_link_visit_url ;");
while (s2.next()) {
float page = 0, time = 0;
page = s2.getFloat("page");
time = s2.getFloat("total_time");
visitOverviewInfo.setPageVisit(page / visitOverviewInfo.getVisit());
visitOverviewInfo.setAvgTimeVisit(time / visitOverviewInfo.getVisit());
}
SqlRowSet s3 =
this.queryForRowSet(
"SELECT COUNT(A.visit) AS exit_visit "
+ " FROM(SELECT idvisit,COUNT(id_url) AS visit "
+ " FROM log_link_visit_url "
+ " GROUP BY idvisit "
+ " HAVING COUNT(*)=1)A ;");
while (s3.next()) {
float exit_visit = 0;
exit_visit = s3.getFloat("exit_visit");
visitOverviewInfo.setExitVisit(exit_visit / visitOverviewInfo.getVisit());
}
String sql =
"INSERT INTO visit_overview_info(visit,unique_visit,first_visit,page_visit,avg_time_visit,exit_visit) "
+ " VALUES ('"
+ visitOverviewInfo.getVisit()
+ "','"
+ visitOverviewInfo.getUniqueVisit()
+ "','"
+ visitOverviewInfo.getFirstVisit()
+ "','"
+ visitOverviewInfo.getPageVisit()
+ "','"
+ visitOverviewInfo.getAvgTimeVisit()
+ "','"
+ visitOverviewInfo.getExitVisit()
+ "');";
this.getJt().execute(sql);
}
public void saveLinkInfo() {
SqlRowSet s =
this.queryForRowSet(
"SELECT idurl,name,title,viewed,unique_view "
+ " FROM log_url "
+ " WHERE STATUS='404';");
while (s.next()) {
LinkInfo linkInfo = new LinkInfo();
linkInfo.setName(s.getString("name"));
if (s.getString("title") == null) linkInfo.setTitle(" ");
else linkInfo.setTitle(s.getString("title"));
linkInfo.setVisit(s.getInt("viewed"));
linkInfo.setUniqueVisit(s.getInt("unique_view"));
String sql =
"INSERT INTO link_info(name,visit,unique_visit,type,title) "
+ " VALUES ('"
+ linkInfo.getName()
+ "','"
+ linkInfo.getVisit()
+ "','"
+ linkInfo.getUniqueVisit()
+ "','DL','"
+ linkInfo.getTitle()
+ "');";
this.getJt().execute(sql);
}
SqlRowSet s1 =
this.queryForRowSet(
"SELECT idurl,name,title,viewed,unique_view " + " FROM log_url " + " WHERE type='O';");
while (s1.next()) {
LinkInfo linkInfo = new LinkInfo();
linkInfo.setName(s1.getString("name"));
if (s1.getString("title") == null) linkInfo.setTitle(" ");
else linkInfo.setTitle(s1.getString("title"));
linkInfo.setVisit(s1.getInt("viewed"));
linkInfo.setUniqueVisit(s1.getInt("unique_view"));
String sql =
"INSERT INTO link_info(name,visit,unique_visit,type,title) "
+ " VALUES ('"
+ linkInfo.getName()
+ "','"
+ linkInfo.getVisit()
+ "','"
+ linkInfo.getUniqueVisit()
+ "','OL','"
+ linkInfo.getTitle()
+ "');";
this.getJt().execute(sql);
}
}
@Override
public List<Module> getAllModule() {
// TODO Auto-generated method stub
String sqlStr =
"SELECT "
+ ID
+ ","
+ MODULE_NAME
+ ","
+ ENABLE
+ ","
+ PICTURE
+ ","
+ TARGET
+ " FROM "
+ TABLE_NAME;
List<Module> moduleList = new ArrayList<Module>();
SqlRowSet rs = jdbcTemplate.queryForRowSet(sqlStr);
while (rs.next()) {
Module module = new Module();
module.setEnable(rs.getInt(ENABLE));
module.setId(rs.getInt(ID));
module.setModuleName(rs.getString(MODULE_NAME));
module.setPicture(rs.getString(PICTURE));
module.setTarget(rs.getString(TARGET));
moduleList.add(module);
}
return moduleList;
}
public int getIdOfVisit(LogInfo logInfo) {
// TODO Auto-generated method stub
// TODO Auto-generated method stub
SqlRowSet s =
this.queryForRowSet(
"SELECT *"
+ " FROM log_visit "
+ "WHERE ip= '"
+ logInfo.getIp()
+ "'"
+ " AND visit_date= '"
+ DateUtil.handleDateFormat(logInfo.getDate())
+ "'"
+ " AND config_os= '"
+ logInfo.getOs()
+ "'"
+ " AND config_browser_name= '"
+ logInfo.getBrowser()
+ "';");
int id = 0;
while (s.next()) {
id = s.getInt("idvisit");
}
return id;
}
public void updateBounceRateAndExitRateForUrl() {
SqlRowSet s = this.queryForRowSet("SELECT * FROM log_url");
while (s.next()) {
int id = s.getInt("idurl");
updateBounceRateAndExitRateForUrl(id);
}
}
/*
* Convert result set into a collection of entities.
*/
private CollectionResource<Entity> buildCollectionResource(String entityType, SqlRowSet rowSet) {
List<EntityResource<Entity>> results = new ArrayList<EntityResource<Entity>>();
// Extract the returned column names. May be a subset of the ones
// requested.
String[] columnNames = rowSet.getMetaData().getColumnNames();
// For all rows returned add an entity to the collection.
while (rowSet.next()) {
EntityProperties properties = new EntityProperties();
// For all columns in this row.
for (String columnName : columnNames) {
Object value = rowSet.getObject(columnName);
// Only return non null values
if (null != value) {
// Add object to the property. getObject() returns an object
// with the correct java type for each sql type. So we don't
// need to cast.
properties.setProperty(new EntityProperty(columnName, value));
}
}
// Create entity.
// Note: Despite the variable name the first arg of both these is
// the entity type name. Not it's key.
Entity entity = new Entity(entityType, properties);
results.add(new EntityResource<Entity>(entity.getName(), entity));
}
// Note: This line looks a bit odd but the {} at the end is required.
return new CollectionResource<Entity>(results) {};
}
public void press() {
// get a list of all the tables and columns
logger.info("Starting MySQL to Mongo Conversion...");
logger.info("Preparing Tables...");
mySQLHandler.initialiseDatabase();
List<Table> tables = mySQLHandler.getTableList();
for (int i = 0; i < tables.size(); i++) {
Table table = tables.get(i);
List<Column> columns = table.getColumns();
List<DBObject> dboList = new ArrayList<DBObject>();
SqlRowSet rs = mySQLHandler.selectAllFromTable(table.getTableName());
logger.info("Creating objects for " + table.getTableName() + "...");
while (rs.next()) {
BasicDBObject dbo = new BasicDBObject();
for (int j = 0; j < columns.size(); j++) {
Column col = columns.get(j);
String colName = col.getColumnName();
switch (col.getType()) {
case Types.INTEGER:
case Types.BIGINT:
dbo.append(colName, rs.getInt(colName));
break;
case Types.DOUBLE:
dbo.append(colName, rs.getDouble(colName));
break;
case Types.DATE:
dbo.append(colName, rs.getDate(colName));
break;
default:
dbo.append(colName, rs.getString(colName));
break;
}
}
dboList.add(dbo);
}
// now insert it
logger.info(
"Inserting " + dboList.size() + " mongo rows into " + table.getTableName() + "...");
table.setNumOfRows(dboList.size());
try {
mongoHandler.createCollection(table.getTableName(), true);
mongoHandler.batchInsert(dboList, table.getTableName());
assert (mongoHandler.getNumObjectsInCollection(table.getTableName()) == dboList.size());
logger.info(table.getTableName() + " DONE!");
} catch (CollectionExistException e) {
e.printStackTrace();
}
}
logger.info(tables.size() + " collections added!");
// now check go get it from mongo and check if the data there is correct
logger.info("Checking mongo consistency...");
for (int i = 0; i < tables.size(); i++) {
Table table = tables.get(i);
assert (mongoHandler.getNumObjectsInCollection(table.getTableName()) == table.getNumOfRows());
logger.info(table.getTableName() + " consistent!");
}
logger.info("MySQL to Mongo Conversion Completed!!!!");
}
private void createOrgUnitUuids() {
try {
SqlRowSet resultSet =
jdbcTemplate.queryForRowSet("SELECT * from organisationunit WHERE uuid IS NULL");
int count = 0;
while (resultSet.next()) {
++count;
int id = resultSet.getInt("organisationunitid");
String sql =
"update organisationunit set uuid = '"
+ UUID.randomUUID().toString()
+ "' where organisationunitid = "
+ id;
jdbcTemplate.update(sql);
}
if (count > 0) {
log.info(count + " UUIDs updated on organisationunit");
}
} catch (Exception ex) // Log and continue
{
log.error("Problem updating organisationunit: ", ex);
}
}
public void saveEntryPageInfo() {
SqlRowSet s =
this.queryForRowSet(
" SELECT DISTINCT u.idurl,u.name,u.title,u.viewed,u.bounce_rate "
+ " FROM log_url u,log_visit v "
+ " WHERE u.idurl = v.entry_url ;");
while (s.next()) {
EntryPageInfo enttyPageInfo = new EntryPageInfo();
enttyPageInfo.setName(s.getString("name"));
if (s.getString("title") == null) enttyPageInfo.setTitle(" ");
else enttyPageInfo.setTitle(s.getString("title"));
enttyPageInfo.setEntrance(s.getInt("viewed"));
enttyPageInfo.setBounce((int) getVisitOnlyOnePage(s.getInt("idurl")));
enttyPageInfo.setBounceRate(s.getFloat("bounce_rate"));
String sql =
"INSERT INTO entry_page_info(name,entrance,bounce,bounce_rate,title) "
+ " VALUES ('"
+ enttyPageInfo.getName()
+ "','"
+ enttyPageInfo.getEntrance()
+ "','"
+ enttyPageInfo.getBounce()
+ "','"
+ enttyPageInfo.getBounceRate()
+ "','"
+ enttyPageInfo.getTitle()
+ "');";
this.getJt().execute(sql);
}
}
public void saveExitPageInfo() {
SqlRowSet s =
this.queryForRowSet(
" SELECT DISTINCT u.idurl,u.name,u.title,u.viewed,u.exit_rate "
+ " FROM log_url u,log_visit v "
+ " WHERE u.idurl = v.exit_url ;");
while (s.next()) {
ExitPageInfo exitPageInfo = new ExitPageInfo();
exitPageInfo.setName(s.getString("name"));
if (s.getString("title") == null) exitPageInfo.setTitle(" ");
else exitPageInfo.setTitle(s.getString("title"));
exitPageInfo.setVisit(s.getInt("viewed"));
exitPageInfo.setExitTimes((int) getVisitExitPage(s.getInt("idurl")));
exitPageInfo.setExitRate(s.getFloat("exit_rate"));
String sql =
"INSERT INTO exit_page_info(name,exit_times,visit,exit_rate,title) "
+ " VALUES ('"
+ exitPageInfo.getName()
+ "','"
+ exitPageInfo.getExitTimes()
+ "','"
+ exitPageInfo.getVisit()
+ "','"
+ exitPageInfo.getExitRate()
+ "','"
+ exitPageInfo.getTitle()
+ "');";
this.getJt().execute(sql);
}
}
public boolean checkUrlExistInVisit(int idUrl, int idvisit) {
SqlRowSet s =
this.queryForRowSet(
"SELECT *" + " FROM log_link_visit_url " + "WHERE idvisit= '" + idvisit + "'");
while (s.next()) {
if (idUrl == s.getInt("id_url")) return true;
}
return false;
}
public float getViewedForUrl(int idUrl) {
SqlRowSet s =
this.queryForRowSet("SELECT viewed " + "FROM log_url " + "WHERE idurl = '" + idUrl + "';");
float view = 0;
while (s.next()) {
view = s.getFloat("view");
}
return view;
}
/*
* Convert result to a single entry.
*/
EntityResource<Entity> createEntityResource(String entityType, SqlRowSet rowSet)
throws JdbcException {
// Extract the returned column names. May be a subset of the ones
// requested.
String[] columnNames = rowSet.getMetaData().getColumnNames();
// Set cursor to first row
if (!rowSet.next()) {
throw (new JdbcException(
Status.NOT_FOUND, "Row not found. Entry with given key possibly not present."));
}
// Build up properties for this row
EntityProperties properties = new EntityProperties();
// For all columns in this row.
for (String columnName : columnNames) {
Object value = rowSet.getObject(columnName);
// Only return non null values
if (null != value) {
// Add object to the property. getObject() returns an object
// with the correct java type for each sql type. So we don't
// need to cast.
properties.setProperty(new EntityProperty(columnName, value));
}
}
// Make an entity
Entity entity = new Entity(entityType, properties);
// Make an entity resource
EntityResource<Entity> entityResource = new EntityResource<Entity>(entityType, entity);
// Check for additional rows. Not expected for a 'single'
// command.
if (rowSet.next()) {
throw (new JdbcException(
Status.INTERNAL_SERVER_ERROR, "Multiple rows returned for a single entity"));
}
return entityResource;
}
public void saveAvgVisitInfo() {
// TODO Auto-generated method stub
SqlRowSet s =
this.queryForRowSet(
"SELECT HOUR,AVG(TEMP.visit)AS visit "
+ "FROM(SELECT HOUR,visit_date,COUNT(*) AS visit "
+ "FROM log_visit "
+ "GROUP BY HOUR,visit_date)TEMP "
+ "GROUP BY HOUR;");
while (s.next()) {
AvgVisit avgVisit = new AvgVisit();
avgVisit.setName(s.getString("HOUR"));
avgVisit.setVisit(s.getFloat("visit"));
String sql =
"INSERT INTO avg_visit(name,visit,type) "
+ "VALUES ('"
+ avgVisit.getName()
+ "','"
+ avgVisit.getVisit()
+ "','hour')";
this.getJt().execute(sql);
}
SqlRowSet s1 =
this.queryForRowSet(
"SELECT TEMP.day,AVG(visit_quantity)AS visit "
+ "FROM (SELECT NAME,DAYNAME(NAME)AS DAY,visit_quantity "
+ " FROM visit_info "
+ " WHERE TYPE='date' "
+ " GROUP BY NAME)TEMP "
+ "GROUP BY TEMP.day;");
while (s1.next()) {
AvgVisit avgVisit = new AvgVisit();
avgVisit.setName(s1.getString("day"));
avgVisit.setVisit(s1.getFloat("visit"));
String sql =
"INSERT INTO avg_visit(name,visit,type) "
+ "VALUES ('"
+ avgVisit.getName()
+ "','"
+ avgVisit.getVisit()
+ "','day')";
this.getJt().execute(sql);
}
}
public float getVisitExitPage(long l) {
SqlRowSet s =
this.queryForRowSet(
"SELECT COUNT(*) as temp " + "FROM log_visit " + "WHERE exit_url='" + l + "';");
float exit = 0;
while (s.next()) {
exit = s.getFloat("temp");
}
return exit;
}
public boolean mesidExist(String mesid) {
// String psd = account + "_" + password;
// psd = Utility.getMD5(psd);
JdbcTemplate jdbcTemplate = this.getJdbcTemplate();
String sql = "select id from smssendstatus where smsid = \"" + mesid + "\";";
SqlRowSet sRowSet = jdbcTemplate.queryForRowSet(sql);
if (sRowSet.next()) {
return true;
} else return false;
}
public String getCateNameById(int classifyId) throws Exception {
String sql = "select name from t_cy_classify where id=?";
Object[] o = new Object[] {classifyId};
SqlRowSet rs = commonDao.getRs(sql, o);
String name = "";
if (rs.next()) {
name = rs.getString(1);
}
return name;
}
@Override
public int queryCountAll() {
String sql = XormUtil.createSqlForSelectCountAll(User.class);
SqlRowSet set = getJdbcTemplateRead().queryForRowSet(sql);
if (set.next()) {
int res = set.getInt(1);
return res;
}
return 0;
}
/** Writes all rows in the SqlRowSet to the given Grid. */
public static void addRows(Grid grid, SqlRowSet rs) {
int cols = rs.getMetaData().getColumnCount();
while (rs.next()) {
grid.addRow();
for (int i = 1; i <= cols; i++) {
grid.addValue(rs.getObject(i));
}
}
}
@Test
public void testLazyNameUpdate() {
Personne p = (Personne) getSession().get(Personne.class, 1000);
p.setLazyName("Arthur");
getSession().flush();
SqlRowSet rs = jdbcTemplate.queryForRowSet("select * from pers1 where id = ?", 1000);
rs.next();
assertEquals(rs.getString("LAZYNAME"), "Arthur", "Must have been updated");
assertEquals(rs.getString("NAME"), "Anybody", "Must not change");
}
public int getIdForUrl(String url) {
// TODO Auto-generated method stub
if (url.equals("-")) return -1;
SqlRowSet s =
this.queryForRowSet("SELECT * " + " FROM log_url " + "WHERE name= '" + url + "';");
int id = 0;
while (s.next()) {
id = s.getInt("idurl");
}
return id;
}
public int countRequestPerVisit(int idvisit) {
// TODO Auto-generated method stub
SqlRowSet s =
this.queryForRowSet(
"SELECT *" + " FROM log_link_visit_url " + "WHERE idvisit= '" + idvisit + "'");
int count = 0;
while (s.next()) {
count++;
}
return count;
}
void saveOsBrowserInfo() {
SqlRowSet s =
this.queryForRowSet(
"SELECT config_os,config_browser_name,COUNT(ip)AS visit,COUNT(DISTINCT ip)AS unique_visit,SUM(visit_total_time)AS total_time "
+ " FROM log_visit "
+ " GROUP BY config_os,config_browser_name ;");
while (s.next()) {
UserSettingInfo userSettingInfo = new UserSettingInfo();
userSettingInfo.setName(s.getString("config_os") + " " + s.getString("config_browser_name"));
userSettingInfo.setVisit(s.getInt("visit"));
userSettingInfo.setUniqueVisit(s.getInt("unique_visit"));
userSettingInfo.setAvgTime(s.getFloat("total_time") / userSettingInfo.getVisit());
SqlRowSet s1 =
this.queryForRowSet(
" SELECT COUNT(*) AS bounce "
+ " FROM log_visit "
+ " WHERE config_os='"
+ s.getString("config_os")
+ "'AND config_browser_name = '"
+ s.getString("config_browser_name")
+ "' AND visit_total_time =0 ;");
while (s1.next()) {
userSettingInfo.setBounceRate(s1.getFloat("bounce") / userSettingInfo.getVisit());
}
String sql =
"INSERT INTO user_setting_info(name,visit,unique_visit,avg_time,bounce_rate,type) "
+ " VALUES ('"
+ userSettingInfo.getName()
+ "','"
+ userSettingInfo.getVisit()
+ "','"
+ userSettingInfo.getUniqueVisit()
+ "','"
+ userSettingInfo.getAvgTime()
+ "','"
+ userSettingInfo.getBounceRate()
+ "','OB');";
this.getJt().execute(sql);
}
}
public void saveVisitByYear() {
SqlRowSet s =
this.queryForRowSet(
"SELECT year,COUNT(*) AS visit " + " FROM log_visit " + " GROUP BY year ;");
while (s.next()) {
VisitInfo visitInfo = new VisitInfo();
visitInfo.setName(s.getString("year"));
visitInfo.setVisitQuantity(s.getInt("visit"));
visitInfo.setType("year");
handleSaveVisitInfo(visitInfo);
}
}
public int addValue(String userId, AddValueDTO addValueDTO) {
String path = addValueDTO.getPath();
String name = addValueDTO.getName();
String value = addValueDTO.getValue();
String notes = addValueDTO.getNotes();
String idQuery = " select max(id) as id from trackit_user_data";
SqlRowSet idRowSet = jdbcTemplate.queryForRowSet(idQuery);
idRowSet.beforeFirst();
int id = -1;
while (idRowSet.next()) {
id = idRowSet.getInt("id");
}
if (id == -1) {
return 0;
}
id = id + 1;
String query =
"insert into trackit_user_data(`id`,`user_id`, `name`, `path`, `last_updated`, `notes`) values(?,?,?,?,?, ?)";
List<Object> args = new ArrayList<Object>();
args.add(id);
args.add(userId);
args.add(name);
args.add(path);
if (addValueDTO.getTime() == -1) {
java.sql.Timestamp sq = new java.sql.Timestamp(new Date().getTime());
args.add(sq);
} else {
args.add(new java.sql.Timestamp(addValueDTO.getTime()));
}
args.add(notes);
int response = jdbcTemplate.update(query, args.toArray());
if (response == 1) {
query =
"insert into trackit_user_values(`user_data_id`, `name`, `numeric_value`) values(?,?,?)";
args = new ArrayList<Object>();
args.add(id);
args.add("count");
args.add(Double.parseDouble(value));
return jdbcTemplate.update(query, args.toArray());
}
return 0;
}
public static void testCase1() {
JdbcTemplate select = new JdbcTemplate();
select.setDataSource(DbTelescope.getDataSource());
SqlRowSet rowSet =
select.queryForRowSet(
"select tsp.get_container_ids(1318919, 'content_digital','C') from dual");
while (rowSet.next()) {
System.out.println("RECORD!");
System.out.println("Data: " + rowSet.getString(1));
}
}
@Override
public List<String> listDisabledContent(int groupId) {
List<String> ret = new LinkedList<String>();
SqlRowSet rs =
this.getJdbcTemplate()
.queryForRowSet(
"SELECT [content_id] FROM [t_group_lessoncontent] WHERE [group_id]=?",
new Object[] {groupId});
while (rs.next()) {
ret.add(rs.getString(1));
}
return ret;
}
public List<List<Object>> getBroadImg() throws Exception {
String sql = "select img1,img2,goodsId from t_category_broadcast order by ordernum";
SqlRowSet rs = commonDao.getRs(sql);
List<List<Object>> list = new ArrayList<List<Object>>();
while (rs.next()) {
List<Object> innerList = new ArrayList<Object>();
innerList.add(rs.getObject(1));
innerList.add(rs.getObject(2));
innerList.add(rs.getObject(3));
list.add(innerList);
}
return list;
}
private void checkMainResourceExistsWithinScope(final String appTable, final Long appTableId) {
final String unscopedSql =
"select t.id from `" + appTable + "` t ${dataScopeCriteria} where t.id = " + appTableId;
final String sql = dataScopedSQL(unscopedSql, appTable);
final SqlRowSet rs = this.jdbcTemplate.queryForRowSet(sql);
if (!rs.next()) {
throw new DatatableNotFoundException(appTable, appTableId);
}
}
