SQL增删改查例子.doc

《SQL增删改查例子.doc》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。

1、数据库连接与关闭类Connectionconn=null;PreparedStatementps=null;ResultSetrs=null;Stringsql="";importjava.sql.Connection;importjava.sql.DriverManager;importjava.sql.*;importjava.sql.SQLException;publicclassDBConnection{publicstaticConnectiongetConnection()throwsSQLException,IllegalA

2、ccessException{Connectionconn=null;try{Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");Stringurl="jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=cwgsj";Stringuser="sa";Stringpassword="123456";conn=DriverManager.getConnection(url,user,password);//Syst

3、em.out.println("数据库连接成功！");}catch(ClassNotFoundExceptione){System.out.println("数据库连接失败："+e.getMessage());}catch(SQLExceptionex){System.out.println("SQLException:"+ex.getMessage());System.out.println("SQLState:"+ex.getSQLState());}returnconn;}publicstaticvoidcloseResource(

4、ResultSetrs,Statementstmt,Connectionconn){if(rs!=null){try{rs.close();rs=null;}catch(SQLExceptione){e.printStackTrace();}}if(stmt!=null){try{stmt.close();stmt=null;}catch(SQLExceptione){e.printStackTrace();}}if(conn!=null){try{conn.close();conn=null;}catch(SQLExceptionex)

5、{System.err.println(ex.getMessage());ex.printStackTrace();System.out.println("-------------------数据库关闭失败！");}}}}查询某单个字段//根据采购单编号查找采购单是否属于大类型publicStringfindBigFlag(Stringbuyid){Stringbigflag="";try{conn=DBConnection.getConnection();Stringsql="selectisbigfromapplywherebuyi

6、d=?";ps=conn.prepareStatement(sql);ps.setString(1,buyid);rs=ps.executeQuery();if(rs.next()){bigflag=rs.getString(1);}}catch(Exceptione){System.out.println("查找该采购申请是否是大宗采购，ConPurchaseDAO");e.printStackTrace();}finally{DBConnection.closeResource(rs,ps,conn);}returnbigflag;}

7、查询所有（对象集合）//根据商品大类编号查找小类编号publicListgetGoodstypeBysmall(Stringsmall){Listlist=newArrayList();try{conn=DBConnection.getConnection();Stringsql="selectgoodsid,goodsnamefromgoodstypewheresmallgoodsid=?";ps=conn.prepareStatement(sql);ps.setString(1,small);rs=ps.executeQuery();

8、while(rs.next()){Goodstypegtype=newGoodstype();gtype.setGoodsid(rs.getString("goodsid"));gtype.s