:cry: 关于此问题,我查了很多资料。大多都是概念性的总结,没有具体的代码实现。
本人的目的是通过学习高效的jdbc代码 封装一个高效的工具包。请各位多多指教。
下面贴出本人写的jdbc代码,请指出不足之处。
[code="java"]
package com.win37.user.idlimp;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import com.win37.common.JsonUtil;
import com.win37.common.Dao.PageDaoTwo;
import com.win37.common.idlimp.PageTwoImp;
import com.win37.db.DBManager;
import com.win37.user.dao.Friend;
import com.win37.user.dao.FriendComment;
import com.win37.user.dao.HomeAricle;
import com.win37.user.dao.HomeUser;
import com.win37.user.dao.LetterMessage;
import com.win37.user.dao.News;
import com.win37.user.dao.Notice;
import com.win37.user.idl.IHomePage;
public class HomePageImp implements IHomePage{
Connection conn=null;
PreparedStatement ps=null;
ResultSet rs=null;
/**
*
* @return 数据库连接对象 没用使用连接池
*/
public Connection getConn()
{
DBManager dbmanager=new DBManager();
try{
conn=dbmanager.GetConnction();
}catch(SQLException e){
System.out.println("获得连接失败");
e.printStackTrace();
}
if(conn==null)
System.out.println(".................");
return conn;
}
public void close(){
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(ps!=null){
try {
ps.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 根据用户编号查询 用户爱好的运动
*
* @param userid
* @return
*/
public String[] queryCatalogidByUserid(String userid){
String sql="select *from run37_RSS where userid='"+userid+"'";
//System.out.println(sql);
String catalogid="",RSSmessage="";
String [] a ={"",""};
conn=this.getConn();
try {
ps=conn.prepareStatement(sql);
rs=ps.executeQuery();
while(rs.next()){
catalogid=rs.getString("catalogid");
RSSmessage=rs.getString("RSSmessage");
}
a[0]=catalogid;
a[1]=RSSmessage;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
finally{
close();
}
return a;
}
/**
* 根据类别名查询 新闻文章
* @param catalogname 类别名
* @return 返回包含HomeAricle的List
*/
public List aircleListQuery( String catalogid ){//类别名
String sql1="select top 10 * from new_aricle where catalogid='"+catalogid+"' order by createtime DESC";
// String sql1="select top 10 *from new_aricle where catalogid=(select catalogid from new_catalog where catalogname='"+catalogname+"')";
// String order=" order by createtime DESC";//限制条件 便于以后修改
// sql1+=order;
// System.out.println(sql1);
List l=new ArrayList();
conn=this.getConn();
try {
ps=conn.prepareStatement(sql1);
rs=ps.executeQuery();
while(rs.next()){
HomeAricle ha=new HomeAricle();
ha.setAricleid(rs.getInt(1));
ha.setAricletitle(rs.getString("aricletitle"));
ha.setAriclecontent(rs.getString("ariclecontent"));
l.add(ha);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
finally{
close();
}
return l;
}
/**
- 开通新闻平台
- @param userid
-
@return
*/
public boolean userPlatformIsvalidateUpdate(String userid){String sql="update run37_user set userPlatformIsvalidate=1 where userid=?";
int i=0; boolean b=false;
conn=this.getConn();
try {
ps=conn.prepareStatement(sql);
ps.setString(1, userid);i=ps.executeUpdate(); if(i>0){ b=true; }
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{close();
}
return b;
}/** * 投稿注册 * @param userid * @return */
public boolean newsRegist(String userid){
int i=0; boolean b=false;
String sql="insert into run37_news_user(nsuserId,nsuserNickname,nsuserCountScore,nsuserCountHanzhu,nsuserType,nsuserlocalTime,newsCountArticle,newsCountComment,newsCountSuccess) values(?,?,?,?,?,?,?,?,?)";conn=this.getConn(); try { ps=conn.prepareStatement(sql); ps.setString(1, userid); ps.setString(2, "昌群");//昵称默认 ps.setInt(3, 0); ps.setInt(4, 0); ps.setInt(5, 1); ps.setString(6, this.getDate()); ps.setInt(7, 0); ps.setInt(8, 0); ps.setInt(9, 0); i=ps.executeUpdate(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ close(); } if(i>0){ b=true; }
return b;
}
/**- 获取当前时间
-
@return
*/
public String getDate(){DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String nowTime=df.format(new Date());return nowTime;
}
public int addFriendMessage(FriendComment friendComment) {
String sql="insert into run37_news_friend_comment(userid,commenterid,content,createtime,positionid) values(?,?,?,?,?)";
conn=this.getConn();
int i=0;
try {
ps=conn.prepareStatement(sql);
ps.setString(1, friendComment.getUserid());
ps.setString(2, friendComment.getCommenterid());
ps.setString(3, friendComment.getContent());
ps.setString(4, friendComment.getCreatetime());
ps.setString(5,friendComment.getPositionid());
i=ps.executeUpdate();} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{close();
}
return i;
}
public int delMessageById(String commenterid) {
int i=0; String sql="delete from run37_news_friend_comment where commentid=?"; conn =this.getConn(); try { ps=conn.prepareStatement(sql); ps.setString(1, commenterid); i=ps.executeUpdate(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally{ this.close(); } // TODO Auto-generated method stub return i;
}
public FriendComment getFriendMessageSingleBean(FriendComment friendComment) {
FriendComment fc=new FriendComment();// 不同的留言 时间应该不同 此处根据时间来查询
String sql="select c.*,u.username,u.userimgUrl from run37_user u,run37_news_friend_comment c where c.commenterid=u.userid and c.positionid=? and c.createtime=? order by createtime DESC";
conn=this.getConn();
try {
ps=conn.prepareStatement(sql);
ps.setString(1, friendComment.getPositionid());
ps.setString(2, friendComment.getCreatetime());
rs=ps.executeQuery();
if(rs.next()){fc.setCommentid(rs.getInt("commentid")); fc.setUserid(rs.getString("userid")); fc.setCommenterid(rs.getString("commenterid")); fc.setContent(rs.getString("content")); fc.setCreatetime(rs.getString("createtime")); fc.setPositionid(rs.getString("positionid")); fc.setUsername(rs.getString("username")); fc.setUserimgUrl(rs.getString("userimgUrl")); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally{ this.close(); } return fc;
}
public List getFriendListByUserId(int count,String userid) {
List l=new ArrayList(); // 不同的留言 时间应该不同 此处根据时间来查询 String sql="select top "+count+" f.*,u.username,u.userimgUrl,u.userlocalAddressIP from run37_user u,run37_friends f where f.newsUFriendsId=u.userid and f.newsuserid=? order by newsUserfriendsid DESC"; conn=this.getConn(); try { ps=conn.prepareStatement(sql); ps.setString(1, userid); rs=ps.executeQuery(); while(rs.next()){ Friend f=new Friend(); f.setNewsUserfriendsid(rs.getInt("newsUserfriendsid")); f.setNewsUFriendsId(rs.getString("newsUFriendsId")); f.setNewsUserid(rs.getString("newsUserid")); f.setNewUFriendState(rs.getInt("newUFriendState")); f.setUserlocalAddressIP(rs.getString("userlocalAddressIP")); f.setUsername(rs.getString("username")); f.setUserimgUrl(rs.getString("userimgUrl")); l.add(f); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally{ this.close(); } return l; }
}
[/code]