问题描述
关于此问题，我查了很多资料。大多都是概念性的总结，没有具体的代码实现。本人的目的是通过学习高效的jdbc代码 封装一个高效的工具包。请各位多多指教。下面贴出本人写的jdbc代码，请指出不足之处。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 blocke.printStackTrace();}}if(ps!=null){try {ps.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.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 blocke.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 blocke.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 blocke.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 blocke.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 blocke.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 blocke.printStackTrace();}finally{this.close();}// TODO Auto-generated method stubreturn 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 blocke.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 blocke.printStackTrace();}finally{this.close();}return l; } } 问题补充：beneo 写道
解决方案
statement是共享的话你一个方法调用结束，close的过程中，另外一个方法在等待statement的返回两个方法都是用同样的statement，就会出现问题
解决方案二：
DAO就是DAO，JDBC就是JDBC，不要把你的DAO说成JDBC，一个JDBC就是一种规范，封装了，其他人其他项目都可以共用，你弄那么多的写死的表名字段名，明明就是DAO，不要JDBC，所以其他的就不用说了，整体不要谈什么搞笑了，根本就不好。你的DAO写的也不那么好，楼上的那几位说了，Connection的问题是一个，况且不谈，你的代码写的不是那么的漂亮，sql语句，既然用了PrepareSatement ,你干嘛还把sql用字符串加起来，用？代替难道不好吗、
解决方案三：
写一个工厂专门来生产Connection 再加上线程同步 还有你的操作数据库的方法不要贴在里面，建议写一个抽象类 以后不断地往里加，这样扩展性很强，每次跟新你的Jar类库的时候很方便你说的都不是jdbc的部分了，都是整个dao框架了....我的sql不是很多的情况，几个jdbc就能搞定，简单就是美
解决方案四：
写一个工厂专门来生产Connection 再加上线程同步 还有你的操作数据库的方法不要贴在里面，建议写一个抽象类 以后不断地往里加，这样扩展性很强，每次跟新你的Jar类库的时候很方便
解决方案五：
引用1. 不要共享connection，connection应该从数据库获取 我说错了，从链接池获得。。。笔误
解决方案六：
看看Spring的JdbcTemplate
解决方案七：
1. 不要共享connection，connection应该从数据库获取2. close的方法不对3. 不要共享引用 Connection conn=null; PreparedStatement ps=null; ResultSet rs=null;会有线程安全问题直接贴我写的jdbc吧 public long insertClusterInfo(ClusterInfo info) throws SQLException { String sql = "INSERT INTO ClusterInfo (GenDate, Count, SampleContent, ClusterInfo.From, ClusterInfo.Status) VALUES(?, ?, ?, ?, ?)"; Connection connection = getConnection(); PreparedStatement pstmt = null; ResultSet resultSet = null; try { pstmt = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); pstmt.setString(1, info.getGenDate()); pstmt.setInt(2, info.getCount()); pstmt.setString(3, toGBK(info.getSampleContent())); pstmt.setString(4, toGBK(info.getFrom())); pstmt.setInt(5, info.getStatus()); pstmt.executeUpdate(); resultSet = pstmt.getGeneratedKeys(); resultSet.next(); return resultSet.getLong(1); } finally { try { if (resultSet != null) resultSet.close(); } finally { try { if (pstmt != null) pstmt.close(); } finally { connection.close(); } } } }