/*
* xulfaces : bring XUL power to Java
*
* Copyright (C) 2005 Olivier SCHMITT
* This library is free software; you can redistribute it and/or
* modify it under the terms of the GNU Lesser General Public
* License as published by the Free Software Foundation; either
* version 2.1 of the License, or (at your option) any later version.
*
* This library is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
* Lesser General Public License for more details.
*
* You should have received a copy of the GNU Lesser General Public
* License along with this library; if not, write to the Free Software
* Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
*/
package org.xulfaces.rubis.admin.dao.jdbc;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import org.xulfaces.rubis.admin.dao.UserDAO;
import org.xulfaces.rubis.model.Region;
import org.xulfaces.rubis.model.User;
public class JDBCUserDAO extends JDBCAbstractDAO implements UserDAO {
public JDBCUserDAO() throws Exception {
super();
}
public Collection<User> findByFirstname(String firstname,int startOffset,int rows) {
Collection<User> users = new ArrayList<User>();
StringBuffer sqlBuffer = new StringBuffer();
sqlBuffer.append("SELECT ");
sqlBuffer.append(" u.ID as ID,u.FIRSTNAME as FIRSTNAME,u.LASTNAME as LASTNAME,u.NICKNAME as NICKNAME,r.ID as REGION_ID FROM USERS as u, REGIONS as r WHERE u.REGION = r.ID AND u.FIRSTNAME like '");
sqlBuffer.append(firstname);
sqlBuffer.append("%'");
ResultSet resultSet = null;
try {
resultSet = excecuteScrollableQuery(sqlBuffer.toString());
if(resultSet.absolute(startOffset)){
int i = 0;
do {
User user = new User();
user.setId(new Integer(resultSet.getInt("ID")));
user.setFirstname(resultSet.getString("FIRSTNAME"));
user.setLastname(resultSet.getString("LASTNAME"));
user.setNickname(resultSet.getString("NICKNAME"));
Region region = new Region();
region.setId(new Integer(resultSet.getInt("REGION_ID")));
user.setRegion(region);
users.add(user);
i++;
}
while((resultSet.next()) && (i < rows));
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
finally {
closeConnection(resultSet);
}
return users;
}
public User loadUser(Integer id) {
User user = null;
StringBuffer sqlBuffer = new StringBuffer();
sqlBuffer.append("SELECT ID,FIRSTNAME,LASTNAME,NICKNAME FROM USERS WHERE ID = ");
sqlBuffer.append(id);
ResultSet resultSet = null;
try {
resultSet = excecuteQuery(sqlBuffer.toString());
if(resultSet.next()){
user = new User();
user.setId(new Integer(resultSet.getInt("ID")));
user.setFirstname(resultSet.getString("FIRSTNAME"));
user.setLastname(resultSet.getString("LASTNAME"));
user.setNickname(resultSet.getString("NICKNAME"));
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
finally {
closeConnection(resultSet);
}
return user;
}
public void saveNewUser(User user) {
StringBuffer sqlBuffer = new StringBuffer();
try {
ResultSet resultSet = this.excecuteQuery("SELECT MAX(ID) as MAX_USER_ID FROM USERS");
if(resultSet.next()){
long id = resultSet.getLong("MAX_USER_ID");
sqlBuffer.append("INSERT INTO USERS (ID,FIRSTNAME,LASTNAME,NICKNAME,PASSWORD,EMAIL,REGION) VALUES (");
id = id + 1;
sqlBuffer.append(id);
sqlBuffer.append(",'");
sqlBuffer.append(user.getFirstname());
sqlBuffer.append("','");
sqlBuffer.append(user.getLastname());
sqlBuffer.append("','");
sqlBuffer.append(user.getNickname());
sqlBuffer.append("','");
sqlBuffer.append(user.getPassword());
sqlBuffer.append("','");
sqlBuffer.append(user.getEmail());
sqlBuffer.append("',");
sqlBuffer.append(user.getRegion().getId());
sqlBuffer.append(")");
excecuteUpdateQuery(sqlBuffer.toString());
user.setId(new Integer((int) id));
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
public void saveUser(User user) {
StringBuffer sqlBuffer = new StringBuffer();
try {
sqlBuffer.append("UPDATE USERS SET FIRSTNAME='");
sqlBuffer.append(user.getFirstname());
sqlBuffer.append("',LASTNAME='");
sqlBuffer.append(user.getLastname());
sqlBuffer.append("',NICKNAME='");
sqlBuffer.append(user.getNickname());
sqlBuffer.append("',PASSWORD='");
sqlBuffer.append(user.getPassword());
sqlBuffer.append("',EMAIL='");
sqlBuffer.append(user.getEmail());
sqlBuffer.append("',REGION=");
sqlBuffer.append(user.getRegion().getId());
sqlBuffer.append(" WHERE ID=");
sqlBuffer.append(user.getId());
excecuteUpdateQuery(sqlBuffer.toString());
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
public void deleteUser(Integer id) {
StringBuffer sqlBuffer = new StringBuffer();
try {
sqlBuffer.append("DELETE FROM USERS WHERE ID=");
sqlBuffer.append(id);
excecuteUpdateQuery(sqlBuffer.toString());
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}