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;
}
/*
* 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 boolean checkUrlExist(String url) {
// TODO Auto-generated method stub
SqlRowSet s =
this.queryForRowSet("SELECT * " + " FROM log_url " + "WHERE name= '" + url + "';");
if (s.first() == true) return true;
else return false;
}
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 updateBounceRateAndExitRateForUrl() {
SqlRowSet s = this.queryForRowSet("SELECT * FROM log_url");
while (s.next()) {
int id = s.getInt("idurl");
updateBounceRateAndExitRateForUrl(id);
}
}
public ComplexValue(SqlRowSet rowSet, boolean isNullValues) {
rowValues = new HashMap<String, Object>(rowSet.getMetaData().getColumnCount());
int index = 1;
for (String columnName : rowSet.getMetaData().getColumnNames()) {
rowValues.put(columnName.toLowerCase(), isNullValues ? null : rowSet.getObject(index++));
}
}
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;
}
@Override
public Integer getLineId(final int lineNo, final String file, final String project) {
final String sql = "SELECT (lineid) FROM lines WHERE " + "project=? AND file=? AND lineno=?;";
SqlRowSet lineRows = jdbcTemplate.queryForRowSet(sql, new Object[] {project, file, lineNo});
if (lineRows.first()) {
return lineRows.getInt("lineId");
}
return null;
}
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;
}
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;
}
@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;
}
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;
}
@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");
}
/** 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));
}
}
}
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;
}
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 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);
}
}
@Override
public LikelihoodData retrieve(final Long likelihoodId) {
final SqlRowSet likelihood = this._getLikelihood(likelihoodId);
likelihood.first();
return new LikelihoodData(
likelihood.getLong("id"),
likelihood.getString("name"),
likelihood.getString("code"),
likelihood.getLong("enabled"));
}
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;
}
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);
}
}
@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;
}
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));
}
}
@Test
public void testInsert() {
Personne p = new Personne();
p.setName("test");
p.setLazyName("testlazy");
getSession().saveOrUpdate(p);
getSession().flush(); // Test cheat
assertNotNull(p.getId());
SqlRowSet rs = jdbcTemplate.queryForRowSet("select * from pers1 where id = ?", p.getId());
rs.next();
assertEquals(rs.getString("NAME"), "test", "Must have been inserted");
assertEquals(rs.getString("LAZYNAME"), "testlazy", "Must have been inserted");
}
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 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 float getVisitOnlyOnePage(long l) {
SqlRowSet s =
this.queryForRowSet(
"SELECT COUNT(*) as visit "
+ "FROM log_visit "
+ "WHERE visit_total_time =0 AND entry_url='"
+ l
+ "';");
float visit = 0;
while (s.next()) {
visit = s.getFloat("visit");
}
return visit;
}
@Test
public void testRowSet2() {
Object[] params = new Object[] {"CA"};
SqlRowSet rowSet =
jdbcTemplate.queryForRowSet("SELECT * FROM countries WHERE CountryISOCode = ?", params);
assertTrue("Has next", rowSet.next());
String isoCode = rowSet.getString("CountryISOCode");
assertTrue(isoCode != null);
String name = rowSet.getString("CountryName");
assertTrue(name != null);
}
@Test
public void testRowSet() {
Object[] params = new Object[] {1};
SqlRowSet rowSet =
jdbcTemplate.queryForRowSet("SELECT * FROM regions WHERE RegionID > ?", params);
assertTrue(rowSet.next());
String regionId = rowSet.getString("RegionID");
assertTrue(regionId != null);
String regionName = rowSet.getString("RegionName");
assertTrue(regionName != null);
}
