java操作数据库常用例子

select

public List<WorklsBean> getHXdatas(String recordId){List<WorklsBean> wlist = new ArrayList<WorklsBean>();Connection conn = null;Statement stmt = null;ResultSet rs = null;String sql = "";try {sql = "select * from whir$sgzzb where whir$sgzzb_foreignkey='"+recordId+"' order by whir$sgzzb_xh";conn = db.getConn();stmt = conn.createStatement();rs = stmt.executeQuery(sql);while(rs.next()){WorklsBean w = new WorklsBean();w.setGh(rs.getString("whir$sgzzb_gh"));w.setXm(rs.getString("whir$sgzzb_xm"));w.setBm(rs.getString("whir$sgzzb_bm"));wlist.add(w);}System.out.println("sql:"+sql);} catch (SQLException e) {System.out.println("error sql:"+sql);e.printStackTrace();} finally {db.close(conn);}return wlist;}

insert

public boolean saveAddress(ReceiveAddress reAddress) throws Exception {DBConnection db = new DBConnection();Connection con = null;PreparedStatement stmt = null;ResultSet rs = null;try {String sql = "insert into d_d_receive_address(user_id,receive_name,full_address,postal_code,mobile,phone,is_default)values(?,?,?,?,?,?,?)";con = db.getConn();stmt = con.prepareStatement(sql);stmt.setInt(1, reAddress.getUser_id());stmt.setString(2, reAddress.getReceive_name());stmt.setString(3, reAddress.getFull_address());stmt.setString(4, reAddress.getPostal_code());stmt.setString(5, reAddress.getMobile());stmt.setString(6, reAddress.getPhone());stmt.setInt(7, 1);int result = stmt.executeUpdate();if(result==0){return false;}} finally {db.close(rs, stmt, con);}return true;}

批量inser

public void insertItem(List<WorklsBean> list,int foreignkey) {DBConnection db = new DBConnection();Connection conn = db.getConn();try {conn.setAutoCommit(false);PreparedStatement pst = conn.prepareStatement("insert into whir$sgzzb(" +"whir$sgzzb_id,whir$sgzzb_gh,whir$sgzzb_foreignkey)" +"values(hibernate_sequence.nextval,?,?,"+foreignkey+")");int index =0;for (int i = 0; i < list.size(); i++) {index = i+1;WorklsBean w = list.get(i);pst.setString(1, w.getGh();pst.addBatch();if (i % 1000 == 0 || i == (list.size() - 1)) {// 可以设置不同的大小；如50，100，500，1000等等pst.executeBatch();conn.commit();pst.clearBatch();}}pst.close();} catch (SQLException e) {e.printStackTrace();}finally{db.close(conn);}}

delete

public void deletMasterData() {Connection conn = null;Statement stmt = null;String sql = "";//删除数据try {conn = db.getConn();stmt = conn.createStatement();sql = "delete from whir$sgz where whir$sgz_drbj='1'";stmt.execute(sql);System.out.println("delete sql："+sql);} catch (Exception e) {e.printStackTrace();} finally {db.close(stmt, conn);}}

update

public void updateJYBJforOne(String id,String userName,String time){Connection conn = db.getConn();Statement stmt = null;String updateSQL= "";try {stmt = conn.createStatement();updateSQL = "update whir$khgl set whir$khgl_sfjy='1',whir$khgl_jyr='"+userName+"',whir$khgl_jysj='"+time+"' where whir$khgl_id="+id;stmt.execute(updateSQL);System.out.println("updateSQLsql:"+updateSQL);} catch (SQLException e) {System.out.println("error sql:"+updateSQL);e.printStackTrace();} finally {db.close(stmt, conn);}}

批量update

public void updateJforAll(String[] ids,String userName,String time){DBConnection db = new DBConnection();Connection conn = db.getConn();try {conn.setAutoCommit(false);PreparedStatement pst = conn.prepareStatement("update whir$khgl set whir$khgl_sfjy='1',whir$khgl_jyr='"+userName+"'," +"whir$khgl_jysj='"+time+"' where whir$khgl_id=?");for(int i=0;i<ids.length;i++){pst.setString(1,ids[i]);pst.addBatch();if (i % 1000 == 0 || i == (ids.length - 1)) {// 可以设置不同的大小；如50，100，500，1000等等pst.executeBatch();conn.commit();pst.clearBatch();}}pst.close();} catch (SQLException e) {e.printStackTrace();}finally{db.close(conn);}}