《sql增删改查例子》由会员分享，可在线阅读，更多相关《sql增删改查例子（6页珍藏版）》请在金锄头文库上搜索。
1、数据库连接与关闭类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,IllegalAccessExcept
2、ionConnection 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(ClassN
3、otFoundException 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
4、 = 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(-数据库关闭失败！);查询某单个字段/根据
5、采购单编号查找采购单是否属于大类型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
6、) 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 goodsi
7、d,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.pr
8、intStackTrace(); 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
9、 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();finallyD
10、BConnection.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.setS
11、tring(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(
12、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
13、(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(
