public List<HobbyType> getAllHobbyTypes() {
Connection conn = ConnectionManager.getInstance().getConnection();
ResultSet rs = null;
List<HobbyType> hts = new ArrayList<HobbyType>();
Statement stat = null;
try {
String sql = "select * from hobby_type";
stat = SqlExecuteUtil.getStatement(conn);
rs = stat.executeQuery(sql);
while (rs.next()) {
HobbyType ht = new HobbyType();
ht.setTypeId(rs.getInt(1));
ht.setName(rs.getString(2));
hts.add(ht);
}
} catch (SQLException e1) {
LoggerUtil.loggerDebug(
logger, "[HobbyAndHobbyTypeQuery， getAllHobbyTypes] 获取兴趣类型失败" + e1.getMessage());
e1.printStackTrace();
}
SqlExecuteUtil.closeResultSet(rs);
SqlExecuteUtil.closeStatement(stat);
SqlExecuteUtil.closeConnection(conn);
return hts;
}
/**
* 根据hobbytype加载hobby
*
* @param typeId
* @return
*/
public List<Hobby> loadHobbyByType(int typeId) {
if (0 == typeId) return null;
Connection conn = ConnectionManager.getInstance().getConnection();
ResultSet rs = null;
List<Hobby> hbs = new ArrayList<Hobby>();
Statement stat = null;
try {
String sql = "select hobby_id, name from hobby where type_id = " + typeId;
stat = SqlExecuteUtil.getStatement(conn);
rs = stat.executeQuery(sql);
while (rs.next()) {
Hobby ht = new Hobby();
ht.setHobbyId(rs.getInt(1));
ht.setName(rs.getString(2));
hbs.add(ht);
}
} catch (SQLException e1) {
LoggerUtil.loggerDebug(
logger,
"[HobbyAndHobbyTypeQuery， loadHobbyByType] 获取兴趣类型失败 ，兴趣类型id是"
+ typeId
+ " "
+ e1.getMessage());
e1.printStackTrace();
}
SqlExecuteUtil.closeResultSet(rs);
SqlExecuteUtil.closeStatement(stat);
SqlExecuteUtil.closeConnection(conn);
return hbs;
}
/**
* 执行更新
*
* @param sql
*/
public void updatePerson(final String sql) {
SqlExecuteUtil.executeSql(
sql,
new DBTemplate() {
@Override
public void execute(Statement stmt) throws SQLException {
stmt.executeUpdate(sql);
}
});
}
/** 删除心情提醒 */
public void deleteMoodTips(int pid) {
String procedure = "{call " + SP_MOOD_TIPS_DELETE_BY_PID_AND_TYPE + "(?, ?)}";
SqlExecuteUtil.executeProcedure(
procedure,
new Object[] {pid, 1},
new ProcedureExecutor() {
@Override
public void execute(CallableStatement stmt) throws SQLException {
stmt.execute();
}
});
}
/**
* 修改密码
*
* @param tableName
* @param username
* @param password
*/
public void fogetPwd(final String tableName, final String username, final String password) {
String procedure = "{call " + SP_PERSON_INFO_FORGET_PWD + "(?, ?, ?)}";
SqlExecuteUtil.executeProcedure(
procedure,
new Object[] {tableName, username, password},
new ProcedureExecutor() {
@Override
public void execute(CallableStatement stmt) throws SQLException {
stmt.execute();
}
});
}
/**
* 修改密码
*
* @param pid
* @param pwd 密码
*/
public void modifyPassword(final int pid, final String pwd) {
String procedure = "{call " + SP_PERSON_INFO_MODIFY_PWD + "(?, ?)}";
SqlExecuteUtil.executeProcedure(
procedure,
new Object[] {pid, pwd},
new ProcedureExecutor() {
@Override
public void execute(CallableStatement stmt) throws SQLException {
stmt.execute();
}
});
}
/**
* @param tableName
* @param username
* @param password
* @return 返回id为-1表示注册失败
*/
public int regiser(String tableName, String username, String password) {
String procedure = "{call " + SP_REGISTER + "(?, ?, ?, ?)}";
Object obj =
SqlExecuteUtil.executeReturnProcedure(
procedure,
new Object[] {tableName, username, password},
new ProcedureExecutorReturn() {
@Override
public Object executeReturn(CallableStatement stmt) throws SQLException {
stmt.registerOutParameter(4, Types.INTEGER);
stmt.execute();
return stmt.getInt(4);
}
});
return Integer.parseInt(obj.toString());
}