Java tutorial
/******************************************************************************* * Copyright (c) 2013 Hani Naguib. * All rights reserved. This program and the accompanying materials * are made available under the terms of the GNU Public License v3.0 * which accompanies this distribution, and is available at * http://www.gnu.org/licenses/gpl.html * * Contributors: * Hani Naguib - initial API and implementation ******************************************************************************/ package com.gvmax.data.user; import java.io.IOException; import java.sql.ResultSet; import java.sql.SQLException; import java.util.List; import javax.sql.DataSource; import org.apache.log4j.Logger; import org.springframework.dao.EmptyResultDataAccessException; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.RowMapper; import com.codahale.metrics.annotation.ExceptionMetered; import com.codahale.metrics.annotation.Timed; import com.gvmax.common.model.GlobalStats; import com.gvmax.common.model.Stats; import com.gvmax.common.model.User; import com.gvmax.common.util.Enc; import com.gvmax.common.util.JsonUtils; /** * Implementation of UserDAO that is JDBC based. */ public class JDBCBasedUserDAO implements UserDAO { private static final Logger logger = Logger.getLogger(JDBCBasedUserDAO.class); public static final String USER_TABLE = "users"; public static final String STATS_TABLE = "stats"; public static final String BLACKLIST_TABLE = "blacklist"; private JdbcTemplate jdbcTemplate; private Enc enc; public JDBCBasedUserDAO(DataSource dataSource, String encKey) { this.jdbcTemplate = new JdbcTemplate(dataSource); this.enc = new Enc(encKey); } @Override @Timed @ExceptionMetered public boolean exists(String email) { // TODO: Review deprecated use int count = jdbcTemplate.queryForInt("select count(0) from " + USER_TABLE + " where email = ?", enc(email)); return count > 0; } @Override @Timed @ExceptionMetered public List<User> getUsers(int offset, int limit) { return jdbcTemplate.query("select user from " + USER_TABLE + " LIMIT ?,?", new Object[] { offset, limit }, new RowMapper<User>() { @Override public User mapRow(ResultSet rs, int rowNum) throws SQLException { String userJson = rs.getString("user"); return toUser(userJson); } }); } @Override @Timed @ExceptionMetered public User retrieve(String email) { String userJson = queryForString("select user from " + USER_TABLE + " where email = ?", enc(email)); return toUser(userJson); } @Override @Timed @ExceptionMetered public User retrieveByPin(String pin) { String userJson = queryForString("select user from " + USER_TABLE + " where pin = ?", enc(pin)); return toUser(userJson); } @Override @Timed @ExceptionMetered public User retrieveByGTalk(String gTalkEmail) { String userJson = queryForString("select user from " + USER_TABLE + " where gTalkEmail = ?", enc(gTalkEmail)); return toUser(userJson); } @Override @Timed @ExceptionMetered public void store(User user) { try { String userJson = JsonUtils.toJson(user); if (user.getEmail() != null) { user.setEmail(user.getEmail().trim().toLowerCase()); } if (user.getgTalkEmail() != null) { user.setgTalkEmail(user.getgTalkEmail().trim().toLowerCase()); } if (exists(user.getEmail())) { jdbcTemplate.update( "update " + USER_TABLE + " set pin = ?, gTalkEmail = ?, user = ? where email = ?", enc(user.getPin()), enc(user.getgTalkEmail()), enc(userJson), enc(user.getEmail())); } else { jdbcTemplate.update("insert into " + USER_TABLE + " (email, pin, gTalkEmail,user) values (?,?,?,?)", enc(user.getEmail()), enc(user.getPin()), enc(user.getgTalkEmail()), enc(userJson)); if (getStats(user.getEmail()) == null) { store(new Stats(user.getEmail(), user.getPin())); } } } catch (IOException e) { logger.error("Unable to store user", e); } } @Override @Timed @ExceptionMetered public void setPassword(String email, String password) { User user = retrieve(email); user.setPassword(password); store(user); } @Override @Timed @ExceptionMetered public void setGVFwdPhone(String email, String number, String type) { User user = retrieve(email); user.setGvFwdPhone(number); user.setGvFwdPhoneType(type); store(user); } @Override @Timed @ExceptionMetered public void delete(String email) { jdbcTemplate.update("delete from " + USER_TABLE + " where email = ?", enc(email)); } // ----------------- // STATS // ----------------- @Override @Timed @ExceptionMetered public GlobalStats getStats() { try { GlobalStats stats = jdbcTemplate.queryForObject( "select " + "count(email) as statsCount," + "sum(smsInCount) as smsInCount," + "sum(smsOutCount) as smsOutCount," + "sum(vmInCount) as vmInCount," + "sum(mcInCount) as mcInCount," + "sum(emailInCount) as emailInCount," + "sum(gTalkCount) as gTalkCount," + "sum(apiCount) as apiCount," + "sum(errorCount) as errorCount," + "sum(invalidEmailCount) as invalidEmailCount," + "sum(fallbackCount) as fallbackCount " + "from " + STATS_TABLE, new RowMapper<GlobalStats>() { @Override public GlobalStats mapRow(ResultSet rs, int row) throws SQLException { GlobalStats stats = new GlobalStats(); stats.setTimestamp(System.currentTimeMillis()); stats.setStatsCount(rs.getInt("statsCount")); stats.setSmsInCount(rs.getInt("smsInCount")); stats.setSmsOutCount(rs.getInt("smsOutCount")); stats.setVmInCount(rs.getInt("vmInCount")); stats.setMcInCount(rs.getInt("mcInCount")); stats.setEmailInCount(rs.getInt("emailInCount")); stats.setgTalkCount(rs.getInt("gTalkCount")); stats.setApiCount(rs.getInt("apiCount")); stats.setErrorCount(rs.getInt("errorCount")); stats.setInvalidEmailCount(rs.getInt("invalidEmailCount")); stats.setFallbackCount(rs.getInt("fallbackCount")); return stats; } }); // TODO: Review deprecated use int userCount = jdbcTemplate.queryForInt("select count(email) from " + USER_TABLE); stats.setUserCount(userCount); return stats; } catch (EmptyResultDataAccessException e) { return null; } } @Override @Timed @ExceptionMetered public Stats getStats(final String email) { try { Stats stats = jdbcTemplate.queryForObject("select * from " + STATS_TABLE + " where email = ?", new Object[] { enc(email) }, new RowMapper<Stats>() { @Override public Stats mapRow(ResultSet rs, int row) throws SQLException { Stats stats = new Stats(email, dec(rs.getString("pin"))); stats.setTimestamp(System.currentTimeMillis()); stats.setSmsInCount(rs.getInt("smsInCount")); stats.setSmsOutCount(rs.getInt("smsOutCount")); stats.setVmInCount(rs.getInt("vmInCount")); stats.setMcInCount(rs.getInt("mcInCount")); stats.setEmailInCount(rs.getInt("emailInCount")); stats.setgTalkCount(rs.getInt("gTalkCount")); stats.setApiCount(rs.getInt("apiCount")); stats.setErrorCount(rs.getInt("errorCount")); stats.setInvalidEmailCount(rs.getInt("invalidEmailCount")); stats.setFallbackCount(rs.getInt("fallbackCount")); return stats; } }); return stats; } catch (EmptyResultDataAccessException e) { return null; } } @Override @Timed @ExceptionMetered public void store(Stats stats) { jdbcTemplate.update("delete from " + STATS_TABLE + " where email = ?", stats.getEmail()); jdbcTemplate.update("insert into " + STATS_TABLE + " " + "(email,pin,smsInCount,vmInCount,mcInCount,emailInCount,gTalkCount,smsOutCount,apiCount,errorCount,invalidEmailCount,fallbackCount) " + " values(?,?,?,?,?,?,?,?,?,?,?,?)", enc(stats.getEmail()), enc(stats.getPin()), stats.getSmsInCount(), stats.getVmInCount(), stats.getMcInCount(), stats.getEmailInCount(), stats.getgTalkCount(), stats.getSmsOutCount(), stats.getApiCount(), stats.getErrorCount(), stats.getInvalidEmailCount(), stats.getFallbackCount()); } @Override @Timed @ExceptionMetered public void incrementSMSInCount(String email) { jdbcTemplate.update("update " + STATS_TABLE + " set smsInCount=smsInCount+1 WHERE email = ?", enc(email)); } @Override @Timed @ExceptionMetered public void incrementVMInCount(String email) { jdbcTemplate.update("update " + STATS_TABLE + " set vmInCount=vmInCount+1 WHERE email = ?", enc(email)); } @Override @Timed @ExceptionMetered public void incrementMCInCount(String email) { jdbcTemplate.update("update " + STATS_TABLE + " set mcInCount=mcInCount+1 WHERE email = ?", enc(email)); } @Override @Timed @ExceptionMetered public void incrementEmailInCount(String email) { jdbcTemplate.update("update " + STATS_TABLE + " set emailInCount=emailInCount+1 WHERE email = ?", enc(email)); } @Override @Timed @ExceptionMetered public void incrementGTalkCount(String email) { jdbcTemplate.update("update " + STATS_TABLE + " set gTalkCount=gTalkCount+1 WHERE email = ?", enc(email)); } @Override @Timed @ExceptionMetered public void incrementApiCount(String email) { jdbcTemplate.update("update " + STATS_TABLE + " set apiCount=apiCount+1 WHERE email = ?", enc(email)); } @Override @Timed @ExceptionMetered public void incrementSMSOutCount(String email, int increment) { jdbcTemplate.update("update " + STATS_TABLE + " set smsOutCount=smsOutCount+1 WHERE email = ?", enc(email)); } @Override @Timed @ExceptionMetered public void incrementErrorCount(String email) { jdbcTemplate.update("update " + STATS_TABLE + " set errorCount=errorCount+1 WHERE email = ?", enc(email)); } @Override @Timed @ExceptionMetered public void incrementFallbackCount(String email) { jdbcTemplate.update("update " + STATS_TABLE + " set fallbackCount=fallbackCount+1 WHERE email = ?", enc(email)); } @Override @Timed @ExceptionMetered public void clearFallbackCount(String email) { jdbcTemplate.update("update " + STATS_TABLE + " set fallbackCount=0 WHERE email = ?", enc(email)); } @Override @Timed @ExceptionMetered public void incrementInvalidEmailCount(String email) { jdbcTemplate.update("update " + STATS_TABLE + " set invalidEmailCount=invalidEmailCount+1 WHERE email = ?", enc(email)); } // -------------- // BLACKLIST // -------------- @Override @Timed @ExceptionMetered public boolean isBlacklisted(String value) { return false; } @Override @Timed @ExceptionMetered public void blacklist(String value) { } // ----------------- // UTILS // ----------------- private String enc(String value) { return enc.encrypt(value); } private String dec(String value) { return enc.decrypt(value); } private User toUser(String json) { if (json == null) { return null; } json = dec(json); try { return JsonUtils.fromJson(json, User.class); } catch (IOException e) { logger.error("Unable to convert json", e); return null; } } private String queryForString(String sql, Object... params) { List<String> res = jdbcTemplate.query(sql, params, new RowMapper<String>() { @Override public String mapRow(ResultSet resultSet, int row) throws SQLException { return resultSet.getString(1); } }); if (res.isEmpty()) { return null; } return res.get(0); } }