/**
*
*
* <pre>
* 사용자의 service_Infra_id 값을 읽음
* SELECT service_Infra_id FROM user WHERE userid=?
* </pre>
*/
@Override
public String readUserServiceInfra(String userId) throws Exception {
String serviceInfra = null;
String sql = "SELECT service_Infra_id FROM user WHERE userid=?";
ResultSet rs = null;
PreparedStatement stmt = null;
try {
stmt = conn.prepareStatement(sql);
stmt.setString(1, userId);
// rs = stmt.executeQuery();
rs = _query(stmt);
if (rs.next()) {
serviceInfra = rs.getString(1);
}
} catch (SQLException e) {
throw e;
} finally {
DAOUtil.closeStatement(stmt);
DAOUtil.closeResultSet(rs);
}
return serviceInfra;
}
@Override
public boolean linkExists(LinkCrawled linkCrawled) throws SQLException {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = this.daoFactory.getConnection();
final Object[] values = {linkCrawled.getLink()};
preparedStatement =
DAOUtil.prepareStatement(connection, this.SQL_CHECK_EXISTS, false, values);
resultSet = preparedStatement.executeQuery();
if (resultSet.next()) {
int numVal = resultSet.getInt("count");
if (numVal > 0) {
return true;
} else {
return false;
}
}
return false;
} catch (final SQLException e) {
LOG.error("Update link_crawled_table fails, " + e.getMessage());
return false;
} finally {
DAOUtil.close(connection, preparedStatement, resultSet);
}
}
/**
*
*
* <pre>
* 주어진 사용자명(string)에 대한 id 값을 구함
* SELECT id FROM user WHERE userid=?
* </pre>
*/
@Override
public int readUserId(String userId) throws Exception {
int id = -1;
// Connection conn = DAOUtil.getConnection();
String sql = "SELECT id FROM user WHERE userid=?";
// String sql = "SELECT id FROM user WHERE name=?";
ResultSet rs = null;
PreparedStatement stmt = null;
try {
stmt = conn.prepareStatement(sql);
stmt.setString(1, userId);
// rs = stmt.executeQuery();
rs = _query(stmt);
if (rs.next()) {
id = Integer.parseInt(rs.getString(1));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
throw e;
} finally {
DAOUtil.closeStatement(stmt);
DAOUtil.closeResultSet(rs);
// DAOUtil.closeJDBCConnection(conn);
}
return id;
}
@Override
public boolean deActivate(String locationUrl, int maxLinkAge) throws SQLException {
if (locationUrl == null) {
return false;
}
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = this.daoFactory.getConnection();
String matchLocationLinkString = locationUrl + "%";
String pastDate = Helper.getPastDate(maxLinkAge);
final Object[] values = {matchLocationLinkString, pastDate};
preparedStatement = DAOUtil.prepareStatement(connection, this.SQL_DEACTIVATE, false, values);
Globals.crawlerLogManager.writeLog(preparedStatement.toString());
preparedStatement.executeUpdate();
return true;
} catch (final SQLException e) {
Globals.crawlerLogManager.writeLog(
"Fails to deactivate link " + locationUrl + " in posting_location");
Globals.crawlerLogManager.writeLog(e.getMessage());
return false;
} finally {
DAOUtil.close(connection, preparedStatement, resultSet);
}
}
@Override
public List<PostingLocation> getActive(int active) throws Exception {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = this.daoFactory.getConnection();
final Object[] values = {active};
preparedStatement =
DAOUtil.prepareStatement(connection, this.SQL_SELECT_BY_ACTIVE, false, values);
resultSet = preparedStatement.executeQuery();
final List<PostingLocation> locations = new ArrayList<PostingLocation>();
while (resultSet.next()) {
final PostingLocation location = this.constructPostingLocationObject(resultSet);
locations.add(location);
}
return locations;
} catch (final SQLException e) {
Globals.crawlerLogManager.writeLog(
"Get posting_location where active = " + active + " fails");
Globals.crawlerLogManager.writeLog(e.getMessage());
return null;
} finally {
DAOUtil.close(connection, preparedStatement, resultSet);
}
}
/**
*
*
* <pre>
* 사용자의 KeepAgentNO 값을 구함
* SELECT keepAgentNO FROM user WHERE userid=?
* </pre>
*/
@Override
public int readUserKeepAgentNO(String userId) throws Exception {
int keepAgentNO = -1;
String sql = "SELECT keepAgentNO FROM user WHERE userid=?";
ResultSet rs = null;
PreparedStatement stmt = null;
try {
stmt = conn.prepareStatement(sql);
stmt.setString(1, userId);
// rs = stmt.executeQuery();
rs = _query(stmt);
if (rs.next()) {
keepAgentNO = rs.getInt(1);
}
} catch (SQLException e) {
throw e;
} finally {
DAOUtil.closeStatement(stmt);
DAOUtil.closeResultSet(rs);
}
return keepAgentNO;
}
/**
*
*
* <pre>
* 사용자아이디가 같은 레코드이 개수를 카운트
* SELECT COUNT(*) AS cn FROM user WHERE userid=?
* </pre>
*/
@Override
public boolean checkUser(String userId) throws Exception {
String sql = "SELECT COUNT(*) AS cn FROM user WHERE userid=?";
ResultSet rs = null;
PreparedStatement stmt = null;
int a = -1;
try {
stmt = conn.prepareStatement(sql);
stmt.setString(1, userId);
// rs = stmt.executeQuery();
rs = _query(stmt);
if (rs.next()) {
a = rs.getInt(1);
}
if (a > 0) {
return true;
} else {
return false;
}
} catch (SQLException e) {
throw e;
} finally {
DAOUtil.closeStatement(stmt);
DAOUtil.closeResultSet(rs);
}
}
/**
*
*
* <pre>
* </pre>
*/
@Override
public void updateUserInfo(User usr) throws Exception {
String sql = "UPDATE user set passwd=? , dn=? , service_Infra_id=? , name =? WHERE userid = ? ";
ResultSet rs = null;
PreparedStatement stmt = null;
try {
stmt = conn.prepareStatement(sql);
stmt.setString(1, usr.getPw());
stmt.setString(2, usr.getDN());
stmt.setString(3, usr.getServiceInfraID());
stmt.setString(4, usr.getName());
stmt.setString(5, usr.getUserID());
// int rows = stmt.executeUpdate();
int rows = _update(stmt);
if (rows != 1) {
throw new SQLException();
}
} catch (SQLException e) {
throw e;
} finally {
DAOUtil.closeStatement(stmt);
DAOUtil.closeResultSet(rs);
}
}
@Override
public List<LinkCrawled> get(int domainId) throws SQLException {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = this.daoFactory.getConnection();
preparedStatement =
DAOUtil.prepareStatement(connection, this.SQL_SELECT_BY_DOMAINID, false, domainId);
resultSet = preparedStatement.executeQuery();
final List<LinkCrawled> linksCrawled = new ArrayList<LinkCrawled>();
while (resultSet.next()) {
final LinkCrawled linkCrawled = this.constructLinkCrawledObject(resultSet);
linksCrawled.add(linkCrawled);
}
return linksCrawled;
} catch (final SQLException e) {
LOG.error("Get link_crawled_table fails, " + e.getMessage());
return null;
} finally {
DAOUtil.close(connection, preparedStatement, resultSet);
}
}
@Override
public boolean update(LinkCrawled linkCrawled) throws SQLException {
Connection connection = null;
PreparedStatement preparedStatement = null;
final ResultSet resultSet = null;
try {
connection = this.daoFactory.getConnection();
final Object[] values = {
linkCrawled.getLink(), linkCrawled.getPriority(), linkCrawled.getId()
};
preparedStatement = DAOUtil.prepareStatement(connection, this.SQL_UPDATE, false, values);
preparedStatement.executeUpdate();
return true;
} catch (final SQLException e) {
LOG.error("Update link_crawled_table fails, " + e.getMessage());
return false;
} finally {
DAOUtil.close(connection, preparedStatement, resultSet);
}
}
/**
*
*
* <pre>
* </pre>
*/
@Override
public List<String> getUserList() throws Exception {
List<String> usrList = new ArrayList<String>();
String sql = "SELECT userid FROM user";
ResultSet rs = null;
PreparedStatement stmt = null;
try {
stmt = conn.prepareStatement(sql);
// rs = stmt.executeQuery();
rs = _query(stmt);
while (rs.next()) {
usrList.add(rs.getString(1));
}
} catch (SQLException e) {
throw e;
} finally {
DAOUtil.closeStatement(stmt);
DAOUtil.closeResultSet(rs);
}
return usrList;
}
/**
*
*
* <pre>
* </pre>
*/
@Override
public void setUserPassword(String userId, String pw) throws Exception {
String sql = "UPDATE user set passwd = ? WHERE userid = ? ";
ResultSet rs = null;
PreparedStatement stmt = null;
try {
stmt = conn.prepareStatement(sql);
stmt.setString(1, pw);
stmt.setString(2, userId);
// int rows = stmt.executeUpdate();
int rows = _update(stmt);
if (rows != 1) {
throw new SQLException();
}
} catch (SQLException e) {
throw e;
} finally {
DAOUtil.closeStatement(stmt);
DAOUtil.closeResultSet(rs);
}
}
@Override
public boolean activate(String url) throws SQLException {
if (url == null) {
return false;
}
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = this.daoFactory.getConnection();
final Object[] values = {url};
preparedStatement = DAOUtil.prepareStatement(connection, this.SQL_ACTIVATE, false, values);
Globals.crawlerLogManager.writeLog(preparedStatement.toString());
preparedStatement.executeUpdate();
return true;
} catch (final SQLException e) {
Globals.crawlerLogManager.writeLog("Fails to activate link " + url + " in posting_location");
Globals.crawlerLogManager.writeLog(e.getMessage());
return false;
} finally {
DAOUtil.close(connection, preparedStatement, resultSet);
}
}
public static void main(String arg[]) {
Connection conn = DAOUtil.getConnection();
try {
UserDAOImpl userDAO = new UserDAOImpl(conn);
List<User> aa = userDAO.getUserObjectList();
for (User user : aa) {
System.out.println(user.toString());
}
DAOUtil.doCommit(conn);
} catch (Exception e) {
e.printStackTrace();
DAOUtil.doRollback(conn);
}
}
@Override
public int create(LinkCrawled linkCrawled) throws SQLException {
if (linkCrawled.getLink() == null) {
return -1;
}
// If the time crawled is not specified, use the current time
if (linkCrawled.getTimeCrawled() == null || linkCrawled.getDateCrawled() == null) {
linkCrawled.setTimeCrawled(Helper.getCurrentTime());
linkCrawled.setDateCrawled(Helper.getCurrentDate());
}
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = this.daoFactory.getConnection();
final Object[] values = {
linkCrawled.getLink(), linkCrawled.get_originalLink(),
linkCrawled.getPriority(), linkCrawled.getDomainTableId1(),
linkCrawled.get_downloadDuration(), linkCrawled.get_extractedTime(),
linkCrawled.get_httpStatusCode(), linkCrawled.get_relevance(),
linkCrawled.get_distanceFromRelevantPage(), linkCrawled.get_freshness(),
linkCrawled.getTimeCrawled(), linkCrawled.getDateCrawled()
};
preparedStatement = DAOUtil.prepareStatement(connection, this.SQL_INSERT, true, values);
preparedStatement.executeUpdate();
// Get the generated key (id)
resultSet = preparedStatement.getGeneratedKeys();
int generatedKey = -1;
if (resultSet.next()) {
generatedKey = resultSet.getInt(1);
}
return generatedKey;
} catch (final SQLException e) {
LOG.error("Insert into link_crawled_table fails, " + e.getMessage());
return -1;
} finally {
DAOUtil.close(connection, preparedStatement, resultSet);
}
}
@Override
public boolean create(PostingLocation location) throws SQLException {
if (!location.isValid()) {
return false;
}
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = this.daoFactory.getConnection();
final Object[] values = {
location.getState(),
location.getCity(),
location.getLatitude(),
location.getLongitude(),
location.getLocation_fk(),
location.getLocation_link_fk(),
location.getDatePosted(),
location.getTimePosted(),
location.getPosting_body(),
location.getTitle(),
location.getUrl(),
location.getActive(),
location.getEmail()
};
preparedStatement = DAOUtil.prepareStatement(connection, this.SQL_INSERT, false, values);
Globals.crawlerLogManager.writeLog(preparedStatement.toString());
preparedStatement.executeUpdate();
return true;
} catch (final SQLException e) {
Globals.crawlerLogManager.writeLog("Insert into table posting_location fails");
Globals.crawlerLogManager.writeLog(e.getMessage());
return false;
} finally {
DAOUtil.close(connection, preparedStatement, resultSet);
}
}
/**
*
*
* <pre>
* 사용자를 추가함 (dn, name, userid, passwd 등의 정보를 넣는다.)
* INSERT INTO user (dn, name, userid, passwd, service_Infra_id) VALUES (?, ?, ?, ?, ?)
* </pre>
*
* @return 입력된 사용자의 id 값
*/
@Override
public int createUser(String dn, String name, String userId, String passwd, String infraMetric)
throws Exception {
String sql =
"INSERT INTO user (dn, name, userid, passwd, service_Infra_id) VALUES (?, ?, ?, ?, ?)";
ResultSet rs = null;
PreparedStatement stmt = null;
int id = -1;
try {
stmt = conn.prepareStatement(sql);
stmt.setString(1, dn);
stmt.setString(2, name);
stmt.setString(3, userId);
stmt.setString(4, passwd);
stmt.setString(5, infraMetric);
// int rows = stmt.executeUpdate();
int rows = _update(stmt);
if (rows != 1) {
throw new SQLException();
}
sql = "SELECT LAST_INSERT_ID()";
stmt = conn.prepareStatement(sql);
// rs = stmt.executeQuery();
rs = _query(stmt);
if (rs.next()) {
id = rs.getInt(1);
}
} catch (SQLException e) {
throw e;
} finally {
DAOUtil.closeStatement(stmt);
DAOUtil.closeResultSet(rs);
}
return id;
}
public int execute() {
int id = -1;
String sql =
this.sql.toString().replace("${_where_}", this.condition.toString()).replace("'?'", "?");
DataSource ds = DataSourceWrapCache.get(dsName);
try {
int rs = 0;
if (args != null && args.size() > 0) {
rs =
(Integer)
JdbcUtil.updateWithArgs(ds.getConnection(), sql, args.toArray(new Object[] {}));
} else {
rs = (Integer) JdbcUtil.update(ds.getConnection(), sql);
}
if (rs > 0 && sql.contains("INSERT INTO")) {
if (Map.class.isAssignableFrom(clazz)) {
if (map == null) {
map = new HashMap<String, Object>();
map.put("idColumn", "id");
map.put("table", this.table);
} else if (map.get("idColumn") == null) {
map.put("idColumn", "id");
}
id = (Integer) DAOUtil.selectMaxId(map, ds.getConnection(), dbType);
} else {
id = (Integer) DAOUtil.selectMaxId(clazz, ds.getConnection(), dbType);
}
}
} catch (SQLException e) {
log.error("sql-->" + sql + "exception:" + StringUtil.getExceptionString(e));
throw new DAOException(sql + " execute exception", e);
}
// this.clear();
return id;
}
/**
*
*
* <pre>
* 주어진 id의 사용자를 삭제
* DELETE FROM user WHERE id=?
* </pre>
*/
@Override
public void deleteUser(int id) throws Exception {
String sql = "DELETE FROM user WHERE id=?";
ResultSet rs = null;
PreparedStatement stmt = null;
try {
stmt = conn.prepareStatement(sql);
stmt.setInt(1, id);
// int rows = stmt.executeUpdate();
int rows = _update(stmt);
if (rows != 1) {
throw new SQLException();
}
} catch (SQLException e) {
throw e;
} finally {
DAOUtil.closeStatement(stmt);
DAOUtil.closeResultSet(rs);
}
}
/**
*
*
* <pre>
* </pre>
*/
@Override
public List<User> getUserObjectList() throws Exception {
List<User> usrList = new ArrayList<User>();
User usr = null;
String sql = "SELECT * FROM user";
PreparedStatement stmt = null;
ResultSet rs = null;
try {
stmt = conn.prepareStatement(sql);
// rs = stmt.executeQuery();
rs = _query(stmt);
while (rs.next()) {
usr = new User();
usr.setId(rs.getInt("id"));
usr.setDN(rs.getString("dn"));
usr.setUserID(rs.getString("userid"));
usr.setPw(rs.getString("passwd"));
usr.setName(rs.getString("name"));
usr.setServiceInfraID(rs.getString("service_Infra_id"));
usr.setUsergroupId(rs.getInt("ug_id"));
usrList.add(usr);
}
} catch (SQLException e) {
throw e;
} finally {
DAOUtil.closeStatement(stmt);
DAOUtil.closeResultSet(rs);
}
return usrList;
}
/**
*
*
* <pre>
* </pre>
*/
@Override
public User getUserInfo(String userId) throws Exception {
String sql = "SELECT * FROM user WHERE userid=?";
ResultSet rs = null;
PreparedStatement stmt = null;
User usr = new User();
try {
stmt = conn.prepareStatement(sql);
stmt.setString(1, userId);
// rs = stmt.executeQuery();
rs = _query(stmt);
if (rs.next()) {
usr.setId(rs.getInt("id"));
usr.setUserID(rs.getString("userid"));
usr.setName(rs.getString("name"));
usr.setServiceInfraID(rs.getString("service_Infra_id"));
usr.setDN(rs.getString("dn"));
usr.setPw(rs.getString("passwd"));
usr.setKeepAgentNo(rs.getInt("KeepAgentNO"));
usr.setUsergroupId(rs.getInt("ug_id"));
usr.setOtpflag(rs.getInt("otp_flag"));
usr.setShared(rs.getBoolean("shared"));
}
} catch (SQLException e) {
throw e;
} finally {
DAOUtil.closeStatement(stmt);
DAOUtil.closeResultSet(rs);
}
return usr;
}
@Override
public boolean update(PostingLocation postingLocation) throws SQLException {
if (postingLocation == null) {
return false;
}
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = this.daoFactory.getConnection();
final Object[] values = {
postingLocation.getEmail(), postingLocation.getActive(), postingLocation.getLocation_fk()
};
preparedStatement = DAOUtil.prepareStatement(connection, this.SQL_UPDATE, false, values);
Globals.crawlerLogManager.writeLog(preparedStatement.toString());
preparedStatement.executeUpdate();
return true;
} catch (final SQLException e) {
Globals.crawlerLogManager.writeLog(
"Fails to update email "
+ postingLocation.getEmail()
+ " in posting_location for id "
+ postingLocation.getLocation_fk());
Globals.crawlerLogManager.writeLog(e.getMessage());
return false;
} finally {
DAOUtil.close(connection, preparedStatement, resultSet);
}
}
@Override
public PostingLocation get(int locationId) throws Exception {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = this.daoFactory.getConnection();
final Object[] values = {locationId};
preparedStatement =
DAOUtil.prepareStatement(connection, this.SQL_SELECT_BY_ID, false, values);
resultSet = preparedStatement.executeQuery();
PostingLocation locations = null;
if (resultSet.next()) {
locations = new PostingLocation();
locations = this.constructPostingLocationObject(resultSet);
}
if (resultSet.next()) {
Globals.crawlerLogManager.writeLog(
"There are two locations with the same id " + locationId);
throw new Exception("There are two locations with the same id");
}
return locations;
} catch (final SQLException e) {
Globals.crawlerLogManager.writeLog("Get posting_location fails");
Globals.crawlerLogManager.writeLog(e.getMessage());
return null;
} finally {
DAOUtil.close(connection, preparedStatement, resultSet);
}
}
/**
*
*
* <pre>
* </pre>
*/
@Override
public void updateUserOtpFlag(String userId, int otp_flag) throws Exception {
String sql = "UPDATE user set otp_flag = ? WHERE userid = ? ";
PreparedStatement stmt = null;
try {
stmt = conn.prepareStatement(sql);
stmt.setInt(1, otp_flag);
stmt.setString(2, userId);
// int rows = stmt.executeUpdate();
int rows = _update(stmt);
if (rows != 1) {
throw new SQLException();
}
} catch (SQLException e) {
throw e;
} finally {
DAOUtil.closeStatement(stmt);
}
}