📄 newsdbaccessor.java~2~
字号:
package ws.woa.news;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;
import ws.woa.util.StrUtil;
/**
* 僯儏乕僗儌僕儏乕儖偺DB傾僋僙僒丅
*
* @author Naoki Takezoe
*/
public class NewsDBAccessor {
/**
* 巜掕偟偨僯儏乕僗ID偺忣曬傪庢摼偟傑偡丅
*
* @param newsID 僯儏乕僗ID
* @param conn 僐僱僋僔儑儞
* @return NewsBean
*/
public static NewsBean getNews(long newsID,Connection conn) throws SQLException {
StringBuffer sb = new StringBuffer();
sb.append("SELECT NewsID ,");
sb.append(" Subject ,");
sb.append(" Content ,");
sb.append(" AcceptFlag ,");
sb.append(" AddDate ,");
sb.append(" RepDate ");
sb.append("FROM News ");
sb.append("WHERE NewsID=" + newsID);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sb.toString());
NewsBean bean = new NewsBean();
if(rs.next()){
bean.setNewsID(rs.getLong("NewsID"));
bean.setSubject(rs.getString("Subject"));
bean.setContent(rs.getString("Content"));
bean.setAcceptFlag(rs.getInt("AcceptFlag"));
bean.setAddDate(rs.getTimestamp("AddDate"));
bean.setRepDate(rs.getTimestamp("RepDate"));
bean.setAcceptID(getAcceptID(newsID,conn));
}
rs.close();
stmt.close();
return bean;
}
/**
* 巜掕偟偨僯儏乕僗ID偺嫋壜ID傪攝楍偱庢摼偟傑偡丅
*
* @param newsID 僯儏乕僗ID
* @param conn 僐僱僋僔儑儞
* @return 嫋壜ID
*/
public static long[] getAcceptID(long newsID,Connection conn) throws SQLException {
String sql = "SELECT NewsID,AcceptID FROM NewsAccept WHERE NewsID=" + newsID;
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
Vector vec = new Vector();
while(rs.next()){
vec.add(new Long(rs.getLong("AcceptID")));
}
rs.close();
stmt.close();
// Vector偐傜long宆攝楍偵媗傔懼偊
long[] acceptID = new long[vec.size()];
for(int i=0;i<vec.size();i++){
acceptID[i] = ((Long)vec.get(i)).longValue();
}
return acceptID;
}
/**
* 巜掕偟偨儐乕僓偑墈棗壜擻側僯儏乕僗傪攝楍偱庢摼偟傑偡丅
*
* @param groupID 僌儖乕僾ID
* @param userID 儐乕僓ID
* @param from 庢摼奐巒峴
* @param limit 庢摼峴悢
* @param conn 僐僱僋僔儑儞
* @return NewsBean[]
*/
public static NewsBean[] getNewsList(long groupID,long userID,int from,int limit,
Connection conn) throws SQLException {
StringBuffer sb = new StringBuffer();
sb.append("SELECT NewsID ,");
sb.append(" Subject ,");
sb.append(" Content ,");
sb.append(" AcceptFlag ,");
sb.append(" AddDate ,");
sb.append(" RepDate ");
sb.append("FROM News ");
sb.append("WHERE AcceptFlag!=3 ");
sb.append("ORDER BY AddDate DESC ");
if(limit!=0){
sb.append("LIMIT " + from + "," + limit);
}
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sb.toString());
Vector vec = new Vector();
while(rs.next()){
long newsID = rs.getLong("NewsID");
int acceptFlag = rs.getInt("AcceptFlag");
long[] acceptID = new long[0];
boolean viewFlag = false;
if(acceptFlag==0){ // 僌儖乕僾扨埵偱嫋壜
acceptID = getAcceptID(newsID,conn);
for(int i=0;i<acceptID.length;i++){
if(acceptID[i]==groupID){
viewFlag = true;
break;
}
}
} else if(acceptFlag==1){ // 儐乕僓扨埵偱嫋壜
acceptID = getAcceptID(newsID,conn);
for(int i=0;i<acceptID.length;i++){
if(acceptID[i]==userID){
viewFlag = true;
break;
}
}
} else if(acceptFlag==2){ // 慡堳偵嫋壜
acceptID = getAcceptID(newsID,conn);
viewFlag = true;
}
if(viewFlag){
NewsBean bean = new NewsBean();
bean.setNewsID(newsID);
bean.setAcceptFlag(acceptFlag);
bean.setAcceptID(acceptID);
bean.setSubject(rs.getString("Subject"));
bean.setContent(rs.getString("Content"));
bean.setAddDate(rs.getTimestamp("AddDate"));
bean.setRepDate(rs.getTimestamp("RepDate"));
vec.add(bean);
}
}
rs.close();
stmt.close();
// Vector偐傜NewsBean宆攝楍偵媗傔懼偊
NewsBean[] newsList = new NewsBean[vec.size()];
for(int i=0;i<vec.size();i++){
newsList[i] = (NewsBean)vec.get(i);
}
return newsList;
}
/**
* 慡偰偺僯儏乕僗傪攝楍偱庢摼偟傑偡丅
*
* @param from 庢摼奐巒峴
* @param limit 庢摼峴悢
* @param conn 僐僱僋僔儑儞
* @return NewsBean[]
*/
public static NewsBean[] getNewsList(int from,int limit,Connection conn) throws SQLException {
StringBuffer sb = new StringBuffer();
sb.append("SELECT NewsID ,");
sb.append(" Subject ,");
sb.append(" Content ,");
sb.append(" AcceptFlag ,");
sb.append(" AddDate ,");
sb.append(" RepDate ");
sb.append("FROM News ");
sb.append("ORDER BY AddDate DESC ");
// if(limit!=0){
// sb.append("LIMIT " + from + "," + limit);
// }
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sb.toString());
Vector vec = new Vector();
while(rs.next()){
NewsBean bean = new NewsBean();
bean.setNewsID(rs.getLong("NewsID"));
bean.setSubject(rs.getString("Subject"));
bean.setContent(rs.getString("Content"));
bean.setAcceptFlag(rs.getInt("AcceptFlag"));
bean.setAddDate(rs.getTimestamp("AddDate"));
bean.setRepDate(rs.getTimestamp("RepDate"));
bean.setAcceptID(getAcceptID(rs.getLong("NewsID"),conn));
vec.add(bean);
}
rs.close();
stmt.close();
// Vector偐傜NewsBean宆攝楍偵媗傔懼偊
NewsBean[] newsList = new NewsBean[vec.size()];
for(int i=0;i<vec.size();i++){
newsList[i] = (NewsBean)vec.get(i);
}
return newsList;
}
/**
* 僯儏乕僗傪搊榐偟傑偡丅
*
* @param subject 審柤
* @param content 杮暥
* @param acceptFlag 嫋壜僼儔僌
* @param acceptID 嫋壜ID
* @param conn 僐僱僋僔儑儞
*/
public static void registNews(String subject,String content,int acceptFlag,long[] acceptID,
Connection conn) throws SQLException {
StringBuffer sb = new StringBuffer();
sb.append("INSERT INTO News (Subject,Content,AcceptFlag,AddDate,RepDate) VALUES (");
sb.append("'" + StrUtil.dbFilter(subject) + "',");
sb.append("'" + StrUtil.dbFilter(content) + "',");
//sb.append(" " + acceptFlag + ",SYSDATE(),SYSDATE())"); // Modified by wangxu for postgresql
sb.append(" " + acceptFlag + ",now(),now())");
Statement stmt = conn.createStatement();
stmt.executeUpdate(sb.toString());
stmt.close();
// ID傪庢摼
long newsID = 0;
Statement stmtID = conn.createStatement();
ResultSet rs = stmtID.executeQuery("SELECT MAX(NewsID) AS NewsID FROM News");
if(rs.next()){
newsID = rs.getLong("NewsID");
}
rs.close();
stmtID.close();
// 嫋壜ID傪峏怴
for(int i=0;i<acceptID.length;i++){
String sql = "INSERT INTO NewsAccept (NewsID,AcceptID) VALUES (" + newsID + "," + acceptID[i] + ")";
Statement stmt2 = conn.createStatement();
stmt2.executeUpdate(sql);
stmt2.close();
}
}
/**
* 僯儏乕僗傪峏怴偟傑偡丅
*
* @param newsID 僯儏乕僗ID
* @param subject 審柤
* @param content 杮暥
* @param acceptFlag 嫋壜僼儔僌
* @param acceptID 嫋壜ID
* @param conn 僐僱僋僔儑儞
*/
public static void updateNews(long newsID,String subject,String content,int acceptFlag,long[] acceptID,
Connection conn) throws SQLException {
StringBuffer sb = new StringBuffer();
sb.append("UPDATE News SET ");
sb.append("Subject ='" + StrUtil.dbFilter(subject) + "',");
sb.append("Content ='" + StrUtil.dbFilter(content) + "',");
sb.append("AcceptFlag = " + acceptFlag + " ,");
sb.append("RepDate = SYSDATE() ");
sb.append("WHERE NewsID=" + newsID);
Statement stmt = conn.createStatement();
stmt.executeUpdate(sb.toString());
stmt.close();
// 嫋壜ID傪峏怴
Statement stmtDel = conn.createStatement();
stmtDel.executeUpdate("DELETE FROM NewsAccept WHERE NewsID=" + newsID);
stmtDel.close();
for(int i=0;i<acceptID.length;i++){
String sql = "INSERT INTO NewsAccept (NewsID,AcceptID) VALUES (" + newsID + "," + acceptID[i] + ")";
Statement stmt2 = conn.createStatement();
stmt2.executeUpdate(sql);
stmt2.close();
}
}
/**
* 僯儏乕僗傪嶍彍偟傑偡丅
*
* @param newsID 僯儏乕僗ID
* @param conn 僐僱僋僔儑儞
*/
public static void deleteNews(long newsID,Connection conn) throws SQLException {
Statement stmt1 = conn.createStatement();
stmt1.executeQuery("DELETE FROM NewsAccept WHERE NewsID="+newsID);
stmt1.close();
Statement stmt2 = conn.createStatement();
stmt2.executeQuery("DELETE FROM News WHERE NewsID="+newsID);
stmt2.close();
}
/**
* 僯儏乕僗偺審悢傪庢摼偟傑偡丅
*
* @param conn 僐僱僋僔儑儞
* @return 審悢
*/
public static int getNewsCount(Connection conn) throws Exception {
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT COUNT(*) AS Count FROM News");
int count = 0;
if(rs.next()){
count = rs.getInt("Count");
}
rs.close();
stmt.close();
return count;
}
/**
* 巜掕偟偨儐乕僓偑墈棗壜擻側僯儏乕僗偺審悢傪庢摼偟傑偡丅
*
* @param groupID 僌儖乕僾ID
* @param userID 儐乕僓ID
* @param conn 僐僱僋僔儑儞
* @return 審悢
*/
public static int getNewsCount(long groupID,long userID,Connection conn) throws Exception {
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT NewsID,AcceptFlag FROM News WHERE AcceptFlag!=3");
int count = 0;
while(rs.next()){
long newsID = rs.getLong("NewsID");
int acceptFlag = rs.getInt("AcceptFlag");
// 僌儖乕僾扨埵偱嫋壜
if(acceptFlag==0){
long[] acceptID = getAcceptID(newsID,conn);
for(int i=0;i<acceptID.length;i++){
if(acceptID[i]==groupID){
count++;
break;
}
}
// 儐乕僓扨埵偱嫋壜
} else if(acceptFlag==1){
long[] acceptID = getAcceptID(newsID,conn);
for(int i=0;i<acceptID.length;i++){
if(acceptID[i]==userID){
count++;
break;
}
}
// 慡偰偺儐乕僓偵嫋壜
} else if(acceptFlag==2){
count++;
}
}
rs.close();
stmt.close();
return count;
}
/**
* 儐乕僓偑巜掕偟偨僯儏乕僗偺墈棗尃尷傪帩偭偰偄傞偐偳偆偐傪庢摼偟傑偡丅
*
* @param conn 僐僱僋僔儑儞
* @param newsID 僯儏乕僗ID
* @param userID 儐乕僓ID
* @param groupID 僌儖乕僾ID
* @return 墈棗壜擻側応崌true丄晄壜偺応崌false丅
*/
public static boolean getCanShowNews(Connection conn,long newsID,
long userID,long groupID) throws SQLException {
String sql = "SELECT AcceptFlag FROM News WHERE NewsID="+newsID;
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
int acceptFlag = -1;
if(rs.next()){
acceptFlag = rs.getInt("AcceptFlag");
}
rs.close();
stmt.close();
// 慡堳偵岞奐
if(acceptFlag==2){
return true;
// 旕岞奐
} else if(acceptFlag==3){
return false;
// 僌儖乕僾暿
} else if(acceptFlag==0){
StringBuffer sb = new StringBuffer();
sb.append("SELECT AcceptID FROM NewsAccept WHERE NewsID="+newsID);
sb.append(" AND AcceptID="+groupID);
Statement stmt2 = conn.createStatement();
ResultSet rs2 = stmt2.executeQuery(sb.toString());
boolean retValue = false;
if(rs2.next()){
retValue = true;
}
rs2.close();
stmt2.close();
return retValue;
// 儐乕僓暿
} else if(acceptFlag==1){
StringBuffer sb = new StringBuffer();
sb.append("SELECT AcceptID FROM NewsAccept WHERE NewsID="+newsID);
sb.append(" AND AcceptID="+userID);
Statement stmt2 = conn.createStatement();
ResultSet rs2 = stmt2.executeQuery(sb.toString());
boolean retValue = false;
if(rs2.next()){
retValue = true;
}
rs2.close();
stmt2.close();
return retValue;
}
return false;
}
}
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -