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);
		}	
	}