sql增删改查例子
数据库连接与关闭类Connection conn = null;PreparedStatement ps = null;ResultSet rs = null;String sql = ""import java.sql.Connection;import java.sql.DriverManager;import java.sql.*;import java.sql.SQLException;publicclassDBConnection publicstatic Connection getConnection()throws SQLException,IllegalAccessExceptionConnection conn= null;tryClass.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");String url ="jdbc:microsoft:sqlserver:/localhost:1433;DatabaseName=cwgsj"String user = "sa"String password = "123456"conn = DriverManager.getConnection(url, user, password);/System.out.println("数据库连接成功！");catch(ClassNotFoundException e)System.out.println("数据库连接失败："+e.getMessage();catch (SQLException ex)System.out.println("SQLException: " + ex.getMessage();System.out.println("SQLState: " + ex.getSQLState();return conn;publicstaticvoid closeResource(ResultSet rs,Statement stmt,Connection conn)if(rs!=null)try rs.close();rs = null; catch (SQLException e) e.printStackTrace();if(stmt!=null)try stmt.close();stmt = null; catch (SQLException e) e.printStackTrace();if(conn!=null)tryconn.close();conn = null;catch(SQLException ex) System.err.println(ex.getMessage();ex.printStackTrace();System.out.println("-数据库关闭失败！");查询某单个字段/根据采购单编号查找采购单是否属于大类型public String findBigFlag(String buyid)String bigflag = ""try conn = DBConnection.getConnection();String sql = "select isbig from apply where buyid=?"ps = conn.prepareStatement(sql);ps.setString(1, buyid);rs = ps.executeQuery();if(rs.next()bigflag = rs.getString(1); catch (Exception e) System.out.println("查找该采购申请是否是大宗采购，ConPurchaseDAO");e.printStackTrace();finallyDBConnection.closeResource(rs, ps, conn);returnbigflag;查询所有（对象集合）/ 根据商品大类编号查找小类编号public List getGoodstypeBysmall(String small) List list = new ArrayList();try conn = DBConnection.getConnection();String sql = "select goodsid,goodsname from goodstype where smallgoodsid=?"ps = conn.prepareStatement(sql);ps.setString(1, small);rs = ps.executeQuery();while (rs.next() Goodstype gtype = new Goodstype();gtype.setGoodsid(rs.getString("goodsid");gtype.setGoodsname(rs.getString("goodsname");list.add(gtype); catch (Exception e) e.printStackTrace(); finally DBConnection.closeResource(rs, ps, conn);return list;/ 查找流程表flow里级别为id的岗位编号及流程编号public Flow findFlowGweiByLevel(int id) Flow flow = new Flow();/ List list = new ArrayList();/System.out.println("id" + id);try conn = DBConnection.getConnection();String sql = "select flowid,gweiid from flow where flowtypeid='f002' and levelflag=?"/System.out.println(sql);ps = conn.prepareStatement(sql);ps.setInt(1, id);rs = ps.executeQuery();if(rs.next()flow.setFlowid(rs.getString(1);flow.setGweiid(rs.getString(2); catch (Exception e) System.out.println("根据流程标号查找出错");e.printStackTrace();finallyDBConnection.closeResource(rs, ps, conn);return flow;增加/ 增加采购申请单publicint addApply(Apply apply) int flag = -1;try conn = DBConnection.getConnection();String sql = "insert into apply(buyid,writeman,applytime,isbig) values(?,?,?,?)"ps = conn.prepareStatement(sql);ps.setString(1, apply.getBuyid();ps.setString(2, apply.getWriteman();ps.setString(3, apply.getApplytime();ps.setString(4, apply.getIsbig();flag = ps.executeUpdate(); catch (Exception e) System.out.println("添加申请单的buyapply.jsp出错");e.printStackTrace(); finally DBConnection.closeResource(null, ps, conn);return flag;更新publicint updateAppreceive(Appreceive app,int id) int flag = -1;try conn = DBConnection.getConnection();String sql = "update appreceive set userid=?,ideas=? ,examtime=? ,isexam='1' where id=?"ps = conn.prepareStatement(sql);ps.setString(1, app.getUserid();ps.setString(2, app.getIdeas();ps.setString(3, app.getExamtime();ps.setInt(4,id);/ps.setString(5, flowid);/ps.setString(6, gweiid);/ flag = ps.executeUpdate(); catch (Exception e) System.out.println("添加采购单的.jsp出错");e.printStackTrace(); finally DBConnection.closeResource(null, ps, conn);return flag;删除/删除被勾选的采购单申请记录publicboolean delPurchase(String id)boolean flag = true;try conn = DBConnection.getConnection();String sql = "delete from purchase where id in(0"for(int i=0;i <id.length;i+) sql+=","+idi; sql+=")" ps=conn.prepareStatement(sql); flag = ps.execute(); catch (Exception e) System.out.println(
