001 /*
002 * Copyright 2010 the original author or authors.
003 *
004 * Licensed under the Apache License, Version 2.0 (the "License");
005 * you may not use this file except in compliance with the License.
006 * You may obtain a copy of the License at
007 *
008 * http://www.apache.org/licenses/LICENSE-2.0
009 *
010 * Unless required by applicable law or agreed to in writing, software
011 * distributed under the License is distributed on an "AS IS" BASIS,
012 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
013 * See the License for the specific language governing permissions and
014 * limitations under the License.
015 */
016 package com.hs.mail.imap.dao;
017
018 import java.sql.Connection;
019 import java.sql.PreparedStatement;
020 import java.sql.ResultSet;
021 import java.sql.SQLException;
022 import java.sql.Statement;
023 import java.util.List;
024
025 import javax.mail.Quota;
026
027 import org.springframework.jdbc.core.PreparedStatementCreator;
028 import org.springframework.jdbc.core.RowMapper;
029 import org.springframework.jdbc.support.GeneratedKeyHolder;
030 import org.springframework.jdbc.support.KeyHolder;
031
032 import com.hs.mail.imap.user.Alias;
033 import com.hs.mail.imap.user.User;
034
035 /**
036 *
037 * @author Won Chul Doh
038 * @since Mar 23, 2010
039 *
040 */
041 public class MySqlUserDao extends AbstractDao implements UserDao {
042
043 public User getUser(long id) {
044 String sql = "SELECT * FROM user WHERE id = ?";
045 return (User) queryForObject(sql, new Object[] { new Long(id) },
046 userMapper);
047 }
048
049 public long getUserID(String address) {
050 String sql = "SELECT id FROM user WHERE userid = ?";
051 return queryForLong(sql, new Object[] { address });
052 }
053
054 public User getUserByAddress(String address) {
055 String sql = "SELECT * FROM user WHERE userid = ?";
056 return (User) queryForObject(sql, new Object[] { address }, userMapper);
057 }
058
059 public int getUserCount(String domain) {
060 String sql = "SELECT COUNT(*) FROM user WHERE userid LIKE ?";
061 return getJdbcTemplate().queryForInt(
062 sql,
063 new Object[] { new StringBuilder("%@").append(escape(domain))
064 .toString() });
065 }
066
067 @SuppressWarnings("unchecked")
068 public List<User> getUserList(String domain, int page, int pageSize) {
069 int offset = (page - 1) * pageSize;
070 String sql = "SELECT * FROM user WHERE userid LIKE ? ORDER BY userid LIMIT ?, ?";
071 return getJdbcTemplate().query(sql,
072 new Object[] {
073 new StringBuilder("%@").append(escape(domain)).toString(),
074 new Integer(offset),
075 new Integer(pageSize) }, userMapper);
076 }
077
078 public long addUser(final User user) {
079 final String sql = "INSERT INTO user (userid, passwd, maxmail_size, forward) VALUES(?, ?, ?, ?)";
080 KeyHolder keyHolder = new GeneratedKeyHolder();
081 getJdbcTemplate().update(new PreparedStatementCreator() {
082 public PreparedStatement createPreparedStatement(Connection con)
083 throws SQLException {
084 PreparedStatement pstmt = con.prepareStatement(sql,
085 Statement.RETURN_GENERATED_KEYS);
086 pstmt.setString(1, user.getUserID());
087 pstmt.setString(2, user.getPassword());
088 pstmt.setLong(3, user.getQuota());
089 pstmt.setString(4, user.getForwardTo());
090 return pstmt;
091 }
092 }, keyHolder);
093 long id = keyHolder.getKey().longValue();
094 user.setID(id);
095 return id;
096 }
097
098 public int updateUser(User user) {
099 String sql = "UPDATE user SET userid = ?, passwd = ?, maxmail_size = ?, forward = ? WHERE id = ?";
100 return getJdbcTemplate().update(
101 sql,
102 new Object[] { user.getUserID(), user.getPassword(),
103 new Long(user.getQuota()), user.getForwardTo(),
104 new Long(user.getID()) });
105 }
106
107 public int deleteUser(long id) {
108 String sql = "DELETE FROM user WHERE id = ?";
109 return getJdbcTemplate().update(sql, new Object[] { new Long(id) });
110 }
111
112 public Alias getAlias(long id) {
113 String sql = "SELECT a.*, u.userid FROM alias a, user u WHERE a.id = ? AND a.deliver_to = u.id";
114 return (Alias) queryForObject(sql, new Object[] { new Long(id) },
115 aliasMapper);
116 }
117
118 public int getAliasCount(String domain) {
119 String sql = "SELECT COUNT(*) FROM alias a, user u WHERE a.alias LIKE ? AND a.deliver_to = u.id";
120 return getJdbcTemplate().queryForInt(
121 sql,
122 new Object[] { new StringBuilder("%@").append(escape(domain))
123 .toString() });
124 }
125
126 @SuppressWarnings("unchecked")
127 public List<Alias> getAliasList(String domain, int page, int pageSize) {
128 int offset = (page - 1) * pageSize;
129 String sql = "SELECT a.*, u.userid FROM alias a, user u WHERE a.alias LIKE ? AND a.deliver_to = u.id ORDER BY a.alias LIMIT ?, ?";
130 return getJdbcTemplate().query(sql,
131 new Object[] {
132 new StringBuilder("%@").append(escape(domain)).toString(),
133 new Integer(offset),
134 new Integer(pageSize) }, aliasMapper);
135 }
136
137 @SuppressWarnings("unchecked")
138 public List<Alias> expandAlias(String alias) {
139 String sql = "SELECT a.*, u.userid FROM alias a, user u WHERE a.alias = ? AND a.deliver_to = u.id";
140 return getJdbcTemplate()
141 .query(sql, new Object[] { alias }, aliasMapper);
142 }
143
144 public long addAlias(final Alias alias) {
145 final String sql = "INSERT INTO alias (alias, deliver_to) VALUES(?, ?)";
146 KeyHolder keyHolder = new GeneratedKeyHolder();
147 getJdbcTemplate().update(new PreparedStatementCreator() {
148 public PreparedStatement createPreparedStatement(Connection con)
149 throws SQLException {
150 PreparedStatement pstmt = con.prepareStatement(sql,
151 Statement.RETURN_GENERATED_KEYS);
152 pstmt.setString(1, alias.getAlias());
153 pstmt.setLong(2, alias.getDeliverTo());
154 return pstmt;
155 }
156 }, keyHolder);
157 long id = keyHolder.getKey().longValue();
158 alias.setID(id);
159 return id;
160 }
161
162 public int updateAlias(Alias alias) {
163 String sql = "UPDATE alias SET alias = ?, deliver_to = ? WHERE id = ?";
164 return getJdbcTemplate()
165 .update(
166 sql,
167 new Object[] { alias.getAlias(),
168 new Long(alias.getDeliverTo()),
169 new Long(alias.getID()) });
170 }
171
172 public int deleteAlias(long id) {
173 String sql = "DELETE FROM alias WHERE id = ?";
174 return getJdbcTemplate().update(sql, new Object[] { new Long(id) });
175 }
176
177 public long getQuotaLimit(long ownerID) {
178 String sql = "SELECT maxmail_size FROM user WHERE id = ?";
179 long limit = queryForLong(sql, new Object[] { new Long(ownerID) });
180 return limit * 1024 * 1024;
181 }
182
183 public long getQuotaUsage(long ownerID) {
184 String sql = "SELECT SUM(size) FROM mailbox b, message m, physmessage p WHERE b.ownerid=? AND b.mailboxid=m.mailboxid AND m.physmessageid=p.id";
185 return queryForLong(sql, new Object[] { new Long(ownerID) });
186 }
187
188 public Quota getQuota(long ownerID, String quotaRoot) {
189 Quota quota = new Quota(quotaRoot);
190 quota.setResourceLimit("STORAGE", getQuotaLimit(ownerID));
191 quota.resources[0].usage = getQuotaUsage(ownerID);
192 return quota;
193 }
194
195 public void setQuota(long ownerID, Quota quota) {
196 String sql = "UPDATE user SET maxmail_size = ? WHERE id = ?";
197 for (int i = 0; i < quota.resources.length; i++) {
198 if ("STORAGE".equals(quota.resources[i].name)) {
199 getJdbcTemplate().update(
200 sql,
201 new Object[] { new Long(quota.resources[i].limit),
202 new Long(ownerID) });
203 quota.resources[i].usage = getQuotaUsage(ownerID);
204 return;
205 }
206 }
207 }
208
209 private static RowMapper userMapper = new RowMapper() {
210 public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
211 User user = new User();
212 user.setID(rs.getLong("id"));
213 user.setUserID(rs.getString("userid"));
214 user.setPassword(rs.getString("passwd"));
215 user.setQuota(rs.getLong("maxmail_size"));
216 user.setForwardTo(rs.getString("forward"));
217 return user;
218 }
219 };
220
221 private static RowMapper aliasMapper = new RowMapper() {
222 public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
223 Alias alias = new Alias();
224 alias.setID(rs.getLong("id"));
225 alias.setAlias(rs.getString("alias"));
226 alias.setDeliverTo(rs.getLong("deliver_to"));
227 alias.setUserID(rs.getString("userid"));
228 return alias;
229 }
230 };
231
232 }