001: /**
002: * $RCSfile$
003: * $Revision: $
004: * $Date: $
005: *
006: * Copyright (C) 2007 Jive Software. All rights reserved.
007: *
008: * This software is published under the terms of the GNU Public License (GPL),
009: * a copy of which is included in this distribution.
010: */package org.jivesoftware.openfire.sip.sipaccount;
011:
012: import org.jivesoftware.database.DbConnectionManager;
013: import org.jivesoftware.util.Log;
014:
015: import java.sql.Connection;
016: import java.sql.PreparedStatement;
017: import java.sql.ResultSet;
018: import java.sql.SQLException;
019: import java.util.ArrayList;
020: import java.util.Collection;
021: import java.util.List;
022:
023: /**
024: *
025: * Database persistence for SipAccount class and database methods for stored SIP Accounts
026: *
027: * @author Thiago Rocha Camargo
028: */
029: public class SipAccountDAO {
030:
031: public static SipAccount getAccountByUser(String username) {
032:
033: String sql = "SELECT username, sipusername, sipauthuser, sipdisplayname, sippassword, sipserver, enabled, "
034: + "status, stunserver, stunport, usestun, voicemail, outboundproxy, promptCredentials FROM sipUser "
035: + "WHERE username = ? ";
036:
037: SipAccount sipAccount = null;
038: Connection con = null;
039: PreparedStatement psmt = null;
040: ResultSet rs = null;
041:
042: try {
043:
044: con = DbConnectionManager.getConnection();
045: psmt = con.prepareStatement(sql);
046: psmt.setString(1, username);
047: rs = psmt.executeQuery();
048:
049: if (rs.next()) {
050: sipAccount = read(rs);
051: }
052:
053: } catch (SQLException e) {
054: Log.error(e.getMessage(), e);
055: } finally {
056: DbConnectionManager.closeConnection(rs, psmt, con);
057: }
058: return sipAccount;
059: }
060:
061: private static SipAccount read(ResultSet rs) {
062: SipAccount sipAccount = null;
063: try {
064:
065: String username = rs.getString("username");
066: String sipusername = rs.getString("sipusername");
067: String authusername = rs.getString("sipauthuser");
068: String displayname = rs.getString("sipdisplayname");
069: String password = rs.getString("sippassword");
070: String server = rs.getString("sipserver");
071: String stunServer = rs.getString("stunserver");
072: String stunPort = rs.getString("stunport");
073: boolean useStun = rs.getInt("usestun") == 1;
074: boolean enabled = rs.getInt("enabled") == 1;
075: String voicemail = rs.getString("voicemail");
076: String outboundproxy = rs.getString("outboundproxy");
077: boolean promptCredentials = rs.getInt("promptCredentials") == 1;
078: SipRegisterStatus status = SipRegisterStatus.valueOf(rs
079: .getString("status"));
080: sipAccount = new SipAccount(username);
081:
082: sipAccount.setSipUsername(sipusername);
083: sipAccount.setAuthUsername(authusername);
084: sipAccount.setDisplayName(displayname);
085: sipAccount.setPassword(password);
086: sipAccount.setServer(server);
087: sipAccount.setEnabled(enabled);
088: sipAccount.setStatus(status);
089: sipAccount.setStunServer(stunServer);
090: sipAccount.setStunPort(stunPort);
091: sipAccount.setUseStun(useStun);
092: sipAccount.setVoiceMailNumber(voicemail);
093: sipAccount.setOutboundproxy(outboundproxy);
094: sipAccount.setPromptCredentials(promptCredentials);
095:
096: } catch (SQLException e) {
097: Log.error(e.getMessage(), e);
098: }
099: return sipAccount;
100: }
101:
102: public static void insert(SipAccount sipAccount)
103: throws SQLException {
104:
105: String sql = "INSERT INTO sipUser (username, sipusername, sipauthuser, sipdisplayname, sippassword, sipserver, enabled, status, stunserver, stunport, usestun, voicemail, outboundproxy, promptCredentials ) "
106: + " values ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
107:
108: Connection con = null;
109: PreparedStatement psmt = null;
110: ResultSet rs = null;
111:
112: try {
113: con = DbConnectionManager.getConnection();
114: psmt = con.prepareStatement(sql);
115: psmt.setString(1, sipAccount.getUsername());
116: psmt.setString(2, sipAccount.getSipUsername());
117: psmt.setString(3, sipAccount.getAuthUsername());
118: psmt.setString(4, sipAccount.getDisplayName());
119: psmt.setString(5, sipAccount.getPassword());
120: psmt.setString(6, sipAccount.getServer());
121: psmt.setInt(7, sipAccount.isEnabled() ? 1 : 0);
122: psmt.setString(8, sipAccount.getStatus().name());
123: psmt.setString(9, sipAccount.getStunServer());
124: psmt.setString(10, sipAccount.getStunPort());
125: psmt.setInt(11, sipAccount.isUseStun() ? 1 : 0);
126: psmt.setString(12, sipAccount.getVoiceMailNumber());
127: psmt.setString(13, sipAccount.getOutboundproxy());
128: psmt.setInt(14, sipAccount.isPromptCredentials() ? 1 : 0);
129: psmt.executeUpdate();
130:
131: } catch (SQLException e) {
132: Log.error(e.getMessage(), e);
133: throw new SQLException(e.getMessage());
134: } finally {
135: DbConnectionManager.closeConnection(rs, psmt, con);
136: }
137:
138: }
139:
140: public static void update(SipAccount sipAccount)
141: throws SQLException {
142:
143: String sql = "UPDATE sipUser SET sipusername = ?, sipauthuser = ?, sipdisplayname = ?, sippassword = ?, sipserver = ?, enabled = ?, status = ?, stunserver = ?, stunport = ?, usestun = ?, voicemail= ?, outboundproxy = ?, promptCredentials = ? "
144: + " WHERE username = ?";
145:
146: Connection con = null;
147: PreparedStatement psmt = null;
148:
149: try {
150:
151: con = DbConnectionManager.getConnection();
152: psmt = con.prepareStatement(sql);
153: psmt.setString(1, sipAccount.getSipUsername());
154: psmt.setString(2, sipAccount.getAuthUsername());
155: psmt.setString(3, sipAccount.getDisplayName());
156: psmt.setString(4, sipAccount.getPassword());
157: psmt.setString(5, sipAccount.getServer());
158: psmt.setInt(6, sipAccount.isEnabled() ? 1 : 0);
159: psmt.setString(7, sipAccount.getStatus().name());
160: psmt.setString(8, sipAccount.getStunServer());
161: psmt.setString(9, sipAccount.getStunPort());
162: psmt.setInt(10, sipAccount.isUseStun() ? 1 : 0);
163: psmt.setString(11, sipAccount.getVoiceMailNumber());
164: psmt.setString(12, sipAccount.getOutboundproxy());
165: psmt.setInt(13, sipAccount.isPromptCredentials() ? 1 : 0);
166: psmt.setString(14, sipAccount.getUsername());
167:
168: psmt.executeUpdate();
169:
170: } catch (SQLException e) {
171: Log.error(e.getMessage(), e);
172: throw new SQLException(e.getMessage());
173: } finally {
174: DbConnectionManager.closeConnection(psmt, con);
175: }
176:
177: }
178:
179: public static void remove(SipAccount sipAccount) {
180:
181: String sql = "DELETE FROM sipUser WHERE username = ?";
182:
183: Connection con = null;
184: PreparedStatement psmt = null;
185:
186: try {
187:
188: con = DbConnectionManager.getConnection();
189: psmt = con.prepareStatement(sql);
190: psmt.setString(1, sipAccount.getUsername());
191: psmt.executeUpdate();
192: psmt.close();
193:
194: } catch (SQLException e) {
195: Log.error(e.getMessage(), e);
196: } finally {
197: DbConnectionManager.closeConnection(psmt, con);
198: }
199:
200: }
201:
202: public static Collection<SipAccount> getUsers(int startIndex,
203: int numResults) {
204:
205: String sql = "SELECT username, sipusername, sipauthuser, sipdisplayname, sippassword, sipserver, enabled, status, stunserver, stunport, usestun, voicemail, outboundproxy, promptCredentials FROM sipUser "
206: + " ORDER BY USERNAME";
207:
208: List<SipAccount> sipAccounts = new ArrayList<SipAccount>(
209: numResults);
210: Connection con = null;
211: PreparedStatement pstmt = null;
212: try {
213: con = DbConnectionManager.getConnection();
214: pstmt = DbConnectionManager
215: .createScrollablePreparedStatement(con, sql);
216: ResultSet rs = pstmt.executeQuery();
217: DbConnectionManager.setFetchSize(rs, startIndex
218: + numResults);
219: DbConnectionManager.scrollResultSet(rs, startIndex);
220: int count = 0;
221: while (rs.next() && count < numResults) {
222: sipAccounts.add(read(rs));
223: count++;
224: }
225: rs.close();
226: } catch (SQLException e) {
227: Log.error(e);
228: } finally {
229: try {
230: if (pstmt != null) {
231: pstmt.close();
232: }
233: } catch (Exception e) {
234: Log.error(e);
235: }
236: try {
237: if (con != null) {
238: con.close();
239: }
240: } catch (Exception e) {
241: Log.error(e);
242: }
243: }
244: return sipAccounts;
245: }
246:
247: public static int getUserCount() {
248: int count = 0;
249:
250: String sql = "SELECT count(*) FROM sipUser";
251:
252: Connection con = null;
253: PreparedStatement pstmt = null;
254: try {
255: con = DbConnectionManager.getConnection();
256: pstmt = con.prepareStatement(sql);
257: ResultSet rs = pstmt.executeQuery();
258: if (rs.next()) {
259: count = rs.getInt(1);
260: }
261: rs.close();
262: } catch (SQLException e) {
263: Log.error(e);
264: } finally {
265: try {
266: if (pstmt != null) {
267: pstmt.close();
268: }
269: } catch (Exception e) {
270: Log.error(e);
271: }
272: try {
273: if (con != null) {
274: con.close();
275: }
276: } catch (Exception e) {
277: Log.error(e);
278: }
279: }
280: return count;
281: }
282:
283: }
|